Primary & Standby Database SYNC Check in Oracle Database

 In Oracle Data Guard, you can check whether the standby database is in sync with the primary database by monitoring the apply lag. The apply lag represents the time delay between when a redo log is generated on the primary database and when it is applied on the standby database. A zero or low apply lag indicates that the standby is relatively up-to-date with the primary, while a high apply lag suggests a potential synchronization delay.

Here are some methods to check whether the standby database is in sync with the primary:

1. Data Guard Broker

If you are using the Data Guard Broker, you can use the DGMGRL command-line interface or Enterprise Manager (Grid Control) to check the status of the Data Guard configuration and monitor the apply lag.
For example, using DGMGRL:

DGMGRL> SHOW DATABASE 'standby_database_name' APPLY_LAG;

DGMGRL> SHOW DATABASE 'standby_database_name' APPLY_LAG;

2. V$DATAGUARD_STATS View

On the primary database, you can query the V$DATAGUARD_STATS view to check the apply lag and other Data Guard-related statistics. The APPLY_LAG column will show the apply lag in seconds.

SQL> SELECT NAME, VALUE FROM V$DATAGUARD_STATS WHERE NAME = 'apply lag';

##OR##

SQL> Select max(sequence#) from v$log_history;

##Run above query on both primary & standby to check the sync##

SQL> SELECT NAME, VALUE FROM V$DATAGUARD_STATS WHERE NAME = 'apply lag';

##OR##

SQL> Select max(sequence#) from v$log_history;

##Run above query on both primary & standby to check the sync##

3. V$ARCHIVED_LOG View

You can also use the V$ARCHIVED_LOG view on the standby database to check the archived redo logs and their timestamps. By comparing the oldest log timestamp with the current timestamp, you can estimate the apply lag.

SQL> SELECT MIN(FIRST_TIME) AS OLDEST_LOG_TIMESTAMP, CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP FROM V$ARCHIVED_LOG;

SQL> SELECT MIN(FIRST_TIME) AS OLDEST_LOG_TIMESTAMP, CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP FROM V$ARCHIVED_LOG;

4. Log Files on Standby

You can also check the standby database's alert log and Data Guard-specific log files for any messages related to apply lag or synchronization issues.

It's important to monitor the Oracle Data Guard configuration regularly to ensure that the standby database is adequately in sync with the primary. A small apply lag is generally acceptable, but a significant lag may indicate performance issues or network problems. If there are considerable delays, investigate and resolve the underlying causes to maintain a robust Data Guard environment for disaster recovery and data protection.