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 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
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
— 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
— 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
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
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:
— 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=’*’;
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)
How to remove a Data Guard Configuration from Primary Database (Doc ID 733794.1)
0 Comments