dba_logstdby_log tips Oracle Database Tips by Donald Burleson

Although, verifying the application data between the Data Guard logical and the primary database is the best method to check the consistency of data on standby database, this may not always be the most appropriate method. There are few views such as dba_logstdby_log in the Oracle data dictionary that can be queried to find the status of the various processes involved in the log transfer and log apply services. In this section, two views that will show the log transfer process and the overall progress of the log apply service will be presented.

Once logs are transferred from the primary site to the standby site, they need to be automatically registered with the logical Oracle instance before the log apply process (lsp0) can read and apply the SQL statements.

The dba_logstdby_log view provides information on the archived logs registered at the standby site. The script, reg_log.sql, from the code depot shows the logs registered with the logical standby database.

set linesize 100
Column Sequence# Format 99999
Column File_Name Format a60
Column Timestamp Format a20

Select
   Sequence#,
   File_Name,
   to_char(Timestamp,'YYYY/MM/DD HH24:MI:SS') Timestamp
From
   see code depot for full scripts
dba_logstdby_log
order by
   Sequence#;

A sample output from the dba_logstdby_log script shows the archived redo logs and the time it was registered with the standby database:

SEQUENCE# FILE_NAME                              TIMESTAMP
--------- -------------------------------------- -------------
      527 /oracle/appsdb/arch/appsdb_1_568.dbf   2003/10/05 19:14:17
      528 /oracle/appsdb/arch/appsdb_1_569.dbf   2003/10/05 19:18:41
      529 /oracle/appsdb/arch/appsdb_1_570.dbf   2003/10/05 19:18:41
      530 /oracle/appsdb/arch/appsdb_1_571.dbf   2003/10/05 19:18:42
      531 /oracle/appsdb/arch/appsdb_1_572.dbf   2003/10/05 19:17:22

A few log switches can be performed on the primary site and then checked to see if this query is showing the new archived logs as registered.

Another view, dba_logstdby_progress, shows the overall progress of the log apply service on the standby site. See the script log_progress.sql from code depot. This script is a query on dba_logstdby_progress view and shows the received, read, and applied SCN by the standby database.

            log_progress.sql

The above text is an excerpt from: the book "Oracle Data Guard", ISBN 0-9745993-8-7, by Bipul Kumar.  Order it directly from the publisher link and save 30%.