September 2007

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

September 2007
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Monitoring Standby Databases

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.

NoCOUG Fall Conference

The Fall Conference of the Northern California Oracle Users Group is another great education opportunity for Oracle DBAs and Developers. Mark your calendar right now for Thursday, October 25 in Pleasanton, CA. This full-day educational event will also feature twelve technical presentations, vendor exhibits, book raffles, networking, and more. Stay updated at http://www.nocoug.org.

Roger Schrag, CTO of Database Specialists, will give a presentation entitled “Moving Oracle Databases across Platforms without Export/Import.” We are also excited that a new member of the Database Specialists team, James Koopmann, will be presenting “Simulating Oracle I/O Workloads to Accurately Configure Storage.” Database Specialists will be exhibiting at the event. Please stop by our booth and say hello.

Oracle Database 11g Released – but Only for Linux So Far

As expected, Oracle Database 11g has been released for Linux. This is in keeping with Linux becoming the operating system of choice for many Oracle database users. The research firm, Gartner, compiled some numbers recently that shows that Oracle on Linux “grew 72% in 2006, which was faster than overall relational database management system market growth and faster than “general RDBMS growt” on Linux (67%).” A good read with mixed perspectives at http://enterpriselinuxlog.blogs.techtarget.com/2007/08/14/does-oracle-11g-mean-more-linux.

As to the Unix and Windows releases of Oracle Database 11g, the best we can find is that they are in progress.

What’s Happening in the World of Oracle News

Missing out on what’s happening in the world of Oracle? Take advantage of specialized news aggregators for Oracle.

A web search reveals various sites offering this service.

Each has their own look, feel, and content sources they pull from. Test drive a few. And if you would like to build your own, you could start looking at the Lilina News Aggregator (http://lilina.cubegames.net). Then let everyone know your personalized rendition of what constitutes “Oracle News”.

New Member of Database Specialists

We are pleased to announce the addition of James Koopmann to our Oracle DBA team. James frequently contributes to the Oracle community by writing white papers and giving presentations. Check out his thoughts on handling NULLS on his blog at http://blogs.ittoolbox.com/database/solutions/archives/handling-nulls-18805.

James’ next presentation will be at the Kansas City Oracle User Group (http://www.kcoug.org) meeting on Thursday, October 4th to discuss “Simulating Oracle I/O Workloads to Accurately Configure Storage.”

You will be hearing a lot more from James as he becomes the new editor of The Specialist beginning next month. Stay tuned!

 

Leave a Reply

Your email address will not be published. Required fields are marked *