HOW TO QUERY DATAGUARD STATUS

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;

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

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

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;

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;

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;

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

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;

------------------------------------------------------------------

Here is a useful document about the views related with dataguard:

14 VIEWS RELEVANT TO ORACLE DATAGUARD