If you want to manage an Oracle data guard easily, then you should use a data guard broker.
The Oracle Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations.
In my configuration, I have already created a Data Guard. You can check this post to see how I created it.
BCN is the primary database and PARIS is the standby database.
In the commands below you can remove the sid=’*’ if you don’t have a RAC database and you should be good to go.
Table of Contents
1. Reset LOG_ARCHIVE_DEST_2
First you need to clear the value for LOG_ARCHIVE_DEST_2 on the primary and on the standby database.
show parameter LOG_ARCHIVE_DEST_2
SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';
Once done you can continue.
2. Set dg_broker_config_file
Set the parameter dg_broker_config_file1 and dg_broker_config_file2 on all instances of primary and standby.
The default location of the broker configuration file is $ORACLE_HOME/dbs or $ORACLE_HOME/database.
If you want broker configuration files in a non default location, set these parameters.
In case of RAC database, set broker configuration file location to shared location and same value on all the instances.
If you don’t have a RAC database no need to use the sid=’*’.
ALTER SYSTEM SET dg_broker_config_file1 = '<path/file_name>.dat' scope=both sid='*';
ALTER SYSTEM SET dg_broker_config_file2 = '<path/file_name>.dat' scope=both sid='*';
OR in case of ASM file system use:
ALTER SYSTEM SET dg_broker_config_file1 = '<+disk group/file_name>.dat' scope=both sid='*';
ALTER SYSTEM SET dg_broker_config_file2 = '<+disk group/file_name>.dat' scope=both sid='*';
If you want the broker configuration files in the default location, then you can ignore this step.
3. Enable the broker
Enable the broker on both primary and standby databases.
If you don’t have a RAC database, no need to use sid=’*’.
ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*';
4. Create the configuration
Connect to DGMGRL on primary:
$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Oct 19 17:47:37 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> CONNECT sys/password;
Connected.
Create configuration on primary:
DGMGRL> CREATE CONFIGURATION 'MYCONF' AS PRIMARY DATABASE IS 'BCN' CONNECT IDENTIFIER IS BCN;
Configuration "MYCONF" created with primary database "BCN"
Add the standby to the configuration:
DGMGRL> ADD DATABASE 'PARIS' AS CONNECT IDENTIFIER IS PARIS MAINTAINED AS PHYSICAL;
Database " PARIS " added
5. Configure the listeners
Configure the listener on the primary server.
Make sure to add the entrance for the broker like that
vi $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv1.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=BCN.localdomain)
(SID_NAME=BCN)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1)
)
(SID_DESC=
(GLOBAL_DBNAME=BCN_DGMGRL.localdomain)
(SID_NAME=BCN)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1)
)
)
And reload the listener
lsnrctl reload
Configure the listener on the standby server.
Make sure to add the entrance for _DGMGRL.
vi $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv2.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PARIS.localdomain)
(SID_NAME=PARIS)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1)
)
(SID_DESC=
(GLOBAL_DBNAME=PARIS_DGMGRL.localdomain)
(SID_NAME=PARIS)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1)
)
)
And reload the standby listener too
lsnrctl reload
6. Enable the configuration
Now you just need to enable the configuration you have just created.
DGMGRL> ENABLE CONFIGURATION;
Enabled.
Use the SHOW command to verify that the configuration and its databases have been successfully enabled and brought online:
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: MYCONF
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
BCN - Primary database
PARIS - Physical standby database
Current status for "MYCONF"
SUCCESS