A! Help: Creating Data Guard Broker for an Existing 12.2 Data Guard Setup with CDB

Wednesday, March 14, 2018

Creating Data Guard Broker for an Existing 12.2 Data Guard Setup with CDB

This post list the steps for adding a data guard broker to an existing 12.2 Data Guard setup. There's an earlier post on setting up data guard broker on 12cR1. The primary database is called prodcdb and standby database is called stbycdb (refer here for other names in DG setup).
12.2 also introduced several new features. Below are few highlights from the DG broker guide.
To increase high availability, you can now set multiple fast-start failover targets and the broker automatically selects which one to use.
You can now start observers on multiple hosts to manage a single Data Guard broker configuration.
The new Data Guard broker property ApplyInstances can be used to engage multiinstance Redo Apply as well as to restrict the number of instances that are involved in redo apply on an Oracle Real Application Clusters (Oracle RAC) database.
The broker now supports migrating a pluggable database (PDB) from one multitenant container database (CDB) to another on the same host. You can migrate a PDB from a primary CDB to another primary CDB or failover a PDB from a standby CDB to a primary CDB.
The DGMGRL commands SHOW DATABASE VERBOSE and SHOW INSTANCE VERBOSE now show the locations of the Oracle alert log file and of the broker log file.
The configuration member to which a connection is made using the CONNECT command is now displayed upon successful completion of the command.
1. DG broker uses static listener service names to connect and start databases. Add static service names to listener.ora file in GI_HOME as grid user. Oracle documentation states as of Oracle Database 12.1.0.2 "a static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used. Broker will use the clusterware to restart an instance". In this case the static _DGMGRL service was created to illustrate the steps that could be used in non-RAC and non Oracle restart setups.
On primary DB

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = prodcdb)
                (SID_NAME = prodcdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )
        (SID_DESC =
                (GLOBAL_DBNAME = prodcdb_DGMGRL)
                (SID_NAME = prodcdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )

)

On standby DB

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = stbycdb)
                (SID_NAME = stbycdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )
        (SID_DESC =
                (GLOBAL_DBNAME = stbycdb_DGMGRL)
                (SID_NAME = stbycdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )

)

Restart the listeners on both primary and standby and view static service registration. On primary

Service "prodcdb_DGMGRL" has 1 instance(s).
  Instance "prodcdb", status UNKNOWN, has 1 handler(s) for this service...

On standby

Service "stbycdb_DGMGRL" has 1 instance(s).
  Instance "stbycdb", status UNKNOWN, has 1 handler(s) for this service...

2. As this is non-RAC database setup, the default locations are chosen for dg broker config files. If this was a RAC configuration then the dg broker config file location must be shared by all instances. On primary the dg broker config files location is shown below.

NAME                           VALUE
------------------------------ ------------------------------------------------------------
dg_broker_config_file1         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr1prodcdb.dat
dg_broker_config_file2         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr2prodcdb.dat

On standby

NAME                           VALUE
------------------------------ ------------------------------------------------------------
dg_broker_config_file1         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr1stbycdb.dat
dg_broker_config_file2         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr2stbycdb.dat

3. Set dg broker to auto start on both primary and standby

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

4. Create the DG broker configuration by adding the primary database.

$ dgmgrl /
Welcome to DGMGRL, type "help" for information.
Connected to "prodcdb"
Connected as SYSDG.
DGMGRL> create configuration dg12c2 as primary database is prodcdb connect identifier is prodcdbtns;
Configuration "dg12c2" created with primary database "prodcdb"

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

4. Add the standby database into the DG broker. Before adding the standby database, the LOG_ARCHIVE_DEST_n value used for redo transport must be cleared on on both primary and standby. Adding of the standby DB would fail without clearing the LOG_ARCHIVE_DEST_n value

DGMGRL> add database stbycdb as connect identifier is stbytns;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

Reason for this has been explained in the previous 12cR1 dg broker post and MOS 1582179.1. Once log_archive_dest is cleared, standby DB addition works. In this DG setup, log_archive_dest_2 is used for redo transport. Reset it on both primary and standby

SQL> alter system reset log_archive_dest_2 scope=both;

System altered.

Add the standby database

DGMGRL> add database stbycdb as connect identifier is stbytns;
Database "stbycdb" added

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

5. Enable the configuration

DGMGRL>  enable configuration;

Enabled.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 168 seconds ago)

6. At this point the DG broker configuration is complete. But it is good to check out the database status, inconsistent properties and validate the databases.

DGMGRL> show database prodcdb

Database - prodcdb

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    prodcdb

Database Status:
SUCCESS

DGMGRL> show database stbycdb

Database - stbycdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 2.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stbycdb

Database Status:
SUCCESS

If the verbose option is used then as stated in new features section log files are shown

DGMGRL> show database verbose prodcdb
...
  Log file locations:
    Alert log               : /opt/app/oracle/diag/rdbms/prodcdb/prodcdb/trace/alert_prodcdb.log
    Data Guard Broker log   : /opt/app/oracle/diag/rdbms/prodcdb/prodcdb/trace/drcprodcdb.log

Database Status:
SUCCESS

Check inconsistent properties

DGMGRL> show database prodcdb inconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

 DGMGRL>  show database stbycdb inconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

Validate the databases

 DGMGRL> validate database prodcdb

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    prodcdb:  YES

DGMGRL> validate database stbycdb

  Database Role:     Physical standby database
  Primary Database:  prodcdb

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    prodcdb:  YES
    stbycdb:  YES

7. As part of the DG setup, if possible carry out a switchover to check if DG broker can bring up the databases without any issue.

DGMGRL> show configuration when primary is stbycdb

Configuration when stbycdb is primary - dg12c2

  Members:
  stbycdb - Primary database
    prodcdb - Physical standby database

Connect with password to dgmgrl and perform the switchover

$ dgmgrl sys/prodcdbdb

DGMGRL> switchover to stbycdb;
Performing switchover NOW, please wait...
Operation requires a connection to database "stbycdb"
Connecting ...
Connected to "stbycdb"
Connected as SYSDBA.
New primary database "stbycdb" is opening...
Oracle Clusterware is restarting database "prodcdb" ...
Switchover succeeded, new primary is "stbycdb"

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  stbycdb - Primary database
    prodcdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 18 seconds ago)

On the new primary (stbycdb) the PDBs will be in read write mode

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PDBAPP1                        READ WRITE NO

On the new standby (prodcdb) PDBs will be in mount mode

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         5 PDBAPP1                        MOUNTED

Switchover back to original primary

DGMGRL> switchover to prodcdb
Performing switchover NOW, please wait...
Operation requires a connection to database "prodcdb"
Connecting ...
Connected to "prodcdb"
Connected as SYSDBA.
New primary database "prodcdb" is opening...
Oracle Clusterware is restarting database "stbycdb" ...
Switchover succeeded, new primary is "prodcdb"

This conclude the setting up of DG broker for an existing 12.2 data guard setup with CDB. Below is an additional step, showing how to increase the protection mode to maximum availability using DG Broker.

Changing Protection Mode to Maximum Availability
The current protection mode is maximum performance with redo transport mode set to ASYNC.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database prodcdb LogXptMode
  LogXptMode = 'ASYNC'

DGMGRL> show database stbycdb LogXptMode
  LogXptMode = 'ASYNC'

To change the protection mode set the redo transport to SYNC and upgrade the protection mode

DGMGRL> edit database prodcdb set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL>  edit database stbycdb set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL> show database prodcdb LogXptMode
  LogXptMode = 'SYNC'

DGMGRL> show database stbycdb LogXptMode
  LogXptMode = 'SYNC'

  
DGMGRL>  EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 29 seconds ago)

A! Help

Labels

Wednesday, March 14, 2018

Creating Data Guard Broker for an Existing 12.2 Data Guard Setup with CDB

About Me

Downloads

Quick Response

Popular

Blog Archive

Total Pageviews

Followers

Oracle Documentation