Oracle 19c Data Guard sandbox created by DBCA -createDuplicateDB

Here are the commands I use to create a sandbox on Linux with a CDB1 database in a Data Guard configuration. I use the latest (19c) DBCA features to create the Primary and duplicate to the Standby.

I’m doing all in a VM which is a Compute Instance provisioned in the Oracle Cloud. In this example, I have an Oracle Linux 7.6 VM.DenseIO2.24 shape with 320GB RAM and 24 cores but remember that you will not be able to scale up/down so choose according to your credits...

I have 40GB in the / filesystem

OS and filesystem installation

I’ve installed the prerequisites as root (preinstall package, sudo and HugePages — here 200GB out of the 314GB I have):

sudo su
yum -y update
yum -y install -y oracle-database-preinstall-18c
grep "oracle" /etc/sudoers || echo "oracle  ALL=(ALL)       NOPASSWD: ALL" >> /etc/sudoers
echo "vm.nr_hugepages=102400" >> /etc/sysctl.conf
sysctl -p

I’ve attached a 8TB block device:

I create a /u01 filesystem on it for my Oracle installation

sudo iscsiadm -m node -o new -T iqn.2015-12.com.oracleiaas:a9a2...
sudo iscsiadm -m node -o update -T iqn.2015-12.com.oracleiaas:a91...
sudo iscsiadm -m node -T iqn.2015-12.com.oracleiaas:a9a21f4...
mkfs.xfs /dev/oracleoci/oraclevdb
mkdir /u01
mount /dev/oracleoci/oraclevdb /u01
echo "/dev/oracleoci/oraclevdb /u01 xfs defaults,_netdev,nofail 0 0" >> /etc/fstab
mkdir -p /u01/app/oracle/product/DB192
chown -R oracle:dba /u01

Oracle software

First, I install the Oracle Home (unzip V981623–01.zip in /u01/app/oracle/product/DB192 and run runInstaller).

su - oracle
mkdir -p /u01/oradata /u01/fast_recovery_area /u01/app/oracle
unzip -d /u01/app/oracle/product/DB192 V981623-01.zip
cat > /u01/oradata/DB192.rsp <<END
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/DB192
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.rootconfig.executeRootScript=false
oracle.install.db.rootconfig.configMethod=SUDO
oracle.install.db.rootconfig.sudoPath=/bin/sudo
oracle.install.db.rootconfig.sudoUserName=oracle
END
/u01/app/oracle/product/DB192/runInstaller -silent \
 -responseFile /u01/oradata/DB192.rsp

You may see that sudo is not accepted without entering the oracle password, so just in case, you can run it manually:

sudo /u01/app/oracle/product/DB192/root.sh

Create the databases

I add a ‘#’ in front of the text for easy copy/paste of the whole setup.

# Set the environment and create the directories

ORACLE_HOME=/u01/app/oracle/product/DB192
mkdir -p /u01/oradata /u01/fast_recovery_area

# Divide the huge pages to fit equally 4 instances

SGA_MB=$(awk '/Hugepagesize.*kB/{s=$2} /HugePages_Total/ {f=$2} END{print s*f/1024/4} ' /proc/meminfo)

# Here is a script to create the standby logs after the creation of the primary database as this is not done by DBCA (one more than online redo logs and no multiplexing). Mention the tread# or you will have some unexpected warnings with validate database.

