Verify Data Guard Implementation

Few Verification Checks you should perform on Physical Standby Database running in Maximum Performance Mode.

For physical standby database to be relevant to your high availability and disaster/recovery requirements, you need to make sure that it's keeping up with the primary database and is in sync with the primary database. Once implemented, you need to make sure that it is up and running at all time. Below is a list of few important tasks that you should perform to verify physical standby database's operation.

Test 1 - Archive Logs on Primary and Standby

When you perform a log switch on the primary database, you will see the corresponding archive log on the standby database server. Archive log location on primary and standby is defined by log_archive_dest_1 and its format by log_archive_format parameter.

In addition, V$ARCHIVE_LOG view on primary displays archive logs generated by both primary and standby databases. Run the following query to verify:

SQL> SELECT STANDBY_DEST,DEST_ID, ARCHIVED, APPLIED, MAX(SEQUENCE#)
2 FROM V$ARCHIVED_LOG
3 GROUP BY STANDBY_DEST, DEST_ID, ARCHIVED, APPLIED;

STA DEST_ID    ARC APP MAX(SEQUENCE#)
--- ---------- --- --- --------------
NO   1         YES  NO 164607
YES  2         YES YES 164607

Test 2 - Archive Log Gap

Query V$ARCHIVE_GAP to determine if you have gaps in archive logs on standby database. Since you should have configured fal_server and fal_client parameters, Oracle Data Guard should automatically resolve the archive log gaps by requesting to the primary database to send the missing archive logs.

But if you don't have the archive logs that the physical standby database needs, then you would have archive log gaps and your standby database would be out of sync. Remedies to fix this issue may vary, but your standby database is out of sync.

Test 3 - Real-time Redo Propagation

You want to make sure that your physical standby database is keeping up with the primary database.

Method 1

On standby, Run the following query to evaluate "apply lag" and "transport lag" parameters. The values for these parameters will vary, but you will get an idea whether your standby database is keeping up with the primary database or not.

SQL> SELECT *
2 FROM V$DATAGUARD_STATS ;

NAME
--------------------------------
VALUE
----------------------------------------------------------------
UNIT TIME_COMPUTED
------------------------------ ------------------------------
apply finish time
+00 00:00:00.0
day(2) to second(1) interval 16-OCT-2010 23:35:11

apply lag
+00 00:00:05
day(2) to second(0) interval 16-OCT-2010 23:35:11

estimated startup time
14
second 16-OCT-2010 23:35:11

standby has been open
N
16-OCT-2010 23:35:11

transport lag
+00 00:00:00
day(2) to second(0) interval 16-OCT-2010 23:35:11

Method 2

On standby, Run the following query to compute redo lag. You may see negative value for RedoAsOf, but at least you would get an idea about synchronization lag.

SQL> SELECT SYSDATE CurrentTime, MAX(LAST_TIME) RedoAsOf,
2 SYSDATE - MAX(LAST_TIME) RedoLag
3 FROM V$STANDBY_LOG ;

CURRENTTIME          REDOASOF           REDOLAG
------------------- ------------------- ----------
10/16/2010:23:41:02 10/16/2010:23:41:03 -.00001157

Method 3

On primary database, run the following query to compute the current SCN

SQL> SELECT CURRENT_SCN FROM V$DATABASE ;

CURRENT_SCN
--------------------
9,084,966,569,214

On secondary database, run the following query to compute the current SCN, and compare it against the value from the previous query.

SQL> SELECT MAX(LAST_CHANGE#) CURRENT_SCN FROM V$STANDBY_LOG ;

CURRENT_SCN
--------------------
9,084,966,569,212