Activation of dgbroker in a Oracle 12.2 configuration

- Primary RAC 2 nodes:
servers: PRIMARY-HOST-NODE1 and PRIMARY-HOST-NODE2
database: PRIMARY-DB-UNIQ-NAME1 and PRIMARY-DB-UNIQ-NAME2
- Standby: RAC 1 node
server: STANDBY-HOST-NODE1
database: STANDBY-DB-UNIQ-NAME1 (here SID has the same name as primary)

Pre-requisites for the dgbroker configuration

Network configuration

Prepare a dedicated local listener on each node

All Data Guard Broker configurations must have a specific static listener entry configured in the local listener of each instance in the Data Guard configuration for the Primary database and all standby databases. A static entry is needed for the Broker to be able to connect remotely to a database that has been shut down by the Broker during certain operations.

listener.ora
Example for node PRIMARY-HOST-NODE1. Has also to be done for PRIMARY-HOST-NODE2 and STANDBY-HOST-NODE1.

LISTENERDG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY-HOST-NODE1)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)

SID_LIST_LISTENERDG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRIMARY-DB-UNIQ-NAME1)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = PRIMARY-DB-UNIQ-NAME1)
)
(SID_DESC =
(GLOBAL_DBNAME = PRIMARY-DB-UNIQ-NAME_DGB)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = PRIMARY-DB-UNIQ-NAME1)
)
)

USE_SID_AS_SERVICE_listenerDG=ON
ADR_BASE_LISTENERDG = /u01/app/oracle

Add alias in the tnsnames.ora

On each node (2 primary/ 1standby) we create an alias on the dedicated listener LISTENERDG (1522 port).

Primary nodes

# Primary alias(for node 2, modify for PRIMARY-HOST-NODE2 and PRIMARY-DB-UNIQ-NAME2
PRIMARY-DB-UNIQ-NAME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY-HOST-NODE1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMARY-DB-UNIQ-NAME1)
(INSTANCE_NAME = PRIMARY-DB-UNIQ-NAME1)
)
)

# Standby alias STANDBY-DB-UNIQ-NAME1
STANDBY-DB-UNIQ-NAME1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY-HOST-NODE1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID =PRIMARY-DB-UNIQ-NAME1)
)
)

Standby node

# Primary alias (use of: scan host and PRIMARY-DB-UNIQ-NAME_DGB service)

PRIMARY-DB-UNIQ-NAME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY-SCAN)(PORT = 1522))
(CONNECT_DATA =
(SERVICE_NAME =PRIMARY-DB-UNIQ-NAME_DGB)
)
)

# Standby alias
STANDBY-DB-UNIQ-NAME1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY-HOST-NODE1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID =PRIMARY-DB-UNIQ-NAME1)
)
)

Database configuration parameters

Dgbroker config files

On the Primary host

Create DGBROKER directory on ASM: +DATA/PRIMARY-DB-UNIQ-NAME/DGBROKER
Set dg_broker_config_file1 and dg_broker_config_file2:

alter system set dg_broker_config_file1 = '+DATA/PRIMARY-DB-UNIQ-NAME/DGBROKER/dr1PRIMARY-DB-UNIQ-NAME.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+DATA/PRIMARY-DB-UNIQ-NAME/DGBROKER/dr2PRIMARY-DB-UNIQ-NAME.dat' scope=both sid='*';

(dg_broker_start parameter has to be=false before modifying dg_broker_config_file)

On the standby

Create DGBROKER directory on ASM: +DATA/STANDBY-DB-UNIQ-NAME1 /DGBROKER

alter system set dg_broker_config_file1 = '+DATA/STANDBY-DB-UNIQ-NAME1/DGBROKER/dr1PRIMARY-DB-UNIQ-NAME.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+DATA/STANDBY-DB-UNIQ-NAME1/DGBROKER/dr2PRIMARY-DB-UNIQ-NAME.dat' scope=both sid='*';

Change of 2 parameters

On primary and standby:

alter system set dg_broker_start=true scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='' scope=both sid='*';

