You're invited to submit your Oracle-related questions to us at askdbspecialists09@dbspecialists.com. Please include your name and telephone number in your email. This month’s question comes to us from Martin in Detroit: We are preparing to deploy a physical standby database using DataGuard with Oracle Database 10g Release 2. How can we monitor the status of the standby?
Ian Jones of the Database Specialists team responds: In Oracle 10g Release 2, the simplest way to monitor the status of a physical standby database is to query the V$DATAGUARD_STATS view on the standby database. For example,
$ sqlplus / as sysdba
SQL> set linesize 120
SQL> column value format a20
SQL> select * from v$dataguard_stats;
NAME VALUE UNIT TIME_COMPUTED
----------------------- ---------------- -------------------- --------------------
transport lag +00 00:00:00 day(2) to second(0) 24-JUL-2007 15:31:13
apply lag +00 08:03:01 day(2) to second(0) 24-JUL-2007 15:31:13
apply finish time +00 00:00:04.7 day(2) to second(1) 24-JUL-2007 15:31:13
estimated startup time 10 second 24-JUL-2007 15:31:13
standby has been open N 24-JUL-2007 15:31:13
From this we can see that the “transport lag” value is currently zero, which means that if the primary database server was to immediately become unavailable there would be less than one second of data loss. The “apply lag” value is just over eight hours, which is a configuration option we have chosen by including “DELAY=480” in the log_archive_dest_2 parameter on the primary database. We have the parameter set on the primary database as follows:
log_archive_dest_2='SERVICE=h1haw LGWR ASYNC DELAY=480 VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE) DB_UNIQUE_NAME=h1haw'
In the event of a disaster at the primary site we would perform a failover which would cause the standby database to recover the last eight hours of archived redo logs and then use the standby log files to complete the recovery. Notice the time_computed column in the query above contains the timestamp of the last update of this data. Under normal operations this data is updated every minute or so; if this time falls significantly behind the current time then there is a problem that needs to be resolved.
In addition to V$DATAGUARD_STATS we can also monitor the arrival of redo data into the standby logs by querying the V$STANDBY_LOG view. For example, the following query gives the time of the latest redo entry available on the standby:
SQL> select to_char(max(last_time),'DD-MON-YYYY HH24:MI:SS') "AVAILABLE_REDO"
2 from v$standby_log;
AVAILABLE_REDO
--------------------
24-JUL-2007 15:31:59
The protection level of the primary database directly affects the transport lag. If the primary database is operating in maximum protection mode, the transport lag will always be zero because the standby is not allowed to fall behind the primary. When a database is operating in maximum availability mode, the transport lag will usually be zero unless there is a problem (e.g. a network outage), in which case the transport lag will increase and the primary database will show a value of RESYNCHRONIZATION in the V$DATABASE.protection_level column until the problem is resolved.
When the database is operating in maximum performance mode the transport lag is determined by the redo generation rate at the primary and the network latency and bandwidth; typically the transport lag in this case will be non-zero.
The recovery mode of the standby database directly affects the apply lag. One way to put a standby database into managed recovery mode is with the following command:
SQL> alter database recover managed standby database disconnect;
In this case the apply lag will reflect any specified delay time plus the time since the last log switch. The time of the last log switch is significant because in conventional managed recovery mode, the standby will only apply redo data when an archived redo log is created (i.e. after a redo log switch), and after allowing for any required delay time.
Alternatively, a standby database can be placed in “real-time apply” recovery mode via the command:
SQL> alter database recover managed standby database using current logfile disconnect;
In this case the apply lag will be small and any delay setting specified on the primary will be ignored; the standby database will apply changes soon after they are written into the standby log file without waiting for a redo log switch.
Beyond V$DATAGUARD_STATS and V$STANDBY_LOG, we can assess the state of a standby database by determining its current SCN and determining the timestamp associated with that SCN. To do this, first we run the following query on the standby database:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
15888221
Next we convert this SCN to a timestamp by running the following query on the primary database:
SQL> select scn_to_timestamp(15888221) "CURRENT_APPLY_TIME" from dual;
CURRENT_APPLY_TIME
-------------------------------
24-JUL-07 07.27.02.000000000 AM
There are many ways to monitor the status of a DataGuard standby database. In this article we have looked at three simple and effective techniques.
About The Specialist 