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

Oracle Database 12c Release 2 New Feature – Create Data Guard Standby Database Using DBCA

  • Posted by Gavin Soorma
  • On March 22, 2017
  • 6 Comments
  • 12.2.0.1, 12c new feature, 12c Release 2, data guard, dbca, High Availability, standby database

One of the real nice new features in Oracle 12c Release 2 (12.2.0.1) is the ability to create an Oracle Data Guard Standby Database using DBCA (Database Configuration Assistant). This really does simplify the process of creating a standby database as well and automates a number of steps in the creation process which were earlier manually performed.

In this example we will see how a 12.2.0.1 Data Guard environment is created via DBCA and then Data Guard Broker (DGMGRL).

The source database is called salesdb and the standby database DB_UNIQUE_NAME will be salesdb_sb.

Primary database host name is host01 and the Standby database host name is host02.

The syntax is:

dbca -createDuplicateDB 
    -gdbName global_database_name 
    -primaryDBConnectionString easy_connect_string_to_primary
    -sid database_system_identifier
    [-createAsStandby 
        [-dbUniqueName db_unique_name_for_standby]]

We will run the command from the standby host host02 as shown below.
 

[oracle@host02 ~]$ dbca -silent -createDuplicateDB -gdbName salesdb -primaryDBConnectionString host01:1521/salesdb -sid salesdb -createAsStandby -dbUniqueName salesdb_sb
Enter SYS user password:
Listener config step
33% complete
Auxiliary instance creation
66% complete
RMAN duplicate
100% complete
Look at the log file "/u02/app/oracle/cfgtoollogs/dbca/salesdb_sb/salesdb.log" for further details.

Connect to the Standby Database and verify the role of the database
 
dg1

 

Note that the SPFILE and Password File for the Standby Database has been automatically created

[oracle@host02 dbs]$ ls -l sp*
-rw-r-----. 1 oracle dba 5632 Mar 22 09:40 spfilesalesdb.ora

[oracle@host02 dbs]$ ls -l ora*
-rw-r-----. 1 oracle dba 3584 Mar 17 14:38 orapwsalesdb

 

Add the required entries to the tnsnames.ora file

dg2

Continue with the Data Guard Standby Database creation using the Data Guard Broker
 

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@host01 archivelog]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Mar 17 14:47:27 2017

connect /
Connected to "salesdb"
Connected as SYSDG.
DGMGRL> create configuration 'salesdb_dg'
> as primary database is 'salesdb'
> connect identifier is 'salesdb';
Configuration "salesdb_dg" created with primary database "salesdb"

DGMGRL> add database 'salesdb_sb' as connect identifier is 'salesdb_sb';
Database "salesdb_sb" added
DGMGRL> enable configuration;
Enabled.

 

Create the Standby Redo Log Files on the primary database

 

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u03/app/oradata/salesdb/redo03.log
/u03/app/oradata/salesdb/redo02.log
/u03/app/oradata/salesdb/redo01.log

SQL> select bytes/1048576 from v$log;

BYTES/1048576
-------------
     200
     200
     200


SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo1.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo2.log' size 200m;

Database altered.


SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo3.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo4.log' size 200m;

Database altered.

 
Create the Standby Redo Log Files on the standby database

 

DGMGRL> connect /
Connected to "salesdb"
Connected as SYSDG.

DGMGRL> edit database 'salesdb_sb' set state='APPLY-OFF';
Succeeded.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size          8619984 bytes
Variable Size           436209712 bytes
Database Buffers   721420288 bytes
Redo Buffers              8155136 bytes
Database mounted.

SQL>  alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo1.log' size 200m;

Database altered.


SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo2.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo3.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo4.log' size 200m;

Database altered.

SQL> alter database open;

Database altered.

SQL>

 
Verify the Data Guard Configuration
 

DGMGRL> edit database 'salesdb_sb' set state='APPLY-ON';
Succeeded.


DGMGRL> show configuration;

Configuration - salesdb_dg

 Protection Mode: MaxPerformance

 salesdb    - Primary database
   salesdb_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 8 seconds ago)

 
Set the property StaticConnectIdentifier to prevent errors during switchover operations
 

