Hi guys
Only applies to 10g database. 11g has different approach and I am trying to make another post for it.
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
- Stop the application & database instances those are online in the TEST server
- Extract the tar balls copied from PRODUCTION instance to relevant folders
- Clone database tier, followed by application tier
- 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:
[code language=”bash” gutter=”false”]
yum install expect -y
[/code]
[code language=”bash” gutter=”false”]
#!/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)"
[/code]
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
[code language=”bash” gutter=”false”]
#!/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
[/code]
appsclone.sh | script enabled with expect which will not ask for the apps password
[code language=”bash” gutter=”false”]
#!/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
[/code]
dbfix.sh | Changing database parameters like SGA, job queue processes etc
[code language=”bash” gutter=”false”]
#!/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"
[/code]
Finally dbalter.sh | All database alter commands are included within this file
[code language=”bash” gutter=”false”]
#!/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
[/code]
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
[code language=”bash” gutter=”false”]
chmod +x ebsclone.sh
[/code]
and execute the ebsclone.sh as “root” (attempts made with other users will fail the cloning)
[code language=”bash” gutter=”false”]
#./ebsclone.sh
[/code]
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!
