You're invited to submit your Oracle-related questions to us at askdbspecialists03@dbspecialists.com. 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.
About The Specialist 