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