如何确定oracle dg正常,如何确定Oracle dg中备机的同步情况

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 <

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