How to Verify Standby Database Sync with Primary Database in 19c

 To check if your standby database is in sync with the primary database, follow these steps:

1.     Compare Sequence Numbers: Look at the latest sequence number generated on the primary database and the latest sequence number applied on the standby database.

2.     Check for Gaps: Ensure there are no missing sequence numbers on the standby database.

The sequence numbers on both databases should match, and there should be no gaps in the sequence on the standby database. If they do match and there are no gaps, the databases are in sync.

Verifying the Status of Primary and Standby Databases in Oracle

On the Primary Server

First, log in to the primary server and run the following SQL query to check the status:

Select name,open_mode,database_role from v$database;
    

On the Standby Server

Log in to the standby server and execute the same SQL query:

Select name,open_mode,database_role from v$database;
    

Verify the gap of the standby

Last Primary Sequence Generated

This query helps you find the last sequence generated by the primary database:

SELECT 
    thread#, 
    MAX(sequence#) AS "Last Primary Seq Generated" 
FROM 
    v$archived_log val, 
    v$database vdb 
WHERE 
    val.resetlogs_change# = vdb.resetlogs_change# 
GROUP BY 
    thread# 
ORDER BY 
    thread#;
   

Last Standby Sequence Received

To identify the last sequence received by the standby database, use the following query:

SELECT 
    thread#, 
    MAX(sequence#) AS "Last Standby Seq Received" 
FROM 
    v$archived_log val, 
    v$database vdb 
WHERE 
    val.resetlogs_change# = vdb.resetlogs_change# 
GROUP BY 
    thread# 
ORDER BY 
    thread#;
    

Last Standby Sequence Applied

This query shows the last sequence applied on the standby database:

  SELECT 
    thread#, 
    MAX(sequence#) AS "Last Standby Seq Applied" 
FROM 
    v$archived_log val, 
    v$database vdb 
WHERE 
    val.resetlogs_change# = vdb.resetlogs_change# 
    AND val.applied IN ('YES', 'IN-MEMORY') 
GROUP BY 
    thread# 
ORDER BY 
    thread#;
  

To identify the DR (Disaster Recovery) server hostname from the Primary Database, you can run the following query. This query will display the hostname of the DR server:

  SELECT 
    machine, 
    program 
FROM 
    v$session 
WHERE 
    username = 'PUBLIC' 
    AND osuser = 'oracle' 
    AND machine <> 'db-01.domen';