Steps to troubleshoot the Oracle Data guard Environment

Commands used to troubleshoot or fix the issue in Oracle Dataguard Environment

Order which we follow to see the issue in Oracle Data guard Environment

Role → Transport → Network → RFS → Gap → MRP/Apply → Lag → Alert log

1. Check Primary/Standby role — Execute on both server

SELECT name,
db_unique_name,
database_role,
open_mode,
protection_mode,
protection_level,
switchover_status
FROM v$database;

2. Check Data Guard parameters

SHOW PARAMETER db_unique_name;
SHOW PARAMETER log_archive_config;
SHOW PARAMETER log_archive_dest_2;
SHOW PARAMETER log_archive_dest_state_2;
SHOW PARAMETER fal_server;
SHOW PARAMETER standby_file_management;

3. Check the configuration

SELECT * FROM v$dataguard_config;

4. Check redo is not reaching on standby ( run on primary)

Check transport service is working or not

SELECT dest_id,
status,
target,
destination,
error,
db_unique_name,
transmit_mode,
archived_seq#,
applied_seq#
FROM v$archive_dest_status
WHERE target='STANDBY';
or
SELECT dest_id,
status,
destination,
error
FROM v$archive_dest
WHERE dest_id=2;

5. Test network connectivity

tnsping STBY
tnsping PRIMARY
lsnrctl status
sqlplus sys@primary as sysdba
sqlplus sys@stby as sysdba

6. Check RFS/MRP on Standby

SELECT process,
status,
thread#,
sequence#
FROM v$managed_standby;
See mainly RFS and MPR process is working with this view:
RFS present + MRP0 present
|
+--> Transport and apply running
RFS present + MRP0 missing
|
+--> Redo arriving but recovery likely not running
RFS missing
|
+--> Investigate redo transport/network/configuration

7. Check archive gap

-- On Standby
SELECT * FROM v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
------- ------------- --------------
1 10500 10510
Its means some archive is missing otherwise no row return

8. Compare the archive geneterated or received

-- On Primary
SELECT thread#,
MAX(sequence#) last_archived
FROM v$archived_log
WHERE archived='YES'
GROUP BY thread#
ORDER BY thread#;
-- On Standby received
SELECT thread#,
MAX(sequence#) last_received
FROM v$archived_log
GROUP BY thread#
ORDER BY thread#;
-- On Standby applied
SELECT thread#,
MAX(sequence#) last_applied
FROM v$archived_log
WHERE applied='YES'
GROUP BY thread#
ORDER BY thread#;

9. Check transport and apply lag

transport lag measures how far redo transport is behind the source; apply lag measures how far the target’s applied data is behind. Oracle also notes that if DATUM_TIME stops changing across queries, the target is not receiving fresh data from the source

SELECT name,
value,
unit,
time_computed,
datum_time
FROM v$dataguard_stats;

10. Check Data Guard errors

SELECT timestamp,
facility,
severity,
error_code,
message
FROM v$dataguard_status
ORDER BY timestamp DESC;
OR
SELECT timestamp,
facility,
severity,
error_code,
message
FROM v$dataguard_status
WHERE severity IN ('Error','Fatal','Warning')
ORDER BY timestamp DESC;

11. Check MPR process is running or not

SELECT process,status FROM v$managed_standby WHERE process LIKE 'MRP%';

12. Start the MPR recovery process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE
DISCONNECT FROM SESSION;

13. Stop the MPR recovery process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

13. Check standby redo logs files

SELECT group#,
thread#,
sequence#,
bytes/1024/1024 size_mb,
archived,
status
FROM v$standby_log
ORDER BY thread#,group#;
OR
SELECT group#,
thread#,
sequence#,
bytes/1024/1024 size_mb,
status
FROM v$log
ORDER BY thread#,group#;
-- Matches size

14. Check the alert log files

Quick commands for primary:

-- 1. ROLE
SELECT database_role,open_mode,protection_mode,protection_level
FROM v$database;
-- 2. TRANSPORT
SELECT dest_id,status,error,db_unique_name,
transmit_mode,archived_seq#,applied_seq#
FROM v$archive_dest_status
WHERE target='STANDBY';
-- 3. Generate redo
ALTER SYSTEM SWITCH LOGFILE;

Quick commands for standby:

-- 4. RFS/MRP
SELECT process,status,thread#,sequence#
FROM v$managed_standby;
-- 5. GAP
SELECT *
FROM v$archive_gap;
-- 6. LAG
SELECT name,value,time_computed,datum_time
FROM v$dataguard_stats;
-- 7. LAST RECEIVED/APPLIED
SELECT thread#,
MAX(sequence#) last_received,
MAX(CASE WHEN applied='YES' THEN sequence# END) last_applied
FROM v$archived_log
GROUP BY thread#
ORDER BY thread#;
-- 8. ERRORS
SELECT timestamp,severity,error_code,message
FROM v$dataguard_status
WHERE severity IN ('Warning','Error','Fatal')
ORDER BY timestamp DESC;