Blog

I will be sharing day to day commands to monitor Logical standby in a Production environment:

We are not going to discuss the architecture of Logical standby, however few imp terminology I would like to mention.

FOREIGN ARCHIVE LOGS VS LOCAL ARCHIVE:

Foreign archives logs are the one which is shipped from the primary database to the logical standby database.

Local archives are self-explanatory, since Logical standby is in READ-WRITE mode, so it generates local archives.

SQL apply process: SQL Apply converts the data from the archived redo log or standby redo log into SQL statements and then executes these SQL statements on the logical standby database. Because the logical standby database remains open, tables that are maintained can be used simultaneously for other tasks such as reporting, summations, and queries.

So this is similar to the MRP process, which I am sure every Oracle DBA on this planet is aware of. It's very important to keep track of this process, failure of this can lead to a lag in your standby data.

This can be monitored using the query:  select state from V$LOGSTDBY_STATE;

The expected output should be either 'APPLYING' OR 'IDLE'.

If the query against V$LOGSTDBY returns no rows then logical apply is not running. We need to further investigate in alert log for the reason why it is not running and fix it accordnigly.

It can be started or stopped as :

SQL>alter database start logical standby apply immediate;  --- to start

SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;  -- TO STOP

The best practice would be schedule a script to monitor this process every 60/30 min. Below script will keep track of the SQL apply process and throw alert if it doesnt meet the condition.

#############################################################################################################################################

DBALIST="xyz@gmail.com";export DBALIST
ORATAB1=/etc/oratab
ALL_DATABASES=`grep "\S" $ORATAB1 | grep -v "^#" | cut -d : -f1`
          for DB in $ALL_DATABASES
          do
      unset  TWO_TASK
      export ORACLE_SID=$DB
      export ORACLE_HOME=`grep "^${DB}:" $ORATAB1|cut -d: -f2 -s`
      echo "---> Database $ORACLE_SID, using home $ORACLE_HOME"
STDBYSTATUS=`sqlplus -s "/ as sysdba" @MIRROR_SYNCH.sql | tail -2 | head -1`
echo "STDBYSTATUS is $STDBYSTATUS"
if [[ "$STDBYSTATUS" == "SQL APPLY NOT ON" ]]
then
echo "SQL APPLY IS NOT ON Sending Mail..."
echo  "MIRRORING STOPPED ON HOSTANAME `hostname` : ${ORACLE_SID}" | mailx -s "ALERT MAIL" $DBALIST
fi
done

##################################################################################################################################################### 

2)  Determine if logical apply is receiving errors while performing apply operations

Log apply services cannot apply unsupported DML statements, DDL statements, 
    and Oracle supplied packages to a logical standby database in SQL apply 
    mode. When an unsupported statement or package is encountered, SQL apply 
    operations stop. To determine if SQL apply has stopped due to errors you 
    should query the DBA_LOGSTDBY_EVENTS view. When querying the view, select 
    the columns in order by EVENT_TIME. This ordering ensures that a shutdown 
    failure appears last in the view.  For example:

             SQL> SELECT XIDUSN, XIDSLT, XIDSQN, STATUS, STATUS_CODE 
                  FROM DBA_LOGSTDBY_EVENTS 
                  WHERE EVENT_TIME = 
                  (SELECT MAX(EVENT_TIME) 
                  FROM DBA_LOGSTDBY_EVENTS);

    If an error requiring database management occurred (such as adding a 
    tablespace, datafile, or running out of space in a tablespace), then you 
    can fix the problem manually and resume SQL apply.

3) Verify that log apply services on the standby are currently running.

To verify that logical apply is currently available to apply changes perform the following query:

SQL> SELECT PID, TYPE, STATUS, HIGH_SCN FROM V$LOGSTDBY;

When querying the V$LOGSTDBY view, pay special attention to the HIGH_SCN column. This is an activity indicator. As long as it is changing each time you query the V$LOGSTDBY view, progress is being made. The STATUS column gives a text description of the current activity.

During log mining:

The READER process reads redo records from the archived redo log files or standby redo log files.

The PREPARER process converts block changes contained in redo records into logical change records (LCRs). Multiple PREPARER processes can be active for a given redo log file. The LCRs are staged in the system global area (SGA), known as the LCR cache.

The BUILDER process groups LCRs into transactions, and performs other tasks, such as memory management in the LCR cache, checkpointing related to SQL Apply restart and filtering out of uninteresting changes.

During apply processing:

The ANALYZER process identifies dependencies between different transactions.

The COORDINATOR process (LSP) assigns transactions to different appliers and coordinates among them to ensure that dependencies between transactions are honored.

The APPLIER processes apply transactions to the logical standby database under the supervision of the coordinator process.

4) Query DBA_LOGSTDBY_PROGRESS to verify that log applies services are progressing. The DBA_LOGSTDBY_PROGRESS view describes the progress of SQL applies operations on the logical standby databases.

For example: SQL> SELECT APPLIED_SCN, APPLIED_TIME, READ_SCN, READ_TIME, NEWEST_SCN, NEWEST_TIME FROM DBA_LOGSTDBY_PROGRESS;

The APPLIED_SCN indicates that committed transactions at or below that SCN have been applied. The NEWEST_SCN is the maximum SCN to which data could be applied if no more logs were received. This is usually the MAX(NEXT_CHANGE#)-1 from DBA_LOGSTDBY_LOG when there are no gaps in the list. When the value of NEWEST_SCN and APPLIED_SCN are equal then all available changes have been applied. If you APPLIED_SCN is below NEWEST_SCN and is increasing then SQL apply is currently processing changes.

6) To monitor dataguard_stats, the best method to track the lag

set lines 300
COL NAME FORMAT A30
COL VALUE FORMAT A30
COL UNIT FORMAT A30
SELECT NAME, VALUE, UNIT FROM V$DATAGUARD_STATS;

7) To monitor which archives are received and applied from the primary database:

col FILE_NAME for a100;
set linesize 300
SELECT FILE_NAME, SEQUENCE# as SEQ#, DICT_BEGIN AS BEG, DICT_END AS END, APPLIED FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#; 

8) Tuning SQL Apply Operations for Logical Standby ( Doc ID 233261.1 )
MAA - SQL Apply Best Practices 10gR2 ( Doc ID 387450.1 ) for more details

Synchronizing tables in a Logical Standby Database (Doc ID 271455.1)
A logical standby apply process is slow, with the BUILDER showing error ORA-44604 (Doc ID 1498892.1)

Happy Learning !!!

Shailesh Singh

DB Alchemist Academy

You may explore our E-Learning Page to have an insight into our High Rated Courses on Udemy