Stop Data Guard
Is Broker Enabled?
First of all, you need to know whether the data guard broker is configured, started and controls over the data guard.
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
SQL> select name, value from v$parameter where name = 'dg_broker_start';
NAME VALUE
-----------------------------------------------------------------
dg_broker_start TRUE
If the returned value is TRUE, then you should use the broker to stop data guard synchronization either in DGMGRL or in EM. If the value is FALSE, you have to issue commands in sqlplus to stop it.
Before we get into how to stop data guard, we should clarify some concepts with followings:
Data Guard Services
Data Guard basically includes two main services, one is the transport service on the primary side, the other is the apply service on the standby side. Cancelling any one of two services will make the synchronization stop, but the alert log will keep warning you that. The formal way is to stop both services.
Data Guard Monitor
The broker will initiate one Data Guard Monitor (DMON) background process on each node after it started. DMON can monitor data guard service on the nodes which is in the same data guard environment. DMON is not a service included in Data Guard. Stopping DMON by disabling configuration cannot stop the data guard synchronization. I will talk about this later.
Now it's your call, if you're using broker, you can go for section A, otherwise, go for section B.
A. With Broker
You can operate these commands by DGMGRL on any node in the same data guard environment.
1. DGMGRL Transport Off
To stop transport in DGMGRL, we can set transport off in the primary database.
It's to disable transport service of the primary database.
DGMGRL> EDIT DATABASE 'primary_db' SET STATE=TRANSPORT-OFF;
Succeeded.
2. DGMGRL Apply Off
To stop apply in DGMGRL, we can set apply off in the standby database.
DGMGRL> EDIT DATABASE 'standby_db' SET STATE='APPLY-OFF';
Succeeded.
Please note that, issuing both EDIT DATABASE SET STATE='TRANSPORT-OFF' and EDIT DATABASE SET STATE='APPLY-OFF' commands makes transporting and applying stop, but the broker is still working and monitoring your every move.
3. DGMGRL Disable Configuration
The above two steps are enough to stop data synchronization service. Optionally, if you want to stop all ability of management and monitor of data guard, you may disable the configuration.
DGMGRL> DISABLE CONFIGURATION;
Disabled.
In the above, the data guard services will stop, but the broker is still running over all instances involved. That is, the broker is not disabled. We can start the data guard services by the broker later.
Please note that, issuing ONLY DISABLE CONFIGURATION can make the broker lose the ability to monitor and control over the data guard, but it does not stop the data guard service at all. Which means, the redo transport services and log apply services in the Data Guard configuration will continue functioning without any change, but you can no longer manage them.
B. Without Broker
1. SQL Transport Off
On the primary database, please make sure the log destination of standby database. It's usually at log_archive_dest_2
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=DR_SITE1 optional reop
en=120
Next, stop transport service by deferring the log destination.
SQL> alter system set log_archive_dest_state_2=DEFER sid='*';
2. SQL Apply Off
On the standby database, please stop the apply service by cancelling the recovering.
SQL> alter database recover managed standby database cancel;
Additionally, you can defer the destination of FAL server for securing primary data from any accidents. But this is optional.
SQL> alter system set log_archive_dest_state_2=DEFER sid='*';
Note 1: sid='*' is for RAC
Note 2: If you want the change applied only in the run-time, set the scope=memory. Otherwise, the scope=both is the default if the database is startup by spfile.
Note 3: Initialization parameter drs_start was deprecated since 9.2.
Also, stopping data guard is an useful practice before you open the standby database to READ ONLY temporarily.
Later on, you may be required to start the data guard services, it is just a reverse procedure of stopping.
Further reading: How to Stop Oracle Database