EBS 11i

This month I thought I would share some pointed tips on automating some tasks that are central to the Apps DBA, as well as some tips on how to prevent those same tasks from biting you in the caboose later on. Those of you that read my articles know that I love automating DBA tasks where appropriate, partly because I like the challenge, and partly because I do not want to have to "remember" to do certain tasks when they are repetitive but not frequent. I add the "appropriate" descriptor here since I have had very interesting conversations with fellow DBAs about the feasibility of scripting things. This a discussion we can have later, but suffice it to say, there are things that should not be automated. Likewise, there are tasks that should be automated, but the cost of doing such far outweighs the benefit. Make sense?

So, tip one is central to cloning. You do clone, right? Surely, you are not one of those folks that let everyone "test" in production are you? If you are, stop reading this article now, navigate away from this knowledge, and don't come back until you have enough sense to know the horrible-ness of your misdeeds!

One of the tasks that we need to do that is very repetitive, is running adpreclone.pl before we clone. Rather than get into the specific details of running adpreclone.pl, know that Metalink Note# 230672.1 has a description of this task. Actually, now that I am looking at the note, it has a detailed accounting of how to execute the script, but does not really go into any detail about why. Of course, the System Administration guide has more details, specifically stating "Execute the following command to collect all the information about the instance:". Hmmm, I may have just found next month's article...

But, back on topic. We know we need to run adpreclone.pl on both the application and database tiers; same script, just different execution parameters. This, of course, is not difficult to remember if you are cloning by hand. But, what happens if you have automated your cloning? Yes, you can execute this script as part of that script but then you would be running adpreclone every time you executed a clone. "What? I thought that was what he told us we needed to do." Confused? Well, let's think this through.

We know that adpreclone.pl gathers information about the source system (which in our case would be the production system) for use in cloning. So, if the source system did not change between clones, would we need to re-run adpreclone.pl? More specifically, if we were going to clone a "few" test and dev systems in short order, would we want to run adpreclone.pl each time? More than likely, the answer is no. But, executing the adpreclone.pl perl script inside your clone script is a way to protect against forgetting to run it at all. However, it is not efficient to do so, and therefore you should investigate a different way to achieve the same result.

As a side note, what happens if we forget to run adpreclone.pl before cloning? Well, depending on what has changed in your environement, nothing. But, again, based on those changes, your target system may not come up at all, or worse, it may come up but have some real quirks to it. We actually encountered this situation some time ago while we were teaching a newer DBA how to clone. At that time we did not have an automated script for cloning so we detailed the procedure for them and let them run with it. After the clone, the target system was not fully usable. When we investigated, we determined that one of our pool of DBAs had added a datafile to the system the night before to address a space issue. It just so happened that the datafile was added about 20 minutes before we took our backup that would later be used for cloning. For his part, the newer DBA forgot to run adpreclone.pl on production before taking that backup. So, the net result was a database that was not able to identify one of the datafiles it needed, leaving our intrepid new DBA stuck with the task of re-cloning.

So, if it is not efficient to make running adpreclone.pl part of the actual cloning script, and you do not want to have to remember to run it each time, what do you do? In our case, we set up a cron entry to run adpreclone. In our configuration, we use the backup of our production system as the baseline for each and every clone. These baselines are set for Saturday morning and Wednesday night. Therefore, our apps tier crontab looks like this:

00 21 3,5 /dba/bin/adpreclone_appl.job prod > /dba/log/adpreclone_appl_prod.log 2>&1 &
00 21 3,5 /dba/bin/adpreclone_db.job prod > /dba/log/adpreclone_db_prod.log 2>&1 &

Adpreclone is run for the application tier and the database tier each Wednesday and Friday night at 2100 hours or 9:00pm. And, to be more specific, we run adpreclone.pl as part of a script that has some error checking built in. The script is:

#!/bin/ksh
#set -x

PROG="`basename ${0}`"
SID="${1}"
export HOSTNAME="`hostname | cut -d . -f1`"
PWDFILE="/var/opt/oracle/local/.pwd"
APPSPASS="`grep "^apps/" ${PWDFILE} | cut -d/ -f2`"
integer valid_sid=0

function usage {
echo "Usage : $ ${PROG} "
exit 1
}

if [ -z "${SID}" ]; then
usage
fi

if [ -z "${APPSPASS}" ]; then
echo "Error: APPS Password missing! Check file (${PWDFILE})!!"
exit 1
fi

