Oracle 11gR2 - Active Data Guard

Oracle 11gR2 - Active Data Guard

Active Data Guard allows a standby database to be opened for read-only access whilst redo is still being applied. For some applications Active Data Guard can represent a more efficient use of Oracle licenses on the standby database. However, this benefit is offset to a certain extent by the fact that Active Data Guard is available on Enterprise Edition only and is cost option which must be licensed on both the primary and standby database.

Several of my customers are currently using Active Data Guard; in general they are very happy with it. A few others have discovered that it is very easy to inadvertently enable Active Data Guard. This is not desirable or advisable as Oracle have instigated licence audits with a large number of UK customers over the past couple of years.
To determine whether a standby database is using Active Data Guard use the following query:

SELECT database_role, open_mode FROM v$database;

For example:

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

If you start a database in SQL*Plus using the STARTUP command and then invoke managed recovery, the Active Data Guard will be enabled. For example:

[oracle@server14]$ sqlplus / as sysdba
SQL> STARTUP
ORACLE instance started.
Total System Global Area 6497189888 bytes
Fixed Size 2238672 bytes
Variable Size 3372222256 bytes
Database Buffers 3103784960 bytes
Redo Buffers 18944000 bytes
Database mounted
Database opened

SQL> SELECT database_role, open_mode FROM v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE WITH SESSION SHUTDOWN;

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

However, if the database is started in SQL*Plus using the STARTUP MOUNT command and then managed recovery is invoked, Active Data Guard will not be enabled.

[oracle@server14]$ sqlplus / as sysdba
SQL> STARTUP MOUNT

ORACLE instance started.
Total System Global Area 6497189888 bytes
Fixed Size 2238672 bytes
Variable Size 3372222256 bytes
Database Buffers 3103784960 bytes
Redo Buffers 18944000 bytes
Database mounted
Database opened

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE WITH SESSION SHUTDOWN;

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

In the database has been started in SQL*Plus using STARTUP MOUNT and the database is subsequently opened read only, then invoking managed recovery will enable Active Data Guard. For example:

[oracle@server14]$ sqlplus / as sysdba
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 6497189888 bytes
Fixed Size 2238672 bytes
Variable Size 3372222256 bytes
Database Buffers 3103784960 bytes
Redo Buffers 18944000 bytes
Database mounted
Database opened

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE WITH SESSION SHUTDOWN;

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

Of course not all databases are started using SQL*Plus.
If you start the database using SRVCTL then the default open mode can be specified in the OCR.
You can check the default open mode for a database using SRVCTL CONFIG DATABASE.
For example if the database is called PROD:

[oracle@server14]$ srvctl config database -d PROD
Database unique name: PROD
Database name: PROD
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA1/PROD/spfilePROD.ora
Domain:Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: PROD
Disk Groups: DATA1, FRA1
Mount point paths:
Services:
Type: SINGLE
Database is administrator managed

In the above example, if the PROD database is started using SRVCTL then the database will be opened in read-only mode.
For example:

[oracle@server14]$ srvctl start database -d PROD
[oracle@server14]$ sqlplus / as sysdba

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE WITH SESSION SHUTDOWN;

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

The default start mode can be modified in the OCR using the SRVCTL MODIFY DATABASE command.

For example:

[oracle@server14]$ srvctl modify database -d PROD -s mount

The database configuration is updated as follows:

[oracle@server14]$ srvctl config database -d PROD
Database unique name: PROD
Database name: PROD
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA1/PROD/spfilePROD.ora
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: PROD
Disk Groups: DATA1, FRA1
Mount point paths:
Services:
Type: SINGLE

Database is administrator managed

When the default start mode is set to mount, Active Data Guard will not be enabled when managed recovery is invoked. For example:

[oracle@server14]$ srvctl start database -d PROD
[oracle@server14]$ sqlplus / as sysdba

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE WITH SESSION SHUTDOWN;

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

You can also specify the start mode as a parameter to the SRVCTL START DATABASE command
For example:

[oracle@server14] srvctl start database -d PROD -o open
[oracle@server14] srvctl start database -d PROD -o mount

Take care when performing a switchover or switchback that the OCR is updated as part of the procedure.

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.