Data Guard Status Check Queries

1. Issue the following query to show information about the protection mode, the protection level, the role of the database, and switchover status:

                SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

2. On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log.

                SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Or

                SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

3. On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

                SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

4. Query the physical standby database to monitor Redo Apply and redo transport services activity at the standby site.

                SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

5. To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view.

                SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

6. The V$DATAGUARD_STATUS fixed view displays events that would typically be triggered by any message to the alert log or server process trace files.

                SELECT MESSAGE FROM V$DATAGUARD_STATUS;

7. Determining Which Log Files Were Not Received by the Standby Site.

                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#);

8.If a delayed apply has been specified or an archive log is missing then switchover may take longer than expected.

Check v$managed_standby

                select process, status, sequence# from v$managed_standby;

OR alternatively:

                select name, applied from v$archived_log;