2012/07/17

Oracle Data Guard 11g - How to safely remove a Data Guard Broker configuration?


When you need safely remove broker-managed Data Guard Configuration then, oracle recommend following steps
You know dg_broker_start parameter is TRUE and data guard configuration information contains in broker configuration files dg_broker_config_file1, dg_broker_config_file2 with broker-managed Data Guard Configuration.
Following steps I use on my test machines witch is running on Oracle Virtual Box. I captures video on this   machines, too. Primary and Standby Database running on Oracle Linux Server 6.2 and  installed Oracle Database 11g Release 2 (11.2.0.3).
DG – is my data guard configuration. admdb is primary database, stldb – is standby database. 

DGMGRL> show configuration
Configuration - dg

  Protection Mode: MaxPerformance
  Databases:
    admdb - Primary database
    stldb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Archive log destinations like as

SQL> show parameter log_archive_dest_

NAME                 TYPE       VALUE
-------------------- ----------- -------------
log_archive_dest_1  string     location=USE_DB_RECOVERY_FILE_
                               DEST, valid_for=(ALL_LOGFILES,
                               ALL_ROLES)
...
log_archive_dest_2  string     service="stldb", LGWR ASYNC NO
                               AFFIRM delay=0 optional compre
                               ssion=disable max_failure=0 ma
                               x_connections=1 reopen=300 db_
                               unique_name="stldb" net_timeou
                               t=30, valid_for=(all_logfiles,
                               primary_role)
...

Safely remove data guard configuration steps :
1. Remove data guard configuration with Data Guard Manager Command LineDGMGRL use remove configuration . 
Connect to Primary database with DGMGRL with SYS user and use command 

DGMGRL> remove configuration
Removed configuration

DGMGRL> show configuration 
ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

2. On the primary database set dg_broker_start parameter FALSE;

SQL> select name , value from v$parameter  
2  where name like '%dg_broker%';

NAME                       VALUE
-----------------------------------------------------------------
dg_broker_start            TRUE

dg_broker_config_file1     /u01/app/oracle/product/11.2.0/dbhome/dbs/dr1admdb.dat

dg_broker_config_file2     /u01/app/oracle/product/11.2.0/dbhome/dbs/dr2admdb.dat

Connect to  primary database with SQL*Plus and use command
SQL> alter system set dg_broker_start=FALSE;
System altered.
SQL> select name , value from v$parameter 
2 where name like '%dg_broker%';
NAME                       VALUE
-----------------------------------------------------------------
dg_broker_start          FALSE
dg_broker_config_file1  /u01/app/oracle/product/11.2.0/dbhome/dbs/dr1admdb.dat
dg_broker_config_file2  /u01/app/oracle/product/11.2.0/dbhome/dbs/dr2admdb.dat


3.
 When we are remove configuration on DGMGRL, then archive destination for service cleared automatically.
SQL> show parameter log_archive_dest

NAME                 TYPE       VALUE
-------------------- ----------- -------------
log_archive_dest_1  string     location=USE_DB_RECOVERY_FILE_
                               DEST, valid_for=(ALL_LOGFILES,
                               ALL_ROLES)
...
log_archive_dest_2  string     
...
4. On standby disable data guard broker
SQL>alter system set dg_broker_start=FALSE;
System altered.
SQL> select name , value from v$parameter 
  2  where name like '%dg_broker%';

NAME                       VALUE
-----------------------------------------------------------------
dg_broker_start            FALSE

dg_broker_config_file1     /u01/app/oracle/product/11.2.0/dbhome/dbs/dr1stbdb.dat
dg_broker_config_file2     /u01/app/oracle/product/11.2.0/dbhome/dbs/dr2stbdb.dat

5. On primary and standby database move broker configuration files.

[oracle@oel62-x64 dbs]$ ls -l dr*.dat
-rw-r----- 1 oracle oinstall 16384 Jul 16 18:00 dr1admdb.dat
-rw-r----- 1 oracle oinstall 16384 Jul 16 17:59 dr2admdb.dat

[oracle@oel62-x64 dbs]$ mv dr1admdb.dat dr1admdb.dat.bak
[oracle@oel62-x64 dbs]$ mv dr2admdb.dat dr2admdb.dat.bak

and


[oracle@oel62-x64-stb dbs]$ ls -l dr*.dat
-rw-r----- 1 oracle oinstall 16384 Jul 16 18:00 dr1stldb.dat
-rw-r----- 1 oracle oinstall 16384 Jul 16 17:59 dr2stldb.dat
[oracle@oel62-x64-stb dbs]$ mv dr1stldb.dat dr1stldb.dat.bak
[oracle@oel62-x64-stb dbs]$ mv dr2stldb.dat dr2stldb.dat.bak 

We are removed data guard configuration safely.


Note :
After remove data guard configuration you can  use old standby database(s) for developer test after switchover to primary and opened read/write mode.

Regards

Mahir M. Quluzade