Query / Queries to Check the status of DATA GUARD

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;