I am not a certified Database/APPS DBA. I wanted to develop a shell/bash script or set of scripts that could duplicate the Oracle EBS PRODUCTION database on demand on a test instance. I thought of doing it as a single script, step by step and later decided to go with multiple scripts to narrow down the damages/or better error correction. Please note, you cannot just copy and start using these scripts at your environment already! You may have to modify many of the parameters & kindly be reminded that, these scripts were developed for an environment that was being cloned repeatedly. Remember to run the main script as “root”
Environment: OS: Oracle Linux 7, Oracle EBS R12 12.2.10, Oracle database 19c, multi-tenant. Approximate size 700GB
Let us create a parameter file for the duplication of EBS database on the TEST instance. The hostname and few other details will be automatically picked up by the shell scripts, however, few other details are provided by the parameter file. This approach gives me greater flexibility to utilize the scripts with different hostnames and contexts. I call this parameter file as “restoreparam.txt”. If you are planning to change the name, you have to replace it in each and every other bash script that is used within the main script.
#Oracle base
s_target_base=/u01/test/
# Source CDB sid
s_source_cdbSid=PRODCDB
# Target CDB sid
s_target_cdbSid=DEVPCDB
# Source EBS database sid (PDB)
s_source_dbSid=PROD
# Target EBS database sid (PDB)
s_target_dbSid=DEVP
# We have database files for both CBD and EBS kept in different paths
# Defining these paths and modifying the RMAN run instructions are crucial for successful restoration and cloning.
s_source_data_path_1=/u01/prod/oradata/PRODCDB/
s_source_data_path_2=/u01/prod/PROD/db/apps_st/data/
# Target data paths
s_data_path_1=/u01/test/oradata/DEVPCDB/
s_data_path_2=/u01/test/DEVP/db/apps_st/data/
# Target PDB Service requires it's own listener information
s_listener_port=1526
# Oracle and Appl manager OS level users
s_db_user=oracle
s_appl_manager=applmgr
# EBS user, by default it is "apps"
s_apps_user=apps
s_apps_pass=********
# Oracle database system account password
s_system_pass=*******
# Path where RMAN backup chunks are kept
s_rman_path=/u04/RMAN/DAILYBKP
# Path where this file be created.
s_rman_param_path=/u04/RMAN/RESTORE
# Target system Oracle inventory path
s_ora_invenoty_path=/u01/test/oraInventory
# Oracle inventory entry identifier for the database. Using this string, entry for the database will be
# deleted from the inventory file. So don't make mistakes
s_ora_inv_dbString=DEVP_DB__u01_test_DEVP_db_tech_st_19_0_0
You can’t make mistakes with the above parameter file. Now we will create the main shell script “doclone.sh”
#!/bin/sh
: <<'END'
This script & associated must be executed as root
This script is meant for single instances.
This script expects both ORACLE, APPLMGR users bash profiles sourcing environment files.
This script was last tested against Oracle EBS R12 12.2.10/19c
Last modified on: 21st October 2024
Author: Rajesh Thampi
License: Public
END
me=$(basename "$0")
parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
ORACLE_USER=`grep 's_db_user' $parameter_file | cut -d "=" -f2`
APPS_USER=`grep 's_appl_manager' $parameter_file | cut -d "=" -f2`
db_path_1=`grep 's_data_path_1' $parameter_file | cut -d "=" -f2`
db_path_2=`grep 's_data_path_2' $parameter_file | cut -d "=" -f2`
rman_path=`grep 's_rman_path' $parameter_file | cut -d "=" -f2`
ORACLE_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')
ORACLE_SID=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_SID"')
ORACLE_CONTEXT=$(su - ${APPS_USER} -c 'echo "$CONTEXT_NAME"')
APPS_SCRIPTS_HOME=$(su - ${APPS_USER} -c 'echo "$RUN_BASE/inst/apps/$CONTEXT_NAME/admin/scripts"')
DB_SCRIPTS_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$ORACLE_CONTEXT
# We'll stop the application and database instances now.
echo "We'll shutdown the EBS instace now"
sh startstopebsr12.sh stop
if [ $? -eq 0 ]; then
echo "We'll shutdown the EBS instace now"
sleep 60
else
echo "There was an error shutting down the instance."
exit 1
fi
# As we are cloning the instance, we can kill all ORACLE & APPLMGR processes immediately
pkill -9 -u ${APPS_USER}
pkill -9 -u ${ORACLE_USER}
echo "All services for ORACLE, APPLMGR are terminated now"
# remove the datafiles from data tops now
rm -rf ${db_path_1}*
rm -rf ${db_path_2}*
echo "All data files are removed from the data tops now"
#change of the ownership of the rman chunks now
chown -R ${ORACLE_USER}:oinstall ${rman_path}
sleep 10
# Start the database as not mounted
sh startdbnomount.sh
if [ $? -eq 0 ]; then
echo "Database started nomount"
else
echo "There were some errors starting the database in the nomount status"
exit 1
fi
# Start RMAN Restore
sleep 10
echo "Starting RMAN restore processes now, this could take many hours based on the current size of the database"
sh dormanrestore.sh
if [ $? -eq 0 ]; then
echo "RMAN restore completed"
else
echo "There were some errors starting the database in the nomount status"
exit 1
fi
sleep 10
# Startup the database and disable archive logging
sh startdbmount.sh
if [ $? -eq 0 ]; then
echo "Starting database and disabling archivelog. Sleep 15 seconds"
else
exit 1
fi
sleep 15
# We will create a backup for the oracle inventory file and remove the database entry before running the adcfgclone
sh dofirstclone.sh
if [ $? -eq 0 ]; then
echo "Initial Cloning completed. Sleep 15 seconds"
else
exit 1
fi
sleep 15
# Setup the PDB and related services now
sh dodbsetup.sh
if [ $? -eq 0 ]; then
echo "PDB recreated and all services are restarted, will sleep 15 seconds"
else
exit 1
fi
sleep 15
# We will setup the UTL files and run the adcfgclone
sh doutlfile.sh
if [ $? -eq 0 ]; then
echo "UTL directories set, final cloning completed. Will sleep 15 seconds"
else
exit 1
fi
sleep 15
# Finally we will run the autoconfig on database and both application file systems
sh doautoconfig.sh
if [ $? -eq 0 ]; then
echo "All done. You can try to restart the applicatio now. Sleeping 10"
else
exit 1
fi
Now we will create other shells scripts that are called from the main script and I will try to explain them briefly as much of them will be pretty familiar for a seasoned DBA. Script “startstopebsr12.sh” accepts one input value, either “start” or “stop”. This script is used to stop the instance from the main script and not parameterized because I use it for multiple contexts.
#!/bin/sh
: <<'END'
This script is meant for single instances.
This script expects both ORACLE, APPLMGR users bash profiles sourcing environment files.
This script was last tested against Oracle EBS R12 12.2.10/19c
Last modified on: 2nd October 2024
Author: Rajesh Thampi
License: Public
END
# I will not be using the clone parameter file as this script is multiple places in different contexts.
# If you want to use it exclusively for the cloning purpose, please refer it here.
me=$(basename "$0")
ORACLE_SID=$(su - oracle -c 'echo "$ORACLE_SID"')
APPS_ORACLE_CONTEXT=$(su - applmgr -c 'echo "$CONTEXT_NAME"')
APPS_SCRIPTS_HOME=$(su - applmgr -c 'echo "$RUN_BASE/inst/apps/$CONTEXT_NAME/admin/scripts"')
DB_SCRIPTS_HOME=$(su - oracle -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$APPS_ORACLE_CONTEXT
if [[ -z $1 ]]; then
echo "No parameter was passed"
exit 1
else
if [[ "$1" == "start" ]]; then
echo "All Oracle EBS R12 Services will be started now."
su - oracle -c "sh $DB_SCRIPTS_HOME/adcdbctl.sh start;"
if [ $? -ne 0 ]; then
echo "Couldn't start the database services successfully. Aborting"
exit 1
else
su - oracle -c "sh $DB_SCRIPTS_HOME/adcdblnctl.sh start $ORACLE_SID;"
fi
if [ $? -ne 0 ]; then
echo "Couldn't start the listener services successfully. Aborting"
exit 1
else
su - applmgr -c "cd $APPS_SCRIPTS_HOME;{ echo apps; echo apps; echo welcome123; } | adstrtal.sh;"
fi
if [ $? -ne 0 ]; then
echo "Couldn't start the Application services successfully. Check log files for errors and try again"
else
echo "All EBS Services were successfully started."
fi
elif [[ "$1" == "stop" ]]; then
echo "All Oracle EBS R12 Services will be stopped now."
su - applmgr -c "cd $APPS_SCRIPTS_HOME;{ echo apps; echo apps; echo welcome123; } | adstpall.sh;"
if [ $? -ne 0 ]; then
echo "Couldn't stop the application services successfully. Aborting"
exit 1
else
su - oracle -c "sh $DB_SCRIPTS_HOME/adcdblnctl.sh stop $ORACLE_SID;"
fi
if [ $? -ne 0 ]; then
echo "Couldn't stop the Listener services successfully. Aborting"
exit 1
else
su - oracle -c "sh $DB_SCRIPTS_HOME/adcdbctl.sh stop immediate;"
fi
if [ $? -ne 0 ]; then
echo "Couldn't stop the Database services successfully. Check log files for errors and try again."
exit 1
else
echo "All EBS Services were successfully stopped."
fi
else
echo "Syntax: sh $me start/stop"
fi
fi
The main script will continue once after both application and database instances are stopped. data files from the data paths will be deleted & ownership of the RMAN files will be set for Oracle user. Oracle services will restarted and the database will not be mounted using the script “startdbnomount.sh”. I didn’t feel parameterization was necessary for this script ;)
#!/bin/sh
su - oracle -c "sqlplus / as sysdba <<EOF
startup nomount;
quit;
EOF"
Now, the main script will call “dormanrestore.sh” script, which is one of the most critical scripts, duplicating the PRODUCTION database. You have to carefully adjust the below to ensure that your database is duplicated successfully. Please give special attentions to comments provided inside the script.
#!/bin/sh
parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
rman_param_path=$(grep 's_rman_param_path' $parameter_file | cut -d "=" -f2)
target_cdbsid=$(grep 's_target_cdbSid' $parameter_file | cut -d "=" -f2)
rman_path=$(grep 's_rman_path' $parameter_file | cut -d "=" -f2)
s_data_path_1=$(grep 's_source_data_path_1' $parameter_file | cut -d "=" -f2)
s_data_path_2=$(grep 's_source_data_path_2' $parameter_file | cut -d "=" -f2)
t_data_path_1=$(grep 's_data_path_1' $parameter_file | cut -d "=" -f2)
t_data_path_2=$(grep 's_data_path_2' $parameter_file | cut -d "=" -f2)
LOGFILE=${rman_param_path}"/full_restore_`date +%d%b%y_%H%M%S`.log"
# Make sure that db_file_name_convert & number of channels are adjusted based on your specific environments.
# Keeping multiple channels open could directly affect the restoration and overall performance of the process.
# Adjust the number of redo log files, size & path based on your specific requirements.
echo "RMAN restore will start now"
su - oracle <<EOF
rman auxiliary / log='$LOGFILE' <<RMN
run
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE disk;
duplicate database to "${target_cdbsid}" backup location '${rman_path}' nofilenamecheck
db_file_name_convert=('${s_data_path_1}','${t_data_path_1}','${s_data_path_2}','${t_data_path_2}')
LOGFILE
GROUP 1 (
'${t_data_path_1}redo01a.log',
'${t_data_path_1}redo01b.log'
) SIZE 1200M ,
GROUP 2 (
'${t_data_path_1}redo02a.log',
'${t_data_path_1}redo02b.log'
) SIZE 1200M ,
GROUP 3 (
'${t_data_path_1}redo03a.log',
'${t_data_path_1}redo03b.log'
) SIZE 1200M ,
GROUP 4 (
'${t_data_path_1}redo04a.log',
'${t_data_path_1}redo04b.log'
) SIZE 1200M ;
}
RMN
EOF
RMAN duplication could take hours based on multiple factors. Size of the database, channels and hardware efficiency. The log file will bear the data and time stamp, keep monitoring it for the progress. Once RMAN successfully completes, main script will call the script “startdbmount.sh”. This script will start the database and mount it, without opening it. The archive logging will be disabled during this stop for the duplicated database.
#!/bin/sh
su - oracle -c "sqlplus / as sysdba <<EOF
shutdown immediate;
startup mount;
alter database noarchivelog;
shutdown immediate;
quit;
EOF"
Now the main script will call “dofirstclone.sh”, that will do the initial clone of the database. A number of parameters are referenced from the parameter file, hence make sure that your parameter is constructed with utmost attention.
#!/bin/sh
parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
rman_param_path=$(grep 's_rman_param_path' $parameter_file | cut -d "=" -f2)
ora_inventory_path=$(grep 's_ora_invenoty_path' $parameter_file | cut -d "=" -f2)
ora_inv_dbString=$(grep 's_ora_inv_dbString' $parameter_file | cut -d "=" -f2)
ORACLE_USER=$(grep 's_db_user' $parameter_file | cut -d "=" -f2)
APPS_USER=$(grep 's_appl_manager' $parameter_file | cut -d "=" -f2)
ORACLE_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')
ORACLE_SID=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_SID"')
ORACLE_CONTEXT=$(su - ${APPS_USER} -c 'echo "$CONTEXT_NAME"')
APPS_SCRIPTS_HOME=$(su - ${APPS_USER} -c 'echo "$RUN_BASE/inst/apps/$CONTEXT_NAME/admin/scripts"')
DB_SCRIPTS_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$ORACLE_CONTEXT
# Application user password
APPS_PASSWD=$(grep 's_apps_pass' $parameter_file | cut -d "=" -f2)
su - ${ORACLE_USER} <<EOF
cd ${ora_inventory_path}/ContentsXML
cp -f inventory.xml inventory_backup.xml
awk '!/'${ora_inv_dbString}'/' inventory.xml > tmpfile.xml && mv tmpfile.xml inventory.xml
EOF
su - ${ORACLE_USER} <<EOF
cd $ORACLE_HOME/appsutil/clone/bin
{ echo $APPS_PASSWD; } | perl adcfgclone.pl dbTechStack $ORACLE_HOME/appsutil/${ORACLE_CONTEXT}.xml
EOF
echo "Cloning successfully completed, will sleep 10 seconds"
sleep 10
su - ${ORACLE_USER} <<EOF
cd $ORACLE_HOME/appsutil/install/$ORACLE_CONTEXT
sqlplus / as sysdba;
startup;
@adupdlib.sql so;
shutdown immediate;
EOF
echo "Database shutdown completed. Sleep 15 seconds"
sleep 15
su - ${ORACLE_USER} <<EOF
sqlplus / as sysdba;
startup;
quit;
EOF
echo "Database restart completed."
Initial cloning should happen without throwing errors. Once the initial cloning done, next script “dodbsetup.sh” setup will drop the PDB from PRODUCTION and recreate the TEST PDB instance, rename the services, reconfigure the local listener for PDB etc.
#!/bin/sh
ORACLE_SID=$(su - oracle -c 'echo "$ORACLE_SID"')
ORACLE_HOME=$(su - oracle -c 'echo "$ORACLE_HOME"')
parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
SOURCE_SID=`grep 's_source_dbSid' $parameter_file | cut -d "=" -f2`
TARGET_SID=`grep 's_target_dbSid' $parameter_file | cut -d "=" -f2`
ORACLE_BASE=`grep 's_target_base' $parameter_file | cut -d "=" -f2`
ORACLE_USER=`grep 's_db_user' $parameter_file | cut -d "=" -f2`
LISTENER_PORT=`grep 's_listener_port' $parameter_file | cut -d "=" -f2`
TARGET_LISTNER=$HOSTNAME:$LISTENER_PORT
source_ebs_patch=$SOURCE_SID"_ebs_patch"
target_ebs_patch=$TARGET_SID"_ebs_patch"
su - ${ORACLE_USER} <<EOF
sqlplus / as sysdba
alter pluggable database ${SOURCE_SID} unplug into '${ORACLE_HOME}/dbs/${SOURCE_SID}.xml';
drop pluggable database ${SOURCE_SID};
create pluggable database ${TARGET_SID} using '${ORACLE_HOME}/dbs/${SOURCE_SID}.xml' NOCOPY SERVICE_NAME_CONVERT=('ebs_${SOURCE_SID}','ebs_${TARGET_SID}','${SOURCE_SID}_ebs_patch', '${TARGET_SID}_ebs_patch');
alter pluggable database all open read write services=all;
alter pluggable database all save state;
alter session set container=${TARGET_SID};
alter system set local_listener='${TARGET_LISTNER}' scope=spfile;
shutdown immediate;
startup;
exit;
EOF
echo "Finished setting up PDB. Sleep 15 seconds after shutdown"
sleep 15
Creation of the PDB and rest shouldn’t take too much time. Now it is time for the final cloning, that will setup the UTL directories and more. Let us see the “doutlfile.sh” now. Please note, prior executing the below script, you must create a copy of PDBNAME_utlfiledir.txt that is available in $ORACLE_HOME/dbs/ path to PDBNAME_utlfiledir.txt.backup (example: DEVP_utlfiledir.txt to DEVP_utlfiledir.txt.backup). This backup will be used for replacing the UTL file directory paths setup within this cycle.
#!/bin/sh
parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
target_dbsid=$(grep 's_target_dbSid' $parameter_file | cut -d "=" -f2)
ORACLE_USER=`grep 's_db_user' $parameter_file | cut -d "=" -f2`
APPS_USER=`grep 's_appl_manager' $parameter_file | cut -d "=" -f2`
echo ${ORACLE_USER}
APPS_PASSWD=`grep 's_apps_pass' $parameter_file | cut -d "=" -f2`
EBS_USER=`grep 's_apps_user' $parameter_file | cut -d "=" -f2`
SYSTEM_PASSWD=`grep 's_system_pass' $parameter_file | cut -d "=" -f2`
ORACLE_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')
ORACLE_SID=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_SID"')
ORACLE_CONTEXT=$(su - ${APPS_USER} -c 'echo "$CONTEXT_NAME"')
APPS_SCRIPTS_HOME=$(su - ${APPS_USER} -c 'echo "$RUN_BASE/inst/apps/$CONTEXT_NAME/admin/scripts"')
DB_SCRIPTS_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$ORACLE_CONTEXT
# We will use a copy of the utilfile for repeated cloning.
su - ${ORACLE_USER} <<EOF
cd ${ORACLE_HOME}
source ${ORACLE_CONTEXT}.env
sh $DB_SCRIPTS_HOME/adcdblnctl.sh start ${ORACLE_SID}
{ echo $APPS_PASSWD; } | perl ${ORACLE_HOME}/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=${ORACLE_HOME}/appsutil/${ORACLE_CONTEXT}.xml -oraclehome=${ORACLE_HOME} -outdir=${ORACLE_HOME}/appsutil/log -mode=getUtlFileDir
cp -f ${ORACLE_HOME}/dbs/${target_dbsid}_utlfiledir.txt.backup ${ORACLE_HOME}/dbs/${target_dbsid}_utlfiledir.txt
{ echo $APPS_PASSWD; echo $SYSTEM_PASSWD; } | perl ${ORACLE_HOME}/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=${ORACLE_HOME}/appsutil/${ORACLE_CONTEXT}.xml -oraclehome=${ORACLE_HOME} -outdir=${ORACLE_HOME}/appsutil/log -mode=setUtlFileDir -servicetype=onpremise
{ echo $APPS_PASSWD; } | perl ${ORACLE_HOME}/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=${ORACLE_HOME}/appsutil/${ORACLE_CONTEXT}.xml -oraclehome=${ORACLE_HOME} -outdir=${ORACLE_HOME}/appsutil/log -mode=syncUtlFileDir -skipautoconfig=yes
cd ${ORACLE_HOME}/appsutil/clone/bin
{ echo $APPS_PASSWD; } | perl adcfgclone.pl dbconfig ${ORACLE_HOME}/appsutil/${ORACLE_CONTEXT}.xml
su - ${APPS_USER} -c "sqlplus ${EBS_USER}/${APPS_PASSWD}@${target_dbsid} <<EOF
EXEC FND_CONC_CLONE.SETUP_CLEAN;
Commit;
quit;
EOF"
Now we will do the autoconfiguration on both database and application tiers using the script “doautoconfig.sh”. I’m leaving the application env file path hardcoded as I missed parameterizing it in the file. Add a new value to the parameter file if you want to refer it from the it. Please note, both my Oracle and Applmgr users bash profiles are modified to call the respective environment files. Missing them in the bash profiles could be one of the reasons for the scripts failing.
#!/bin/sh
parameter_file="/u04/RMAN/RESTORE/restoreparam.txt"
ORACLE_USER=`grep 's_db_user' $parameter_file | cut -d "=" -f2`
APPS_USER=`grep 's_appl_manager' $parameter_file | cut -d "=" -f2`
APPS_PASSWD=`grep 's_apps_pass' $parameter_file | cut -d "=" -f2`
SYSTEM_PASSWD=`grep 's_system_pass' $parameter_file | cut -d "=" -f2`
ORACLE_SID=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_SID"')
ORACLE_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')
ORACLE_CONTEXT=$(su - ${APPS_USER} -c 'echo "$CONTEXT_NAME"')
DB_SCRIPTS_HOME=$(su - ${ORACLE_USER} -c 'echo "$ORACLE_HOME"')/appsutil/scripts/$ORACLE_CONTEXT
su - ${ORACLE_USER} <<EOF
cd $DB_SCRIPTS_HOME
{ echo $APPS_PASSWD; } | sh adautocfg.sh
EOF
su - ${APPS_USER} <<EOF
{ echo $APPS_PASSWD; } | adautocfg.sh;
sqlplus system/${SYSTEM_PASSWD};
alter trigger ebs_logon disable;
quit;
EOF
su - ${APPS_USER} <<EOF
source /u02/oracle/apps/EBSapps.env patch;
{ echo $APPS_PASSWD; } | adautocfg.sh;
sqlplus system/${SYSTEM_PASSWD};
alter trigger ebs_logon enable;
quit;
EOF
su - ${APPS_USER} <<EOF
source /u02/oracle/apps/EBSapps.env run;
EOF
That’s all. If there were no errors, it is okay to start the test instance now. If you are coming across issues that I have overlooked, do let me through comments.
