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;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.
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
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 <<EOF
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
rm ${TSDR}
rm ${TSSTAT}
Hello - assuming 10gR2, you are better off simply using "APPLY LAG" from V$DATAGUARD_STATS in the standby database - see http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1080.htm#I1030413
ReplyDeleteThanks 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.
ReplyDeleteI 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?