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 APPLYThe 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 mountThe 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 MOUNTEDYou 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 mountTake 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