Thursday, May 10, 2012

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.


Note : Below command helps to get the archive gap or archive lag from standby database.


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PR01PIMI  READ ONLY WITH APPLY

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#)
"Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )  2    3    4    5 
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#))
ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#))
APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1
/  6    7    8    9   10   11   12   13   14 

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  72253                 72253          0
         2                  63653                 63653          0
         3                  57721                 57720          1
         4                  63051                 63050          1
         5                  61088                 61086          2
         6                  98916                 98915          1
         7                  68499                 68499          0
         8                  77052                 77052          0

8 rows selected.

No comments:

Post a Comment