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:



8 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

  4. Vivek says:

    Awesome post……. your article is really informative and helpful for me and other bloggers too

    Oracle Fusion SCM Online Training

  5. naveensai says:

    Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.


    Oracle Fusion Financials Online Training

  6. Ashok says:

    I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Oracle Fusion HCM Online Training

  7. sushma says:

    Hey Really Thanks for sharing the best information regarding category, hope you will write more great blogs.
    Oracle Fusion Financials Online Training

  8. raju says:

    Thank you for sharing such a nice and interesting blog.
    Oracle Fusion Financials Online Training

Powered by Blogger.

Page Views

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