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.

 

For more information please check Oracle Support Doc ID 2196935.1

Comments