Data Guard Broker Commands for Managing Oracle Database
Share 
A comprehensive guide to the key Data Guard Broker commands for managing Oracle Data Guard configurations, including setting up the broker, creating configurations, enabling databases, troubleshooting, and performing switchover operations.
| Action | Command and Description |
|---|---|
| Setup DG Broker in the Standby Setup | ALTER SYSTEM SET dg_broker_config_file1 = '/path/to/standby1.dat' scope=both sid='*';Use: Set broker config file for the primary or standby DB. |
ALTER SYSTEM SET dg_broker_config_file2 = '/path/to/standby2.dat' scope=both sid='*';Use: Set second broker config file for the primary or standby DB. | |
ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/broker/standby1.dat' scope=both sid='*';Use: Set ASM broker config file for Oracle RAC. | |
ALTER SYSTEM SET dg_broker_config_file2 = '+DATA/broker/standby2.dat' scope=both sid='*';Use: Set second ASM broker config file for Oracle RAC. | |
ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*';Use: Enable the DG Broker for both primary and standby DBs. | |
| Create Configuration in DG Broker | $dgmgrlUse: Enter the Data Guard Broker command line utility. |
DGMGRL> CONNECT sys/password;Use: Connect to DG Broker using SYS credentials. | |
DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'PRIMARY_DB' CONNECT IDENTIFIER IS PRIMARY_DB;Use: Create configuration with primary DB and connection identifier. | |
| Add Standby Database to the Configuration | DGMGRL> ADD DATABASE 'STANDBY_DB' AS CONNECT IDENTIFIER IS STANDBY_DB MAINTAINED AS PHYSICAL;Use: Add standby DB to the configuration. |
| Enable the Configuration | DGMGRL> ENABLE CONFIGURATION;Use: Enable the Data Guard Broker configuration. |
| View Configuration of DG Broker | DGMGRL> show configuration;Use: Display the current Data Guard configuration. |
DGMGRL> show configuration verbose;Use: Display detailed Data Guard configuration. | |
| View Database Information | DGMGRL> show database 'PRIMARY_DB';Use: Display information for the primary database. |
DGMGRL> show database 'STANDBY_DB';Use: Display information for the standby database. | |
DGMGRL> show database verbose 'PRIMARY_DB';Use: Display detailed information for the primary database. | |
| View Status Report of Databases | show database PRIMARY_DB statusreport;Use: Display status report of the primary database. |
| View Database Inconsistent Properties | show database PRIMARY_DB InconsistentProperties;Use: Display inconsistent properties for the primary database. |
show database STANDBY_DB InconsistentProperties;Use: Display inconsistent properties for the standby database. | |
show database PRIMARY_DB InconsistentLogXptProps;Use: Display inconsistent log transport properties for the primary database. | |
show database STANDBY_DB InconsistentLogXptProps;Use: Display inconsistent log transport properties for the standby database. | |
| Check Archived Log Files on Primary Database | show database PRIMARY_DB sendQentries;Use: Display the archived log files status on the primary database. |
| Check Received Log Sequence on Standby Database | DGMGRL> show database STANDBY_DB recvqentries;Use: Display received log sequence information on the standby database. |
| Check Database Wait Events | DGMGRL> show database PRIMARY_DB topwaitevents;Use: Display top wait events for the primary database. |
| Validate Database Information | dgmgrl> validate database verbose 'PRIMARY_DB';Use: Validate database information for the primary database. |
dgmgrl> validate database 'PRIMARY_DB';Use: Validate database information for the primary database. | |
dgmgrl> validate database 'STANDBY_DB';Use: Validate database information for the standby database. | |
| Enable Tracing for Troubleshooting | DGMGRL> edit configuration set property tracelevel=support;Use: Enable tracing for troubleshooting. |
DGMGRL> edit database PRIMARY_DB set property LogArchiveTrace=8191;Use: Enable log archive tracing for the primary database. | |
DGMGRL> edit database STANDBY_DB set property LogArchiveTrace=8191;Use: Enable log archive tracing for the standby database. | |
DGMGRL> EDIT INSTANCE * ON DATABASE 'PRIMARY_DB' SET PROPERTY LogArchiveTrace=8191;Use: Enable log archive tracing for all instances on the primary database in RAC. | |
| Disable Tracing | DGMGRL> edit configuration reset property tracelevel;Use: Disable tracing for the Data Guard Broker configuration. |
DGMGRL> edit database PRIMARY_DB reset property logarchivetrace;Use: Disable log archive tracing for the primary database. | |
DGMGRL> edit database STANDBY_DB reset property logarchivetrace;Use: Disable log archive tracing for the standby database. | |
| Switchover Using DGMGRL | DGMGRL> connect sys/password;Use: Connect to DG Broker using SYS credentials. |
DGMGRL> switchover to STANDBY_DB;Use: Perform a switchover operation to the standby database. | |
| Convert Physical Standby to Snapshot Standby | DGMGRL> convert database 'STANDBY_DB' to snapshot standby;Use: Convert a physical standby database to a snapshot standby. |
| Convert Snapshot to Physical Standby DB | DGMGRL> CONVERT DATABASE 'STANDBY_DB' to PHYSICAL STANDBY;Use: Convert a snapshot standby database to a physical standby. |
