Monitor Data Guard

Oracle Data Guard concept make sure high availability,  disaster recovery and data protection of your enterprise database. In Case of disaster or data correction your database can easily run on data guard.

Data Guard can be of two types:

  1. Physical Stand By Database-

Physical Stand by Database provides physical identical copy of your database. It has similar schema and schema. Physical Stand by synchronized by applying redo logs from primary database.

  1. Logical Stand by Database-

Logical Stand by database provides logical information of the data files. It can be of different structure and physical org. Logical Stand by synchronizes by using sql statement and then execute sql statement in logical database.

Query to monitor the data guard status:

Run in Production to get the database role and thread and sequence of archived_log

a. select name, database_role from v$database;

b. select thread#,max(sequence#) from v$archived_log group by thread#;

Run in DR Database:

a. select thread#,max(sequence#) from v$log_history group by thread#;
b. select name,database_role from v$database;

Command to see MRP & RFS services are running or not

select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;

Take current sync status using below query:

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

If there is any lag between the Production Database versus Standby Database then you have to check the following:

  1. Space in DR using df -h
  2. MRP and RFS status using below query
    select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;

  3. If MRP or RFS has issue, you may have to restart it
    ALTER database RECOVER MANAGED STANDBY DATABASE CANCEL;
    alter database mount standby database;
    alter database recover managed standby database disconnect;

  4. You may have to manually copy the Archive from Production to stand by.

In this way you can monitor the DR and incase of issue you can resolve the issue related to DR.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8