Thursday, December 10, 2009

eTRM- e-Business Electronic Manual

A useful tool for Applications DBAs and anyone for that matter who works with the e-Business suite is that online electronic reference manual or eTRM.

The latest details are available on the Oracle Support site URL listed below:
http://www.oracle.com/us/support/040368

In addition, reference note ETRM DIRECT ACCESS DOCUMENT [ID 150230.1] will link you to the eTRM directly as long as you have a valid Oracle Metalink or MyOracle Support account. Now, lets take a quick look at what the eTRM can do for you in terms of working with the EBS Suite for Oracle Applications.

In a nutshell, the eTRM is an online way to examine the structure of data dictionary objects for the Oracle Applications e-Business Suite. Below is the welcome screen to show the details.



Let's walk through an example in which we want to find out what the different views and tables exist for the FND objects within R12. We do a search on tables and views for FND as shown in the following example.



This gives us a detailed listing for the FND_USER table as shown below.



From here we can look at sample queries and dependencies for the FND_USER table.

Now we can move back to examine more definitions for the APPS schema.




If we look at the FND_PRODUCT_INSTALLATIONS definition we can obtain useful queries as shown below.

By choosing the link to APPLSYS.FND_PRODUCT_INSTALLATIONS we can query and list our details for product versions installed.






In fact, we can grab a nice query to paste into SQL*PLUS or TOAD to show us the details:





This tool helps eliminate the complex guesswork of where dependencies lie and thereby simplifies the work for the busy Apps DBA and developer.



Finding details on configuration for the Apps DBA is excellent with the eTRM if for instance we look at the following section



Looking for patch information and detailed configuration information can be time consuming and tedious work. Now with eTRM, literally you can let your fingers do the walking.



I can grab the query listed below and paste into SQL*PLUS or TOAD to obtain the patch listing in seconds.



And the results from the query are listed below:

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!

Sunday, December 6, 2009

Finding products installed for Oracle R12 E-Business Applications

Recently I was asked for a quick way to find out the status and inventory of products currently installed for Oracle Applications E-Business 12i Suite.

Besides logging into OAM for Oracle R12 EBS, an easier way is to query the FND_PRODUCT_INSTALLATIONS table under the APPS schema. Lets take a sneak peak with a quick example. First, to look at the column definitions for FND_PRODUCT_INSTALLATIONS, we issue a DESCRIBE from SQL*PLUS while logged into Oracle R12 Applications as the APPS schema:


SQL> desc fnd_product_installations
Name Null? Type
----------------------------------------- -------- ----------------------
APPLICATION_ID NOT NULL NUMBER(15)
ORACLE_ID NOT NULL NUMBER(15)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER(15)
LAST_UPDATE_LOGIN NOT NULL NUMBER(15)
PRODUCT_VERSION VARCHAR2(30)
STATUS VARCHAR2(1)
INDUSTRY VARCHAR2(1)
TABLESPACE VARCHAR2(30)
INDEX_TABLESPACE VARCHAR2(30)
TEMPORARY_TABLESPACE VARCHAR2(30)
SIZING_FACTOR NUMBER
INSTALL_GROUP_NUM NUMBER(15)
DB_STATUS VARCHAR2(1)
PATCH_LEVEL VARCHAR2(30)


As you can see from the above columns, we can obtain all sorts of useful information about products currently installed.

Now lets use the following query against the FND_PRODUCT_INSTALLATIONS to list the currently installed products and patch levels:



SQL> select product_version, patch_level
from fnd_product_installations;

PRODUCT_VERSION
----------------------
PATCH_LEVEL
----------------------
12.0.0
R12.FPA.B.1

12.0.0
R12.ZPB.B.1




We also have another useful FND table in the APPS schema called FND_PRODUCT_GROUPS that can be used to show product release information for currently installed Oracle R12 EBS applications.


SQL> select product_group_name,product_group_type,release_name
2 from fnd_product_groups;

PRODUCT_GROUP_NAME
---------------------------------------------------------------
PRODUCT_GROUP_TYPE
---------------------------------------------------------------
RELEASE_NAME
---------------------------------------------------------------
Default product group
Standard
12.1.1


These two FND tables are useful for checking product versions and patch levels within your Oracle R12 E-Business applications environments.

Thursday, December 3, 2009

How to find list of AutoPatch Sessions and Patches for Oracle R12 EBS

Another useful tip besides using the Oracle Applications Manager (OAM) interface to
the R12 EBS Patch Wizard for discovering patches and adpatch session details is to
execute the adphrept.sql script located under the $AD_TOP/patch/115/sql directory for
Oracle E-Business Applications with Release 12.

The syntax for collecting the patch information is to login to the apps tier for R12 EBS for Oracle Applications as the APPS schema account shown below:


cd $AD_TOP/patch/115/sql
sqlplus {applmgr}/{applmgr password}@{SID} @adphrept.sql {query depth} {patch_name or ALL} {patchtype or ALL} {level or ALL} {language or ALL} {$APPL_TOP or ALL}


Detailed syntax is available in the Oracle Applications Patching Procedures Release 12.1 manual in Chapter 5 available from Oracle Technology Network as shown in the URL below:

http://download.oracle.com/docs/cd/B53825_03/current/acrobat/121adpp.pdf

So lets say we want to find the patch information for dates between September 1, 2009 and December 1, 2009 to inventory all currently applied EBS patches. We can issue the script as follows.


$ cd $AD_TOP/patch/115/sql
$ sqlplus apps@TEST @adphrept.sql 3 ALL ALL 09/01/2009 12/01/2009 \
ALL ALL ALL ALL ALL N N N N N dec09.xml


The result is returned to an XML file called adfilerep.xml
that can be viewed by a web browser or XML file viewer.


$ sqlplus apps@TEST @adphrept.sql 3 ALL ALL 09/01/2009 12/01/2009 ALL ALL ALL ALL ALL N N N N N dec09.xml

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Dec 3 18:42:04 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Please be patient, this will take a very long time.

If you are prompted for any parameters, please exit and review the parameter list you have submitted.

Gathering data for report. Report output will be written to adfilerep.xml.


As Oracle advises, this takes anywhere from 20-40 minutes to compile the list of patches into the XML file.

The output will spool the XML file along with the associated XSL files called adpchrep.xsl (under $AD_TOP/html directory) which should be copied to the same directory to view the patch details with either and XML or web browser. OK so here is the trick to view the report. Copy the XML file and XSL files to the $OA_HTML directory. Then you can view the report in web browser.


Another way to examine patches applied is via the script called adpchlst.sql.
This script lives under the $AD_TOP/patch/115/sql directory and provides you with the ability to view list of all patches in a given date range. For example the following script called by adpchlst.sql can tell us details between December 1,2009 and December 3, 2009:


$ cd $AD_TOP/patch/115/sql
$ sqlplus apps/xxxx@TEST @adpchlst.sql 12/01/2009 12/03/2009

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Dec 3 19:17:45 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Report of patches applied between 12/01/2009 and 12/03/2009

APPL TOP NAME BUG NUMBER APPLIED AS
--------------------------------------------------------
GLOBAL 8766170 EXPLICIT
GLOBAL 8782125 EXPLICIT
GLOBAL 8894390 EXPLICIT
GLOBAL 8926397 EXPLICIT
GLOBAL 8938206 EXPLICIT
GLOBAL 8938292 EXPLICIT
GLOBAL 8946954 EXPLICIT

APPL-TOP DEFINITIONS:

NAME SYSTEM_NAME DESCRIPTION
------------------------------------------------------------
GLOBAL TEST
Complete updated view of all applied patches



These are useful scripts to have at hand when creating patch inventory for tracking patches. As an Apps DBA, patching is a core activity that fills many hours of the working day.

Cheers,
Ben

How to find applied patches for R12 EBS Oracle Applications

One key task for Oracle Application EBS Database Administrators is to perform patching on regular basis for maintenance pack upgrades and bug fixes. However, with busy work schedules, keeping track of applied patches can be a challenge.

Oracle R12 Applications uses the following tables under the APPS schema to track the status for bug fixes and patches with adpatch:

ad_applied_patches
ad_bugs


SQL> desc ad_applied_patches
Name Null? Type
----------------------------------------- -------- ----------------------------
APPLIED_PATCH_ID NOT NULL NUMBER
RAPID_INSTALLED_FLAG VARCHAR2(1)
PATCH_NAME NOT NULL VARCHAR2(120)
PATCH_TYPE NOT NULL VARCHAR2(30)
MAINT_PACK_LEVEL VARCHAR2(30)
SOURCE_CODE NOT NULL VARCHAR2(3)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
IMPORTED_FLAG VARCHAR2(1)
IMPORTED_FROM_DB VARCHAR2(30)
IMPORTED_ID NUMBER
MERGE_DATE DATE
DATA_MODEL_DONE_FLAG VARCHAR2(1)


A useful query to track status of applied patches for the E-Business Suite with Oracle EBS is the following:

SQL> select applied_patch_id, last_update_date
from ad_applied_patches
order by last_update_date;


APPLIED_PATCH_ID LAST_UPDATE_DAT
---------------- ---------------
41444 03-NOV-09
41445 03-NOV-09
42444 09-NOV-09
42445 09-NOV-09
43444 18-NOV-09
44444 24-NOV-09
45444 30-NOV-09
45445 30-NOV-09
45446 30-NOV-09
45447 30-NOV-09
45448 03-DEC-09

APPLIED_PATCH_ID LAST_UPDATE_DAT
---------------- ---------------
45449 03-DEC-09
45450 03-DEC-09
45451 03-DEC-09

Hope this quick tip helps fellow Oracle Apps DBAs.

Cheers,
Ben