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:

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!

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.

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

Using R12 adctrl utility for managing jobs

The Oracle E-Business Suite provides a comprehensive suite of DBA utilities that every Apps DBA needs to master to be an effective EBS Applications DBA. In this blog, I will cover the useful and often misunderstood utility called ADCTRL. The adctrl utility is a key utility in the toolkit of every veteran Oracle Applications DBA professional. We will use 12.0.6 release for R12 Oracle E-Business Suite.

ADCTRL is used to manage worker processes that perform a pivotal role in the function
of batch processsing for patching and many other Applications activities.

ADCTRL is located under $AD_TOP/bin directory with Oracle E-Business Suite.

$ cd $AD_TOP/bin

To begin a new adctrl session, you enter the adctrl at a command window prompt:

$ adctrl

Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA

AD Controller

Version 12.0.0

NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.

Your default directory is '/d1/bin/apps/apps_st/appl'.
Is this the correct APPL_TOP [Yes] ?

AD Controller records your AD Controller session in a text file
you specify. Enter your AD Controller log file name or press [Return]
to accept the default file name shown in brackets.

Filename [adctrl.log] :

************* Start of AD Controller session *************
AD Controller version: 12.0.0
AD Controller started at: Wed Sep 23 2009 22:25:12

APPL_TOP is set to /EBS121/bin/apps/apps_st/appl

Reading product information from file...

Reading language and territory information from file...

Reading language information from applUS.txt ...


Enter the ORACLE username of Application Object Library [APPLSYS] : apps

Enter the ORACLE password of Application Object Library [APPS] :

AD Controller is verifying your username/password.
The status of various features in this run of AD Controller is:

<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE No 1 -1 Y N N Y N N
PREREQ No 6 -1 Y N N Y N N
CONCURRENT_SESSIONS No 2 -1 Y Y N Y Y N
PATCH_TIMING No 2 -1 Y N N Y N N
PATCH_HIST_IN_DB No 6 -1 Y N N Y N N
SCHEMA_SWAP No 1 -1 Y N N Y Y N
JAVA_WORKER No 1 -1 Y N N Y N N
CODELEVEL No 1 -1 Y N N Y N N



AD Controller Menu
---------------------------------------------------

1. Show worker status

2. Tell worker to restart a failed job

3. Tell worker to quit

4. Tell manager that a worker failed its job

5. Tell manager that a worker acknowledges quit

6. Restart a worker on the current machine

7. Exit

After you have entered the APPLMGR username and password the above menu will open for ADCTRL.

Here we have 7 options.

You can start, stop and monitor the workers. Lets monitor status of workers with option 1:


Enter your choice [1] :


Control
Worker Code Context Filename Status
------ -------- ----------------- -------------------------- --------------
1 Run Generic R120 BOMFMCPY.pll Running
2 Run Generic R120 BOMFMMDE.pll Running
3 Run Generic R120 BOMFMOPR.pll Running
4 Run Generic R120 BOMFMREV.pll Running
5 Run Generic R120 BOMFMCON.pll Running
6 Run Generic R120 BOMFMBM2.pll Running
7 Run Generic R120 BOMFMBM3.pll Running
8 Run Generic R120 BOMFMBM1.pll Running

Undocumented Option 8:


This will allow you to continue processing a job and kill the adworker that failed.


Back at the main adctrl menu choose option 8:

Enter your choice [1] : 8

Enter the worker number(s)/range(s) or 'all' for all workers,
or press [Return] to go back to the menu :

To select the "Skip worker" option, type the number 8 and
press Return.

This can be useful in testing a patch and you need to let the patch or job run to completion.



ADWORKER LOGFILES for R12 EBS

How to find the log files for these workers? It is confusing to the newbie.

cd $APPL_TOP/admin/{ORACLE_SID}

Example:

apps@sandiego:/d1/bin/apps/apps_st/appl/admin
$ cd $APPL_TOP/admin/VIS/log

to find the adworker log files we do a ls command:

apps@sandiego:/d1/bin/apps/apps_st/appl/admin/VIS/log
$ ls -ls adw*.log
45871 -rw-r--r-- 1 apps dba 46934789 Sep 23 22:19 adwork001.log
25974 -rw-r--r-- 1 apps dba 26560214 Sep 23 22:19 adwork002.log
26037 -rw-r--r-- 1 apps dba 26637070 Sep 23 22:19 adwork003.log
25251 -rw-r--r-- 1 apps dba 25824196 Sep 23 22:18 adwork004.log
16379 -rw-r--r-- 1 apps dba 16750699 Sep 23 22:18 adwork005.log
17120 -rw-r--r-- 1 apps dba 17510037 Sep 23 22:18 adwork006.log
15612 -rw-r--r-- 1 apps dba 15965759 Sep 23 22:18 adwork007.log
16177 -rw-r--r-- 1 apps dba 16543829 Sep 23 22:18 adwork008.log

