100 Essential SQL Queries for Oracle 19c Active Data Guard DBA Operations

Production Execution Conventions

The article establishes clear execution tags: [Primary] = run only on primary; [Standby] = run only on standby; [Both] = run on either. [Read-only] = pure queries safe anytime; [Admin] = DDL/management commands requiring change-window approval and impact assessment. All commands use 19c syntax (12.2+ compatible); FETCH FIRST n ROWS ONLY requires 12c+. Commands marked [⚠️ High Risk] may trigger role changes, protection-mode shifts, or data loss — verify primary/standby status before execution.

1. Environment & Version Information (1–8)

1. Database version: SELECT banner FROM v$version; [Both][Read-only]

2. Component versions:

SELECT product, version, status FROM product_component_version;

[Both][Read-only]

3. Database role & open mode:

SELECT name, db_unique_name, database_role, open_mode FROM v$database;

[Both][Read-only]. For ADG, open_mode should be READ ONLY WITH APPLY.

4. Protection mode/level: SELECT protection_mode, protection_level FROM v$database; [Both][Read-only]

5. DBID/name/creation time: SELECT dbid, name, created FROM v$database; [Both][Read-only]

6. Platform & endianness: SELECT platform_name, platform_id FROM v$database; [Both][Read-only]. Critical for cross-platform (e.g., Linux↔AIX) deployments.

7. Instance & host info:

SELECT instance_name, host_name, version, status, logins FROM v$instance;

[Both][Read-only]

8. CDB/PDB open status:

SELECT con_id, name, open_mode FROM v$containers ORDER BY con_id;

[Both][Read-only] (root container only in CDB architecture).

2. Data Guard Configuration & Transport (9–20)

9. Broker config:

SELECT database, CONNECT_IDENTIFIER, DATAGUARD_ROLE, status, ENABLED FROM v$dg_broker_config;

[Both][Read-only] (when Broker enabled).

10. DG member list:

SELECT db_unique_name, PARENT_DBUN, DEST_ROLE, CURRENT_SCN FROM v$dataguard_config ORDER BY db_unique_name;

[Both][Read-only]

11. All archive destinations:

SELECT dest_id, destination, status, error FROM v$archive_dest WHERE dest_id <= 31 AND status <> 'INACTIVE' ORDER BY dest_id;

[Both][Read-only]

12. Archive destination status detail:

SELECT dest_id, status, type, database_mode, recovery_mode FROM v$archive_dest_status WHERE dest_id <= 31 ORDER BY dest_id;

[Both][Read-only]. recovery_mode = MANAGED REAL TIME APPLY indicates real-time apply (normal ADG state).

13. DG message log (last 30):

SELECT severity, message, timestamp FROM v$dataguard_status ORDER BY timestamp DESC FETCH FIRST 30 ROWS ONLY;

[Both][Read-only]

14. Recent DG errors:

SELECT severity, message, timestamp FROM v$dataguard_status WHERE severity IN ('Error','Fatal') ORDER BY timestamp DESC FETCH FIRST 20 ROWS ONLY;

[Both][Read-only]

15. Log transport mode (SYNC/ASYNC):

SELECT dest_id, destination, transmit_mode, affirm, net_timeout FROM v$archive_dest WHERE dest_id <= 31 AND status <> 'INACTIVE' ORDER BY dest_id;

[Both][Read-only]. Maximum Protection/Availability typically use SYNC; Maximum Performance uses ASYNC.

16. Archive delay (DELAY):

SELECT dest_id, destination, delay_mins FROM v$archive_dest WHERE dest_id <= 31 AND delay_mins > 0;

[Both][Read-only]

17. Archive destination errors:

SELECT dest_id, destination, status, error, fail_date FROM v$archive_dest WHERE error IS NOT NULL;

[Both][Read-only]

18. log_archive_dest_n parameters:

set line 999; col name for a25; col value for a88; SELECT name, value FROM v$parameter WHERE name LIKE 'log_archive_dest%' ORDER BY name;

[Both][Read-only]

19. log_archive_config parameter:

SELECT value FROM v$parameter WHERE name = 'log_archive_config';

[Both][Read-only]. Expected: DG_CONFIG=(PRIMDB,STBYDB).