Dataguard configuration

On primary or standby;
dgmgrl (in the database Oracle_home)

connect /
— Optional: in case a configuration already exists, we drop it.
disable database STANDBY-DB-UNIQ-NAME1
remove configuration;

-- Configuration creation
CREATE CONFIGURATION dgb_prod AS PRIMARY DATABASE IS PRIMARY-DB-UNIQ-NAME CONNECT IDENTIFIER IS PRIMARY-DB-UNIQ-NAME;

-- Add 1 standby db
ADD DATABASE STANDBY-DB-UNIQ-NAME1 AS CONNECT IDENTIFIER IS STANDBY-DB-UNIQ-NAME1 MAINTAINED AS PHYSICAL;

-- Enable
enable configuration;

-- see config
show configuration
(Result:)
Configuration - dgb_prod
Protection Mode: MaxPerformance
Members:
PRIMARY-DB-UNIQ-NAME - Primary database
STANDBY-DB-UNIQ-NAME1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 36 seconds ago)

Verification of the configuration

show configuration

show database verbose STANDBY-DB-UNIQ-NAME1

validate database ‘STANDBY-DB-UNIQ-NAME1’

validate database ‘PRIMARY-DB-UNIQ-NAME’

Switchover

— Always connect with password for switchover/Failover
connect sys/my-sys-password@PRIMARY-DB-UNIQ-NAME

switchover to STANDBY-DB-UNIQ-NAME1

show configuration verbose

— Return to primary
connect sys/my-sys-password@PRIMARY-DB-UNIQ-NAME

switchover to STANDBY-DB-UNIQ-NAME1 PRIMARY-DB-UNIQ-NAME

show configuration verbose

Failover

FAILOVER TO STANDBY-DB-UNIQ-NAME1

— If flashback enabled, the original primary database can now be configured as a standby.
REINSTATE DATABASE PRIMARY-DB-UNIQ-NAME

Snapshot Standby

— If flashback database is enabled on primary (not mandatory for standby)
dgmgrl
connect sys/my-sys-password@PRIMARY-DB-UNIQ-NAME
CONVERT DATABASE cdb1_stby TO SNAPSHOT STANDBY;
[Work read/write on STANDBY-DB-UNIQ-NAME1]
— When finished working, convert it back to a standby database
CONVERT DATABASE cdb1_stby TO PHYSICAL STANDBY;

Disable/Enable the log transport on the primary

OFF

dgmgrl /
DGMGRL> edit database ‘cbuerpp’ set state=TRANSPORT-OFF;
DGMGRL> show database verbose ‘cbuerpp’
Database – cbuerpp
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
…..

If we look at the db parameter:
SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_state_2 string RESET

ON

DGMGRL> edit database ‘cbuerpp’ set state=TRANSPORT-ON;

Disable/Enable the log apply in the standby database

APPLY-OFF

EDIT DATABASE ‘cbuerps’ SET STATE=’APPLY-OFF’;

APPLY-ON

EDIT DATABASE ‘cbuerps’ SET STATE=’APPLY-ON’;

Other commands

— Change Protection mode
EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

— Change property of a member (see properties with « show database verbose STANDBY-DB-UNIQ-NAME1 »)
edit database STANDBY-DB-UNIQ-NAME1 set property LogXptMode=’SYNC’;
edit database STANDBY-DB-UNIQ-NAME1 set property StandbyFileManagement=’AUTO’;

— Change a parameter (e.g. database verbose)
EDIT DATABASE ‘STNDBY’ SET PROPERTY ‘Enterprise Manager Name’ = ‘STNDBY.MY_DB_DOMAIN’;

EDIT DATABASE ‘MYDB’ SET PROPERTY ‘Enterprise Manager Name’ = ‘MYDB.MY_DB_DOMAIN’;

More information of RAC, in the following article, Oracle cluster components:  Shared vs Dedicated Database components in a RAC architecture. Specific RAC components. The benefits of integrating the Oracle cluster architecture

Author: Vincent Fenoll – Oracle DBA Montréal