valid_sid=`ps -ef | grep pmon | grep ${SID} | grep -v grep | wc -l | awk '{print $1}'`

if [ ${valid_sid} -eq 0 ];then
echo "Invalid Oracle SID !\n"
usage
fi

. ${HOME}/ap${SID}_${HOSTNAME}.env

cd ${COMMON_TOP}/admin/scripts/${SID}_${HOSTNAME}
perl adpreclone.pl appsTier pwd=${APPSPASS}

The script governing the database tier is identical with one exception: after running its checks, it sources the dbtier env file and then executes:

. ${HOME}/or${SID}_${HOSTNAME}.env

cd ${ORACLE_HOME}/appsutil/scripts/${SID}_${HOSTNAME}
perl adpreclone.pl dbTier pwd=${APPSPASS}

Make sense? So, now we have this script that auto-magically runs just before we take our baselines for all clones. Thus, we insure that adpreclone.pl is run, but not any more than it needs to be.

Of course, now that I say it that way, let me include another script that we use to monitor what is actually scheduled for the crontab. We have had issues in the past where we set something and then proceeded to forget about it. So, we wrote a quick script to monitor for that situation. The following script runs every day and lets us know what has been scheduled via cron for that night. The script looks like this:

#!/usr/bin/sh

SERVER=`uname -n`
LOG_FILE=/home/clone/Active_Crontab.$SERVER
DBADMIN=DBAs@hotmail.com

echo "This is a listing of the active crontab entries for the CLONE user on ${SERVER}. Section 2 shows all cron entries. Please verify that active entries are correct..." >>${LOG_FILE}

echo "" >>${LOG_FILE}
echo "##########STARTING PROCEDURE###########################################" >>${LOG_FILE}
echo "" >>${LOG_FILE}
echo "" >>${LOG_FILE}
echo "The following are the active entries for this crontab..." >>${LOG_FILE}
echo "" >>${LOG_FILE}
echo 'Executing: crontab -l | grep ^[0-9]' >>${LOG_FILE}
echo "" >>${LOG_FILE}
crontab -l | grep ^[0-9] >>${LOG_FILE}
echo "" >>${LOG_FILE}
echo "" >>${LOG_FILE}
echo "" >>${LOG_FILE}
echo "##################################################" >>${LOG_FILE}
echo "##################################################" >>${LOG_FILE}
echo "" >>${LOG_FILE}
echo "" >>${LOG_FILE}
echo 'Executing: crontab -l' >>${LOG_FILE}
echo "" >>${LOG_FILE}
crontab -l >>${LOG_FILE}
echo "" >>${LOG_FILE}
echo "" >>${LOG_FILE}
echo "##########ENDING PROCEDURE#############################################" >>${LOG_FILE}
echo "Sending email to DBA(s): ${DBADMIN} at `date`" >>${LOG_FILE}
cat ${LOG_FILE} | mailx -s "Crontab entries for CLONE User on ${SERVER} server" ${DBADMIN}
rm -f ${LOG_FILE}

And we see the following email each day. This small piece of code insures that we never have jobs executing that we don't know about.

This is a listing of the active crontab entries for the OACLONE user on hpux3. Section 2 shows all cron entries. Please verify that active entries are correct...

##########STARTING PROCEDURE###########################################


The following are the active entries for this crontab...

Executing: crontab -l | grep ^[0-9]

00 16 /dba/bin/active_cron_entries.sh
30 02 1 /dba/bin/autoclone.sh devl > /dba/log/clone/autoclone_devl_`date +'%m%d%y'`.log &2>1

#######################################################################
#######################################################################

Executing: crontab -l

###Check Crontab Entries
00 16 /dba/bin/active_cron_entries.sh
###Autoclone of PTCH
#20 15 4 /dba/bin/autoclone.sh ptch > /dba/log/clone/autoclone_ptch_`date +'%m%d%y'`.log &2>1
###Autoclone of DEVL
30 02 1 /dba/bin/autoclone.sh devl > /dba/log/clone/autoclone_devl_`date +'%m%d%y'`.log &2>1
###Autoclone of TEST
#30 02 0 /dba/bin/autoclone.sh test > /dba/log/clone/autoclone_test_`date +'%m%d%y'`.log &2>1
###Scheduled STOP of local instances
#00 17 5 /dba/bin/stop_apps.sh ptch
#05 17 5 /dba/bin/stop_apps.sh devl
#10 17 5 /dba/bin/stop_apps.sh test
###Scheduled START of local instances
#05 08 0 /dba/bin/start_apps.sh ptch
#10 06 1 /dba/bin/start_apps.sh devl
#15 06 1 /dba/bin/start_apps.sh test

##########ENDING PROCEDURE#############################################
Sending email to DBA(s): DBAs@hotmail.com at Wed Feb 25 16:00:01 MST 2009

There you have it! Tip one on overcoming forgetfulness using the cron facility and automation.

Barbara Matthews attended Mike Swing's Oracle R11i/R12 Applications DBA Concepts and Admininstration class last week. Here's her review:

So last week, I decided to refresh my E-Business Suite Applications DBA skills. I spent a week in lovely Salt Lake City at the TruTek offices. My goal when I take training, read a paper, or attend a presentation, is to walk away with what I'll call a "golden nugget". A golden nugget for me is something that I wouldn't have known otherwise, something that caught my attention and that I knew would be very useful. As it turns out, I picked up several golden nuggets in this class, so I'll share some of them with you here.

First off, this class is a mixture of lecture and hands on work. Mike has a very deep understanding of the Applications. His biggest difficulty appears to be clamping down on the firehose of information that he wants to share. "Better too much than too little!", I say. Well, I said that until I had to hand my luggage over at the airport, where I worried that Mike's 7 pound class handout would tip me over the luggage weight limit.

The class covered topics that I knew would be useful for an Apps DBA. On Monday we covered the 11i architecture, a hands on workshop where we installed Release 11.5.10.2, information about oraInventory, the 11i file system, and the shared $APPL_TOP. I found the shared $APPL_TOP discussion to be particularly helpful, because I knew that companies should use it, but didn't know the exact details of how to implement it. Mike's reviews of the Release 11i and Release 12 architectures tend to be a bit overwhelming, but I agree with Mike that you really need to understand the architecture and where it came from to be able to support it as a DBA. And nobody ever said anything about the Oracle Applications was simple, particularly the underlying architecture.

Now, I have to admit, we had a little mishap on our first day. Mike's equipment had last been used by Release 12 instances, and it turns out that those instructions that you've heard time and time again about how you need to carefully review the Operating System Release Notes really are important - we had to de-install some operating system packages and install other versions to make the Release 11.5.10.2 install work. Who would have thought?

On Tuesday, we covered the admin utilities and autoconfig. We tried our hand at changing an autoconfig template, and we used admergepatch to merge some patches. Ah, admergepatch. If you don't know how to use this, you absolutely must learn. It really helps decrease upgrade time. We also started cloning our Release 11i instance. I took great comfort from the knowledge that Oracle's cloning scripts have improved tremendously over the years.

On Wednesday, we finished up our clone. Somehow I managed to mess up the permissions on mine, but eventually I got things pointed to where they were supposed to point. We also covered more architecture topics - this time on Release 12. We talked more about Apache, 10gAS and configuration, and we installed Release 12. Packing two installs and a clone into this class really gave us a workout.

On Thursday, we covered Release 12's OPMN. We didn't have enough time to do a Release 11i/Release 12 upgrade (Mike has a separate class for that), but Mike walked us through his notes, as he has done this upgrade many times. Then we moved on to failover, load balancing and parallel concurrent processing, three topics near and dear to Mike's heart. He did a terrific demo with three machines that showed how failover kicks in. Incidentally, you don't have to be a RAC user to take advantage of failover - Mike showed us how the concurrent manager could be set up to fail over to another server. He's done some very interesting research on the parameters that affect how quickly failover takes place. Mike also showed how failover behaves differently between Release 11i and Release 12. I considered that information to be a golden nugget for sure, because with Release 11i, you don't have to specify your failover server in your concurrent manager setup. You do with Release 12, so if you didn't know that, you might be in for a rude awakening!

On Friday we covered troubleshooting issues. We looked at some of the log files and Mike showed us where to find them and how to interpret them. We also covered how to change the APPS password, and finished up with a rousing presentation about security.

All in all, it was a great class. I highly recommend it, particularly if you're transitioning from being a DBA to being an Applications DBA. The amount of knowledge that you have to have to take on the Applications DBA role is daunting, and this hands on class really provides you with the necessary information to do that job well.

Next on my list? I think I'll take a whirl at the Release 11i to Release 12 Upgrade Class, but not until I've practiced my cloning and install skills a bit more.