This articles contains useful dgmgrl commands to manage the dataguard environments.
1. Setup DG broker in the standby setup.(Run on both primary and standby)
- For standalone db :
ALTER SYSTEM SET dg_broker_config_file1 = '\U01\oradata\dr1node.dat' scope=both sid='*';
ALTER SYSTEM SET dg_broker_config_file2 = '\U01\oradata\dr2node.dat' scope=both sid='*';
-- For oracle RAC/ASM file system;
ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/broker/dr1node.dat' scope=both sid='*';
ALTER SYSTEM SET dg_broker_config_file2 = '+DATA/broker/dr2node.dat' scope=both sid='*';
ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*';
2. Create configuration in dgbroker:
-- on primary
$dgmgl
DGMGRL> CONNECT sys/;
Connected.
-- create configuration with primary db_unique_name and its service name .
DGMGRL> CREATE CONFIGURATION 'PROD_DG' AS PRIMARY DATABASE IS 'PRIMDB' CONNECT IDENTIFIER IS PRIMDB;
Configuration "PRIMDB" created with primary database "PRIMDB"
--- Add standby in the configuration:
DGMGRL> ADD DATABASE 'STYDB' AS CONNECT IDENTIFIER IS STYDB MAINTAINED AS PHYSICAL;
Database "STYDB" added
3. Enable the configuration
DGMGRL> ENABLE CONFIGURATION;
Enabled.
At this stage our dg broker setup is completed.
4. View configuration of dgbroker:
DGMGRL> show configuration
DGMGRL> show configuration verbose
5. view database informations:
-- Here PRIMDB and STYDB are db_unique_name of primary and standby db
DGMGRL> show database 'PRIMDB'
DGMGRL > show database 'STYDB'
DGMGRL> show database verbose 'PRIMDB'
6. View statusreport of databases
-- Here PRIMDB and STYDB are db_unique_name of primary and standby db
show database PRIMDB statusreport
7. View database inconsistent properties
-- Here PRIMDB and STYDB are db_unique_name of primary and standby db
show database PRIMDB InconsistentProperties
show database PRIMDB InconsistentLogXptProps
show database STYDB InconsistentProperties
show database STYDB InconsistentLogXptProps
8. Check whether all logfiles are archived or not( on primary)
show database PRIMDB sendQentries
PRIMARY_SEND_QUEUE
STANDBY_NAME STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
CURRENT 1022762318 1 294 10/30/2019 11:09:26 12298130044308 274219
9. Check information of received log sequence(not applied) ( Run for standby)
DGMGRL>show database STYDB recvqentries
STANDBY_RECEIVE_QUEUE
STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
NOT_APPLIED 1022762318 1 293 10/30/2019 10:03:06 10/30/2019 11:09:26 12298109948824 12298130044308 3487164
10. Check database wait events:
DGMGRL>show database PRIMDB topwaitevents
11.Validate database information:
dgmgrl> validate database verbose 'PRIMDB'
dgmgrl> validate database 'PRIMDB'
dgmgrl> validate database 'STYDB'
12. Enable tracing for troubleshooting:
-- For standalone:
DGMGRL> edit configuration set property tracelevel=support;
DGMGRL> edit database PRIMDB set property LogArchiveTrace=8191;
DGMGRL> edit database STYDB set property LogArchiveTrace=8191;
-- For RAC:
DGMGRL> EDIT INSTANCE * ON DATABASE 'PRIMDB' SET PROPERTY LogArchiveTrace=8191;
13. Disable tracing:
DGMGRL> edit configuration reset property tracelevel ;
DGMGRL> edit database PRIMDB reset property logarchivetrace;
DGMGRL> edit database STYDB reset property logarchivetrace;
14. Switchover using dgmgrl:
DGMGRL> connect sys/oracle
Password:
Connected as sys.
DGMGRL> switchover to STYDB
Performing switchover NOW, please wait...
Operation requires a connection to instance "STYDB1" on database "STYDB"
Connecting to instance "STYDB1"...
Connected as SYSDBA.
New primary database "STYDB" is opening...
Oracle Clusterware is restarting database "PRIMDB" ...
Switchover succeeded, new primary is "STYDB"
15. Convert physical standby to snapshot standby
DGMGRL> convert database 'STYDB' to snapshot standby;
16. Convert snapshot to physical standby db
DGMGRL> CONVERT DATABASE 'STYDB' to PHYSICAL STANDBY;
dataguarddgmgrl DATAGUARD