Edit database ‘salesdb’ set property StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=salesdb_DGMGRL)(INSTANCE_NAME=salesdb)(SERVER=DEDICATED)))';
Edit database ‘salesdb_sb’ set property StaticConnectIdentifier=StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host02.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=salesdb_sb_DGMGRL)(INSTANCE_NAME=salesdb)(SERVER=DEDICATED)))';

Edit listener.ora on primary database host and add the lines shown below. Reload the listener.
 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = salesdb_DGMGRL)
      (SID_NAME = salesdb)
        )
  )

 
Edit listener.ora on standby database host and add the lines shown below. Reload the listener.
 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = salesdb_sb_DGMGRL)
      (SID_NAME = salesdb)
        )
  )
 6

6 Comments

Samrat Banik
  • Mar 22 2017
Thanks Gavin for this wonderful post explaining this nice new feature of 12.2. One doubt I have: Say I have two ASM DGs on primary named +DATA_PRIM and +FRA_PRIM and the corresponding DGs on DR site are named +DATA_SB and +FRA_SB. So basically, we need to map the files with *_file_name_convert. If we take the above approach, where are we specifying this? Do we need to have the init.ora configured beforehand itself before we initiate the DBCA and in that init file we need to specify these type of convert parameters? Thanks Samrat
Abhisek
  • Mar 23 2017
Thanks buddy, it's very helpful.
Hitarth Trivedi
  • Apr 15 2017
Excellent!! What about Primary is rac and standby also you want rac what would change?
amit
  • May 4 2017
hi gaving , first of all i would like to thank you for providing awesome knowledge, i have learnt lots from your blog. today i tried to implement new feature of 12cR2 creating standby using DBCA. all looks good but everytime am getting stuck in between, beloaw are the detailed problem description [oracle@db1 admin]$ dbca -silent -createDuplicateDB -gdbName prd -primaryDBConnectionString 192.168.198.21:1521/prd -sid prdstd -sysPassword Sharma12$ -createAsStandby -dbUniqueName prdstd Listener config step 33% complete Auxiliary instance creation 66% complete RMAN duplicate DBCA Operation failed. Look at the log file "/opt/app/cfgtoollogs/dbca/prdstd/prd4.log" for further details. below are the log file details. [oracle@db1 admin]$ cat /opt/app/cfgtoollogs/dbca/prdstd/prd3.log [ 2017-05-03 21:36:17.991 IST ] Listener config step DBCA_PROGRESS : 33% [ 2017-05-03 21:36:18.769 IST ] Auxiliary instance creation DBCA_PROGRESS : 66% [ 2017-05-03 21:36:29.178 IST ] RMAN duplicate [ 2017-05-03 21:39:57.587 IST ] echo set off RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting Duplicate Db at 03-MAY-17 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=35 device type=DISK current log archived contents of Memory Script: { backup as copy reuse targetfile '/opt/app/oracle/product/12cR2/dbs/orapwprd' auxiliary format '/opt/app/oracle/product/12cR2/dbs/orapwprdstd' ; restore clone from service '192.168.198.21:1521/prd' spfile to '/opt/app/oracle/product/12cR2/dbs/spfileprdstd.ora'; sql clone "alter system set spfile= ''/opt/app/oracle/product/12cR2/dbs/spfileprdstd.ora''"; } executing Memory Script Starting backup at 03-MAY-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=50 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 05/03/2017 21:39:57 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/03/2017 21:39:57 ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified ORA-17629: Cannot connect to the remote database server RMAN> echo set on my listener.ora getting created at standby side with strange numbers everytime, SID_LIST_LISTENER20170503213556 = (SID_LIST = (SID_DESC = (SID_NAME = prdstd) ) ) LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521)) ) LISTENER20170503213556 = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1522)) ) kindly help Gavin to resolve the issue
The Oracle DBA Girl
  • Sep 8 2017
Oh wow...I didn't know Oracle had this feature, I'm about to load up Virtual Box now and test this out. Extremely descriptive post. Will let you know how this works out this weekend.
S. Prabhu
  • Oct 25 2017
Awe some Gavin. Even the switchover worked as expected. You Rock always :-)

Leave Reply

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