v$dataguard_status script Oracle Database Tips by Donald Burleson

Data Guard

v$dataguard_status

In this case, only the stdby.1_232.dbf file was available on the standby archive destination, and the stdby.1_231.dbf was required to complete the media recovery, so the MRP process re-fetched it. The MRP log file in the BACKGROUP_DUMP_DEST directory can be checked to verify this. 

Here are basic v$dataguard statistics in the v$dataguard_stats view:

set lines 120
column value format a20
select * from v$dataguard_stats;

Also, the alert log file on the standby site can be checked to see if the database is in managed recovery mode.  It will show that the archived log name has been applied to the Oracle instance by MRP process.

-- Extract from alert log file from standby site
alter database recover managed Oracle instance disconnect from session
Wed Sep 17 16:39:47 2003
Attempt to start background Managed Standby Recovery process
MRP0 started with pid=13
MRP0: Background Managed Standby Recovery process started

Moreover, v$dataguard_status and v$managed_standby views give information about the processes involved in media recovery on the standby site. The following shows the output of the dg_stats.sql script from the code depot. For a complete description of the Data Guard v$ views, get the "free 11g poster".

The script has been executed on the standby database.

            dg_stats.sql script to display v$dataguard_status

Set linesize 140
column Timestamp Format a20
column Facility  Format a24
column Severity  Format a13
column Message   Format a60 trunc
 
Select
   to_char(timestamp,'YYYY-MON-DD HH24:MI:SS') Timestamp,
   Facility,
   Severity,
   Message
   see code depot for full script
From
   v$dataguard_status
Order by
   Timestamp;

Here is a sample of the output from this script:

TIMESTAMP            FACILITY                 SEVERITY      MESSAGE
-------------------- ------------------------ ------------- ---------------------
2003-SEP-26 17:00:08 Log Transport Services   Informational ARC0: Archival started
2003-SEP-26 17:00:08 Log Transport Services   Informational ARC1: Archival started
2003-SEP-26 17:00:39 Log Apply Services       Control       Attempt to start background Managed
Standby Recovery process
2003-SEP-26 17:00:45 Log Apply Services       Warning       Media Recovery Waiting for thread 1 seq# 237
2003-SEP-26 17:03:15 Log Apply Services       Informational Media Recovery Log /oracle/appsdb/arch/appsdb_1_237.dbf
2003-SEP-26 17:03:15 Log Apply Services       Warning       Media Recovery Waiting for thread 1 seq# 238
2003-SEP-26 17:03:45 Log Apply Services       Informational Media Recovery Log /oracle/appsdb/arch/appsdb_1_238.dbf
2003-SEP-26 17:03:45 Log Apply Services       Warning       Media Recovery Waiting for thread 1 seq# 239
2003-SEP-26 17:04:00 Log Apply Services       Informational Media Recovery Log /oracle/appsdb/arch/appsdb_1_239.dbf
2003-SEP-26 17:04:00 Log Apply Services       Warning       Media Recovery Waiting for thread 1 seq# 240

Here is another script with v$dataguard_status:

select *
  from (select TIMESTAMP,
               completion_time "ArchTime",
               SEQUENCE#,
               round((blocks * block_size) / (1024 * 1024), 1) "Size Meg",
               round((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                      OVER(order by TIMESTAMP)) * 24 * 60 * 60,
                     1) "Diff(sec)",
               round((blocks * block_size) / 1024 /
                     decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                            0,
                            1,
                            (TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                     1) "KB/sec",
               round((blocks * block_size) / (1024 * 1024) /
                     decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                            0,
                            1,
                            (TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                     3) "MB/sec",
               round(((lead(TIMESTAMP, 1, TIMESTAMP) over(order by TIMESTAMP)) -
                     completion_time) * 24 * 60 * 60,
                     1) "Lag(sec)"
          from v$archived_log a, v$dataguard_status dgs
         where a.name = replace(dgs.MESSAGE, 'Media Recovery Log ', '')
           and dgs.FACILITY = 'Log Apply Services'
         order by TIMESTAMP desc)
 where rownum < 10;