cat > /u01/oradata/add_standby_log.sql <<'SQL'
exec for i in (select t,g+rownum g,s from (select thread# t,max(bytes) s,count(*)+1 c from v$log group by thread#),(select rownum n from xmltable('1 to 100')),(select max(group#) g from v$log) where n<=c) loop execute immediate 'alter database add standby logfile thread '||i.t||' group '||i.g||' size '||i.s; end loop;
exit
SQL

# Primary database creation

grep ^CDB1A: /etc/oratab || $ORACLE_HOME/bin/dbca -silent \
 -createDatabase -gdbName CDB1 -sid CDB1A \
 -createAsContainerDatabase true -numberOfPdbs 1 -pdbName PDB1 \
 -sysPassword oracle -systemPassword oracle \
 -pdbAdminPassword oracle \
 -datafileDestination /u01/oradata -useOMF true -storageType FS \
 -recoveryAreaDestination /u01/fast_recovery_area \
 -recoveryAreaSize 10240 -enableArchive true \
 -memoryMgmtType AUTO_SGA -totalMemory ${SGA_MB:=4096} \
 -createListener LISTENER1A:1521 \
 -emConfiguration EMEXPRESS -emExpressPort 5501 \
 -templateName General_Purpose.dbc \
 -databaseType OLTP -sampleSchema true -redoLogFileSize 100 \
 -customScripts /u01/oradata/add_standby_log.sql \
 -initParams \
db_unique_name=CDB1A,dg_broker_start=true,shared_pool_size=600M

# Database duplication for the standby — this is possible from DBCA in 19c:

grep ^CDB1B: /etc/oratab || $ORACLE_HOME/bin/dbca -silent \
 -createDuplicateDB -gdbName CDB1 -sid CDB1B \
 -createAsStandby -dbUniqueName CDB1B -sysPassword oracle \
 -primaryDBConnectionString $(hostname):1521/CDB1A \
 -datafileDestination /u01/oradata -useOMF true -storageType FS \
 -recoveryAreaDestination /u01/fast_recovery_area \
 -recoveryAreaSize 10240 -enableArchive true \
 -createListener LISTENER1B:1522

# fix local listener (I see no reason to resolve it in tnsnames.ora and anyway DBCA fails to update it correctly). Note that, being on the same host, I need two listeners because the same services (the PDB names) are registered by both instances.

. oraenv <<< CDB1A
ORACLE_SID=CDB1A sqlplus / as sysdba <<<"alter system set local_listener='//$(hostname):1521';"
ORACLE_SID=CDB1B sqlplus / as sysdba <<<"alter system set local_listener='//$(hostname):1522';"

# Enable flashback (I’ll use FSFO)

for i in CDB1{B,A} ; do ORACLE_SID=$i rman target / <<<'alter database flashback on;' ; done

# Create a CDB1 service for the primary

ORACLE_SID=CDB1A sqlplus / as sysdba <<'SQL'
exec dbms_service.create_service(service_name=>'CDB1',network_name=>'CDB1'); dbms_service.start_service(service_name=>'CDB1');
create or replace trigger start_role_services
  after db_role_change on database
begin
  for i in (select database_role, open_mode from v$database) loop
  if i.database_role='PRIMARY' then dbms_service.start_service('CDB1'); else dbms_service.start_service('CDB1'); end if;
  end loop;
end;
/
show errors
SQL

# Listener static entries (I remove first the SID_LIST added by DBCA)

sed -ie '/^SID_LIST/,$d' $ORACLE_HOME/network/admin/listener.ora
cat >>$ORACLE_HOME/network/admin/listener.ora <<CAT
SID_LIST_LISTENER1A=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=CDB1A_DGMGRL)(SID_NAME=CDB1A)(ORACLE_HOME=$ORACLE_HOME)))
SID_LIST_LISTENER1B=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=CDB1B_DGMGRL)(SID_NAME=CDB1B)(ORACLE_HOME=$ORACLE_HOME)))
CAT
for i in {1521..1522} ; do lsnrctl reload //$(hostname):$i ; done

# A minimal Data Guard Broker configuration that allows FSFO

