In this tutorial, we will remove one standby database (PRODDR2 in this example) from Data Guard configuration with two standby Databases. The Primary Database is a 2-node RAC.
Note:
-It is recommended to try out these procedures on a Test System before attempting on the Production System.
-Primary Database PROD: instance PROD1 on node rac1, instance PROD2 on node rac2.
-Standby Database: PRODDR1 on node stdb1, PRODDR2 on node stdby2.
-ASM1/ASM2 are the names of the ASM Instances on rac1/rac2.
-Usernames/instances/DB/nodes, etc. are names taken from my system. Make sure you replace them with values appropriate for your system.
-$GRID_HOME is the software location of the Grid Infrastructure (GI).
-grid user is the GI software owner.
-$ORACLE_HOME is the software location of the Oracle Database.
-oracle user is the DB software owner.
Step 1 – Removing Standby Database PRODDR2 from the Broker Configuration.
A) Check the current DG configuration.
Note: Run these commands on the Primary Database.
As oracle user, run the following commands:
[oracle@rac1 ~]$ dgmgrl
DGMGRL> connect sys
Password: *****
Connected to "PRODDR2"
Connected as SYSDG.
DGMGRL> show configuration;
Configuration - BISDB
Protection Mode: MaxPerformance
Members:
PROD - Primary database
PRODDR1 - Physical standby database
PRODDR2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 30 seconds ago)
B) Remove PRODDR2 from the DG configuration.
Note: If you get the following error, it means that you were trying to run the command from the database that you are trying to remove. Try it again from the Primary database.DGMGRL> disable database PRODDR2;
Disabled.
DGMGRL> remove database PRODDR2;
Removed database "proddr2" from the configuration
DGMGRL> show configuration;
Configuration - BISDB
Protection Mode: MaxPerformance
Members:
PROD - Primary database
PRODDR1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 66 seconds ago)
DGMGRL> remove database PRODDR2;
Error: ORA-16688: command cannot be issued on a disabled member
Failed.
Step 2 – Check these parameters on the database instances. Verify there are no log_archive_dest_n set for PRODDR2. On Primary and remaining Standby, verify that only PROD and PRODDR1 is listed in log_archive_config. db_file_name_convert and log_file_name_convert should be ok as is.
SQL> set linesize 180 pages 9999
SQL> col value for a105
SQL> col name for a30
SQL> select name, value
2 from v$parameter
3 where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_3',
4 'log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3','remote_login_passwordfile',
5 'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',
6 'log_file_name_convert', 'standby_file_management','db_create_file_dest');
NAME VALUE
------------------------------ ---------------------------------------------------------------------------------------------------------
db_file_name_convert PRODDR1, PROD, PRODDR2, PROD
log_file_name_convert PRODDR1, PROD, PRODDR2, PROD
log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY, valid_for=(ALL_LOGFILES,ALL_ROLES)
log_archive_dest_2
log_archive_dest_3 service="proddr1", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 re
open=300 db_unique_name="PRODDR1" net_timeout=30, valid_for=(online_logfile,all_roles)
log_archive_dest_state_1 ENABLE
log_archive_dest_state_2 ENABLE
log_archive_dest_state_3 ENABLE
fal_server
log_archive_config dg_config=(PROD,PRODDR1)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 10
db_create_file_dest +DATA
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name PROD
db_unique_name PROD
17 rows selected.
References:
How to Remove One Standby Database from a Data Guard Configuration (Doc ID 2196935.1)