News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle Data Guard Broker New Features and Creating a CDB Standby Database via DBCA

  • Posted by Gavin Soorma
  • On July 6, 2019
  • 0 Comments
  • 18c, 19c, 19c new features, data guard, data guard broker, dgmgrl

Oracle 12c Release 2 introduced the ability to execute a DGMGRL command script via the @ command as well as the ability to call host operating system commands via the HOST command.

A number of new Data Guard Broker features have been introduced in Oracle 18c like the DGMGRL commands like VALIDATE DATABASE SPFILE, VALIDATE NETWORK CONFIGURATION, VALIDATE SPFILE,SET ECHO ON , SET TIME ON. In addition the DMON (Data Guard Monitor process) started by the Broker is also visible via the V$DATAGUARD_PROCESS view which was introduced in 12c Release 2.

In Oracle 19c we can export the broker configuration to be stored in a text file via the command EXPORT CONFIGURATION TO which then serves as a backup of the current broker configuration. We can then use the IMPORT CONFIGURATION command which enables us to import the broker configuration metadata that was previously exported via the EXPORT CONFIGURATION command.

This note describes the simple process of creating a Data Guard Physical Standby Database for a Multitenant Container Database using DBCA and using the Data Guard Broker scripting feature to execute a DGMGRL script to automate the creation of the Data Guard Broker configuration. We can see the use of some of the new features described above.

Use the DBCA -createAsStandby option to create a Data Guard physical standby database in a Multitenant database environment.
 
Environment:

  • host02: CDB1 – Primary Container Database
  • host03: CDB1_SB – Standby Container Database

 
Note that an auxiliary connection is automatically being made and the RMAN DUPLICATE command is being silently invoked by DBCA.
 


[oracle@host03 dbs]$ dbca -silent -createDuplicateDB -gdbName CDB1.localdomain -primaryDBConnectionString host02:1521/CDB1.localdomain -sid cdb1 -createAsStandby -dbUniqueName CDB1_SB
Enter SYS user password:

Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/CDB1_SB/CDB1_SB.log" for further details.

[oracle@host03 dbs]$ ls sp*
spfileCDB1.ora

[oracle@host03 dbs]$ export ORACLE_SID=CDB1

[oracle@host03 dbs]$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 28 23:34:46 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED              READ ONLY  NO
3 PDB1               READ ONLY  NO
4 PDB2               READ ONLY  NO
SQL>

 

If we examine the log file we can see that DBCA is internally running the DUPLICATE command to create the standby database.
 

...
...
duplicate target database
for standby
from active database
dorecover
nofilenamecheck
;
}
[progressPage.flowWorker] [ 2019-07-01 15:12:33.539 AWST ] [RMANEngine.done:1663]  Done called
[progressPage.flowWorker] [ 2019-07-01 15:12:33.539 AWST ] [RMANEngine.spoolOff:1549]  Setting spool off = /u01/app/oracle/cfgtoollogs/dbca/CDB1_SB/rmanUtil
[progressPage.flowWorker] [ 2019-07-01 15:12:33.539 AWST ] [RMANEngine.executeImpl:1294]  m_bExecQuery=false
[progressPage.flowWorker] [ 2019-07-01 15:12:33.539 AWST ] [RMANEngine.executeImpl:1302]  Command being written to rman process=exit;
...
...

 

We can execute a host operating system command directly from DGMGRL prompt. Here we examine the contents of the DGMGRL script file which we will use to create the Data Guard configuration.
 


DGMGRL> ! cat cre_data_guard_broker.cfg
Executing operating system command(s):" cat cre_data_guard_broker.cfg"
connect sys/G#vin2407@cdb1;
set time on;
set echo on;
-- #############################################;
-- Creating the Data Guard Broker Configuration;
-- #############################################;
create configuration 'cdb1_dg' as primary database is 'cdb1' connect identifier is 'cdb1';
add database 'cdb1_sb' as connect identifier is 'cdb1_sb';
enable configuration;
host sleep 30;
show database 'cdb1';
show database 'cdb1_sb';
-- #############################################;
-- Checking the Data Guard Broker Configuration;
-- #############################################;
show database 'cdb1' 'InconsistentProperties';
show database 'cdb1' 'InconsistentLogXptProps';
validate network configuration for all;
validate database 'cdb1_sb' spfile;

 

Create the Data Guard Broker configuration
 

DGMGRL> @cre_data_guard_broker.cfg
Connected to "cdb1"
Connected as SYSDBA.
-- #############################################;
-- Creating the Data Guard Broker Configuration;
-- #############################################;
Configuration "cdb1_dg" created with primary database "cdb1"
Database "cdb1_sb" added
Enabled.
Executing operating system command(s):" sleep 30;"

Database - cdb1

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

Database Status:
SUCCESS

Database - cdb1_sb

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: 32.00 KByte/s
Real Time Query:    ON
Instance(s):
cdb1

Database Status:
SUCCESS


-- #############################################;
-- Checking the Data Guard Broker Configuration;
-- #############################################;

INCONSISTENT PROPERTIES
INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

INCONSISTENT LOG TRANSPORT PROPERTIES
INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

Connecting to instance "cdb1" on database "cdb1" ...
Connected to "cdb1"
Checking connectivity from instance "cdb1" on database "cdb1 to instance "cdb1" on database "cdb1_sb"...
Succeeded.
Connecting to instance "cdb1" on database "cdb1_sb" ...
Connected to "CDB1_SB"
Checking connectivity from instance "cdb1" on database "cdb1_sb to instance "cdb1" on database "cdb1"...
Succeeded.

Oracle Clusterware is not configured on database "cdb1".
Connecting to database "cdb1" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host02.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb1_DGMGRL.localdomain)(INSTANCE_NAME=cdb1)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "cdb1".

Oracle Clusterware is not configured on database "cdb1_sb".
Connecting to database "cdb1_sb" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host03.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDB1_SB_DGMGRL.localdomain)(INSTANCE_NAME=cdb1)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "cdb1_sb".

Connecting to "cdb1".
Connected to "cdb1"

Connecting to "cdb1_sb".
Connected to "CDB1_SB"


Parameter settings with different values:

audit_file_dest:
cdb1 (PRIMARY) : /u01/app/oracle/admin/cdb1/adump
cdb1_sb          : /u01/app/oracle/admin/CDB1_SB/adump

 

Export the Data Guard Broker configuration, then remove the configuration followed by an import of the broker configuration.
 

DGMGRL> EXPORT CONFIGURATION TO 'orcl_dgb.exp';
Succeeded.

DGMGRL> remove configuration;
Removed configuration

DGMGRL> show configuration;
ORA-16596: member not part of the Oracle Data Guard broker configuration

Configuration details cannot be determined by DGMGRL

DGMGRL> IMPORT CONFIGURATION FROM 'orcl_dgb.exp';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration - orcl_dg

  Protection Mode: MaxPerformance
  Members:
  orcl_sb - Primary database
    orcl    - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
ENABLED
 2

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *