Monitoring the Data Guard SQL apply service Oracle Database Tips by Donald BurlesonDecember 9, 2015

Oracle Data Guard

Monitoring Overall Progress of the SQL Apply Operation

A quick query on the dba_logstdby_progress view shows the overall progress of the SQL Apply operation on the standby site. The applied_scn column holds the system change number, which has been read and applied on the database, and the newest_scn has the latest system change number that has been received on the standby database. An equal value in the applied_scn and newest_scn indicates that all the logs received by the standby site have been applied on the database.

The script log_progress.sql from code depot can be used to find the overall progress:

Set linesize 100
Column Applied_Scn Format 999999
Column Read_Scn Format 999999
Column Newest_Scn Format 999999

Select
   Applied_Scn,
   Read_Scn,
   Newest_Scn
From  
   DBA_LOGSTDBY_PROGRESS;

The following is a sample output from the log_progress.sql script:
 

APPLIED_SCN   READ_SCN NEWEST_SCN
----------- ---------- ----------
     168011     167225     168133

This script shows there are few changes still to be applied on the logical Oracle instance in order for it to be consistent with the primary database.