by Jeff Stonacek, Principal Architect
In Oracle Database version 11g, a new feature called Active Data Guard (ADG) was introduced. This feature extends traditional Data Guard by allowing the standby database to continue to apply redo while remaining open for queries. This feature works well for people who want to run near real-time reports from a standby database, offloading this activity from the primary database. Active Data Guard is not free, however. Active Data Guard carries a list price of $11,500 USD, and both the primary and standby databases must be licensed if they reside on different physical servers.
The danger with Active Data Guard is that some of our customers open their standby databases and do not realize that ADG is in use. If a customer opens a standby database incorrectly, ADG can unknowingly be triggered, causing a potential licensing liability.
Standby Database Read Only
Contrary to how some understand the situation, a standby database can be opened in read only mode without the need for Active Data Guard licenses. The steps to do this are as follows. To start, we will assume that the standby database is mounted and in managed recovery mode.
SQL> alter database recover managed standby database cancel; SQL> alter database open read only;
This will open a standby database in read only mode, allowing the users to run queries against the database. This will not trigger Active Data Guard usage in DBA_FEATURE_USAGE_STATISTICS. However, the database will not perform any recovery while in read only mode. The steps to return the standby database to managed recovery mode and roll the database forward are as follows.
SQL> shutdown immediate; SQL> startup mount; SQL> alter database recover managed standby database disconnect;
Triggering Active Data Guard
The licensing concern lies in the fact that ADG can be triggered without any warning whatsoever. If the standby database is open in read only mode, and then managed recovery is started, ADG is triggered.
SQL> alter database recover managed standby database cancel; SQL> alter database open;
At this point, the standby database is open in read only mode and can be queried. There is no ADG usage as the database is not recovering, however.
If a DBA comes along and notices that the standby database is falling behind, the instinct is to enable managed recovery. If this happens, ADG is triggered and a licensing liability is incurred.
SQL> alter database recover managed standby database disconnect;
The exact same syntax is used to enable managed recovery with the database open in read only mode as with the database open in a mounted state.
To avoid usage of ADG, it is important to query the state of the database prior to starting managed recovery. The expected output is as follows:
SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED
If open_mode shows as read only, then managed recovery cannot be started without triggering ADG usage.
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY
If managed recovery is started while the database is open as read only, the open_mode changes to the following:
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY
Conclusion
In this blog we have explained the difference between Active Data Guard and traditional Data Guard when operating in read only mode. Active Data Guard carries a license liability, whereas traditional Data Guard, even in read only mode, does not. If you want to open your Data Guard database as read only, and do not want to pay for Active Data Guard, then care must be taken when opening the standby database. Be aware that a licensing liability can easily (and unknowingly) be incurred for Active Data Guard.
In part two of this blog, we will explore the use of the underscore parameter (_query_on_physical) to disable ADG. Spoiler alert… Oracle Support does not advise using the parameter without their direction.
