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

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

  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:

[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!

Advertisements

Linux LVM (Logical Volume Manager) | AKA Spanned volumes

Hi guys

We’ve a EBS instance that totals almost 1TB physical size hosted on a high end IBM server & periodically we clone the instance to insure that the cold backups are reliable for DR purposes.

Recently we’ve decommissioned one HP ML110 G6 server with single xeon processor, 8GB memory that was dedicated for obsolete bio-metric monitoring and reporting running Windows 2003. I thought of using the same server for future restorations of EBS cold backups & realized that the server doesn’t support RAID 5 & moreover the built-in RAID is categorized under “fakeRAID”, which uses the built-in RAID technology, depending upon the CPU for the crippled RAID processing.

Using the HP Pavilion Easy Setup CD, I created an array and to my total disappointment found that Linux doesn’t read the fakeRAID while an installation is attempted.

The above were attempted because the ML110 G6 had 4 numbers of 500GB SATA HDD drives and I needed 1TB on a single volume. My database instance size as on date is 493GB, which would scream lack of space on a single 500GB partition. So I started reading about software RAID, which was too complex to setup with my minimal exposure to Linux. Further readings brought me to LVM (Logical Volume Manager) using which one can create spanned volumes as like in Windows.

Before proceeding further, please be aware of the RISKS associated with spanned volumes AKA LVM with multiple drives

How to?

We’ll consider a fresh installation of CentOS6/RHEL6/OEL6 for the exercises

Source thread (Please, please read)

Hardware: HP ML110 G6, 8GB memory, 4x500GB SATA HDD

Linux installation details

Installed Linux on HDD#1 (/dev/sda), 10GB boot, 4GB Swap, 110G / & balance as extended partition

Now, I am left with 3 HDDs, which are “untouched”, ie, no partitions are made

  1. /dev/sdb
  2. /dev/sdc
  3. /dev/sdd

As I have mentioned, my requirement was to have 1TB of storage for the cloning purposes, hence I chose 2x500GB (/dev/sdb, /dev/sdc)

First I created partitions using “fdisk”, the age old command line utility, even though better structured GUI is available with latest Linux distributions

Login to terminal as “root”

$fdisk /dev/sdb

n (new parition) -> p (primary partition) -> 1 (number of partitions) -> w (Write changes)

Repeated the same for /dev/sdc

$fdisk /dev/sdc

n (new parition) -> p (primary partition) -> 1 (number of partitions) -> w (Write changes)

We’ll use the following 3 commands to create our LVM

  1. pvcreate
  2. vgcreate
  3. lvcreate

create two physical volumes

$pvcreate /dev/sdb1 /dev/sdc1

create one volume group with the two physical volumes

$vgcreate VG_DATA /dev/sdb1 /dev/sdc1

create one logical volume

$lvcreate -l 100%FREE -n DATA VG_DATA

create the file system on your new volume

$mkfs.ext4 /dev/VG_DATA/DATA  #You may use ext3, based on your Linux distribution

$mkdir -p /u01

mount the volume (mount /dev/VG_DATA/DATA /u01)

That’s all folks, I have created my1st LVM aka spanned volume in Linux.

If you are planning to create logical volumes using multiple disks, be aware of the risks. You may lose millions worth data if no proper backups are taken and recovery could be a nightmare!

Not limited to total data loss, performance issues also should be considered, especially when such a setup hosts databases which require faster I/O.

for Windows7bugs

rajesh