Create a 12c physical standby database on ODA X5-2

ODA X5-2 simplifies and speeds up the creation of a 12c database quite considerably with oakcli. You can take advantage of this command by also using it in the creation of physical standby databases as I discovered when I had to setup Dataguard on as many as 5 production and 5 acceptance databases within a very short time.

I used the “oakcli create database …” command to create both primary and standby databases really fast and went on from there to setup a Dataguard Bbroker configuration in max availability mode. Where you would normally duplicate a primary database on to a skeleton standby database that’s itself without any data or redo files and starts up with a pfile, working with 2 fully configured databases is a bit different. You do not have to change a db_unique_name after the RMAN duplicate, which proved to be quite an advantage, and the duplicate itself doesn’t have to address any spfile adaptations because it’s already there. But you may get stuck with some obsolete data and redo files of the original standby database that can fill up the filesystem. However, as long as you remove these files in time, just before the RMAN duplicate, this isn’t much of an issue.

What I did to create 12c primary database ABCPRD1 on one ODA and physical standby database ABCPRD2 on a second ODA follows from here. Nodes on oda1 are oda10 and oda11, nodes on oda2 are oda20 and oda21. The nodes I will use are oda10 and oda20.

-1- Create parameterfile on oda10 and oda20
oakcli create db_config_params -conf abcconf
-- parameters:
-- Database Block Size  : 8192
-- Database Language    : AMERICAN
-- Database Characterset: WE8MSWIN1252
-- Database Territory   : AMERICA
-- Component Language   : English
-- NLS Characterset     : AL16UTF16
file is saved as: /opt/oracle/oak/install/dbconf/abcconf.dbconf

-2- Create database ABCPRD1 on oda10 and ABCPRD2 on oda20
oda10 > oakcli create database -db ABCPRD1 -oh OraDb12102_home1 -params abcconf
oda20 > oakcli create database -db ABCPRD2 -oh OraDb12102_home1 -params abcconf
-- Root  password: ***
-- Oracle  password: ***
-- SYSASM  password - During deployment the SYSASM password is set to 'welcome1 - : ***
-- Database type: OLTP
-- Database Deployment: EE - Enterprise Edition
-- Please select one of the following for Node Number >> 1
-- Keep the data files on FLASH storage: N
-- Database Class: odb-02  (2 cores,16 GB memory)

