How to Remove One Standby Database from a Data Guard Configuration

 

How to Remove One Standby Database from a Data Guard Configuration

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.