Edit (07,2013) A newer version of this topic is here:

-----------------------------------------------------------------------------
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:



3 Responses so far.

  1. Thanks Emre. It is good to recall all the data guard views, particularly the link you provided for views is very useful.

    -Vimal

  2. Anonymous says:

    It is good to copy link info for backup purposes. It is no longer valid. Anyway thank you for the good info.

    Pooh

  3. Anonymous says:

    Hi Emre,

    On my environment the query "Which Log Files Were Not Received by the Standby Site" shows multiple records. But actually the select * from v$archive_gap; does not returned record. Also there are no lags reported by select name, value from v$dataguard_stats;

    Do you know why is this?

    BR,
    Pooh

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -