Monday, December 7, 2009

Fixing the R12 EBS utility to use SPFILE instead of PFILE for R12

During some maintenance this past weekend, I had to shutdown and startup a large number of R12 EBS environments after a hardware/network change. Now if you are an Apps DBA, Oracle really wants us to use the AD scripts for both apps tier and db tiers to perform these tasks. While you technically can shutdown and startup the Oracle database tier fine via SQL*PLUS, Oracle best practices recommend using these AD scripts. For the apps tier, the scripts live under $INST_TOP/admin/scripts or $ADMIN_SCRIPTS_HOME. For the database tier, the AD scripts for managing the database and listener live under the $ORACLE_HOME/appsutil/scripts/{SID} directory.

To my chagrin and thanks to fellow DBAs, I learned the hard lesson that the addbctl.sh script uses the old Oracle database initializaton file or pfile (init.ora) INSTEAD of using the newer and more correct spfile. Everything came up fine but I needed to restart things manually using SQL*PLUS on the database tier instead of using the addbctl.sh script. The way addbctl.sh works is that it calls another script, adstrtdb.sql which starts the database using the pfile instead of the spfile. SO, we can fix the problem by a quick edit of the adstrtdb.sql script to call the spfile and that would start the database tier with spfile. By the way, this is not documented anyplace by Oracle to my knowledge so it is an undocumented tip and should be tested in a sandbox non-production environment FIRST before you implement this for production.

Now let's take a look at how these two key scripts work:

The main startup and shutdown script for the R12 EBS Apps database tier is called
addbctl.sh


#!/bin/sh
# $Header: addbctl.sh 120.1 2006/04/25 22:24:44 swkhande noship $
# *===========================================================================+
# | Copyright (c) 1996 Oracle Corporation, Redwood Shores, California, USA |
# | All rights reserved |
# | Applications Division |
# +===========================================================================+
# |
# | FILENAME
# | addbctl.sh
# |
# | DESCRIPTION
# | Start / Stop database TEST
# |
# | USAGE
# | addbctl.sh [start|stop] {immediate|abort|normal}
# |
# | PLATFORM
# | Unix Generic
# |
# | NOTES
# |
# | HISTORY
# |
# +===========================================================================+
# dbdrv: none

header_string="$Header: addbctl.sh 120.1 2006/04/25 22:24:44 swkhande noship $"
prog_version=`echo "$header_string" | awk '{print $3}'`
program=`basename $0`
usage="\t$program [start|stop] {normal|immediate|abort}"

printf "\nYou are running $program version $prog_version\n\n"

if [ $# -lt 1 ];
then
printf "\n$program: too few arguments specified.\n\n"
printf "\n$usage\n\n"
exit 1;
fi

control_code="$1"

if test "$control_code" != "start" -a "$control_code" != "stop" ; then
printf "\n$program: You must either specify 'start' or 'stop'\n\n"
exit 1;
fi

shutdown_mode="normal"

DB_VERSION="db111"

#
# We can't change "internal" to "/ as sysdba" for 817 - see bug 2683817.
#

if [ "$DB_VERSION" = "db817" ]
then
priv_connect="internal"
else
priv_connect="/ as sysdba"
fi

if test "$control_code" = "stop"; then
if test $# -gt 1; then
shutdown_mode="$2";

if test "$shutdown_mode" != "normal" -a \
"$shutdown_mode" != "immediate" -a \
"$shutdown_mode" != "abort" ; then
printf "\n$program: invalid mode specified for shutdown\n"
printf "\tThe mode must be one of 'normal', 'immediate', or 'abort'\n\n"
exit 1;
fi
fi
fi

ORA_ENVFILE="/TEST/bin/db/tech_st/db/tech_st/11.1.0/TEST_sandiego.env"
DB_NAME="TEST"

#
# setup the environment for Oracle and Applications
#

if [ ! -f $ORA_ENVFILE ]; then
printf "Oracle environment file for database $DB_NAME is not found\n"
exit 1;
else
. $ORA_ENVFILE
fi

if test "$control_code" = "start" ; then
printf "\nStarting the database $DB_NAME ...\n\n"
sqlplus /nolog @/TEST/bin/db/tech_st/db/tech_st/11.1.0/appsutil/scripts/TEST_sandiego/adstrtdb.sql "$priv_connect"
exit_code=$?

else

printf "\nShutting down database $DB_NAME ...\n\n"
sqlplus /nolog @/TEST/bin/db/tech_st/db/tech_st/11.1.0/appsutil/scripts/TEST_sandiego/adstopdb.sql "$priv_connect" $shutdown_mode
exit_code=$?

fi

printf "\n$program: exiting with status $exit_code\n\n"
exit $exit_code



Basically the addbctl.sh script is a wrapper to call SQL*PLUS to shutdown or startup the database tier. This script in turn calls another script, adstrtdb.sql to do the heavy lifting to startup the database tier for R12 EBS. Herein lies the crux of the problem. The adstrtdb.sql uses the pfile INSTEAD of the correct spfile to startup the database tier. So we need to edit this script to fix the problem.
Below is a copy of the adstrtdb.sql script.


REM $Header: adstrtdb.sql 120.1 2006/04/25 22:22:56 swkhande noship $
REM +========================================================================+
REM | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA
REM | All Rights Reserved
REM +========================================================================+
REM | FILENAME
REM | adsrttdb.sql
REM |
REM | DESCRIPTION
REM | Script to startup database
REM |
REM | USAGE
REM | sqlplus /nolog @adstrtdb.sql
REM |
REM | NOTES
REM |
REM | HISTORY
REM =========================================================================+
REM
REM ###############################################################
REM
REM This file is automatically generated by AutoConfig. It will be read and
REM overwritten. If you were instructed to edit this file, or if you are not
REM able to use the settings created by AutoConfig, refer to Metalink Note
REM 387859.1 for assistance.
REM
REM ###############################################################

REM
REM dbdrv: none

REM connect / as sysdba;
WHENEVER SQLERROR EXIT 9
define USER="&1"
connect &USER;

startup pfile=/TEST/bin/db/tech_st/db/tech_st/11.1.0/dbs/initTEST.ora

exit



As you can see the line in the adstrtdb.sql script has a problem:

startup pfile=/TEST/bin/db/tech_st/db/tech_st/11.1.0/dbs/initTEST.ora

So we replace it with:

startup spfile=/TEST/bin/db/tech_st/db/tech_st/11.1.0/dbs/spfileTEST.ora

As a final note, you will need to run AutoConfig on the apps and database tier to sync up the changes to the scripts. Instructions to do so are provided by Metalink Note: 387859.1, Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12

Execute the following command to run AutoConfig on the database tier.

On Unix:


sh /appsutil/scripts//adautocfg.sh


I also reviewed this item with Oracle support and they referenced another note to answer this question as well. Oracle support referred me to Metalink Note 457257.1

How to use an SPfile with Auto Config 457257.1

It is due to two unpublished bugs as to why the pfile is used by default in the scripts.

5566776 "AUTOCONFIG: STARTUP DATABASE WITH ADDBCTL.SH USING SPFILE"
6682471 "AUTOCONFIG: STARTUP DATABASE WITH ADDBCTL.SH USING SPFILE"

One note from a fellow Apps DBA reader is that you will also need to create a custom template in order for the changes to the adstrtdb.sql script to remain intact, otherwise autoconfig will put the pfile reference back into the script!

AutoConfig uses the template file to build the adstrtdb.sql script, but creating and modifying a custom template will allow the change to persist. You will need to create the custom template on the database tier as shown below:

[oratest@sandiego]$ grep pfile $ORACLE_HOME/appsutil/template/adstrtdb.sql

startup pfile=%s_db_oh%/dbs/init%s_dbSid%.ora

[oratest@sandiego]$ mkdir $ORACLE_HOME/appsutil/template/custom

[oratest@sandiego]$ cp $ORACLE_HOME/appsutil/template/adstrtdb.sql $ORACLE_HOME/appsutil/template/custom

[oratest@sandiego]$ vi $ORACLE_HOME/appsutil/template/custom/adstrtdb.sql

[oratest@sandiego]$ grep pfile $ORACLE_HOME/appsutil/template/custom/adstrtdb.sql

startup spfile=%s_db_oh%/dbs/spfile%s_dbSid%.ora


Now, when you run the addbctl.sh script to startup the database tier, it will use the correct spfile instead of older pfile!

3 comments:

jpiwowar said...

Hi Ben,

Interesting tip, thanks for sharing. I confess that I've never run across this problem, because I'm a bad kid and usually use the traditional (non-AD script) methods to stop the database and listener. :-)

