Oracle E-Business Suite R12.0 | Automating clone process

Hi guys

A clone is the exact replica of a production instance, against what you do all tests, custom development and patch deployments to insure that your attempts are NOT going to break the PRODUCTION instance once such are moved over to.

How often consultants & users may request for a fresh clone (with latest data) depends upon many factors. During the implementation time, a DBA could be bombarded with requests for cloning almost once in couple of days. Although I am “not a dba”, I have been doing cloning to learn & understand the technology from last couple of years time & trust me, it is NOT at all fun. Especially once after you are familiar with the tasks.

Throughout last few months I was trying to “fully automate” the entire cloning process and made significant advancements with the process. I will share my experiences with you today

Scenario:

We have a cron job initiated by “root” user, starting by 2:30 PM every Friday, that shuts down the application and database after running pre-cloning. The same script makes tar balls for both the database and application nodes in separate files and then copies the tar balls to our TEST instance server.

Logically once the tar balls are copied to TARGET (TEST) server, following activities are expected from the DBA

  1. Stop the application & database instances those are online in the TEST server
  2. Extract the tar balls copied from PRODUCTION instance to relevant folders
  3. Clone database tier, followed by application tier
  4. Tune the database with TEST server specific SGA/job queue processes etc parameters

What if I am too lazy & a scripting junkey who wants to automate the entire activities using shell scripts? The following demonstrates such an attempt

Why not a cron job? ;)

The first step will be, creating auto response files for both database and application nodes. I have already detailed a how to here

ebsclone.sh | This shell script calls a number of other shell scripts to facilitate the entire cloning process

Please note, my Oracle database user is “oraprod” and application manager is “applprod”. If you are planning to copy the below script(s), make sure you change the physical paths, user details according to your specific environment.

Both the database and application manager user accounts are enhanced with .bash_profile values. Hence most of my scripts will not populate the environment variables prior executing other scripts.

I am using “expect”, that YOU must install, if not already installed in order to automate the cloning process. If you are using Oracle linux, you can install expect by issuing the following command:

yum install expect -y
#!/bin/bash
# As a precaution to make sure the port pool is available during automated
# Cloning, we will kill all orphaned processes those were not closed during
# DB, APPS stop

# Kill all processes for both oraprod & applprod users
echo "Killing all processes for Oracle user"
pkill -u oraprod
echo "Killing all processes for Application user"
pkill -u applprod
echo "All processes for both Oracle and Application users were killed"

sleep 30

echo "$(date)"

#Remove the existing physical folder for database files
cd /u01
find oraprod -delete
echo "finished deleting Oracle top at $(date)"
#Extract files for database top from the cold backup archive
echo "Extract database backup file at $(date)"
time tar -zxf /u02/backup/PROD_DAILY_BACKUP_db.tar.gz
echo "Finished extracting database backup file at $(date)"

#Remove the existing physical folder for application files

cd /u03
find applprod -delete
echo "finished deleting Application top at $(date)"

#Extract files for application top from the cold backup archive
echo "Extract application backup file at $(date)"
time tar -zxf /u02/backup/PROD_DAILY_BACKUP_apps.tar.gz u06/applprod/PROD/apps
echo "Finished extracting application backup file at $(date)"

#Move the files around based on your configuration files (db.xml & apps.xml)

mv /u01/u05/oraprod /u01
mv /u03/u06/applprod /u03

#Change the ownership of the folders, so that corresponding users could read & execute files

chown -R oraprod:oinstall /u01/oraprod
chown -R applprod:oinstall /u03/applprod

########################################
#Start the cloning
########################################

echo "Database cloning phase starts now, $(date)"

#su - oraprod -c "perl /u01/oraprod/PROD/db/tech_st/10.2.0/appsutil/clone/bin/adcfgclone.pl dbTier /u01/clonescripts/db.xml"

/root/scripts/dbclone.sh

sleep 30

echo "Application cloning phase starts now, $(date)"

# su - applprod -c "perl /u03/applprod/PROD/apps/apps_st/comn/clone/bin/adcfgclone.pl appsTier /u01/clonescripts/apps.xml"

/root/scripts/appsclone.sh

