Create RAC Standby Database Using DBCA [19c Version]

DBCA evolves the same way than most Oracle Components, and since 12.2 version, you can create Standby Databases directly using DBCA.

Before you start, is important to check your version limitation, so for example, 12.2 only allows single instance Databases to be created (see this article by Frank Pachot [https://blog.dbi-services.com/12cr2-dbca-can-create-a-standby-database/]), but that is not longer the case in 18c or like we will see on this post in 19c.

For simplicity, I'm using a Primary Database already in Archivelog mode and ready to have a standby

INSTANCE HOST_NAME               DATABASE_ROLE
-------- ----------------------- ---------------
db192    rac1-node2.raclab.local PRIMARY      
db191    rac1-node1.raclab.local PRIMARY      

The first thing we need to do in the primary site, is to add the new Standby name to tnsnames file:

[oracle@rac1-node1 ~]$ nano /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
[...]

DBCADG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbcadg)
    )
  )

Now in Standby, I always try to setup the environment variables you want to create the standby with

[oracle@rac2-node1 ~]$ . oraenv
ORACLE_SID = [oracle] ? dbcadg1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.3.0/dbhome_1

This is an example of the command we can use to create the standby.
You should be able to use the "tnsnames" names for "primaryDBConnectionString" parameter, but I had some issues and decided to point to a primary node directly

dbca -silent    \
-createDuplicateDB  \
-gdbName db19    \
-sid dbcadg    \
-initParams db_create_file_dest=+DATA,db_recovery_file_dest=+RECO \
-primaryDBConnectionString rac1-node1:1521/db19    \
-sysPassword "Welcome1" \
-createAsStandby    \
-adminManaged    \
-databaseConfigType RAC    \
-nodelist rac2-node1,rac2-node2 -dbUniqueName dbcadg

Lets create the standby

[oracle@rac2-node1 ~]$ dbca -silent    \
> -createDuplicateDB    \
> -gdbName db19    \
> -sid dbcadg    \
> -initParams db_create_file_dest=+DATA,db_recovery_file_dest=+RECO \
> -primaryDBConnectionString rac1-node1:1521/db19    \
> -sysPassword "Welcome1" \
> -createAsStandby    \
> -adminManaged    \
> -databaseConfigType RAC    \
> -nodelist rac2-node1,rac2-node2 -dbUniqueName dbcadg
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/dbcadg/dbcadg.log" for further details.
[oracle@rac2-node1 ~]$  

If you want a more detailed progress (I really recommend it to see what system actually does) check dbca cfgtoollogs directory
(normally "/u01/app/oracle/cfgtoollogs/dbca/DB_NAME/")

[oracle@rac2-node1 ~]$ tail -f /u01/app/oracle/cfgtoollogs/dbca/dbcadg/trace.log_2020-08-14_01-23-33PM
[Thread-611] [ 2020-08-14 13:28:10.974 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=
[Thread-611] [ 2020-08-14 13:28:10.975 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=executing command: SET NEWNAME
[Thread-611] [ 2020-08-14 13:28:10.996 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=
[Thread-611] [ 2020-08-14 13:28:11.032 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=Starting restore at 14-AUG-20
[Thread-611] [ 2020-08-14 13:28:11.042 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=using channel ORA_AUX_DISK_1
[Thread-611] [ 2020-08-14 13:28:11.290 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=
[Thread-611] [ 2020-08-14 13:28:11.753 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=channel ORA_AUX_DISK_1: starting datafile backup set restore
[Thread-611] [ 2020-08-14 13:28:11.754 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=channel ORA_AUX_DISK_1: using network backup set from service rac1-node1:1521/db19

[....]

[Thread-611] [ 2020-08-14 13:31:46.126 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=contents of Memory Script:
[Thread-611] [ 2020-08-14 13:31:46.127 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output={
[Thread-611] [ 2020-08-14 13:31:46.128 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=   sql clone "create spfile from memory";
[Thread-611] [ 2020-08-14 13:31:46.130 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=   shutdown clone immediate;
[Thread-611] [ 2020-08-14 13:31:46.131 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=   startup clone nomount;
[Thread-611] [ 2020-08-14 13:31:46.132 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=   restore clone from service  'rac1-node1:1521/db19' standby controlfile;
[Thread-611] [ 2020-08-14 13:31:46.134 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=}
[Thread-611] [ 2020-08-14 13:31:46.135 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=executing Memory Script
[Thread-611] [ 2020-08-14 13:31:46.136 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=

[....]

[Thread-611] [ 2020-08-14 13:33:19.226 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
[Thread-611] [ 2020-08-14 13:34:34.359 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16
[Thread-611] [ 2020-08-14 13:34:34.428 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=channel ORA_AUX_DISK_1: starting datafile backup set restore
[Thread-611] [ 2020-08-14 13:34:34.429 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=channel ORA_AUX_DISK_1: using network backup set from service rac1-node1:1521/db19
[Thread-611] [ 2020-08-14 13:34:34.677 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
[Thread-611] [ 2020-08-14 13:34:34.678 BST ] [RMANEngine.readSqlOutput:997]  Log RMAN Output=channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA

and voila!, new Standby database created

oracle@rac2-node1 ~$  srvctl status database -d dbcadg -v
Instance dbcadg1 is running on node rac2-node1. Instance status: Open,Readonly.
Instance dbcadg2 is running on node rac2-node2. Instance status: Open,Readonly.
oracle@rac2-node1 ~$ 

Now, few things you need to consider (left the output at the end):

• The standby is register in CRS with primary role (not sure if I missed an option)

• No tnsnames.ora entry is added for the standby

• Unless you create Standby with same name, the DG broker configuration will be created in wrong directory (maybe there is an option to add though..)

• The Data Guard Broker is not started except if it was set in advance to true on primary

• If they are not already in the primary, no Standby Redo are created (but that is also true for RMAN Ducplicate)

Like with everything else, some people pefer RMAN Duplicate, or some other scripts/methods, but I thought it was a nice way to create standby, and what I think is important, is that you have all the options available so you can make the right decisions depending on your circumstances

If your system is simple enough, I bet you can add further options or script to have your system created automatically... like Ansible :)