For each adworker process, a log file is generated that shows activity and errors if any. Lets examine one of these log files:


************* Start of AD Worker session *************
AD Worker version: 12.0.0
AD Worker started at: Fri Sep 18 2009 16:33:10

APPL_TOP is set to /d1/bin/apps/apps_st/appl

Worker id = 1
Started by: adpatch

NLS_LANG value from the environment is : AMERICAN_AMERICA.UTF8
NLS_LANG value for this AD utility run is : AMERICAN_AMERICA.UTF8

Connecting to SYSTEM......Connected successfully.

Already asked for the ORACLE username and password
of Application Object Library.
(The given username/password was: APPLSYS/*****)

Connecting to APPLSYS......Connected successfully.

The status of various features in this run of AD Worker is:

<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE Yes 1 1 Y N N Y N Y
PREREQ Yes 6 6 Y N N Y N Y
CONCURRENT_SESSIONS No 2 2 Y Y N Y Y N
PATCH_TIMING Yes 2 2 Y N N Y N Y
PATCH_HIST_IN_DB Yes 6 6 Y N N Y N Y
SCHEMA_SWAP Yes 1 1 Y N N Y Y Y
JAVA_WORKER Yes 1 1 Y N N Y N Y
CODELEVEL Yes 1 1 Y N N Y N Y


By understanding where to find the adworker log files you can troubleshoot E-Business issues quickly and become a better DBA.

Stay tuned as we cover more Apps DBA tips in future posts!

Cheers,
Ben

Tips and Tricks for Installing Oracle R12 E-Business

Dear readers,

I will present a brief overview of tips for monitoring the installation of an R12 E-Business environment for Oracle Applications. For our example, we installed a fresh new Vision instance of Oracle Applications (12.0.1) running under Oracle Enterprise Linux 5.2 (32 bit). During an installation, Oracle shows a basic splash screen:



Ok thats nice but still hides much of the actual work being performed under the covers. How do we troubleshoot the installation process? Oracle writes multiple log files during each step of the process for R12 to several directories:

Database Logfile ($ORACLE_HOME):
/d01/oracle/VIS/db/tech_st/10.2.0/appsutil/log

Apps Tier Logfile ($APPL_TOP):

/d01/oracle/VIS/inst/apps/VIS_bensr12/logs



R12 has several new changes from 11i Applications, one is the new instance directory
or INST_TOP directory.







We had a file access permissions problem during the install as reviewed by the output of the logfile:



Once we fixed it and granted correct permissions to it, we were able to complete the installation. Once the installation completes, R12 completes a series of post-install checks to verify all is correct. If anything fails, the Rapid Wizard installer throws an error with a red checkmark on items that have errors.



So, how do we fix this error? We have to bounce the apps tier and it solves the login page error. To do so, we use the scripts adstpall.sh and adstrtal.sh



Once the apps middle tier is back online, we can now access the login page for R12:



and once we sign in as sysadmin we are ready for training and testing with our new R12 Vision instance:



Stay tuned for upcoming tips with patching and concurrent manager tips with R12.

Fun and Games with OEL 5 Linux missing packages

Dear readers,

Recently as of yesterday I decided to do a new R12 EBS install this time with a Vision Demo instance to have a new EBS environment with fresh seed data to test our new features and for GRC (Govern Risk Control). I ran up2date oracle-validated to install the missing rpms and most of the R12 install went fine and took 9 hours!

However, after the install, I noticed that the listener and install did not succeed and found root cause was that some of the OEL Linux rpms failed to all download and install earlier. As shown below this is caused by kernel dependency problems:




Unfortunately in my enthusiasm and also due to the wee hours to get R12 setup, I did not realize my oversight. Then when R12 failed to start with listener errors and library missing errors and after search on Metalink, it dawned on me the issue was indeed these missing Redhat 5 packages. Oracle Enterprise Linux really is just Red Hat Linux kernel.

To resolve problems with the RPMs that failed to download and install correctly from the Oracle Unbreakable Linux Network (ULN), I had to run the up2date-nox --configure command and choose option 19 to bypass the kernel dependency warning.




We need to clear these warning messages out so that we can re-run up2date and download and install the missing Linux rpms.




And voila! Just like magic, we were able to download these key packages for Linux
so we can complete the R12 EBS installation.

Oracle 11g Release 2 Now Available

Oracle 11g Release 2 has been announced and available for download:

http://www.oracle.com/technology/software/products/database/index.html

As a beta tester for the Oracle 11gR2 team, I am excited and look forward to implementing many of the new features for 11gR2 with customers. Many new whitepapers are available at following sites:

http://www.oracledatabase11g.com/Authentication/Login_w.html?url=/?src=6811170&Act=28

I will be blogging on the many new features for 11gR2 in addition to Fusion Middleware and R12 EBS topics this year.

Cheers,
Ben

DBA Quick Tip of the week: Measuring Redo Generated

One topic that DBAs frequently need to assess when planning for new projects such as 11g Data Guard and 11g Streams is how to calculate redo generated by an Oracle database. This is key to understand because redo generation affects throughput and performance with both standby databases and replication based activities. I am going to show you a quick and dirty way to calculate the amount of redo generated by an Oracle 11g database. To do so, you will need to run a new 11g Automatic Workload Repository (AWR) report either from Oracle Enterprise Manager or via the awrrpt.sql script.
Second, your database must currently be licensed to use the AWR


Now lets examine our 11g AWR report:

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
BEN11G 3769640271 ben11g 1 18-Jun-09 15:17 11.1.0.6.0 NO

Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
sandiego.localdo Linux IA (32-bit) 1 .99

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 24 04-Aug-09 12:55:42 37 1.2
End Snap: 30 07-Aug-09 18:11:18 36 1.0
Elapsed: 4,635.59 (mins)
DB Time: 1.47 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 76M 76M Std Block Size: 8K
Shared Pool Size: 156M 156M Log Buffer: 5,988K


Scroll down to the following section of the AWR report:

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.0 0.0 0.00 0.03
DB CPU(s): 0.0 0.0 0.00 0.03
Redo size: 62.9 5,874.9
Logical reads: 1.2 113.1
Block changes: 0.3 31.5
Physical reads: 0.0 2.0
Physical writes: 0.0 2.0
User calls: 0.0 1.0
Parses: 0.1 9.9
Hard parses: 0.0 0.1
W/A MB processed: 8,664.4 808,959.0
Logons: 0.0 0.3
Executes: 0.2 18.8
Rollbacks: 0.0 0.0
Transactions: 0.0


Under the heading for Load Profile generated by the AWR, you can examine redo size generated by second, per transaction, per execution or per system call. So in our example above, we had a grand total of 62.9K of redo per second. We can correlate this to the V$ data dictionary performance views with the following SQL*PLUS script that queries against the V$LOG and V$DATABASE performance views:


SELECT Start_Date,
Start_Time,
Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),
2) AS Mbytes,
Vdb.NAME AS Dbname
FROM (SELECT To_Char(Vlh.First_Time,
'YYYY-MM-DD') AS Start_Date,
To_Char(Vlh.First_Time,
'HH24') || ':00' AS Start_Time,
COUNT(Vlh.Thread#) Num_Logs
FROM V$log_History Vlh
GROUP BY To_Char(Vlh.First_Time,
'YYYY-MM-DD'),
To_Char(Vlh.First_Time,
'HH24') || ':00') Log_Hist,
V$log Vl,
V$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date,
Log_Hist.Start_Time;



START_DATE START NUM_LOGS MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2009-03-21 15:00 8 400 BEN11G
2009-03-21 16:00 1 50 BEN11G
2009-03-22 10:00 1 50 BEN11G
2009-03-23 17:00 4 200 BEN11G
2009-03-25 21:00 2 100 BEN11G
2009-03-26 13:00 2 100 BEN11G
2009-04-06 22:00 1 50 BEN11G


We can then take the number of redo logs from the NUM_LOGS column of the above query and multiply by size of each online redo log (ORL) file.


SQL> select group#, members, bytes, archived, status
2 from v$log
3 ;

GROUP# MEMBERS BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 52428800 YES INACTIVE
2 1 52428800 NO CURRENT
3 1 52428800 YES INACTIVE


I will leave the math calculations up to you.

For those of you who cannot afford the AWR licensed option for 10g/11g, in this down economy, I will provide you with a low cost low tech way to figure out how much redo is generated by Oracle.

Another method to calculate redo generated for Oracle is to examine the pattern of log switches that occur within your Oracle database. Upon examination of your alert.log file, you can take a differential of log switches to assess exactly how much redo is generated at peak and idle times.

First lets perform some log switches in our test 11g database!

SQL> alter system switch logfile;

System altered.


Note: we are using 11gR1 on Oracle Enterprise Linux 5.2 for the examples here. So we start a new ADRCI session

[oracle@sandiego ~]$ adrci

ADRCI: Release 11.1.0.6.0 - Beta on Fri Aug 7 18:41:22 2009

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

ADR base = "/u01/app/oracle"
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/rdbms/ben11g/ben11g
2: diag/clients/user_oracle/host_3399978961_11
3: diag/clients/user_unknown/host_411310321_11
4: diag/tnslsnr/sandiego/listener
Q: to quit


2009-08-07 18:45:58.653000 -07:00
Thread 1 advanced to log sequence 21
Current log# 3 seq# 21 mem# 0: /u01/app/oracle/oradata/ben11g/redo03.log
2009-08-07 18:46:03.171000 -07:00
Thread 1 advanced to log sequence 22
Current log# 1 seq# 22 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log
Thread 1 cannot allocate new log, sequence 23
Checkpoint not complete
Current log# 1 seq# 22 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log
2009-08-07 18:46:05.556000 -07:00
Thread 1 advanced to log sequence 23
Current log# 2 seq# 23 mem# 0: /u01/app/oracle/oradata/ben11g/redo02.log
2009-08-07 18:46:07.060000 -07:00
Thread 1 advanced to log sequence 24
Current log# 3 seq# 24 mem# 0: /u01/app/oracle/oradata/ben11g/redo03.log
2009-08-07 18:46:09.209000 -07:00
Thread 1 advanced to log sequence 25
Current log# 1 seq# 25 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log



So you can add up the total number of logs between log switches and divide by total time to obtain a rough estimate.

By understanding how to calculate redo generation, you will be prepared to understand one key component of planning for your Data Guard, Streams or replication environments as this will give you some idea on how much network bandwidth you will require to efficiently move the redo logs between source and target databases.



UPDATE:


Fellow Oracle professional, Coskan Gundogar provided a SQL script to examine redo log generation. Here is the sample output:

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Aug 9 17:06:48 2009

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


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

SQL> select trunc(first_time),sum(blocks*block_size)
from (select distinct first_change#,first_time,blocks,block_size,completion_time
from v$archived_log)
group by trunc(first_time)
order by trunc(first_time);

TRUNC(FIR SUM(BLOCKS*BLOCK_SIZE)
--------- ----------------------
26-MAR-09 51228160
06-APR-09 44119040
18-JUN-09 35488768
07-AUG-09 51233280

The primary difference in this script is that it queries redo generation from the V$ARCHIVED_LOG dynamic performance view rather than the earlier script that queries the V$LOG_HISTORY and V$LOG dynamic performance views.

So there you have it, multiple ways to calculate exactly how much redo is generated by your Oracle database! Stay tuned, next time we will provide some quick tips on tuning network and disk I/O for your Oracle database.

Cheers,
Ben

New Books on SOA and Oracle technology from PACKT Press

As I am writing a new book for Oracle 11g RAC, my new publisher asked me to review new Oracle books on SOA technology. Below are exciting new titles from PACKT Press.


SOA Developer Suite Guide




The SOA Developer Suite Guide provides a comprehensive introduction to deploying robust SOA architectures with the Oracle SOA Suite and BPEL language. Since many clients use or plan to deploy SOA, I find this useful as a DBA and Oracle consultant to quickly learn how to architect elegant solutions with SOA and Oracle technology.




SOA Cookbook





I like this book as I am new to many of the SOA developer tools from Oracle including the BPEL language. This guide is excellent primer for learning how to implement and deploy SOA with Oracle! The hands on approach is excellent for Oracle developers who need to ramp up quickly with creating new SOA architectures.


BPEL Cookbook






Next we have a cookbook for SOA technology with Oracle. The book BPEL Cookbook is great for learning techniques to develop robust BPEL programs for SOA implementations with Oracle.



Open LDAP Developer Guide




LDAP is a network methodology for deploying elegant network solutions with many Oracle Fusion Middleware products
This guide provides a nice solid foundation for Oracle identity management and security professionals who need to understand LDAP concepts for deployments.


Finally, I am reviewing a book on Oracle Hyperion Essbase which is complex OLAP business intelligence technology in use by many large companies these days that deploy E-Business and data warehouse environments.

Oracle Hyperion Essbase 9 Implementation Guide

Voyage to the Mothership- Oracle HQ

It is intermission time so grab your popcorn, in our break from a pure tech topic, I want to blog on my amazing trip to the mothership, that is to Oracle headquarters in Redwood Shores! In my 12+ years of working on Oracle databases, this was my first pilgrammage to visit the campus and HQ for Oracle!






I have always wanted to take a trip to where it all began.




As an Oracle Professional, I have interviewed many times with Oracle but unfortunately it either was not the right fit or recently a hiring freeze nixed my efforts to join Oracle. It really is luck and timing. It really is about good fortune, timing and knowing the right people at the right time in life.

I finally finished my third book on Oracle database technology. What led me to the visit to Oracle HQ you might ask? I went to a session on Oracle R12: it was fun and helped to refresh my EBS DBA skills on patching, cloning, and managing Oracle 12i or R12 EBS environments. I met some cool people most of the class works for Oracle support! It was fun! And I even managed to fix my errors in my VMWare configuration for R12 EBS on Oracle Enterprise Linux! Had to fix routing and network configuration issues then had to stop and restart the apps tier services. And voila! Now my VMWare works for R12! Woohoo!

I can say this: if you have the golden opportunity to visit Oracle HQ: take it! Oracle employees at HQ in Redwood Shores are blessed to work on one of the nicest corporate environments. Period. From the amazing fitness center with classes, gym, and Olympic swimming pool to amazing gourmet meals served at the many cafes on campus at budget prices (I had a grilled NY strip steak for less than $8!), people seem happy to work for and at Oracle! That explains why Oracle DEMANDS Ivy League pedigrees from many potential employees such as Stanford. I ran into Julian Dyke while learning new features for R12. Such a great RAC expert and very nice guy. Funny the people we run into by chance. Now, I did a session at another Oracle center in Los Angeles and in stark contrast, the center was NOT as nice as Oracle HQ! Views of Barbie and Mattel do not compare to the serenity of Oracle HQ!




Besides the 20 foot parking structure with Ken and Barbie, I really was not impressed by El Segundo, California and the one floor that Oracle training occupies there. But, a short 2 hour drive was more convenient from San Diego than the flight required to SFO for my visit to Oracle HQ. Was it worth it? Heck yeah, and if I am lucky to meet Larry someday and can pick his brain for a few minutes, perhaps I can make the planet a better and happier place with less suffering,

Oracle R12 EBS Applications with Oracle 10g Application Server

Dear readers,

We will provide some tips on how to integrate key features of Oracle 10gAS with the R12 EBS Applications suite.
First, we will provide a quick summary of the architecture that exists within Oracle E-Business Suite Release 12 in terms of
Oracle 10gAS.

Oracle Metalink Note # 415007.1 is a FAQ that provides a useful summary of ways to integrate the Oracle R12 EBS Suite with Oracle 10g Application Server. Even though Oracle Weblogic is the heir apparent for future Application Server/Fusion Middleware directions as evidenced by the SOA suite products, fact is that Oracle 10g Application Server is the core middleware technology at work for Oracle R12 Applications E-Business Suite.

One key difference between a stand alone Oracle 10g Application Server environment versus R12 EBS is that the
Oracle 10gAS Infrastructure is NOT SUPPORTED in the Release 12 of Oracle E-Business Suite for Oracle 12i Applications. Oracle Metalink Note 251627.1tells further details on how to workaround this issue.


Oracle Application Server 10g integration with Oracle EBS R12


Oracle EBS R12 uses many of the components that exist in Oracle 10g Application Server. For Oracle R12, these include:

- OHS- the Oracle HTTP Server (Apache 1.3)
- PL/SQL
- Forms Server
- Reports Server
- OC4J- Oracle Containers for J2EE


What versions of Oracle Application Server 10g can be used with Oracle E-Business Suite Release 12?


Oracle EB R12 has an Oracle 10g application server instance (10.1.2) that runs the Forms and Reports Services. In addition to this application server instance, we also have a secondary application server instance (10.1.3) which is used to run the OC4J instance. By default, when you install Oracle EBS R12, these 10gAS instances are installed and created by the R12 Rapid Install process.

Oracle R12 EBS supports the following Oracle Application Server 10g services:

Single Sign-On 10g (10.1.2.0.2)
Oracle Internet Directory 10g (10.1.2.0.2)
Portal 10g (10.1.4)
Discoverer 10g (10.1.2.0.2)
Web Cache 10g (10.1.2.0.2)
Oracle Business Integration 10g (10.1.3)
Oracle BPEL Process Manager 10g (10.1.3)

A complete discussion on how to setup and configure all of these products with EBS R12 would require a book in itself so I will kindly refer you to the Oracle EBS R12 Tech Stack Roadmap guide available in Oracle Metalink Note # 380482.1

Our main discussion will now shift to SSL and R12 EBS for Oracle 10g Application Server.

I have two homework assignments for you, besides the previous Metalink notes, I have another humble request. Either download or order the 29 DVDs for R12 EBS. Install a new test instance single tier for R12. You can do this at low cost with an external 250Gb portable drive, low cost laptop with 2-4Gb RAM, and VMWare. I built my R12 EBS test environment on Oracle Enterprise Linux for under $1000 and this included all softwares which are available from the Oracle Edelivery store (about $125 for non-commercial usage trial learning versions). Second, the following Metalink Notes are germane to our discussion for SSL, OID, and SSO with R12 EBS and Oracle 10g Application Server.

Note 376811.1, Integrating Oracle E-Business Suite Release 12 with Oracle Internet Directory and Oracle Single Sign-On

Note 403537.1, Best Practices for Securing Oracle E-Business Suite Release 12
Note 376694.1, Using the Oracle Wallet Manager Command Line Interface with Oracle E-Business Suite Release 12
Note 376700.1, Enabling SSL in Oracle Applications Release 12
Note 380490.1, Oracle E-Business Suite Release 12 Configuration in a DMZ

Since we already covered OID and SSO in great detail, for now, I will focus on SSL and R12 EBS for Oracle 10g Application Server.

SSL and Oracle 12i (Release 12) Applications


For us, the biggest change for using the Secure Sockets Layer (SSL) in terms of R12 and Oracle 10gAS is the use of mod_ossl module (plugin) for the Oracle HTTP Server (OHS). Both mod_ssl and mod_ossl enable strong cryptography for OHS (Apache 1.3). However, the key difference to note here is that mod_ossl is based on Oracle's implementation of SSL which supports SSL 3.

Oracle R12 EBS manages SSL certificates from the Oracle Wallet Manager (OWM) which we saw earlier in addition, we can also manage the wallets for SSL certificates with the new ORAPKI command line interface (CLI). The good news here for Oracle EBS, is that R12 now uses the Forms Listener Servlet, so we do not have a Forms server that existed in earlier releases of EBS such as 11i. This means that a separate certificate is no longer needed for Forms. Forms share the same wallet as the Oracle HTTP Server (OHS). However, I would like to point out an important caveat or gotcha. We no longer can use the Forms Server listener with the ConnectMode=https. This is no longer supported in Release 12i for Oracle E-Business Applications with R12. For R12 EBS, ConnectMode=https will only work with the JInitiator that contains the Oracle SSL libraries. Release 12 uses Sun's Java Plugin so in case you need to use https for the forms communication layer, it is imperative that you use the servlet architecture.

Middle Tier Configuration Steps to Enable SSL with Oracle R12 Applications



By default the location for the Oracle wallet in R12 is under $INST_TOP/certs/Apache. This directory contains a wallet with demo certificates. If you wish to use these certificates for testing start with Step 8 below to configure SSL, and then do Steps 1 through 7 when you are ready to switch to real certificates.

The demo certificates are not secure and should never be used in a production environment.

The main steps for setting up SSL on the Middle Tier are:

1. Set Your Environment.
2. Create a wallet.
3. Create a Certificate Request.
4. Submit the Certificate Request to a Certifying Authority.
5. Import your Server Certificate to the Wallet.
6. Copy the Apache Wallet to the OPMN Wallet.
7. Update the JDK Cacerts File.
8. Update the Context File.
9. Run Autoconfig.
10. Restart the middle tier services.

We will use the Oracle Wallet Manager (OWM) to configure SSL and certificates.
An optional method is to use the CLI (Command Line Interface) with Oracle Wallet Manager for R12. To do so, I refer you to Oracle Metalink Note# 376694.1: Using the Oracle Wallet Manager Command Line Interface in Release 12. Ready for some real fun? OK! Great, lets configure SSL for Oracle E-Business Release 12i.

Set the R12 Environment

Login as applmgr or the OS user who owns the Apps Tier for your Oracle 12i EBS environment.
Source the environment file

[applmgr@ebs scripts]$ whoami
applmgr
[applmgr@ebs scripts]$ cd $APPL_TOP
[applmgr@ebs appl]$ . APPSPROD_ebs.env
[applmgr@ebs appl]$


Create the Wallet



Navigate to $INST_TOP/certs/Apache directory to create the new wallet.

[applmgr@ebs appl]$ cd $INST_TOP/certs/Apache
[applmgr@ebs Apache]$ pwd
/d01/app/oracle/inst/apps/PROD_ebs/certs/Apache
[applmgr@ebs Apache]$ ls
cwallet.sso ewallet.p12

You should see the demo wallets that Rapid Install created after R12 was installed as shown above. Start the OWM (Oracle Wallet Manager) as shown in the figure example listed below.




Select the option Wallet-> New
It will prompt you with “Your default wallet directory doesn't exist. Do you wish to create it now?” Choose No.





The new wallet screen will now prompt you to enter a password for your wallet.
Enter the password



The new empty wallet is created. We do need to create the new certificate request so choose yes.





Common Name: is the name of your server including the domain.
Organizational Unit: (optional) The unit within your organization.
Organization: is the name of your organization.
Locality/City: is your locality or city.
State/Province: is the full name of your State or Province - do not abbreviate.

Select your Country from the drop down list.
Click OK.







Submit the Certificate Request to a Certifying Authority (CA)


We see our new wallet details below.




Now that we have our new wallet, we will need to export the Certificate Request and then submit it to a Certifying Authority (CA). Here are the steps.


Click on Certificate [Requested] to Highlight it.
From the menu click Operations -> Export Certificate Request





Save the file as server.csr
From the menu click Wallet and then click Save.




Change the Directory to the fully qualified wallet directory which in our case would be
/d01/app/oracle/inst/apps/PROD_ebs/certs/Apache


Click the OK button.


From the menu click Wallet and check the Auto Login box.




We will need to use the password whenever we open the wallet with Oracle Wallet Manager or if we ever need to perform operations on the wallet. Auto login allows for simplified maintenance tasks in the future.

Exit out of OWM

Our wallet directory now contains the following files:

[applmgr@ebs Apache]$ whoami;pwd;ls
applmgr
/d01/app/oracle/inst/apps/PROD_ebs/certs/Apache
cwallet.sso ewallet.p12 server.csr



Import Server Certificate to the Wallet.


Once we have received our Server Certificate the Certifying Authority (CA) we need to import it into our wallet. We copy the certificate to server.crt in the wallet directory by either using ftp or copy and paste into a server.crt file.
We use OWM (Oracle Wallet Manager) to perform this task.


Modify Wallet for Oracle Process and Notification Manager (OPMN)


Oracle Applications R12 Rapid Install creates a default "demo" type opmn wallet in the $INST_TOP/certs/opmn directory that is available for basic SSL testing. Now that the Apache wallet has been created for us, we need to to use these same certificates for opmn. We need to perform the the following steps to backup and copy the wallets:

Navigate to the $INST_TOP/certs/opmn directory.
Create a new directory named BAK
Move the ewallet.p12 and cwallet.sso files to the BAK directory just created.
Copy the ewallet.p12 and cwallet.sso files from the $INST_TOP/certs/Apache directory to the $INST_TOP/certs/opmn directory.


[applmgr@ebs Apache]$ cd $INST_TOP/certs/opmn
[applmgr@ebs opmn]$ mkdir BAK
[applmgr@ebs opmn]$ cp /d01/app/oracle/inst/apps/PROD_ebs/certs/Apache/e* cw* $INST_TOP/certs/opmn/BAK

[applmgr@ebs BAK]$ pwd
/d01/app/oracle/inst/apps/PROD_ebs/certs/opmn/BAK
[applmgr@ebs BAK]$ ls
cwallet.sso ewallet.p12


Make SSL Updates for R12 EBS Context File.



Now we need to update our R12 EBS Context file variables to account for SSL configuration.
To do so, we need to use the Oracle Applications Manager (OAM) Context Editor to change the SSL related variables as shown in the following screenshots.




We choose AutoConfig under System Configuration on the Oracle 12i Applications Manager (OAM) interface to access our context files.




Now we need to edit the Context file for Apps Tier to enable SSL for R12 E-Business environment.
Change variables for s_url_protocol from http to https for SSL and make sure to set the s_webssl_port to 4433 which is the default SSL port. Below is an example.









Finalize changes for R12 EBS and SSL Configuration - Autoconfig


Now we need to run the Autoconfig utility to have our changes made to our R12 context file for SSL. We do this by executing the adautocfg.sh script located in the Middle Tier (Apps Tier) under the $ADMIN_SCRIPTS_HOME directory.

$ cd $ADMIN_SCRIPTS_HOME
[applmgr@ebs scripts]$ ls
adalnctl.sh adcmctl.sh adformsrvctl.sh adopmnctl.sh adstrtal.sh java.sh L308828.log sqlnet.log
adapcctl.sh adexecsql.pl adoacorectl.sh adpreclone.pl gsmstart.sh jtffmctl.sh mwactl.sh
adautocfg.sh adformsctl.sh adoafmctl.sh adstpall.sh ieo L308827.log mwactlwrpr.sh

$ adautocfg.sh
Enter the APPS user password:

The log file for this session is located at: /d01/app/oracle/inst/apps/PROD_ebs/admin/log/05222339/adconfig.log

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.
Using CONFIG_HOME location : /d01/app/oracle/inst/apps/PROD_ebs
Classpath : /d01/app/oracle/apps/apps_st/comn/java/lib/appsborg2.zip:/d01/app/oracle/apps/apps_st/comn/java/classes

Using Context file : /d01/app/oracle/inst/apps/PROD_ebs/appl/admin/PROD_ebs.xml

Context Value Management will now update the Context file
The log file for this session is located at: /d01/app/oracle/inst/apps/PROD_ebs/admin/log/05222339/adconfig.log

AutoConfig is configuring the Applications environment...

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Configuring templates from all of the product tops...
Configuring AD_TOP........COMPLETED
Configuring FND_TOP.......COMPLETED
Configuring ICX_TOP.......COMPLETED
Configuring IEO_TOP.......COMPLETED
Configuring BIS_TOP.......COMPLETED
Configuring AMS_TOP.......COMPLETED
Configuring CCT_TOP.......COMPLETED
Configuring WSH_TOP.......COMPLETED
Configuring CLN_TOP.......COMPLETED
Configuring OKE_TOP.......COMPLETED
Configuring OKL_TOP.......COMPLETED
Configuring OKS_TOP.......COMPLETED
Configuring CSF_TOP.......COMPLETED
Configuring IGS_TOP.......COMPLETED
Configuring IBY_TOP.......COMPLETED
Configuring JTF_TOP.......COMPLETED
Configuring MWA_TOP.......COMPLETED
Configuring CN_TOP........COMPLETED
Configuring CSI_TOP.......COMPLETED
Configuring WIP_TOP.......COMPLETED
Configuring CSE_TOP.......COMPLETED
Configuring EAM_TOP.......COMPLETED
Configuring FTE_TOP.......COMPLETED
Configuring ONT_TOP.......COMPLETED
Configuring AR_TOP........COMPLETED
Configuring AHL_TOP.......COMPLETED
Configuring OZF_TOP.......COMPLETED
Configuring IES_TOP.......COMPLETED
Configuring CSD_TOP.......COMPLETED
Configuring IGC_TOP.......COMPLETED

AutoConfig completed successfully.
[applmgr@ebs scripts]$


Restart Apps R12 EBS middle tier services.


Our final step on the road to SSL for Oracle Release 12 for the E-Business Suite is to stop and restart the middle tier services. We use the adapcctl.sh script located in the $ADMIN_SCRIPTS_HOME directory to stop and restart the middle tier Apache services.

[applmgr@ebs scripts]$ adapcctl.sh stop

You are running adapcctl.sh version 120.6.12000000.2

Stopping OPMN managed Oracle HTTP Server (OHS) instance ...

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /d01/app/oracle/inst/apps/PROD_ebs/logs/appl/admin/log/adapcctl.txt for more information ...

[applmgr@ebs scripts]$ adapcctl.sh start

You are running adapcctl.sh version 120.6.12000000.2

Starting OPMN managed Oracle HTTP Server (OHS) instance ...

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /d01/app/oracle/inst/apps/PROD_ebs/logs/appl/admin/log/adapcctl.txt for more information ...

[applmgr@ebs scripts]$ adapcctl.sh status

You are running adapcctl.sh version 120.6.12000000.2

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: PROD_ebs.ebs.sandiego.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
OC4J | oafm | 8269 | Alive
OC4J | forms | 8267 | Alive
OC4J | oacore | 8268 | Alive
HTTP_Server | HTTP_Server | 21490 | Alive


adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /d01/app/oracle/inst/apps/PROD_ebs/logs/appl/admin/log/adapcctl.txt for more information ...


There are of course many advanced options for SSL configuration beyond the scope of what we have covered today. I encourage you to dig deeper into the above reference Metalink notes and test to find out what SSL options will benefit your Oracle E-Business Release 12 environments. As you can now see, SSL configuration requires different steps for the E-Business Suite than for a stand alone Oracle 10g Application Server environment. Until next time where we provide some troubleshooting tips on Oracle 10g Application Server.

Cheers,
Ben