19c Data Guard Series Part I using dbca to create a copy

We are currently scripting our Data Guard creation.

 One of the new features in 19c ( and maybe in 18c as well) is to use the dbca to create a standby. In a normal non RAC or non GI installation

I would never make use of this but on RAC it is different as the dbca also takes care of registering the resources in the clusterware, etc

So easiest would be to write a wrapper around the dbca and execute this is in silent mode, something we already did for the creation of CDBs, registering those in OUD etc….

The configuration : Exadata X4 test system with and oracle user and grid user. So here we go what are the steps we needed to follow.

In this example we have a primary db called cdbt01 with db_unique_name c99v02t2 the standby will be called c99v01t1

. setoraenv cdbt011
rman target /
CONFIGURE DEVICE TYPE DISK parallelism 8;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA2/C99V02T1/snapshot_ctl.cf';

The SQL PART

sqlplus / as sysdba

-- on the db
--force logging is enable
--standby redologs
sqlplus / as sysdba
DECLARE
   v_lgp NUMBER := 10;
   v_slgp NUMBER := 100;
   v_lgs NUMBER ; --size in MB for the log groups
   v_dynsql varchar2(4000);
   v_lgrps NUMBER := 5;
   v_standbyloc varchar2(20) := '+RECO2';
   v_param varchar2(10);
 BEGIN
   select force_logging into v_param from v$database;
   if v_param  ='NO' then
     v_dynsql := 'ALTER DATABASE FORCE LOGGING';
     EXECUTE IMMEDIATE  v_dynsql ;
  end if;
   v_dynsql := 'select distinct bytes/1024/1024 from v$log';
   EXECUTE IMMEDIATE  v_dynsql into v_lgs;
   FOR i IN 1..2 LOOP
           FOR j in 0..v_lgrps-1 LOOP
                   v_lgp := (i * 10) + j;
                   v_dynsql := 'ALTER DATABASE ADD logfile thread ' ||  i   || ' group ' ||  v_lgp   || ' size
' || v_lgs || 'M';
                   execute immediate  v_dynsql;
           end loop;
           FOR j in 0..(v_lgrps) LOOP
                 v_slgp := (i* 100) + j ;
                 v_dynsql := 'ALTER DATABASE ADD  standby logfile thread ' || i   || ' group ' ||  v_slgp  ||
chr(39) || v_standbyloc || chr(39) || ' size ' || v_lgs || 'M' ;
    end loop;
   end loop;
end; / 
execute immediate  v_dynsql;
--drop the old redologs created from 1..9

SQLPLUS> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,
ALL_ROLES) db_unique_name=c20v02t1';
SQLPLUS>create user C##REDO_DG identified by  default tablespace users temporary
tablespace temp;
SQLPLUS>grant sysoper to C##REDO_DG;
SQLPLUS>alter system set standby_file_management='AUTO';

Things to do on the standby nodes 

If we have TDE enabled on the 19c Database we must copy over the wallet files to a shared location for our new RAC Standby 

--copy wallet files 
in our case : 
mkdir /encwallet/c99v01t1
copy wallet files to here

Create a temporary directory which you will use temporarily as TNS_ADMIN while using the DBCA create sqlnet.ora file in this directory
with following contents 

NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT) 
NAMES.DEFAULT_DOMAIN = acme.com 
DIAG_ADR_ENABLED=ON

--create  a directory for you temporary TNS_ADMIN 

[orls@exa99adm01vm01 c99v01t1]$mkdir /home/oracle/dbca_dupl

[orls@exa99adm01vm01 c99v01t1]$export TNS_ADMIN=/home/oracle/dbca_dupl
[orls@exa99adm01vm01 c99v01t1]$ echo $TNS_ADMIN/
/home/orls/dbca_dupl/

 We can now start the dbca but we must customize a bit

Make sure you not forget the cluster_interconnects parameter
the custom scripts has both cluster_interconnects of the rac cluster as we can not pass that parameter for both instances dbca invocation time In this example we use one vip to do the copy but passing the scan of the cluster

dbca -silent -createDuplicateDB -gdbName cdbt01.swift.com
-sid cdbt01 -primaryDBConnectionString "exa99db01vm02-
vip.swift.corp:1500/c20v02t1_bck1.swift.com" 
-initParams "wallet_root=/encwallet/phils,db_unique_name=c20v01t1,
audit_file_dest=/u02/app/orls/admin/c99v01t1,sga_max_size=2G,sga_target=2G,
tde_configuration='KEYSTORE_CONFIGURATION=FILE',db_recovery_file_dest=+RECO1,
cluster_interconnects=192.168.200.17:192.168.200.18" 
 -adminManaged -datafileDestination +DATA1
 -nodelist exa99adm01vm01,exa99adm02vm01 
-databaseConfigType RAC -createAsStandby -dbUniqueName c99v01t1 
-sysPassword   -customScripts
/home/orls/dbca_dupl/cluster_inter.sql

Important parameters are the cluster interconnects parameter and of course the TDE_configuration parameters otherwise redo will not be able to be applied.

In the post script following is done

If you omit the cluster_interconnects parameter you get following error

[FATAL] ORA-27528: Transport: RDS required by Engineered System is not enable 

alter system set cluster_interconnects ='192.168.200.17:192.168.200.18' sid='cdbt011' scope=spfile;
alter system set cluster_interconnects ='192.168.200.19:192.168.200.20' sid='cdbt012' scope=spfile;

Afterwards don’t forget the to cleanup the listeners the dbca created under the oracle user What is next is to put this in a DGBROKER configuration in 2020 you do everything with the Broker Read more in part II