Oracle Data Guard Broker (DGMGRL) Commands

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

Key DGMGRL commands to configure, manage, validate, troubleshoot, and switch over Oracle Data Guard environments.

Broker Setup (DB Level)

-- Set Broker config file locations
ALTER SYSTEM SET dg_broker_config_file1 = '/path/to/standby1.dat' SCOPE=BOTH SID='*';
ALTER SYSTEM SET dg_broker_config_file2 = '/path/to/standby2.dat' SCOPE=BOTH SID='*';

-- For ASM setups (RAC)
ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/broker/standby1.dat' SCOPE=BOTH SID='*';
ALTER SYSTEM SET dg_broker_config_file2 = '+DATA/broker/standby2.dat' SCOPE=BOTH SID='*';

-- Start the Broker
ALTER SYSTEM SET dg_broker_start = TRUE SCOPE=BOTH SID='*';

Create & Enable Configuration

$ dgmgrl                              -- Launch DGMGRL
DGMGRL> CONNECT sys/password;        -- Connect to Broker

-- Create primary configuration
DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'PRIMARY_DB' CONNECT IDENTIFIER IS PRIMARY_DB;

-- Add standby
DGMGRL> ADD DATABASE 'STANDBY_DB' AS CONNECT IDENTIFIER IS STANDBY_DB MAINTAINED AS PHYSICAL;

-- Enable
DGMGRL> ENABLE CONFIGURATION;

View Configuration & Status

SHOW CONFIGURATION;                     -- Summary view  
SHOW CONFIGURATION VERBOSE;            -- Detailed view  
SHOW DATABASE 'PRIMARY_DB';            -- DB info  
SHOW DATABASE 'STANDBY_DB';            -- Standby info  
SHOW DATABASE VERBOSE 'PRIMARY_DB';    -- Detailed primary info  

-- Status reports & inconsistencies
SHOW DATABASE PRIMARY_DB STATUSREPORT;
SHOW DATABASE PRIMARY_DB InconsistentProperties;
SHOW DATABASE STANDBY_DB InconsistentProperties;
SHOW DATABASE PRIMARY_DB InconsistentLogXptProps;
SHOW DATABASE STANDBY_DB InconsistentLogXptProps;

Log & Queue Checks

SHOW DATABASE PRIMARY_DB SENDQENTRIES;  -- Archivelog status (primary)  
SHOW DATABASE STANDBY_DB RECVQENTRIES;  -- Received logs (standby)  
SHOW DATABASE PRIMARY_DB TOPWAITEVENTS; -- Wait events (primary)  

Validation

VALIDATE DATABASE 'PRIMARY_DB';                 -- Validate config  
VALIDATE DATABASE VERBOSE 'PRIMARY_DB';         -- Verbose output  
VALIDATE DATABASE 'STANDBY_DB';                 -- Validate standby  

Tracing for Debug

-- Enable tracing
EDIT CONFIGURATION SET PROPERTY TraceLevel = SUPPORT;
EDIT DATABASE PRIMARY_DB SET PROPERTY LogArchiveTrace = 8191;
EDIT DATABASE STANDBY_DB SET PROPERTY LogArchiveTrace = 8191;

-- For all RAC instances
EDIT INSTANCE * ON DATABASE 'PRIMARY_DB' SET PROPERTY LogArchiveTrace = 8191;

-- Disable tracing
EDIT CONFIGURATION RESET PROPERTY TraceLevel;
EDIT DATABASE PRIMARY_DB RESET PROPERTY LogArchiveTrace;
EDIT DATABASE STANDBY_DB RESET PROPERTY LogArchiveTrace;

Switchover & Convert

-- Switchover operation
CONNECT sys/password;
SWITCHOVER TO STANDBY_DB;

-- Convert standby to snapshot
CONVERT DATABASE 'STANDBY_DB' TO SNAPSHOT STANDBY;

-- Convert snapshot back to physical
CONVERT DATABASE 'STANDBY_DB' TO PHYSICAL STANDBY;