One small note: If you want your changes to adstrdb.sql to persist through an AutoConfig run, you need to create a custom template for the adstrtdb.sql script. Otherwise, you're going to get the pfile reference back:

[oravis@londo 11.1.0]$ grep pfile $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adstrtdb.sql

startup spfile=/u01/ebs/R12VIS/db/tech_st/11.1.0/dbs/spfileR12VIS.ora

[oravis@londo 11.1.0]$ $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh
Enter the APPS user password:
(autoconfig output snipped)

[oravis@londo 11.1.0]$ grep pfile $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adstrtdb.sql

startup pfile=/u01/ebs/R12VIS/db/tech_st/11.1.0/dbs/initR12VIS.ora

AutoConfig uses the template file to build the adstrtdb.sql script, but creating and modifying a custom template will allow the change to persist:

[oravis@londo 11.1.0]$ grep pfile $ORACLE_HOME/appsutil/template/adstrtdb.sql

startup pfile=%s_db_oh%/dbs/init%s_dbSid%.ora

[oravis@londo 11.1.0]$ mkdir $ORACLE_HOME/appsutil/template/custom

[oravis@londo 11.1.0]$ cp $ORACLE_HOME/appsutil/template/adstrtdb.sql $ORACLE_HOME/appsutil/template/custom

[oravis@londo 11.1.0]$ vi $ORACLE_HOME/appsutil/template/custom/adstrtdb.sql

[oravis@londo 11.1.0]$ grep pfile $ORACLE_HOME/appsutil/template/custom/adstrtdb.sql

startup spfile=%s_db_oh%/dbs/spfile%s_dbSid%.ora

[oravis@londo 11.1.0]$ $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh
(AutoConfig output snipped again)

[oravis@londo 11.1.0]$ grep pfile $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adstrtdb.sql

startup spfile=/u01/ebs/R12VIS/db/tech_st/11.1.0/dbs/spfileR12VIS.ora

Apologies if the output lines are strangely wrapped; hopefully all of that comes through OK. :-)

Regards,

John P.

Ben Prusinski said...

Hi John,

Thanks for the great catch with the changes for the adstrtdb.sql script! And yes, using SQL*PLUS to shutdown and startup the database tier is easier than messing around with the AD scripts. I just wish Oracle EBS Apps DBA development team would fix this bug since it has been around for 5+ years and has not been fixed yet!

Cheers,
Ben

jpiwowar said...

Me again!

The dangers of late-night commenting are that I sometimes fail to remember things, like syntax and testing. :-) I just tried to restart my Vision database after working the example in my earlier comment, and just for fun I used the addbctl.sh script. Imagine my surprise when it returned an error:

[oravis@londo ~]$ addbctl.sh start

You are running addbctl.sh version 120.1

Starting the database R12VIS ...
[SQLPLUS startup snipped]

Connected to an idle instance.
SP2-0714: invalid combination of STARTUP options
Disconnected

This, of course, is because spfile=path_to_spfile is not a valid form of the startup command. The correct thing to do in the custom template is to just remove everything after the startup command altogether:

[oravis@londo ~]$ grep startup $ORACLE_HOME/appsutil/template/custom/adstrtdb.sql
REM | Script to startup database
startup
[oravis@londo ~]$ grep startup $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adstrtdb.sql
REM | Script to startup database
startup


This will enable the default behavior of using the spfile if it exists, and falling back to a properly-named pfile otherwise.

Apologies for the gaffe, and for inadvertently misleading you and your readers!

Regards,

John P.