cat > /u01/oradata/configure_dgb.sql <<DGB
create configuration CDB1 as primary database is CDB1A connect identifier is '//$(hostname):1521/CDB1A_DGMGRL';
add database CDB1B as connect identifier is '//$(hostname):1522/CDB1B_DGMGRL';
edit database CDB1A set property LogXptMode='SYNC';
edit database CDB1B set property LogXptMode='SYNC';
enable configuration;
edit database CDB1A set property StandbyFileManagement='AUTO';
edit database CDB1B set property StandbyFileManagement='AUTO';
edit database CDB1B set property FastStartFailoverTarget='CDB1A';
edit database CDB1A set property FastStartFailoverTarget='CDB1B';
edit configuration set protection mode as MaxAvailability;
enable fast_start failover observe only;
DGB
dgmgrl sys/oracle </u01/oradata/configure_dgb.sql

# Set ‘Y’ in oratab for dbstart (but you will have to lsnrctl start LISTENER1A and LISTERNER1B first anyway because dbstart starts only the default LISTENER)

sudo sed -ie '/^CDB1[AB]:/s/:N$/:Y/' /etc/oratab

# archivelog deleted on both side without need to backup them (as long as there are no gap)

for i in CDB1{B,A} ; do ORACLE_SID=$i rman target / <<<'configure archivelog deletion policy to applied on all standby;' ; done

# Some verifications and testing two switchovers so that we know it works

for i in {1521..1522} ; do lsnrctl status //$(hostname):$i ; done
grep ^CDB1[AB] /etc/oratab
sleep 15
dgmgrl sys/oracle <<<'show configuration'
dgmgrl sys/oracle <<<'show database verbose CDB1B'
sleep 15
dgmgrl sys/oracle <<<'validate database CDB1B' | grep -EC99 "Ready for Switchover: *Yes" && dgmgrl sys/oracle <<<'switchover to CDB1B'
sleep 15
dgmgrl sys/oracle <<<'validate database CDB1A' | grep -EC99 "Ready for Switchover: *Yes" && dgmgrl sys/oracle <<<'switchover to CDB1A'
dgmgrl sys/oracle <<<'show configuration lag'

# start the observer

(cd /var/tmp && nohup dgmgrl -silent sys/oracle "start observer file is '/var/tmp/CDB1_observer.dat' logfile is '/var/tmp/CDB1_observer.log'")&

However, since 12c we should be able to run it as:

dgmgrl sys/oracle <<<"start observer in background file is '/var/tmp/CDB1_observer.dat' logfile is '/var/tmp/CDB1_observer.log' connect identifier is 'CDB1A' "

Drop the databases

The following will clean everything (drop databases CDB1A and CDB1B and listeners)

exit # this to prevent and eager copy/paste
ORACLE_HOME=/u01/app/oracle/product/DB192
mkdir -p /u01/oradata /u01/fast_recovery_area
for i in $(awk -F: '/^CDB[1-2][A-B]?:/{print $1}' /etc/oratab | sort -r)
do
 $ORACLE_HOME/bin/dbca -silent -deleteDatabase -sourceDB $i \
 -forceArchiveLogDeletion -sysDBAPassword oracle -sysDBAUserName sys
done
for i in {1521..1524} ; do lsnrctl stop //$(hostname):$i ; done
rm -rf /u01/oradata/CDB1* \
       /u01/fast_recovery_area/CDB1* \
       $ORACLE_HOME/network/admin/{listener,tnsnames,sqlnet}.ora \
       /u01/app/oracle/cfgtoollogs/dbca/CDB1{A,B} \
       /u01/app/oracle/diag/rdbms/*/CDB1{A,B} \
       $ORACLE_HOME/dbs/*{A,B}*\
       $ORACLE_HOME/rdbms/log/*{A,B}*\
       $ORACLE_HOME/rdbms/audit/*{A,B}*

I do the same with a CDB2 database and that’s my sandbox for many demos. Something like copy/pasting the previous commands through

sed -e s/CDB1/CDB2/g  s/LISTENER1/LISTENER2/g  s/1521/1523/g s/1522/1524/g s/PDB1/PDB2/g | sh

In summary, DBCA automates the duplicate for standby. It would be nice to get the listeners created properly with static strings, and the Data Guard Broker configuration setup as well. And also standby redo log creation.