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.

2. 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 syncronizes 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 MRPO & 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 -hP
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.