echo "EBS Cloning completed, $(date)"
######################################
#Optional steps for changing database SGA parameters,
#startup configuration files to spfile etc
######################################
echo "Changing database parameters, $(date)"

/root/scripts/dbfix.sh

echo "Done! Application online with changed database parameters, $(date)"

Now I will copy the code for each script called within the ebsclone.sh script
dbclone.sh | script enabled with expect which will not ask for the apps password

#!/usr/bin/expect -f
set force_conservative 0  ;

# set to 1 to force conservative mode even if
# script wasn't run conservatively originally

if {$force_conservative} {
        set send_slow {1 .1}
        proc send {ignore arg} {
                sleep .1
                exp_send -s -- $arg
        }
}

set timeout -1

spawn su - oraprod -c "perl /u01/oraprod/PROD/db/tech_st/10.2.0/appsutil/clone/bin/adcfgclone.pl dbTier /u01/clonescripts/db.xml"

match_max 100000

expect -exact "\r
Enter the APPS password : "
send -- "apps\r"

expect eof

appsclone.sh | script enabled with expect which will not ask for the apps password

#!/usr/bin/expect -f
set force_conservative 0  ;

# set to 1 to force conservative mode even if
# script wasn't run conservatively originally

if {$force_conservative} {
        set send_slow {1 .1}
        proc send {ignore arg} {
                sleep .1
                exp_send -s -- $arg
        }
}

set timeout -1

spawn su - applprod -c "perl /u03/applprod/PROD/apps/apps_st/comn/clone/bin/adcfgclone.pl appsTier /u01/clonescripts/apps.xml"

match_max 100000

expect -exact "\r
Enter the APPS password : "
send -- "apps\r"

expect eof

dbfix.sh | Changing database parameters like SGA, job queue processes etc

#!/bin/bash
su - applprod -c "/u03/applprod/PROD/inst/apps/PRODBAK_erp-prodbak/admin/scripts/adstpall.sh apps/apps"
su - oraprod -c /root/scripts/dbalter.sh
su - applprod -c "/u03/applprod/PROD/inst/apps/PRODBAK_erp-prodbak/admin/scripts/adstrtal.sh apps/apps"

Finally dbalter.sh | All database alter commands are included within this file

#!/bin/bash
export ORACLE_HOME=/u01/oraprod/PROD/db/tech_st/10.2.0
export ORACLE_SID=PRODBAK

#http://www.cyberciti.biz/faq/unix-linux-test-existence-of-file-in-bash/
#Check whether spfile already exist
file="/u01/oraprod/PROD/db/tech_st/10.2.0/dbs/spfilePRODBAK.ora"
if [ -f "$file" ]
then
	echo "$file found. Aborting database configuration now"
exit;
else
	echo "$file not found."
fi

sqlplus "/ as sysdba" <<EOF
create spfile from pfile;
shutdown immediate;
startup;
alter system set sga_max_size=8G scope=spfile;
alter system set sga_target=8G scope=spfile;
alter system set job_queue_processes=10 scope=both;
shutdown immediate;
! cp /u01/oraprod/PROD/db/tech_st/10.2.0/dbs/initPRODBAK.ora /u01/oraprod/PROD/db/tech_st/10.2.0/dbs/initPRODBAK.ora.original
! >/u01/oraprod/PROD/db/tech_st/10.2.0/dbs/initPRODBAK.ora
! echo "spfile=/u01/oraprod/PROD/db/tech_st/10.2.0/dbs/spfilePRODBAK.ora" >>/u01/oraprod/PROD/db/tech_st/10.2.0/dbs/initPRODBAK.ora
startup;
exit;
EOF

The above five scripts should do what they are meant to. Just copy the files to same folder
Change the execute mode of ebsclone.sh

chmod +x ebsclone.sh

and execute the ebsclone.sh as “root” (attempts made with other users will fail the cloning)

#./ebsclone.sh

Prior attempting, please make sure all the above scripts are modified with absolute paths, referring to your existing partitions & other

Download the scripts here

References:

Sample expect script

https://community.oracle.com/thread/2558592?start=0&tstart=0

Linux: Find whether a file already exist

http://www.cyberciti.biz/faq/unix-linux-test-existence-of-file-in-bash/

 

Happy cloning!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: