Home  >  Oracle Database Administration (DBA)  >  Replication  >  Monitor a Standby Database (10g)

1. Determine the status of redo log files

SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;

2. Determine the most recent archived redo log file

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

3. Determine the most recent archived redo log file at each destination

SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
             FROM V$ARCHIVE_DEST_STATUS
           WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

4. Find out if archived redo log files have been received

You can issue a query at the primary database to find out if an archived redo log file was not received at a particular site. Each destination has an ID number associated with it. You can query the DEST_ID column of the V$ARCHIVE_DEST fixed view on the primary database to identify each destination's ID number.

Assume the current local destination is 1, and one of the remote standby destination IDs is 2. To identify which log files are missing at the standby destination, issue the following query:

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE#

            FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL 
                         WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# 

                                                                                        FROM V$ARCHIVED_LOG 
                                                                                      WHERE DEST_ID=2 
                                                                                          AND THREAD# = LOCAL.THREAD#);

For details about monitoring the archiving status of the primary database click here.

5. Monitoring the Performance of Redo Transport Services

The waits related to a standby database can be found in V$SYSTEM_EVENT

Wait Events for Destinations Configured with the LGWR ASYNC Attributes