20. Archive process count:

SELECT value FROM v$parameter WHERE name = 'log_archive_max_processes';

[Both][Read-only]

3. Standby Log Receive & Apply (21–35)

21. Standby background processes:

SELECT process, status, client_process, thread#, sequence# FROM v$managed_standby ORDER BY process;

[Standby][Read-only]

22. MRP process status:

SELECT process, status, thread#, sequence#, delay_mins FROM v$managed_standby WHERE process LIKE 'MRP%';

[Standby][Read-only]. status = APPLYING_LOG = applying; WAIT_FOR_LOG = waiting (normal).

23. Real-time apply check:

SELECT dest_id, recovery_mode FROM v$archive_dest_status WHERE type = 'STANDBY' AND dest_id <= 31;

[Standby][Read-only]

24. Media recovery progress:

SELECT item, sofar, total, units FROM v$recovery_progress WHERE type = 'Media Recovery';

[Standby][Read-only]

25. Transport & apply lag:

SELECT name, value, unit FROM v$dataguard_stats WHERE name IN ('transport lag','apply lag');

[Both][Read-only]. Core production metric; unit +hh:mm:ss.

26. Apply finish time estimate:

SELECT name, value, unit FROM v$dataguard_stats WHERE name IN ('apply finish time','estimated startup time');

[Standby][Read-only]

27. Last received log:

SELECT value FROM v$dataguard_stats WHERE name = 'latest received log';

[Standby][Read-only]

28. Last applied log:

SELECT value FROM v$dataguard_stats WHERE name = 'latest applied log';

[Standby][Read-only]

29. Recent archive apply detail:

SELECT thread#, sequence#, applied, completion_time FROM v$archived_log ORDER BY completion_time DESC FETCH FIRST 15 ROWS ONLY;

[Standby][Read-only]

30. Standby Redo Logs:

SELECT group#, thread#, sequence#, bytes, used, status FROM v$standby_log ORDER BY group#;

[Standby][Read-only]. status = ACTIVE and used near full = currently receiving (normal).

31. Max applied sequence (standby):

SELECT thread#, MAX(sequence#) last_applied FROM v$archived_log WHERE applied = 'YES' GROUP BY thread# ORDER BY thread#;

[Standby][Read-only]

32. Max generated sequence (primary):

SELECT thread#, MAX(sequence#) last_generated FROM v$log_history GROUP BY thread# ORDER BY thread#;

[Primary][Read-only]

33. Log gap detection: SELECT * FROM v$archive_gap; [Standby][Read-only]. No rows = no gap; rows present = urgent archive reship needed.

34. Manual register missing archive:

ALTER DATABASE REGISTER LOGFILE '/data/arch/STBYDB/1_1234_1234567890.arc';

[Standby][Admin][⚠️ High Risk]. Use only when v$archive_gap shows gaps and FAL auto-reship fails; path must be visible on standby.

35. Log apply rate:

SELECT item, sofar, units FROM v$recovery_progress WHERE item LIKE '%Apply Rate%';

[Standby][Read-only]

4. Sync Status & Gap Monitoring (36–50)

36. Full DG stats: Formatted query of v$dataguard_stats with columns name, value, unit, time_computed. [Both][Read-only]

37. RFS receive processes:

SELECT process, status, thread#, sequence#, client_process FROM v$managed_standby WHERE process = 'RFS';

[Standby][Read-only]

38. ARC processes:

SELECT process, status, thread#, sequence# FROM v$managed_standby WHERE process LIKE 'ARC%';

[Both][Read-only]

39. RFS block-level progress:

SELECT process, thread#, sequence#, block#, blocks FROM v$managed_standby WHERE process = 'RFS';

[Standby][Read-only]

40. Primary redo generation stats:

SELECT name, value FROM v$sysstat WHERE name IN ('redo size','redo entries');

[Primary][Read-only]

41. Standby redo apply stats: Same query on standby. [Standby][Read-only]

42. Primary/standby sequence comparison: Run max sequence queries on both sides; widening gap indicates sync issues — correlate with v$archive_gap and alert log. [Both][Read-only]

43. Daily standby archive apply volume:

