Oracle 19c Data Guard sandbox created by DBCA -createDuplicateDB

OS and filesystem installation

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

su - oraclemkdir -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
sudo /u01/app/oracle/product/DB192/root.sh

Create the databases

ORACLE_HOME=/u01/app/oracle/product/DB192
mkdir -p /u01/oradata /u01/fast_recovery_area
SGA_MB=$(awk '/Hugepagesize.*kB/{s=$2} /HugePages_Total/ {f=$2} END{print s*f/1024/4} ' /proc/meminfo)
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
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
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
. 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';"
for i in CDB1{B,A} ; do ORACLE_SID=$i rman target / <<<'alter database flashback on;' ; done
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
sed -ie '/^SID_LIST/,$d' $ORACLE_HOME/network/admin/listener.oracat >>$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
cat > /u01/oradata/configure_dgb.sql <<DGBcreate 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
sudo sed -ie '/^CDB1[AB]:/s/:N$/:Y/' /etc/oratab
for i in CDB1{B,A} ; do ORACLE_SID=$i rman target / <<<'configure archivelog deletion policy to applied on all standby;' ; done
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'
(cd /var/tmp && nohup dgmgrl -silent sys/oracle "start observer file is '/var/tmp/CDB1_observer.dat' logfile is '/var/tmp/CDB1_observer.log'")&
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

exit # this to prevent and eager copy/pasteORACLE_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 ; donerm -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}*
sed -e s/CDB1/CDB2/g  s/LISTENER1/LISTENER2/g  s/1521/1523/g s/1522/1524/g s/PDB1/PDB2/g | sh

--

--

--

Developer Advocate at Yugabyte, Open Source distributed SQL database 🚀 Also Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

SVG Introduction and Creating a custom SVG with Adobe Photoshop and Illustrator

Early praise for Pragmatic Unit Testing in Java 8 with JUnit

4.4 Passing Parameters to Closures

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Franck Pachot

Franck Pachot

Developer Advocate at Yugabyte, Open Source distributed SQL database 🚀 Also Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member

More from Medium

YugabyteDB on Jelastic in the Hidora cloud 🇨🇭

Install Cassandra on Mac OS

ksqlDB —real-time SQL magic in the cybersecurity scenario— part 1

Big Data Platform Migration Form EMR To EMR on EKS — Success Story