Introduction
Though all of the Data Guard management and administration operations can be performed using SQL*Plus, it is recommended to use the separate Data Guard Broker interface (dgmgrl). There may be special circumstances in your organization to just use SQL*Plus alone and not use the dgmgrl utility anymore.
This blog post provides the list of steps to be performed to disable and remove the Broker configuration and only have SQL*Plus to administer the Data Guard configuration.
Solution
The dgmgrl utility can be invoked from any database host in the Data Guard configuration to disable and remove the Broker configuration. This post assumes a 2 node configuration with one primary database host and another standby database host.
1) Set the standby instance environment.
2) dgmgrl
3) connect /
4) show configuration;
5) disable configuration;
6) remove configuration;
7) Now set the +ASM instance environment on both standby and primary database host (assuming the Oracle databases are using ASM) and perform the below steps.
a) cd $ORACLE_HOME/network/admin
b) Make a backup of the existing Oracle Grid Infrastructure's listener.ora
c) Now edit listener.ora and remove the entry having DGMGRL.
d) Restart the listener service.
8) Now set the Oracle database instance environment and execute the below command on both the hosts.
a) sqlplus / as sysdba
b) alter system set dg_broker_start = FALSE scope=both;
9) On the primary database, the log_archive_dest_state_2 parameter value that describes the log shipping status to the standby host may have a value of RESET (Assuming this is the parameter used for log shipping status description to standby among several other log_archive_dest_state_n parameters). Make it enable again by executing the command alter system set log_archive_dest_state_2 = enable scope=both;
10) Now login to the standby database server and set the standby database instance and execute the below commands.
a) sqlplus / as sysdba
b) shutdown immediate;
c) startup mount;
d) ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
e) exit
f) ps -ef | grep -i "mrp" (Must show the mrp managed recovery process on the standby server.)
11)
select process,CLIENT_PROCESS,SEQUENCE#,status
from v$managed_standby
where process like '%MRP0%' or client_process like '%LGWR%';
The sequence# must be within +/-2 compared to the value obtained from Current log sequence value of the primary database and that helps us validate the the log shipping service on primary and log apply service on standby are working successfully.
