What is the current status of Data Guard?

While working in one of Data Guard projects, I was asked about the current sync status of standby. I started talking about gap, SCN, or applied and received archive log numbers, and so on, which obviously did not make sense to business users. The only thing they’d like to know is whether or not data at standby is up-to-date with that of primary, or if not, what date/time of standby data it is at right now.

All existing scripts we do have in house are just checking the received or applied SCNs or thread# of archive logs. In order to make sense out of these numbers for business users or managers, I will need to convert them into date and time. Fortunately, I recalled the “scn_to_timestamp” function which allows me to convert the SCN number to its corresponding timestamp.

I can get the current_scn from v$database of the standby. However, in the case of physical standby, when database is being mounted (recovery mode), this function does not work. It should work fine with the logical standby which is opened all the time.

SQL> select scn_to_timestamp(current_scn) from v$database;
select scn_to_timestamp(current_scn) from v$database
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01219: database not open: queries allowed on fixed tables/views only

To resolve this, once getting the current_scn on standby, I can log on to the primary and get the time stamp corresponding to the SCN number.

Sample below is the KSH script to obtain the date/time based on a SCN. Note that the syntax to check the gap is added as well. The account connecting to primary just needs SELECT_CATALOG_ROLE.

# Get SCN number from DR
DRSCN=/tmp/drscn$$.log
${SQLPLUS} -s /nolog <<EOF
connect / as sysdba
set heading off
set feedback off
col current_scn format 999999999999999999999999
spool ${DRSCN}
select current_scn from v\$database;
spool off
exit
EOF
DR_SCN=`cat ${DRSCN}`

rm ${DRSCN}

# Get Timestamp from Primary
TSDR=/tmp/tsdr$$.log
TSSTAT=/tmp/tsstat$$.log
${SQLPLUS} -s /nolog &lt&ltEOF
connect user/password@PRIMARY
set heading off
set feedback off
spool ${TSDR}
select
to_char(scn_to_timestamp(${DR_SCN}),'MM/DD/YYYY HH24:MI')
from dual;
spool off
spool ${TSSTAT}
select
case
when
scn_to_timestamp(${DR_SCN}) > systimestamp - interval '1' hour
  then 'OK'
when
scn_to_timestamp(${DR_SCN}) > systimestamp - interval '2' hour
  then 'WARNING'
else 'CRITICAL'
end "STATUS"
from dual;
spool off
exit
EOF

