Database News You Can Use
A monthly newsletter for Oracle usersMarch 2006
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: Checking Standby Database Status
You’re invited to submit your Oracle-related questions to us at firstname.lastname@example.org. Please include your name and telephone number in your email.
This month’s question comes to us from Mahmoud in Bahrain: We have communication problems between our primary and standby database, so sometimes a log does not get transported to the standby. How can I know the last archived redo log that was applied to the standby database while it is in recovery mode?
Roger Schrag of the Database Specialists team responds: Application users cannot log in and run queries against database tables on a physical standby database while it is in recovery mode, but you can log in as the SYS user and query the v$ views. You can run the following query on the standby database in order to find the sequence number of the last archived redo log that was applied:
SELECT MAX (sequence#) last_log_applied FROM v$log_history;
Next you can run the following query on the primary database in order to find the sequence number of the current online redo log:
SELECT MAX (sequence#) current_log FROM v$log;
By comparing these two figures, you can tell if your standby database has fallen far behind the primary; a standby that is one log behind the primary is probably doing fine, while a standby that is many logs behind might be a cause for concern.
If you are using DataGuard to manage your standby database, you can find out the last log that was applied to the standby database without actually logging into the standby database. This is because the standby communicates status back to the primary database, and this status information is available to us in the v$archived_log view.
The following query run on the primary database will show the sequence number of the last log applied to the DataGuard standby database specified by the log_archive_dest_2 parameter:
SELECT MAX (sequence#) last_log_applied FROM v$archived_log WHERE dest_id = 2 AND applied = 'YES';
You can change the dest_id value in this query accordingly if you use other than log_archive_dest_2 to specify the standby database.
The v$archived_log view contains a wealth of additional information about archived redo logs, such as the first and last timestamp in a log, status information, whether it was deleted or compressed or requested by FAL, just to name a few.
The v$archived_log and v$log_history views make it easy to determine the last archived redo log applied to a standby database. Monitoring scripts can quickly be put together to alert when a standby database has fallen more than a specific number of redo logs (or a certain number of minutes) behind the primary.
The Northern California Oracle Users Group held its first conference of 2006 last month. There were three tracks full of some great technical sessions. But even if you missed the meeting, you can still benefit by reading some of the posted presentations and white papers from the conference speakers. Here are just a few of the presentations now available online at http://www.nocoug.org/presentations.html:
Don’t miss the latest white paper by Roger Schrag of Database Specialists. In his newest paper, Roger takes a look at the performance implications of Oracle’s read-consistency and concurrency mechanisms. He states that “Oracle has a strict read-consistency model coupled with high concurrency that sets it apart from other database products such as Microsoft’s SQL Server.” Roger goes on to write that “this great functionality comes at a cost to performance—Oracle has to do more work to ensure read-consistency if one user is updating a table while another user is querying it.”
If performance issues are a concern to you, be sure to learn more about how concurrency and read-consistency impact performance at http://dbspecialists.wpengine.com/presentations/concurrent_updates.html.
When considering purchasing a book, it’s always nice to read some reviews first. DBAZine writer Chris Foot has written a review of Thomas Kyte’s latest book: Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions.
Foot also reviews Cost-Based Oracle Fundamentals by Jonathan Lewis. Foot explains that, “This is not a book on how to administer Oracle; it is an educational textbook on understanding the most complex facet of an Oracle database environment, the optimizer.”
If you’re interested in computer history, Payton Byrd reviews a book about Commodore that points out some interesting facts about the company. They were the biggest computer company in the world in 1982, made and sold the most successful 8-bit processor, sold the first color desktop publishing computers, and put CD-ROM on the TV first. You’ll find Byrd’s full review of On the Edge: The Spectacular Rise and Fall of Commodore by Brian Bagnall.