Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will tell you about Data Guard Broker-Removing Database And Broker Configuration In Environment.
We may want to REMOVE one, several or the entire Broker Configuration in the Broker Environment. The operation steps are as follows.
1. Before REMOVE, we query the Databases in Configuration and Environment separately.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | DGMGRL> show configuration Configuration - Broker _Configuration Protection Mode: MaxPerformance Databases: primary - Primary database standby - Physical standby database logical - Logical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> show database primary Database - primary Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): primary1 primary2 Database Status: SUCCESS DGMGRL> show database standby Database - standby Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 640.00 KByte/s Real Time Query: ON Instance(s): primary1 (apply instance) primary2 Database Status: SUCCESS DGMGRL> show database logical Database - logical Role: LOGICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 0 Byte/s Instance(s): primary1 primary2 (apply instance) Database Status: SUCCESS |
2. After REMOVE the configuration, we do some checks to find out how the system will work.
We query the status of the DG_BROKER_START parameter.
1 2 3 4 5 | [Primary-1] SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE |
We question the existence of the DMON process.
1 2 | [oracle@primary1 ~]$ ps -ef | grep ora_dmon | grep -v grep oracle 14329 1 0 Jan14 ? 00:00:06 ora_dmon_primary1 |
We query the existence of the configuration files.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | ASMCMD> pwd +data/primary/BROKER_CONF_FILE ASMCMD> ls dr1primary.dat ASMCMD> pwd +data/standby/BROKER_CONF_FILE ASMCMD> ls dr1standby.dat ASMCMD> pwd +data/logical/BROKER_CONF_FILE ASMCMD> ls dr1logical.dat ASMCMD> pwd +fra/primary/BROKER_CONF_FILE ASMCMD> ls dr2primary.dat ASMCMD> pwd +fra/standby/BROKER_CONF_FILE ASMCMD> ls dr2standby.dat ASMCMD> pwd +fra/logical/BROKER_CONF_FILE ASMCMD> ls dr2logical.dat |
We are querying the values of the LOG_ARCHIVE_DEST_n parameters.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [Primary-1] SQL> column name format a30 [Primary-1] SQL> column value format a94 [Primary-1] SQL> set linesize 9000 [Primary-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary log_archive_dest_2 service="standby", LGWR ASYNC delay=0 optional compression=disable max_failure=0 max_ connections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(all_logfiles,prim ary_role) log_archive_dest_3 service="logical", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_ connections=1 reopen=300 db_unique_name="logical" net_timeout=30, valid_for=(all_logfiles,prim ary_role) |
1 2 3 4 5 6 7 8 | [Physical-1] SQL> set linesize 9000 [Physical-1] SQL> column name format a30 [Physical-1] SQL> column value format a94 [Physical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby |
1 2 3 4 5 6 7 8 | [Logical-1] SQL> column name format a30 [Logical-1] SQL> column value format a94 [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logical |
We are querying the values of the LOG_ARCHIVE_CONFIG parameters.
1 2 3 4 5 | [Primary-1] SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(primary,standby,logical) |
1 2 3 4 5 | [Physical-1] SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(standby,primary,logical) |
1 2 3 4 5 | [Logical-1] SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(logical,primary,standby) |
We are querying some information of databases in Environment.
1 2 3 4 5 6 7 | [Primary-1] SQL> select d.open_mode, d.database_role, a.database_mode, a.recovery_mode, a.protection_mode from v$archive_dest_status a, v$database d where a.database_mode <> 'UNKNOWN'; OPEN_MODE DATABASE_ROLE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE -------------------- ---------------- --------------- ----------------------- -------------------- READ WRITE PRIMARY OPEN IDLE MAXIMUM PERFORMANCE READ WRITE PRIMARY MOUNTED-STANDBY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE READ WRITE PRIMARY OPEN LOGICAL REAL TIME APPLY MAXIMUM PERFORMANCE |
We are questioning the SYNC status of Primary and Standby Databases.
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 555 1 313 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 555 1 313 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 555 1 YES 313 2 YES |
3. We REMOVE from the Logical Standby Database Broker Configuration.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DGMGRL> remove database "logical" Removed database "logical" from the configuration DGMGRL> show configuration Configuration - Broker _Configuration Protection Mode: MaxPerformance Databases: primary - Primary database standby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
We are querying the value of the LOG_ARCHIVE_CONFIG parameter.
1 2 3 4 5 | [Primary-1] SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(primary,standby) |
1 2 3 4 5 | [Physical-1] SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(standby,primary) |
1 2 3 4 5 | [Logical-1] SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(logical) |
We are querying the value of the LOG_ARCHIVE_DEST_n parameter.
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> column name format a30 [Primary-1] SQL> column value format a94 [Primary-1] SQL> set linesize 9000 [Primary-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary log_archive_dest_2 service="standby", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_ connections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(all_logfiles,prim ary_role) |
1 2 3 4 5 6 7 8 | [Physical-1] SQL> column name format a30 [Physical-1] SQL> column value format a94 [Physical-1] SQL> set linesize 9000 [Physical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby |
1 2 3 4 5 6 7 8 | [Logical-1] SQL> column name format a30 [Logical-1] SQL> column value format a94 [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logical |
We question the value of the DMON process.
1 2 | [oracle@logical1 ~]$ ps -ef |grep ora_dmon |grep -v grep oracle 4812 1 0 Jan14 ? 00:00:03 ora_dmon_primary1 |
We query the value of the DG_BROKER_START parameter.
1 2 3 4 5 | [Logical-1] SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE |
CONCLUSION
The REMOVE database is removed from the LOG_ARCHIVE_CONFIG parameter.
The values in the LOG_ARCHIVE_DEST_n parameter that provides Redo-Transport to the REMOVE database are deleted.
The DG_BROKER_START parameter remains at TRUE.
The DMON process continues to run.
Configuration files in the REMOVE database continue to exist in the relevant directories.
4. We REMOVE from the Broker Configuration with the Physical Standby Database PRESERVE DESTINATIONS syntax.
1 2 | DGMGRL> remove database "standby" preserve destinations; Removed database "standby" from the configuration |
We query the broker configuration.
1 2 3 4 5 6 7 8 9 10 11 12 | DGMGRL> show configuration Configuration - Broker _Configuration Protection Mode: MaxPerformance Databases: primary - Primary database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
We are querying the value of the LOG_ARCHIVE_DEST_n parameter.
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> column name format a30 [Primary-1] SQL> column value format a94 [Primary-1] SQL> set linesize 9000 [Primary-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary log_archive_dest_2 service="standby", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_ connections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(all_logfiles,prim ary_role) |
We are querying the value of the LOG_ARCHIVE_CONFIG parameter.
1 2 3 4 5 | [Primary-1] SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(primary,standby) |
1 2 3 4 5 | [Physical-1] SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(standby,primary) |
CONCLUSION
The REMOVE database is not removed from the LOG_ARCHIVE_CONFIG parameter.
The values in the LOG_ARCHIVE_DEST_n parameter that provide Redo-Transport to the REMOVE database are not deleted.
The DG_BROKER_START parameter remains at TRUE.
The DMON process continues to run.
Configuration files in the REMOVE database continue to exist in the relevant directories.
5. A Broker Configuration with all values from step one and step 2 is REMOVE without the PRESERVE DESTINATIONS systax.
1 2 3 4 | DGMGRL> remove configuration Removed configuration DGMGRL> show configuration ORA-16532: Data Guard broker configuration does not exist |
Configuration details cannot be determined by DGMGRL
We query the value of the LOG_ARCHIVE_DEST_n parameter.
1 2 3 4 5 6 7 | [Primary-1] SQL> column name format a30 [Primary-1] SQL> column value format a94 [Primary-1] SQL> set linesize 9000 [Primary-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary |
The value of the LOG_ARCHIVE_CONFIG parameter is queried.
1 2 3 4 5 | [Primary-1] SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string nodg_config |
We query the status of the DMON process.
1 2 | [oracle@primary1 ~]$ ps -ef | grep ora_dmon |grep -v grep oracle 29544 1 0 21:02 ? 00:00:00 ora_dmon_primary1 |
We query the value of the DG_BROKER_START parameter.
1 2 3 4 5 6 7 | [Primary-1] SQL> show parameter dg_broker NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string +data/PRIMARY/BROKER_CONF_FILE/dr1primary.dat dg_broker_config_file2 string +FRA/PRIMARY/BROKER_CONF_FILE/dr2primary.dat dg_broker_start boolean TRUE |
It is questioned whether the Broker Configuration Files are in the relevant locations.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [grid@primary1 ~]$ asmcmd ASMCMD> ls DATA/ FRA/ OCR_VOTE/ ASMCMD> cd +data ASMCMD> ls PRIMARY/ ASMCMD> cd primary ASMCMD> ls BROKER_CONF_FILE/ CHANGETRACKING/ CONTROLFILE/ DATAFILE/ DATAGUARDCONFIG/ ONLINELOG/ PARAMETERFILE/ TEMPFILE/ spfileprimary.ora ASMCMD> cd BROKER_CONF_FILE ASMCMD> ls dr1primary.dat |
CONCLUSION
It can be seen that there is no data guard conf in the LOG_ARCHIVE_CONFIG parameter.
LOG_ARCHIVE_DEST_n parameters that provide Redo-Transport are blank.
The DG_BROKER_START parameter remains at TRUE.
The DMON process continues to run.
Configuration files in the REMOVE database continue to exist in the relevant directories.
6. What to do after the BROKER Configuration is REMOVE.
We set the DG_BROKER_START parameter to FALSE.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Primary-1] SQL> alter system set dg_broker_start=FALSE scope=both sid='*'; System altered. [Primary-1] SQL> show parameter dg_broker NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string +data/PRIMARY/BROKER_CONF_FILE /dr1primary.dat dg_broker_config_file2 string +FRA/PRIMARY/BROKER_CONF_FILE/ dr2primary.dat dg_broker_start boolean FALSE |
Configuration files are deleted from the respective directories.