11 thoughts on “What is the current status of Data Guard?”

  1. Thanks for a tip. The only issue with V$DATAGUARD_STATS based on my quick test is that the TIME_COMPUTED seems to slow to refresh data.

    I started with using SCN approach.

    SQL@STANDBY> select current_scn from v$database;

    CURRENT_SCN
    ——————–
    9889432454772

    SQL@PRIMARY> select scn_to_timestamp(9889432454772) from dual;

    SCN_TO_TIMESTAMP(9889432454772
    —————————————————————————
    14-FEB-08 09.23.00.000000000 PM

    SQL@PRIMARY> select scn_to_timestamp(current_scn) from v$database;

    SCN_TO_TIMESTAMP(CURRENT_SCN)
    —————————————————————————
    14-FEB-08 09.32.22.000000000 PM

    You can see it is only about 9 minutes gap based on using SCN number then converting to timestamp.

    When I run the v$dataguard_stats on Standby to validate. It shows the gap of 4 hours 50 minutes.

    SQL> select name, value, time_computed from v$dataguard_stats where name=’apply lag’;

    NAME VALUE TIME_COMPUTED
    ————————- ——————– ——————————
    apply lag +00 04:50:51 14-FEB-2008 21:10:14

    Even after waiting for an hour later, it still shows a large gap (even larger).

    SQL> select name, value, time_computed from v$dataguard_stats where name=’apply lag’;

    NAME VALUE TIME_COMPUTED
    ————————- ——————– ——————————
    apply lag +00 05:53:54 14-FEB-2008 21:10:14

    I rechecked using SCN approach. The applying seems still going fine (maintaining about 10 minutes lagging).

    SQL@STANDBY> select current_scn from v$database;

    CURRENT_SCN
    —————
    9889432556445

    SQL@PRIMARY> select scn_to_timestamp(9889432556445) from dual;

    SCN_TO_TIMESTAMP(9889432556445
    —————————————————————————
    14-FEB-08 10.11.48.000000000 PM

    SQL@PRIMARY> select scn_to_timestamp(current_scn) from v$database;

    SCN_TO_TIMESTAMP(CURRENT_SCN)
    —————————————————————————
    14-FEB-08 10.24.44.000000000 PM

    Wonder how to get TIME_COMPUTED refreshed?

  2. I use the same procedure, but I also check de dbfile headers.

    select a.checkpoint_change# checkpoint_datafile , b.checkpoint_change# checkpoint_db,b.ARCHIVE_CHANGE#,b.current_scn from
    (select checkpoint_change# from v$datafile_header) a ,
    (select checkpoint_change#,ARCHIVE_CHANGE#,current_scn from v$database) b;

    However even with lgwr async and real time apply i seem to have delays, for me this is not assuring at all because we would like to loose as little data as possible in case of failover (however we cannot put the db in lgwr sync for the moment).

    for instance :

    select a.checkpoint_change# checkpoint_datafile , b.checkpoint_change# checkpoint_db,b.ARCHIVE_CHANGE#,b.current_scn from
    (select checkpoint_change# from v$datafile_header) a ,
    (select checkpoint_change#,ARCHIVE_CHANGE#,current_scn from v$database) b
    SCN_TO_TIMESTAMP(CURRENT_SCN) SCN_TO_TIMESTAMP(5816051357) SYSTIMESTAMP
    —————————— —————————————–
    09-MAR-11 11.22.58.000000000 AM 09-MAR-11 11.18.46.000000000 AM 09-MAR-11 11.23.00.871964 AM +01:00

    so basically the standby is lagging 5 minutes behind, however v$dataguard_stats shows that they are in sync.

    which one should you trust ?

    1. Phillipe,

      I’ve never tried checking the database file header. Personally I think checking the file header or SCN is more accurate, but is required custom coding obviously. But please keep in mind that it may take time for changes to apply and reflect on the file’s header (even though it will make an attempt to apply them in real time). For disaster recovery, as long as the changes are shipped to the DR server (may be pending on being applied), we will not loose any data.

      1. hello ittichai,

        I just opened an sr to ask what to believe, because for me the headers of the db files should also be updated.

        I will post an update as soon as I have the info

  3. Hi Ittichai,

    It seems the v$dataguard_stats to be accurate than the current_scn ..I dont know why . the above method shows 10 hours delay .. Although the method seemed to be correct for other databases.There is 1 database where there is no archive log gaps as well as there is no lag for any process but still shows 10 hour delay.Will dig in and let you know if I find any.

  4. Hi Ittichai,

    The delay is not 10 hours as observed but 45 minutes[But still we have a difference might be we are in RAC dg? ] . The following is the script written and might be helpful for anyone .

    set head off
    set numf 99999999999
    set feedback off
    set echo off
    set serveroutput on
    DECLARE
    v_sec number;
    db_name varchar2(10);
    v_interval INTERVAL DAY TO SECOND;
    BEGIN
    select substr(value,1,length(value)) into v_interval from v$dataguard_stats where name=’apply lag’;
    select extract(second from v_interval) + extract(minute from v_interval) * 60 +
    extract(hour from v_interval) *60*60 + extract(day from v_interval) *60*60*24 into v_sec from dual;
    select value into db_name from v$parameter where name like ‘db_unique_name’;
    if v_sec > 900 then
    dbms_output.put_line(db_name ||’ is lagging by ‘|| v_interval ||’ Status – Critical Seconds -‘||v_sec);
    elsif v_sec is null then
    dbms_output.put_line(db_name ||’ is broken Status – Critical ‘);
    else
    dbms_output.put_line(db_name ||’ is lagging by ‘|| v_sec||’ Seconds Status : OK’);
    end if;
    END;
    /

Leave a Reply to Venky Cancel Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top