-3- Setup db_name ABCPRD for both databases... this is a prerequisite for Dataguard
oda10 > sqlplus / as sysdba
oda10 > shutdown immediate;
oda10 > startup mount
oda10 > ! nid TARGET=sys/*** DBNAME=ABCPRD SETNAME=YES
oda10 > Change database name of database ABCPRD1 to ABCPRD? (Y/[N]) => Y
oda10 > exit

oda20 > sqlplus / as sysdba
oda20 > shutdown immediate;
oda20 > startup mount
oda20 > ! nid TARGET=sys/*** DBNAME=ABCPRD SETNAME=YES
oda20 > Change database name of database ABCPRD2 to ABCPRD? (Y/[N]) => Y
oda20 > exit

-4- Set db_name of both databases in their respective spfile as well as ODA cluster,
    and reset the db_unique_name after startup back from ABCPRD to ABCPRD1|ABCPRD2
oda10 > sqlplus / as sysdba    
oda10 > startup mount
oda10 > alter system set db_name=ABCPRD scope=spfile;
oda10 > alter system set service_names=ABCPRD1 scope=spfile;
oda10 > ! srvctl modify database -d ABCPRD1 -n ABCPRD
oda10 > shutdown immediate
oda10 > startup
oda10 > alter system set db_unique_name=ABCPRD1 scope=spfile;
oda10 > shutdown immediate;
oda10 > exit

oda20 > sqlplus / as sysdba    
oda20 > startup mount
oda20 > alter system set db_name=ABCPRD scope=spfile;
oda20 > alter system set service_names=ABCPRD2 scope=spfile;
oda20 > ! srvctl modify database -d ABCPRD2 -n ABCPRD
oda20 > shutdown immediate
oda20 > startup
oda20 > alter system set db_unique_name=ABCPRD2 scope=spfile;
oda20 > shutdown immediate;
oda20 > exit

-5- Startup both databases from the cluster.
oda10 > srvctl start database -d ABCPRD1
oda20 > srvctl start database -d ABCPRD2

Currently, 2 identical configured databases are active with the same db_name, which is a first condition for the following configuration of Dataguard Broker. By just matching the db_name between databases and keeping the db_unique_name as it was, ASM database and diagnostic directory names remain as they are.

Also, the spfile entry in the cluster continues to point to the correct directory and file, as well as the init.ora in $ORACLE_HOME/dbs. Because the standby started with an existing and correctly configured spfile you no longer need to retrieve it from the primary. It simplifies and reduces the RMAN duplicate code to just a one line command, apart from login and channel allocation.

-6- Add Net Service Names for ABCPRD1 and ABCPRD2 to your tnsnames.ora on oda10 and oda20
ABCPRD1_DGB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oda10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ABCPRD1_DGB)
    )
  )

ABCPRD2_DGB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oda20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ABCPRD2_DGB)
    )
  )

-7- Add as a static service to listener.ora on oda10 and oda20
oda10 > SID_LIST_LISTENER =
oda10 >   (SID_LIST =
oda10 >     (SID_DESC =
oda10 >       (GLOBAL_DBNAME = ABCPRD1_DGB)
oda10 >       (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
oda10 >       (SID_NAME = ABCPRD1)
oda10 >     ) 
oda10 >   )        

oda20 > SID_LIST_LISTENER =
oda20 >   (SID_LIST =
oda20 >     (SID_DESC =
oda20 >       (GLOBAL_DBNAME = ABCPRD2_DGB)
oda20 >       (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
oda20 >       (SID_NAME = ABCPRD2)
oda20 >     ) 
oda20 >   )

-8- Restart listener from cluster on oda10 and oda20
oda10 > srvctl stop listener
oda10 > srvctl start listener

oda20 > srvctl stop listener
oda20 > srvctl start listener

-9- Create 4 standby logfiles on oda10 only (1 more than nr. of redologgroups and each with just 1 member)
    The RMAN duplicate takes care of the standby logfiles on oda20, so don't create them there now
oda10 > alter database add standby logfile thread 1 group 4 size 4096M;
oda10 > alter database add standby logfile thread 1 group 5 size 4096M;
oda10 > alter database add standby logfile thread 1 group 6 size 4096M;
oda10 > alter database add standby logfile thread 1 group 7 size 4096M;
oda10 > exit

-10- Start RMAN duplicate from oda20
oda20 > srvctl stop database -d ABCPRD2
oda20 > srvctl start database -d ABCPRD2 -o nomount
oda20 > *****************************************************************************
oda20 > ********* !!! REMOVE EXISTING DATA EN REDO FILES OF ABCPRD2 NOW !!! *********
oda20 > *****************************************************************************
oda20 > rman target sys/***@ABCPRD1 auxiliary sys/***@ABCPRD2
oda20 > .... RMAN> 
oda20 > run {
oda20 > allocate channel d1 type disk;
oda20 > allocate channel d2 type disk;
oda20 > allocate channel d3 type disk;
oda20 > allocate auxiliary channel stby1 type disk;
oda20 > allocate auxiliary channel stby2 type disk;
oda20 > duplicate target database for standby nofilenamecheck from active database;
oda20 > }
oda20 > exit

And there you are… primary database ABCPRD1 in open read-write mode and standby database ABCPRD2 in mount mode. The only thing left to do now is the dataguard broker setup, and activate flashback and force_logging on both databases.

-11- Setup broker files in shared storage (ASM) and start brokers on oda10 and oda20
oda10 > sqlplus / as sysdba
oda10 > alter system set dg_broker_config_file1='/u02/app/oracle/oradata/datastore/.ACFS/snaps/ABCPRD1/ABCPRD1/dr1ABCPRD1.dat' scope=both; 
oda10 > alter system set dg_broker_config_file2='/u02/app/oracle/oradata/datastore/.ACFS/snaps/ABCPRD1/ABCPRD1/dr2ABCPRD1.dat' scope=both;
oda10 > alter system set dg_broker_start=true scope=both;
oda10 > exit

oda20 > sqlplus / as sysdba
oda20 > alter system set dg_broker_config_file1='/u02/app/oracle/oradata/datastore/.ACFS/snaps/ABCPRD2/ABCPRD1/dr1ABCPRD2.dat' scope=both; 
oda20 > alter system set dg_broker_config_file2='/u02/app/oracle/oradata/datastore/.ACFS/snaps/ABCPRD2/ABCPRD1/dr2ABCPRD2.dat' scope=both;
oda20 > alter system set dg_broker_start=true scope=both;
oda20 > exit

-12- Create broker configuration from oda10
oda10 > dgmgrl sys/***
oda10 > create configuration abcprd as primary database is abcprd1 connect identifier is abcprd1_dgb;
oda10 > edit database abcprd1 set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oda10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ABCPRD1_DGB)(INSTANCE_NAME=ABCPRD1)(SERVER=DEDICATED)))';
oda10 > add database abcprd2 as connect identifier is abcprd2_dgb maintained as physical;
oda10 > edit database abcprd2 set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oda20)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ABCPRD2_DGB)(INSTANCE_NAME=ABCPRD2)(SERVER=DEDICATED)))';
oda10 > enable configuration;
oda10 > edit database abcprd2 set state=APPLY-OFF;
oda10 > exit

-13- Enable flashback and force logging on both primary and standby database
oda10 > sqlplus / as sysdba
oda10 > alter database force logging;
oda10 > alter database flashback on;
oda10 > exit

oda20 > sqlplus / as sysdba
oda20 > alter database force logging;
oda20 > alter database flashback on;
oda20 > exit
oda20 > srvctl stop database -d abcprd2
oda20 > srvctl start database -d abcprd2 -o mount

oda10 > srvctl stop database -d abcprd1
oda10 > srvctl start database -d abcprd1

-14- Configure max availability mode from oda10
oda10 > dgmgrl sys/*** 
oda10 > edit database abcprd2 set state=APPLY-ON;
oda10 > edit database abcprd1 set property redoroutes='(LOCAL : abcprd2 SYNC)';
oda10 > edit database abcprd2 set property redoroutes='(LOCAL : abcprd1 SYNC)';
oda10 > edit configuration set protection mode as maxavailability;
oda10 > show database abcprd1 InconsistentProperties;
oda10 > show database abcprd2 InconsistentProperties;
oda10 > show configuration
oda10 > validate database abcprd2;
oda10 > exit

You should now have a valid 12c Max Availability Dataguard configuration, but you better test it thoroughly with
some switchovers and a failover before taking it into production. Have fun!