SELECT TO_CHAR(completion_time,'YYYY-MM-DD') day, thread#, COUNT(*) logs FROM v$archived_log WHERE applied = 'YES' GROUP BY TO_CHAR(completion_time,'YYYY-MM-DD'), thread# ORDER BY day DESC FETCH FIRST 10 ROWS ONLY;

[Standby][Read-only]

44. Hourly/daily primary archive generation (capacity planning): Queries using v$archived_log with DEST_ID=1, calculating MB per hour/day. [Primary][Read-only]

45. Alert log ORA- errors (last 1 day):

SELECT originating_timestamp, message_text FROM v$diag_alert_ext WHERE message_text LIKE '%ORA-%' AND originating_timestamp > SYSDATE - 1 ORDER BY originating_timestamp DESC FETCH FIRST 30 ROWS ONLY;

[Both][Read-only]

46. DG-related alerts (ORA-16xx/31xx/125xx): Similar query with specific error patterns. [Both][Read-only]

47. Last archive time per thread:

SELECT thread#, MAX(first_time) last_arch FROM v$archived_log GROUP BY thread#;

[Both][Read-only]

48. Archive destination validity:

col DESTINATION for a35; SELECT dest_id, destination, valid_now, valid_type, valid_role FROM v$archive_dest WHERE dest_id <= 31 AND status <> 'INACTIVE' ORDER BY dest_id;

[Both][Read-only]

49. RAC instance status:

SELECT inst_id, instance_name, status, database_status FROM gv$instance ORDER BY inst_id;

[Both][Read-only]

50. Archive log summary by thread:

SELECT thread#, COUNT(*) cnt, MIN(sequence#) min_seq, MAX(sequence#) max_seq FROM v$archived_log GROUP BY thread# ORDER BY thread#;

[Both][Read-only]

5. ADG Read-Only Queries & Snapshot Standby (51–60)

51. Confirm ADG read-only + real-time apply: SELECT name, open_mode, database_role FROM v$database; [Standby][Read-only]. Expect open_mode = READ ONLY WITH APPLY, database_role = PHYSICAL STANDBY.

52. Current SCN: SELECT to_char(current_scn) FROM v$database; [Both][Read-only]

53. Database time: SELECT SYSDATE FROM dual; [Both][Read-only]

54. ADG user sessions:

SELECT sid, serial#, username, program, status FROM v$session WHERE type = 'USER' ORDER BY sid;

[Standby][Read-only]

55. Standby recovery background sessions:

SELECT sid, serial#, program, event, wait_class FROM v$session WHERE program LIKE '%MRP%' OR program LIKE '%RFS%' OR program LIKE '%ARC%';

[Standby][Read-only]

56. Undo usage (ADG reporting queries):

SELECT begin_time, end_time, undotsn, maxquerylen FROM v$undostat WHERE end_time > SYSDATE - 1 ORDER BY begin_time;

[Standby][Read-only]

57. Temp tablespace usage:

SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,1) mb FROM dba_temp_files GROUP BY tablespace_name;

[Standby][Read-only]

58. Snapshot standby status: Same as #51; snapshot standby shows open_mode = READ WRITE, database_role = SNAPSHOT STANDBY (for testing only). [Standby][Read-only]

59. Convert snapshot back to physical standby: ALTER DATABASE CONVERT TO PHYSICAL STANDBY; [Standby][Admin][⚠️ High Risk]. Must end all read-write sessions first; restart instance and restart log apply after conversion.

60. ADG user session top wait events:

SELECT event, COUNT(*) cnt, ROUND(SUM(seconds_in_wait)) secs FROM v$session WHERE type = 'USER' AND wait_class <> 'Idle' GROUP BY event ORDER BY secs DESC FETCH FIRST 10 ROWS ONLY;

[Standby][Read-only]

6. Archive Log Management (61–70)

61. Recent archive logs:

SELECT name, thread#, sequence#, blocks, block_size, completion_time FROM v$archived_log ORDER BY completion_time DESC FETCH FIRST 20 ROWS ONLY;

[Both][Read-only]

62. Applied archive space total:

SELECT ROUND(SUM(blocks * block_size)/1024/1024/1024,2) gb FROM v$archived_log WHERE applied = 'YES';

[Standby][Read-only]

63. Archive count/size by destination:

SELECT dest_id, COUNT(*) cnt, ROUND(SUM(blocks * block_size)/1024/1024,1) mb FROM v$archived_log GROUP BY dest_id;

[Both][Read-only]

64. Unapplied archives:

SELECT thread#, sequence#, name FROM v$archived_log WHERE applied = 'NO' ORDER BY thread#, sequence#;

[Standby][Read-only]

65. FRA parameters:

SELECT name, value FROM v$parameter WHERE name IN ('db_recovery_file_dest','db_recovery_file_dest_size');

[Both][Read-only]

66. FRA usage:

SELECT name, ROUND(space_limit/1024/1024/1024,2) limit_gb, ROUND(space_used/1024/1024/1024,2) used_gb, ROUND(space_reclaimable/1024/1024/1024,2) reclaim_gb FROM v$recovery_file_dest;

[Both][Read-only]

67. Archive mode: SELECT log_mode FROM v$database; [Both][Read-only]. ADG requires log_mode = ARCHIVELOG.

68. Manual log switch (primary): ALTER SYSTEM SWITCH LOGFILE; [Primary][Admin]. Used to trigger archive for transport verification.

69. Archive current online log (primary): ALTER SYSTEM ARCHIVE LOG CURRENT; [Primary][Admin]

70. Undeleted archive time span:

SELECT MIN(completion_time) oldest, MAX(completion_time) newest, COUNT(*) cnt FROM v$archived_log WHERE deleted = 'NO';

[Both][Read-only]. Assess retention with RMAN policy; always use RMAN for archive cleanup, never delete files manually .

7. Role Transition & Protection Modes (71–85)

71. Switchover status: SELECT database_role, switchover_status FROM v$database; [Both][Read-only]. Primary expects TO STANDBY / SESSIONS ACTIVE; standby expects TO PRIMARY / NOT ALLOWED.

72. Comprehensive DB status:

SELECT database_role, open_mode, protection_mode, switchover_status FROM v$database;

[Both][Read-only]

73. Primary switchover readiness:

SELECT switchover_status FROM v$database WHERE switchover_status IN ('TO STANDBY','SESSIONS ACTIVE','NOT ALLOWED');

[Primary][Read-only]

74. Standby gap check pre-switchover: SELECT * FROM v$archive_gap; [Standby][Read-only]. Zero rows required to proceed.

75. MRP status pre-switchover:

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

[Standby][Read-only]

76. Primary switchover to standby:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

[Primary][Admin][⚠️ High Risk]. Requires checks 71–75; primary shuts down — must STARTUP MOUNT after.

77. Standby switchover to primary: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; [Standby][Admin][⚠️ High Risk]. Then ALTER DATABASE OPEN;.

78. Start real-time apply:

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

[Standby][Admin]. 19c recommended syntax; if mounted, follow with ALTER DATABASE OPEN; for ADG.

79. Stop log apply: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; [Standby][Admin]

80. Open standby read-only (ADG): ALTER DATABASE OPEN; [Standby][Admin]. From mount state; becomes READ ONLY WITH APPLY with real-time apply.

81. Set protection mode to Maximum Availability:

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

[Primary][Admin][⚠️ High Risk]. Requires SYNC transport and all standbys reachable; prefer DGMGRL for management.

82. Broker protection mode:

select DATABASE,DATAGUARD_ROLE,ENABLED,STATUS,VERSION from v$dg_broker_config;

[Both][Read-only]

83. Flashback database status: SELECT flashback_on FROM v$database; [Both][Read-only]

84. Primary/standby SCN comparison: Run SELECT to_char(current_scn) FROM v$database; on both. Standby SCN lag is normal; focus on apply lag — persistent widening = abnormal. [Both][Read-only]

85. Switchover/failover history:

SELECT originating_timestamp, message_text FROM v$diag_alert_ext WHERE message_text LIKE '%Switchover%' OR message_text LIKE '%Failover%' ORDER BY originating_timestamp DESC FETCH FIRST 10 ROWS ONLY;

[Both][Read-only]

8. Performance Monitoring (86–95)

86. Standby top wait events:

SELECT event, wait_class, total_waits, ROUND(time_waited_micro/1000000,1) secs FROM v$system_event WHERE wait_class <> 'Idle' ORDER BY time_waited_micro DESC FETCH FIRST 10 ROWS ONLY;

[Standby][Read-only]

87. Recovery parallelism parameters:

SELECT name, value FROM v$parameter WHERE name IN ('recovery_parallelism','parallel_execution_enabled');

[Both][Read-only]

88. MRP process count:

SELECT process, COUNT(*) FROM v$managed_standby WHERE process LIKE 'MRP%' GROUP BY process;

[Standby][Read-only]

89. RFS session waits:

SELECT sid, serial#, program, event, seconds_in_wait FROM v$session WHERE program LIKE '%RFS%';

[Standby][Read-only]

90. Log transport network params:

SELECT dest_id, destination, transmit_mode, net_timeout, max_connections FROM v$archive_dest WHERE dest_id <= 31 AND status <> 'INACTIVE' ORDER BY dest_id;

[Both][Read-only]

91. SGA info: SELECT * FROM v$sgainfo; [Both][Read-only]

92. PGA usage:

SELECT name, ROUND(value/1024/1024,1) mb FROM v$pgastat WHERE name IN ('maximum PGA allocated','total PGA allocated');

[Both][Read-only]

93. Physical I/O stats:

SELECT name, value FROM v$sysstat WHERE name IN ('physical reads','physical writes','physical read total bytes');

[Both][Read-only]

94. Host load (Linux/Unix):

SELECT stat_name, value FROM v$osstat WHERE stat_name IN ('LOAD','CPU_USED_TICKS') ORDER BY stat_name;

[Both][Read-only]. Windows lacks LOAD; use CPU_USED_TICKS.

95. Currently transporting log:

SELECT process, status, thread#, sequence#, client_process FROM v$managed_standby WHERE client_process IN ('LGWR','ARCH');

[Standby][Read-only]

9. Parameters & Daily Maintenance (96–100)

96. Key DG parameters:

SELECT name, value FROM v$parameter WHERE name IN ('standby_file_management','fal_client','fal_server','db_file_name_convert','log_file_name_convert','dg_broker_start') ORDER BY name;

[Both][Read-only]. standby_file_management must be AUTO for automatic datafile creation on standby.

97. Archive format & primary destination:

SELECT name, value FROM v$parameter WHERE name IN ('log_archive_format','log_archive_dest_1');

[Both][Read-only]

98. Redo thread status: SELECT thread#, status, enabled FROM v$thread; [Both][Read-only]

99. Archive log status summary:

SELECT archived, applied, status, COUNT(*) FROM v$archived_log GROUP BY archived, applied, status ORDER BY archived, applied;

[Both][Read-only]

100. ADG core health check (single command set): Combines five checks: ① role/open_mode/protection/switchover_status/SCN; ② transport/apply lag; ③ v$archive_gap (no rows = OK); ④ archive destination errors (no rows = OK); ⑤ MRP status ( APPLYING_LOG / WAIT_FOR_LOG = OK). Conclusion: all five green = ADG healthy. [Both][Read-only]

Appendix: Usage Recommendations

Read-only queries (tagged [Read-only]) safe anytime in production (avoid peak for large stats queries).

Admin commands (tagged [Admin]) only in change windows after completing pre-checks (71–75, 33, 25).

Prefer DGMGRL for switchover : DGMGRL> switchover to stbydb;; manual SQL only for Broker-unavailable emergencies.

Archive cleanup via RMAN only : RMAN> delete archivelog all completed before 'sysdate-7'; — never delete files manually.

All SQL validated on 19c (12.2+ compatible); minor view column differences across patch sets — verify with DESC.

Appendix: ADG Quick Troubleshooting SQL

Additional practical snippets: standby parameter dump, primary/standby sync checks via gv$archived_log, process status via v$managed_standby and 12c+ v$dataguard_process, role/open_mode query, recovery mode check, various apply start/stop syntaxes (parallel, 19c, 11g), error diagnosis queries, alert log scans, and unapplied archive analysis by thread/sequence/time.