Use Case Description:
In that example we have one primary database testdb2 and two standby database testdb1 and testdb3. We will remove one Standby database from data guard configuration (No RAC configure)
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) To remove from Broker:
-- On Primary Database testdb2
[oracle@testdb2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration;
Configuration - testdb
Protection Mode: MaxPerformance
Databases:
testdb2 - Primary database
testdb1 - Physical standby database
testdb3 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> disable database testdb3;
Disabled.
DGMGRL> remove database testdb3;
Removed database "testdb3" from the configuration
DGMGRL> show configuration verbose;
Configuration - testdb
Protection Mode: MaxPerformance
Databases:
testdb2 - Primary database
testdb1 - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
B) On the Primary database:
-- On testdb2 Remove Standby database testdb3 Destination from log_archive_dest_n
SQL> show parameter log_archive_dest_4;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_4 string service="testdb3", LGWR ASYNC
NOAFFIRM delay=0 optional comp
ression=disable max_failure=0
max_connections=1 reopen=300 d
b_unique_name="testdb3" net_ti
meout=30, valid_for=(all_logfi
les,primary_role)
SQL> alter system set log_archive_dest_4='' scope=both;
System altered.
-- Remove Standby database testdb3 from log_archive_config parameter
SQL> show parameter log_archive_config;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(testdb1,testdb2,testdb3)
SQL> alter system set log_archive_config='dg_config=(testdb1,testdb2)' scope=both;
System altered.
C) Remove the broker dr.dat configuration files (i.e. show parameter dg_broker_config) from the Standby database machine that you are removing only and if it's the only standby in the Data Guard configuration on that machine.
-- TESTDB3 Standby
SQL> show parameter dg_broker_config_file
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
dg_broker_config_file1 string /opt/oracle/product/11.2.0.3/dbs/dr1testdb3.dat
dg_broker_config_file2 string /opt/oracle/product/11.2.0.3/dbs/dr2testdb3.dat
# rm -r /opt/oracle/product/11.2.0.3/dbs/dr1testdb3.dat
# rm -r /opt/oracle/product/11.2.0.3/dbs/dr2testdb3.dat
2. If you are not using the Data Guard Broker, then to remove it manually do:
A) On the Primary database:
-- On testdb2 Remove Standby database testdb3 Destination from log_archive_dest_n
SQL> show parameter log_archive_dest_4;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_4 string service="testdb3", LGWR ASYNC
NOAFFIRM delay=0 optional comp
ression=disable max_failure=0
max_connections=1 reopen=300 d
b_unique_name="testdb3" net_ti
meout=30, valid_for=(all_logfi
les,primary_role)
SQL> alter system set log_archive_dest_4='' scope=both;
System altered.
-- Remove Standby database testdb3 from log_archive_config parameter
SQL> show parameter log_archive_config;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(testdb1,testdb2,testdb3)
SQL> alter system set log_archive_config='dg_config=(testdb1,testdb2)' scope=both;
System altered.
SQL> alter system set fal_server='testdb1’ scope=both;
System altered.
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.
-- TESTDB3 Standby
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_dest_3 string service="testdb2", LGWR ASYNC
NOAFFIRM delay=0 optional comp
ression=disable max_failure=0
max_connections=1 reopen=300 d
b_unique_name="testdb2" net_ti
meout=30, valid_for=(all_logfi
les,primary_role)
SQL> alter system set log_archive_dest_3='' scope=both;
System altered.
SQL> show parameter log_archive_config;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_config string dg_config=(testdb1,testdb2,testdb3)
SQL> alter system set log_archive_config='' scope=both;
System altered.
For more information please check Oracle Support Doc ID 2196935.1