Loading....

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)
        )
  )
Please follow and like us:

Last Update: June 23, 2020  

March 22, 2017 234 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , , , ,

6 thoughts on “Oracle Database 12c Release 2 New Feature – Create Data Guard Standby Database Using DBCA

  1. Samrat Banik

    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

  2. Hitarth Trivedi

    Excellent!! What about Primary is rac and standby also you want rac what would change?

  3. amit

    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

  4. The Oracle DBA Girl

    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.

Leave a Comment

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

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?