Oracle supplies two views to monitor how up to date your standby database is when compared to your primary database. One view is v$dataguard_stats, and the other is v$standby_apply_snapshot. Each of these views is only updated every 30 seconds, or so. Even then, I wonder if they are not “behind”. See below…
#!/home/oracle/local/bin/python
import cx_Oracle
connection = cx_Oracle.connect("***", "***", "sb_db", cx_Oracle.SYSDBA)
connection2 = cx_Oracle.connect("***", "***", "prim", cx_Oracle.SYSDBA)
cursor = connection.cursor()
cursor2 = connection2.cursor()
cursor.execute("select max(last_change#) from v$standby_log")
for gap in cursor.fetchall():
cursor2.execute("select (systimestamp - scn_to_timestamp(:1)) from dual",[gap[0]])
for row in cursor2.fetchall():
print "current gap in seconds in the standby redo log (as measured by last_change# in v$standby_log) at the standby is " + str(row[0])
cursor.execute("select to_char(value) from v$dataguard_stats where name = 'transport lag'")
for gap in cursor.fetchall():
print "transport lag as measured in v$dataguard_stats at the standby is " + str(gap[0])
cursor2.close()
connection2.close()
cursor.close()
connection.close()
All this script does is get the last_change# as recorded in the standby logfile at the standby, and then takes this change number and “reverse engineers” it’s timestamp by using the scn_to_timestamp function at the primary. We then select out the transport lag as measured by the v$dataguard_stats view at the standby.
We see below that when we ran this, the SCN written to the standby redo log was generated at the primary 6.16 seconds earlier (give or take three seconds), while the standby view says we are behind almost two minutes!
15:10:58 oracle@emgrid01 ~ >./standby.py
current gap in seconds in the standby redo log (as measured by last_change# in v$standby_log) at the standby is 0:00:06.169764
transport lag as measured in v$dataguard_stats at the standby is +00 00:01:48
15:11:04 oracle@emgrid01 ~ >
For good reason, management does not like to see a two minute gap for software that costs hundreds of thousands of dollars, so this was a useful exercise.