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