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