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_statusFROM 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_statusWHERE target='STANDBY';orSELECT dest_id, status, destination, errorFROM v$archive_destWHERE dest_id=2;
5. Test network connectivity
tnsping STBYtnsping PRIMARYlsnrctl status sqlplus sys@primary as sysdbasqlplus 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 runningRFS present + MRP0 missing | +--> Redo arriving but recovery likely not runningRFS missing | +--> Investigate redo transport/network/configuration
7. Check archive gap
-- On StandbySELECT * FROM v$archive_gap;THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#------- ------------- --------------1 10500 10510Its means some archive is missing otherwise no row return
8. Compare the archive geneterated or received
-- On PrimarySELECT thread#, MAX(sequence#) last_archivedFROM v$archived_logWHERE archived='YES'GROUP BY thread#ORDER BY thread#;-- On Standby receivedSELECT thread#, MAX(sequence#) last_receivedFROM v$archived_logGROUP BY thread#ORDER BY thread#;-- On Standby appliedSELECT thread#, MAX(sequence#) last_appliedFROM v$archived_logWHERE 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_timeFROM v$dataguard_stats;
10. Check Data Guard errors
SELECT timestamp, facility, severity, error_code, messageFROM v$dataguard_statusORDER BY timestamp DESC;OR SELECT timestamp, facility, severity, error_code, messageFROM v$dataguard_statusWHERE 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 DATABASEUSING CURRENT LOGFILEDISCONNECT 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, statusFROM v$standby_logORDER BY thread#,group#;OR SELECT group#, thread#, sequence#, bytes/1024/1024 size_mb, statusFROM v$logORDER BY thread#,group#;-- Matches size
14. Check the alert log files
Quick commands for primary:
-- 1. ROLESELECT database_role,open_mode,protection_mode,protection_levelFROM v$database;-- 2. TRANSPORTSELECT dest_id,status,error,db_unique_name, transmit_mode,archived_seq#,applied_seq#FROM v$archive_dest_statusWHERE target='STANDBY';-- 3. Generate redoALTER SYSTEM SWITCH LOGFILE;
Quick commands for standby:
-- 4. RFS/MRPSELECT process,status,thread#,sequence#FROM v$managed_standby;-- 5. GAPSELECT *FROM v$archive_gap;-- 6. LAGSELECT name,value,time_computed,datum_timeFROM v$dataguard_stats;-- 7. LAST RECEIVED/APPLIEDSELECT thread#, MAX(sequence#) last_received, MAX(CASE WHEN applied='YES' THEN sequence# END) last_appliedFROM v$archived_logGROUP BY thread#ORDER BY thread#;-- 8. ERRORSSELECT timestamp,severity,error_code,messageFROM v$dataguard_statusWHERE severity IN ('Warning','Error','Fatal')ORDER BY timestamp DESC;