How to Remove One Standby Database from a Data Guard Configuration

Uncategorized |

Find the LAD (log_archive_dest_n) and the db_unique_name for the Standby that is being removed: 
show parameter log_archive_dest_
Take note of those that have values such as:
log_archive_dest_1                   string      LOCATION=+RECOC1 VALID_FOR=(AL
                                                 L_LOGFILES,ALL_ROLES) DB_UNIQU
                                                 E_NAME=SOAHDR
log_archive_dest_2                   string      SERVICE=PROD VALID_FOR=(ONLINE
                                                 _LOGFILES,PRIMARY_ROLE) DB_UNI
                                                 QUE_NAME=SOAPRODH
show parameter db_unique_name
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_unique_name                       string      SOAHDR

Take note of the current Data Guard parameter configurations:
Set linesize 200
Set pagesize 80
col name for a24
col value for a80
select name, value from  v$parameter where name in (‘log_archive_config’, ‘db_file_name_convert’,’log_file_name_convert’, ‘log_archive_dest_1’, ‘log_archive_dest_2’, ‘log_archive_dest_state_2’, ‘dg_broker_start’, ‘dg_broker_config_file1’, ‘dg_broker_config_file2’, ‘standby_archive_dest’,
‘standby_file_management’, ‘fal_server’, ‘fal_client’, ‘dg_broker’);
NAME                     VALUE
———————— ——————————————————————————–
db_file_name_convert
log_file_name_convert
log_archive_dest_1       LOCATION=+RECOC1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SOAHDR
log_archive_dest_2       SERVICE=SOAPRODH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SOAPRODH
log_archive_dest_state_2 enable
standby_archive_dest     ?/dbs/arch
fal_client               SOAHDR1,SOAHDR2
fal_server               prod
log_archive_config       DG_CONFIG=(SOAHDR,SOAPRODH)
standby_file_management  AUTO
dg_broker_start          TRUE
dg_broker_config_file1   +RECOC1/soahdr/dr1soahdr.dat
dg_broker_config_file2   +RECOC1/soahdr/dr2soahdr.dat
13 rows selected.

The below steps are Assuming you are in Maximum Performance mode.  If you are Maximum Availability mode then the other standby will need to be using SYNC Redo transport/LogXptMode to satisfy the Primary configuration before you can remove the other Standby. 
Steps to Remove a Single Standby from the Multi-Standby Data Guard Configuration:

  1. If you are using the Data Guard Broker, you need to remove it from the Broker using steps:
  A) Remove database from Broker:
On the Primary database:
dgmgrl sys/sys@SOAPRODH
  dgmgrl> disable database <db_unique_name>; << The Standby db_unique_name you are removing
  dgmgrl> remove database <db_unique_name>;
dgmgrl> show configuration verbose;
  — the Standby you are removing will no longer show in the configuration

B) On the Primary database:
Go into sqlplus and make sure it removed the standby from the Data Guard configuration from the DG Broker parameters: 
sqlplus sys/sys@SOAPRODH as sysdba
show parameter log_archive_dest_n   << replace ‘n’ with the # for the Standby Redo transport destination to make sure it’s removed
— if it’s not removed then you’ll have to remove it manually using:
alter system set log_archive_dest_n=” scope=both sid=’*’ << use sid=’*’; at the end if you are RAC
show parameter log_archive_config
— It’s a dynamic parameter so you can alter system and remove it from the list for example:
alter system set log_archive_config=’dg_config=(PRIMARY,STANDBY1,STANDBY2)’ scope=both; << use sid=’*’; at the end if you are RAC
  C) On the Standby database:
Remove the broker dr.dat configuration files from the Standby database machine that you are removing only and if it’s the only standby in the Data Guard configuration on the that machine.
sqlplus sys/sys@SOAHDR as sysdba
show parameter dg_broker_config
NAME                                 TYPE        VALUE
———————————— ———– ——————————
dg_broker_config_file1               string      +RECOC1/soahdr/dr1soahdr.dat
dg_broker_config_file2               string      +RECOC1/soahdr/dr2soahdr.dat
alter system set DG_BROKER_CONFIG_FILE1=” scope=both sid=’*’;
alter system set DG_BROKER_CONFIG_FILE2=” scope=both sid=’*’;
exit
rm +RECOC1/soahdr/dr1soahdr.dat
rm +RECOC1/soahdr/dr2soahdr.dat
  
D) remove the tnsnames entries in both Servers if necessary – standby & primary.

2. If you are NOT Using the Data Guard Broker, then to remove it manually, do:

A) On the Primary database:  

            a) Remove it’s configuration from the LAD parameter:
sqlplus sys/sys@SOAPRODH as sysdba
show parameter log_archive_dest_n << replace the ‘n’ with the # for the Standby Redo transport destination 
alter system set log_archive_dest_n=” scope=both; << use sid=’*’; at the end if you are RAC
show parameter log_archive_config
— Remove the Standby’s db_unique_name from the list
— It’s a dynamic parameter so you can alter system and remove it from the list for example:
alter system set log_archive_config=’dg_config=(PRIMARY,STANDBY1,STANDBY2)’ scope=both;

b) If the fal_server is pointing to the Standby you are removing, then you need to remove it from the fal_server parameter:
alter system set fal_server=’STANDBY1,STANDBY2′ scope=both sid=’*’;

B) On the Standby database:
Note: If you are going to remove the standby from it’s machine, then you don’t need to do the following.
  a) Reset any log_archive_dest_n parameters that may be pointing back to the primary or the other standby
show parameter log_archive_dest_
alter system set log_archive_dest_n=” scope=both sid=’*’;

b) remove the log_archive_config parameter:
alter system set log_archive_config=” scope=both sid=’*’; << use sid=’*’; at the end if you are RAC
    or you can set like:
alter system set log_archive_config=NODG_CONFIG scope=both sid=’*’;
Reference:
How to remove a Data Guard Configuration from Primary Database (Doc ID 733794.1)