Welcome to the database wizard!

How to fix archive stuck error with Oracle 11gR2

2011-11-28T11:33:00.000-08:00

Recently, I encountered an issue with Oracle R12 EBS environment unable to login to database tier with APPS schema with our Oracle 11gR2 database tier.

ORA-00257: archiver error. Connect internal only, until freed.

Sure enough, upon examination of the ADRCI alert.log entries, I found this:

* 2011-10-27 16:24:51.990 4265 krsh.c
ARC1: Error 19809 Creating archive log file to '+FRA'
*** 2011-10-27 16:24:51.990 2864 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
DDE rules only execution for: ORA 312
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
DDE: Problem Key 'ORA 312' was flood controlled (0x5) (no incident)
ORA-00312: online log 2 thread 3: '+DATA/vis/onlinelog/group_2.259.743840813'
ORA-00312: online log 2 thread 3: '+FRA/vis/onlinelog/group_2.259.743840815'

Solution:
The Fast Recovery Area (FRA) is full with old archive log files so we need to clean this up by removing old archive logs.
If you have production environment, back these up to tape or storage for recovery and archival purposes.

Login to RMAN and run the following commands:

RMAN> CONNECT target /

connected TO target DATABASE: INSTANT (DBID=1234567890)

RMAN> crosscheck archivelog ALL;

Note: You can schedule this command once a week.
The crosscheck command does NOT DELETE the information about the logs that it
could NOT find ON disk, it just updates their STATUS IN the repository AS 'EXPIRED'.
TO obtain a list OF logs marked AS 'EXPIRED' USE the following command:

RMAN> list expired archivelog ALL;

IF it IS NOT necessary TO keep the information about these
logs IN the repository, DELETE them WITH command:

RMAN> DELETE expired archivelog ALL;

RMAN> delete archivelog all completed before 'SYSDATE-1';

The above RMAN command will delete old archive logs past 24 hours.

Now you should be able to connect to the database.

Below are some useful queries to check on FRA available space:

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 "%" FROM v$asm_diskgroup;

NAME FREE_MB TOTAL_MB %
------------------------------ ---------- ---------- ----------
DATA 823641 1044450 78.8588252
FRA 207743 511992 40.5754387
LOG 306958 307191 99.9241514

SQL>

set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/

NAME Size MB Used MB
------------------------------------------------------------ ---------- ----------
+FRA 102400 302965

If you want to change the default size for the FRA you can issue the following command from SQL*PLUS:

SQL> alter system set db_recovery_file_dest_size=600G;

System altered.

Then you should archive logs:
SQL> archive log all;
3 logs archived.

My review Oracle OpenWorld 2011

2011-10-16T13:58:00.001-07:00

Dear readers,

Oracle OpenWorld 2011 was a fantastic event with many new product announcements including Oracle Public Cloud as well as Oracle Big Data Appliances along with Exalytics for Business Intelligence. Before the conference, I had the pleasure of attending the Oracle ACE Director briefing at Oracle HQ in Redwood Shores under NDA to learn of new developments at Oracle.

After the ACED briefing, I headed to San Francisco with my fellow Oracle ACE Director, Arup Nanda and we checked into the Hilton Hotel. The computers failed during checkin so we had the keep the patience of a saint which paid off with a free complimentary full breakfast. My hotel room had the most amazing view of the entire San Francisco Bay area including views of the Golden Gate bridge and Alcatraz island!

My favorite part of the conference was meeting new people and the special interest groups (SIG) for virtualization with Oracle. I had a great time meeting a lot of Oracle gurus including Jonathan Lewis, Tim Gorman, Doug Burns, James Morle, Michael Abbey, and my co-author Syed Jaffar Hussein of my Oracle 11g RAC book as well as James and Kerry from PACKT Press. Some more cool things at OOW this year was the America's cup winning yacht:

This year we had a great time at OTN night and the appreciation event was wonderful with Sting in concert. Funny because it seemed like Sting was singing in slow motion on songs like Roxanne!

The lightshow with Sting was incredible!

On Tuesday, I delivered my session on database security and would like to thank the 400+ attendees who came to my session. I do hope it was informative and useful to your journey to implementing security for your Oracle environments. The weather was a bit challenging with lots of rain and cold this year.

Fortunately I had an umbrella to keep me dry.
I would like to thank Justin, Lillian, and Vikki for all their hard work to make the OOW conference and ACE briefing a total victory and success this year.

OpenWorld 2011

2011-09-28T11:41:00.000-07:00

Dear readers,

It is rapidly approaching time again for one of the best technical conferences, Oracle OpenWorld!

Come learn how to secure your Oracle database environment with new features at my session next week.

Session ID: 02180
Session Title: Lockdown: Secure Your Database in a Day
Room: Moscone South- 303
Date/Time: 10/4/11, 11:45-12:45

I will also be hosting a book signing after the event in the Oracle bookstore at OpenWorld. Hope to see everyone there.

Cheers,
Ben

Howto- Install Oracle 11g Grid Control Enterprise Manager (OEM) Server

2011-09-20T21:14:00.001-07:00

With the new features for Oracle 11g Grid Control Enterprise Manager (OEM), it is valuable tool now for managing large Oracle environments. I recall from Oracle 9i OEM days that it was a buggy tool and back then years ago, I used SQL*Plus and command line tools. However, in 11gR1, OEM has emerged as an excellent monitoring tool. I still use command line tools most of the time but for monitoring, its a great product. With that said, let's get started in building a new Oracle11gR1 Grid Control Enterprise Manager (OEM) environment on Linux!

Preparation

OEM 11g Grid Control server is a resource hog and requires A LOT more memory than 10g OEM Grid Control so make sure you allocate a new server with at least 16GB of RAM or you will have performance issues.

Summary of Steps

1. Download the OEM 11g Grid Control software from Oracle Edelivery or OTN site (otn.oracle.com)
2. Download Oracle Weblogic 10.3.2 Server

Note; DO NOT use later version of Weblogic as this will NOT work with setup for 11g Grid Control Server!

3. Download Java JDK version 1.6 or later
4. Download required packages for Linux

Setup Java and Weblogic:

1. install and configure java

export PATH=$PATH:/usr/local/jdk/jdk1.5.0_10/bin

2. install and setup oem grid database
In our case, we used Oracle 11.2.0.2 database for the repository with Grid Control

3. post-db config tasks

$ emca -deconfig dbcontrol db -repos drop -SYS_PWD -SYSMAN_PWD

Login to the OEM repository Oracle 11gR2 database
Make the following initialization parameter changes.

sqlplus / AS SYSDBA

ALTER SYSTEM SET log_buffer=10485760 SCOPE=SPFILE;
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
ALTER SYSTEM SET session_cached_cursors=200 SCOPE=SPFILE;

In another shell window, un-install the database control:

$ emca -deconfig dbcontrol db -repos drop -SYS_PWD oracle -SYSMAN_PWD oracle

STARTED EMCA at Sep 3, 2011 11:43:41 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: oem
Listener port number: 1521

----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 3, 2011 11:43:54 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/oem/emca_2011_09_03_11_43_41.log.
Sep 3, 2011 11:43:54 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Sep 3, 2011 11:44:15 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Sep 3, 2011 11:45:23 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 3, 2011 11:45:25 AM

4. Install weblogic server 10.3.2

$ java -d64 -jar wls1032_generic.jar

5. Apply patch for weblogic

Next we must apply the "WDJ7" patch to the installation by running the Smart Update utility.

Notes:

The installation will complete without this patch, so if you have any trouble you can ignore it.
Newer versions of the Smart Update utility no longer allow you to download patches using the utility itself. For the revised patch download instructions see MOS Notes 876004.1 and 1302053.1.

download patch from MOS site

unzip p9438213_10320_Generic.zip -d /u01/weblogic/utils/bsu/cache_dir

Execute the following command to apply the Weblogic patch with the BSU utility:

Syntax:
./bsu.sh -prod_dir= -patch_download_dir= -status=downloaded -view -verbose

For example:
./bsu.sh -prod_dir=/opt/bea/weblogic92 -patch_download_dir=/opt/bea/utils/bsu/cache_dir -status=downloaded -view -verbose

This is the syntax for the command to install a patch:

./bsu.sh -prod_dir=/u01/weblogic -patchlist=WDJ7 -verbose -install

This is the syntax for the command to check if the patch is installed:
./bsu.sh -prod_dir= -patch_download_dir= -status=applied -verbose -view

For example:
./bsu.sh -prod_dir=/opt/bea/weblogic92 -status=applied -verbose -view

$ cd /u01/app/oracle/Middleware/utils/bsu
$ sh bsu.sh
Update the patch client by clicking the "OK" button on the message dialog.

./bsu.sh -prod_dir=/u01/weblogic/wlserver_10.3 -patch_download_dir=/u01/weblogic/utils/bsu -patchlist=4D53,NIXN,XLXA -verbose -install

./bsu.sh -prod_dir=/u01/weblogic/wlserver_10.3 -patch_download_dir=/u01/weblogic/utils/bsu -patchlist=WDJ7 -verbose -install

Unable to apply patch for weblogic 10.3.2 server

Oh no Mr. Bill! We hit a Weblogic bug! Seems like the BSU utility does not play well with some online systems. So, we tried the following approach:

$ ./bsu.sh -prod_dir=/u01/weblogic/wlserver_10.3 -patch_download_dir=/u01/weblogic/utils/bsu -patchlist=4D53,NIXN,XLXA -verbose -install

Encountered unrecognized patch ID: 4D53
Encountered unrecognized patch ID: NIXN
Encountered unrecognized patch ID: XLXA

$ ./bsu.sh -view -patch_download_dir=/u01/weblogic/utils/bsu/cache_dir -status=applied -verbose -prod_dir=/u01/weblogic/wlserver_10.3
ProductName: WebLogic Server
ProductVersion: 10.3 MP2
Components: WebLogic Server/Core Application Server,WebLogic Server/Admi
nistration Console,WebLogic Server/Configuration Wizard and
Upgrade Framework,WebLogic Server/Web 2.0 HTTP Pub-Sub Serve
r,WebLogic Server/WebLogic SCA,WebLogic Server/WebLogic JDBC
Drivers,WebLogic Server/Third Party JDBC Drivers,WebLogic S
erver/WebLogic Server Clients,WebLogic Server/WebLogic Web S
erver Plugins,WebLogic Server/UDDI and Xquery Support,WebLog
ic Server/Workshop Code Completion Support
BEAHome: /u01/weblogic
ProductHome: /u01/weblogic/wlserver_10.3
PatchSystemDir: /u01/weblogic/utils/bsu
PatchDir: /u01/weblogic/patch_wls1032
Profile: Default
DownloadDir: /u01/weblogic/utils/bsu/cache_dir
JavaVersion: 1.6.0_14
JavaVendor: Sun

Let's Fix this issue with apply Weblogic Patch:

Solution: rename patch-catalog-

I found the answer on the OTN Oracle forums:

https://forums.oracle.com/forums/thread.jspa?threadID=1064396

Now, this is not supported by Oracle so you should not do this in a production environment without guidance of Oracle support! Since we are doing a test setup, we can experiment!

$ mv patch-catalog_15563.xml patch-catalog.xml
$ cd ..
$ ./bsu.sh -prod_dir=/u01/weblogic/wlserver_10.3 -patch_download_dir=/u01/weblogic/utils/bsu/cache_dir -patchlist=4D53,NIXN,XLXA -verbose -install
Checking for conflicts..
No conflict(s) detected

Starting installation of Patch ID: 4D53
Installing /u01/weblogic/utils/bsu/cache_dir/4D53.jar
Extracting /u01/weblogic/patch_wls1032/patch_jars/Bug9100465_1032.jar
Updating /u01/weblogic/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar
Old manifest value: Class-Path=
New manifest value: Class-Path=../../../patch_jars/Bug9100465_1032.jar
Result: Success

Starting installation of Patch ID: NIXN
Installing /u01/weblogic/utils/bsu/cache_dir/NIXN.jar
Extracting /u01/weblogic/patch_wls1032/patch_jars/bug8990616_1032.jar
Updating /u01/weblogic/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar
Old manifest value: Class-Path=../../../patch_jars/Bug9100465_1032.jar
New manifest value: Class-Path=../../../patch_jars/bug8990616_1032.jar ../../../patch_jars/Bug9100465_1032.jar
Result: Success

Starting installation of Patch ID: XLXA
Installing /u01/weblogic/utils/bsu/cache_dir/XLXA.jar
Extracting /u01/weblogic/patch_wls1032/patch_jars/Bug9221722_1032.jar
Updating /u01/weblogic/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar
Old manifest value: Class-Path=../../../patch_jars/bug8990616_1032.jar ../../../patch_jars/Bug9100465_1032.jar
New manifest value: Class-Path=../../../patch_jars/Bug9221722_1032.jar ../../../patch_jars/bug8990616_1032.jar ../../../patch_jars/Bug9100465_1032.jar
Result: Success

Verify weblogic patch was installed successfully

Ok, lets verify again that the Weblogic Patch was applied:

$ ./bsu.sh -view -patch_download_dir=/u01/weblogic/utils/bsu/cache_dir -status=applied -verbose -prod_dir=/u01/weblogic/wlserver_10.3

ProductName: WebLogic Server
ProductVersion: 10.3 MP2
Components: WebLogic Server/Core Application Server,WebLogic Server/Admi
nistration Console,WebLogic Server/Configuration Wizard and
Upgrade Framework,WebLogic Server/Web 2.0 HTTP Pub-Sub Serve
r,WebLogic Server/WebLogic SCA,WebLogic Server/WebLogic JDBC
Drivers,WebLogic Server/Third Party JDBC Drivers,WebLogic S
erver/WebLogic Server Clients,WebLogic Server/WebLogic Web S
erver Plugins,WebLogic Server/UDDI and Xquery Support,WebLog
ic Server/Workshop Code Completion Support
BEAHome: /u01/weblogic
ProductHome: /u01/weblogic/wlserver_10.3
PatchSystemDir: /u01/weblogic/utils/bsu
PatchDir: /u01/weblogic/patch_wls1032
Profile: Default
DownloadDir: /u01/weblogic/utils/bsu/cache_dir
JavaVersion: 1.6.0_14
JavaVendor: Sun

Patch ID: 4D53
PatchContainer: 4D53.jar
Checksum: -653702491
Severity: optional
Category: Web App
CR/BUG:
Restart: true
Description: weblogic http client implementation, does not handle keepali
ve conf (http1.0)

Patch ID: NIXN
PatchContainer: NIXN.jar
Checksum: -1413651003
Severity: optional
Category: Core
CR/BUG:
Restart: true
Description: Connecting to wls with non-ssl IIOP port fails if the port i
s enabled on a server running without a non-ssl IIOP channel
.

Patch ID: XLXA
PatchContainer: XLXA.jar
Checksum: 1670664663
Severity: optional
Category: Web App
CR/BUG:
Restart: true
Description: Jsp compiler shouldn't recompile jsp page when page-check-se
conds is set to -1

Whew! Now, that we have Weblogic 10.3.2 installed and patched, we need to change settings and resize the OEM 11g Grid Control repository database.

Resize undo tablespace to 500G

To avoid an error during installation for Grid Control, you need to resize the undo tablespace.

select file_name from dba_data_files where tablespace_name ='UNDOTBS1';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/oem/undotbs01.dbf

Then we alter the size of the undo tablespace file, set autoextend on and maxsize to unlimited:

SQL> select file_name from dba_data_files where tablespace_name ='UNDOTBS1';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/oem/undotbs01.dbf

SQL> alter database datafile '/u01/app/oracle/oradata/oem/undotbs01.dbf' resize 1500M;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/oem/undotbs01.dbf' autoextend on maxsize unlimited;

Database altered.

Start the OEM 11g Grid Control installation program with ./runInstaller

I will spare you the screenshots as these are available online in the Oracle 11g Grid Control installation documentation and also at Dr. Tim Hall's blog at http://www.oracle-base.com/articles/11g/GridControl11gR1InstallationOnOEL5.php

Once the Grid Control setup completes, take down the following information for reference:

1. Enterprise Manager Grid Control URL: https://oem:7799/em
2. Admin Server URL: https://oem:7101/console

The following details need to be provided during the additional OMS install:

1. Admin Server Hostname: oem
2. Admin Server Port: 7101

Special thanks to Dr. Tim Hall for his excellent blog spot and walk through on 11g Grid Control. As you can see, Oracle 11gR1 Grid Control is far more resource intensive to setup and run than previous versions.

Oracle 11gR1 Grid Control Enterprise Manager Collection fails

2011-09-20T20:50:00.000-07:00

Recently we had issue with Oracle 11g Enterprise Manager (OEM) Grid control failing to collect up to data host data with following error from Grid Control:

This was a cryptic error and after research, we found the solution in the following My Oracle Support:

Note: 271598.1
Grid Agent Configuration: Certain Operations from Grid Console Fail With 'ERROR: NMO Not Setuid-root (Unix-only)'

So, I performed the following steps to resolve the issue with Oracle 11g Grid Control:

./emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.

Open a new shell window logged in as the root user:

# cd /home/oracle/agent11g/
# ./root.sh

As the oracle user, start the agent on the monitored host:

$ ./emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Starting agent ....... started.

Now comes the secret sauce or step that solves issue:

./emctl control agent runcollection rac1:host host_storage
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully

In the above command emctl control agent runcollection hostname:host host_storage
you will update the details sent to the OEM 11g Grid Control server.

Perform upload to OEM 11g Grid Control Server:

$ ./emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Verify it has resolved issue. You will see the current host status as shown in the below example:

Stay tuned for more Oracle 11g Grid control tips. I will share my lessons learned next for the installation process with Oracle 11gR1 Grid Control Server.

Oracle 11g Agent silent installation with OEM 11g Grid Control

2011-09-20T20:26:00.000-07:00

Dear readers,

After setup of a new Oracle 11gR1 Grid control Enteprise manager (OEM) server, I had to deploy agents to monitor the Oracle environments. Unfortunately, the mass push automation process via OEM 11g Grid Control kept failing and numerous support calls to Oracle Support alas yielded no success. I was able to quickly automate the process via silent installation and deployment of Oracle 11g agents with Grid Control 11g. Below is the process of steps to setup the Oracle 11g agents for Oracle 11g Grid Control:

1) Download and unzip the agent 11g software to hosts monitored. In my case, I needed to configure agents for Oracle 11g RAC on Red Hat Enterprise Linux 5 platform.

cd /home/oracle

unzip Linux_x86_64_Grid_Control_agent_download_11_1_0_1_0.zip

Configure response file for silent agent installation

Before we can kickoff the silent install for the 11g agent, you need to edit the response file called additional_agent.rsp located under the $AGENT_HOME/response directory as shown in my example below:

cd /home/oracle/linux_x64/response

Edit the file with editor of your choice, in my case, I used vi editor on Linux.

Since we don't want updates and don't care about My Oracle support details, we will comment out the email address specifics.

$ vi additional_agent.rsp

SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
#DECLINE_SECURITY_UPDATES=
#MYORACLESUPPORT_USERNAME=
#MYORACLESUPPORT_PASSWORD=
#COLLECTOR_SUPPORTHUB_URL=

INSTALL_UPDATES_SELECTION="skip"
#STAGE_LOCATION=

#PROXY_USER=
#PROXY_PWD=
#PROXY_HOST=
#PROXY_PORT=

ORACLE_AGENT_HOME_LOCATION=/home/oracle

b_silentInstall=true
CLUSTER_NODES=rac1,rac2,rac3,rac4

#-------------------------------------------------------------------------------
OMS_HOST=oem
OMS_PORT=4900
AGENT_REGISTRATION_PASSWORD=yourpassword

Since we have an Oracle 11g RAC environment, we set the parameter in the response file for CLUSTER_NODES to the cluster RAC node names. If you do not have RAC, you can comment this parameter out. Make sure to set the correct hostname, OMS port number and agent registration password or your silent agent installation will fail.

Perform silent installation for Oracle 11g Agent with OEM Grid Control 11g

Navigate to the AGENT_HOME/agent directory

cd /home/oracle/linux_x64/agent

Execute the silent agent installer script with the correct options and parameters as shown in the below example:

./runInstaller -silent -responseFile /home/oracle/linux_x64/response/additional_agent.rsp \
-invPtrLoc /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

Note: you must grant the correct location for the Oracle Inventory file (oraInst.loc) or the silent installation will fail for the agent.

Once you silent installation has succeeded you should see the following status:

Running Configuration assistant "Agent Configuration Assistant"

Performing free port detection on host=rac1
Performing targets discovery and agent configuration

Securing the agent

Starting the agent

Configuration assistant "Agent Configuration Assistant" Succeeded

AgentPlugIn:agent configuration finished with status = true

Running Configuration assistant "Agent Add-on Plug-in"

Configuration assistant "Agent Add-on Plug-in" Succeeded

Finally, we verify that we can show all of the agents deployed for the Oracle 11g RAC environment

$ cd /home/oracle/agent11g/bin

$ ./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /home/oracle/agent11g/rac1
Agent binaries : /home/oracle/agent11g
Agent Process ID : 13181
Parent Process ID : 13157
Agent URL : https://rac1:3872/emd/main
Repository URL : https://oem:4900/em/upload
Started at : 2011-09-20 20:28:06
Started by user : oracle
Last Reload : 2011-09-20 20:28:06
Last successful upload : 2011-09-20 20:38:49
Total Megabytes of XML files uploaded so far : 15.68
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 31.34%
Last successful heartbeat to OMS : 2011-09-20 20:39:10
---------------------------------------------------------------
Agent is Running and Ready

The beauty is when you specify the RAC cluster nodes in the first response file as we did above, is that all of the cluster nodes will have the silent install agent performed at same time!

Application Tier crashes at 100% CPU load

2011-09-20T13:29:00.000-07:00

We had an issue with our Oracle R12 EBS environment when the CPU spiked constantly at 100% when load testing with lots of concurrent active users.

After much research, it was determined to be caused by

Bug 6519700 ESC: CSE: R12SIP: 6513826 FRMWEB RUNAWAY PROCESS CONSUMING 100% CPU-MIDDLE TIER

The fix was to shutdown the apps middle tier server and configure the following forms environment variables:

FORMS_RECORD_GROUP_MAXFORMS_CATCHTERM

1) First we had to update the context file on the application middle tier as follows:

s_forms_catchterm=0

2) Since the FORMS_RECORD_GROUP_MAX is not a default variable in the app tier context file, you need to create a custom template as follows.

cd $AD_TOP/admin/template
mkdir custom
cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/template/custom/APPLSYS_ux.env

Edit the custom template file:

vi $AD_TOP/admin/template/custom/APPLSYS_ux.env

Set the environment variable FORMS_RECORD_GROUP_MAX to a high value such as 10000 or greater.

FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX

Save the file.

Run autoconfig utility for apps middle tier server.

Start app tier services and test.

Now we now longer have this issue.

Load Balancing Part I: Oracle R12 EBS and Oracle 11g RAC- Manual process

2011-09-20T13:21:00.000-07:00

I had to configure an Oracle R12 (12.1.1/12.1.3) E-Business Suite and two node Oracle 11gR2 RAC environment recently to take advantage of load balancing.

However, instead of using a hardware load balancer such as Cisco ACE, we had to find a manual process to implement this goal.

First stop was to review the documentation listed below on My Oracle Support (http://support.oracle.com):

Note # 380489.1 Using Load Balancers with Oracle E-Business Suite Release 12
Note # 603325.1 Using Cisco ACE Series Application Control Engine with Oracle E-Business Suite 12
Note # 226880.1 Configuration of Load Balancing and Transparent Application Failover
Note # 262298.1 Load Balancing Doesn't Balance the Number of Sessions Across All Available Nodes
Note # 887522.1 11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained

Note #1080674.1 JDBC Drivers and Failover Mechanisms (FCF, TAF, SCAN )
Note #1100024.1 Master Note for Oracle JDBC High Availability Features
Note #247135.1 How to Implement Load Balancing With RAC Configured System Using JDBC

Now, based on my experience with load balancers and Oracle, you should in most cases take the default setup of running your application middle tier server nodes in the default servlet mode to work with Cisco ACE and other hardware load balancers.

There is no compatibility with socket mode for hardware load balancers after discussions with Oracle support, you MUST run in servlet mode for hardware load balancing.

With that said, you could also use a web cache 10gAS application server to use software load balancing instead of the expensive hardware load balancer.

Our challenge was to implement a pure software load balance setup with Oracle R12 EBS and 11gR2 RAC environment with the ability to run the application middle tier nodes in socket mode for performance and application compatibility reasons.

Configure application middle tier environment for load balancing

First, we had to edit the following context variables on the apps tier for load balancing using context editor in the Oracle Applications Manager (OAM):

s_tools_twotask=VIS_BALANCE
s_weboh_twotask=VIS_BALANCE
s_apps_jdbc_connect_alias=VIS_BALANCE

Make sure to run the Oracle R12 EBS autoconfig utility ($ADMIN_SCRIPTS_HOME/adautocfg.sh) after changing the above context variables in your app tier context file.

Note: take a backup of the context file before making changes in case you need to revert back to original configuration!

The above context variables for the apps tier should point to the load balance setup in tnsnames.ora on the two RAC cluster nodes as shown below:

VIS_BALANCE=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=YES)
(FAILOVER=YES)
(ADDRESS=(PROTOCOL=tcp)(HOST=node1-vip)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=node2-vip)(PORT=1521)) )
(CONNECT_DATA= (SERVICE_NAME=VIS)
) )

So you will need to create the entry called VIS_BALANCE for example as shown in the above tnsnames.ora file for the app tier nodes and database tier RAC Nodes.

Configure listener.ora

Sometimes even if you setup load balancing correctly, the distribution will not be sent equally across the cluster nodes. To resolve this issue, there is an undocumented parameter you can set to avoid this problem in your listener.ora file.

vi listener.ora

PREFER_LEAST_LOADED_NODE_listener_name=OFF

Register listener and services

Now that we have completed these changes, we need to register the listener services for all nodes.

Then unset and reset the remote_listener parameter for node1.

sqlplus '/as sysdba'
alter system set remote_listener='' scope=both;
alter system register;
alter system set remote_listener='VIS_REMOTE' scope=both;
alter system register;

Stop and restart the database and listeners.

lsnrctl stop
lsnrctl start
lsnrctl reload

Verify that both listeners display both instances in the status and services output.
lsnrctl services
lsnrctl status

Since load balancing is a complex topic, we just scratched the surface here in this post to highlight a quick and easy way to implement manual load balancing at the database layer with Oracle R12 EBS and Oracle 11gR2 RAC.

Future post will cover how to load balance the application middle tier.

Oracle 11g agent fails to upload to Oracle 11g Grid Control

2011-09-20T12:46:00.000-07:00

Recently, I built a new Oracle 11gR1 Grid Control Enterprise Manager (OEM) server at work to monitor our complex environments.

All worked fine with agents, OMS and Grid Control until the other day when the agent failed to upload the status to Grid control.

After checking status for the agent and trying to perform a manual upload as listed below,
I received the following errors:

"emctl upload failed: collection status disabled by upload manager"

So I checked on disk space and plenty was available on the filesystem where the agent was located.
After finding a hit on My Oracle Support (MOS) site under Doc ID 396238.1,

I reviewed the following parameters in the emd.properties file under the
AGENT_HOME/sysman/emd directory:

UploadFileSize
UploadMaxBytes
UploadMaxDiskUsedPct

These were set to 2048, 50, and 98 for UploadFileSize, UploadMaxBytes, and UploadMaxDiskUsed by default when I checked the emd.properties file.

Sure enough the default values were too small thus causing the agent upload to fail.

First, I shutdown the agent with the emctl stop agent command.

Next, I changed the parameters to larger values for size of files:

$ cd /home/oracle/agent11g/sysman/config
vi emd.propertiesUploadFileSize=4096

## The maximum number of megabytes(MB) the upload manager will support in the
# upload directory before temporarily disabling collections, logging and tracing
##

UploadMaxBytesXML=500

# The maximum amount (%) of disk space that can be used on the EMD filesystem
# before the following is disabled:
# - Collection of data (upload manager)
# - Logging and tracing
# - Diagnosability traces
#@description=
#@valueType=Int
#@LOV=#@default=98#

UploadMaxDiskUsedPct=99

Then I started the agent and performed manual upload to OEM Grid OMS server:

./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /home/oracle/agent11g/node1
Agent binaries : /home/oracle/agent11g
Agent Process ID : 16864
Parent Process ID : 16828
Agent URL : https://node1:3872/emd/main
Repository URL : https://oem:4900/em/upload
Started at : 2011-09-20 11:33:05
Started by user : oracle
Last Reload : 2011-09-20 11:33:05
Last successful upload : 2011-09-20 13:22:32
Total Megabytes of XML files uploaded so far : 6.27
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 5.30%
Last successful heartbeat to OMS : 2011-09-20 13:36:26
---------------------------------------------------------------
Agent is Running and Ready

$ ./emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Now it works and OEM Grid Control can receive the updates from the agent on the server monitored.Cheers,Ben

Using PL/SQL and SQL to change profile options for Oracle R12 EBS

2011-07-28T11:38:00.000-07:00

Recently I had to fix an unusual problem with Oracle R12 (12.1.1) E-Business Suite system after changing some default timeout parameters. Since I was unable to login to the Oracle Applications Manager (OAM) graphical interface and had weird errors below, I needed a way to switch these profile values back to the lower default values. I set the ICX timeout parameters (ICX: Session Timeout, ICX: Limit Time, and ICX: Limit Connect) way too high in our test environment to experiment with user logout issues. SO I had the below errors:

Oracle error 1841: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1468 ORA-06512: at line 1 has been detected in FND_SESSION_MANAGEMENT.CHECK_SESSION.

Oracle error 1841: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1468 ORA-06512: at "

APPS.FND_SESSION_MANAGEMENT", line 1236 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 421 ORA-06512: at line 1 has been detected in FND_AOLJ_UTIL.is_Valid_ICX.

Servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details.

Needless to say this was extremely frustrating problem to solve as the few hits on My Oracle Support (formerly Metalink) along with an SR to support were unable to provide me with the solution and root cause. Finally after some research and discussion with a very smart support manager from Oracle (thank you Warwick!), I used a SQL and PL/SQL script to reset the values to much lower timeout settings and then I cleared the web browser cache. This solved the issue.

Sometimes you may not want to login or be able to access the OAM browser due to such issues. In this case, you want to use a SQL and/or PL/SQL wrapper script to modify profile settings. Below is the script to check for current values of profiles within Oracle R12 EBS:

set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set linesize 120
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format A60 wrap
spool profile_options.txt
select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME,
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%&profile_name%')
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, level_set;

The above script queries the Oracle R12 EBS database for values against the FND_PROFILE tables in the APPS schema.
Ok so now that we have our settings for profiles, let's show how we changed these for ICX profile settings to provide the solution.

DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('ICX_LIMIT_CONNECT', '2000', 'SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;
/

To use the above PL/SQL script to change profile parameters, it uses the FND_PROFILE package for Oracle R12 EBS with the SAVE function within PL/SQL which in turn takes three parameters as shown by the package definition listed below:

SQL> desc FND_PROFILE

FUNCTION BUMPCACHEVERSION_RF RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SUBSCRIPTION_GUID RAW IN
P_EVENT WF_EVENT_T IN/OUT

FUNCTION DEFINED RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

FUNCTION DELETE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_LEVEL_NAME VARCHAR2 IN
X_LEVEL_VALUE VARCHAR2 IN DEFAULT
X_LEVEL_VALUE_APP_ID VARCHAR2 IN DEFAULT
X_LEVEL_VALUE2 VARCHAR2 IN DEFAULT

PROCEDURE GET
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VAL VARCHAR2 OUT

FUNCTION GET_ALL_TABLE_VALUES RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DELIM VARCHAR2 IN

PROCEDURE GET_SPECIFIC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME_Z VARCHAR2 IN
USER_ID_Z NUMBER IN DEFAULT
RESPONSIBILITY_ID_Z NUMBER IN DEFAULT
APPLICATION_ID_Z NUMBER IN DEFAULT
VAL_Z VARCHAR2 OUT
DEFINED_Z BOOLEAN OUT
ORG_ID_Z NUMBER IN DEFAULT
SERVER_ID_Z NUMBER IN DEFAULT

FUNCTION GET_TABLE_VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

PROCEDURE INITIALIZE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
USER_ID_Z NUMBER IN DEFAULT
RESPONSIBILITY_ID_Z NUMBER IN DEFAULT
APPLICATION_ID_Z NUMBER IN DEFAULT
SITE_ID_Z NUMBER IN DEFAULT
PROCEDURE INITIALIZE_ORG_CONTEXT

PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VAL VARCHAR2 IN

PROCEDURE PUTMULTIPLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAMES VARCHAR2 IN
VALS VARCHAR2 IN
NUM NUMBER IN

FUNCTION SAVE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_VALUE VARCHAR2 IN
X_LEVEL_NAME VARCHAR2 IN
X_LEVEL_VALUE VARCHAR2 IN DEFAULT
X_LEVEL_VALUE_APP_ID VARCHAR2 IN DEFAULT
X_LEVEL_VALUE2 VARCHAR2 IN DEFAULT

FUNCTION SAVE_USER RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_VALUE VARCHAR2 IN

FUNCTION VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

FUNCTION VALUE_SPECIFIC RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
USER_ID NUMBER IN DEFAULT
RESPONSIBILITY_ID NUMBER IN DEFAULT
APPLICATION_ID NUMBER IN DEFAULT
ORG_ID NUMBER IN DEFAULT
SERVER_ID NUMBER IN DEFAULT

FUNCTION VALUE_WNPS RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

In our above example, we gave FND_PROFILE.SAVE the required three parameters to set the profile values for ICX:Limit Connect based on time duration of 2000 which allows for up to a maximum of 2000 connections permitted in a single session. We want a large value so that the many batch processes and concurrent jobs processed by functional users can take place without terminating abnormally while processing such things as invoices and order shipments for Oracle Financials within the Oracle R12 E-Business Suite.

Add node fails due to time synchronization issue with NTPD and CTSSD

2011-07-12T14:24:00.000-07:00

Today while checking the status of a four node Oracle 11gR2 RAC environment, I noticed something was wrong with time synchronization between the cluster nodes. Even though I had our system administrator configure NTPD for the environment, the Cluster Verification Utility (CVU) failed on NTPD errors and showed the Cluster Synchronization Services Daemon (CTSSD) in Observer mode when I ran a check of the clock synchronization:

oracle@rac1 ~]$ cluvfy comp clocksync -n all

Verifying Clock Synchronization across the cluster nodes

Checking if Clusterware is installed on all nodes...
Check of Clusterware install passed

Checking if CTSS Resource is running on all nodes...
CTSS resource check passed

Querying CTSS for time offset on all nodes...
Query of CTSS for time offset passed

Check CTSS state started...
CTSS is in Observer state. Switching over to clock synchronization checks using NTP

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
NTP Configuration file check passed

Checking daemon liveness...
Liveness check passed for "ntpd"
Check for NTP daemon or service alive passed on all nodes

NTP daemon slewing option check failed on some nodes
Check failed on nodes:
rac1,rac2,rac3,rac4
PRVF-5436 : The NTP daemon running on one or more nodes lacks the slewing option "-x"
Clock synchronization check using Network Time Protocol(NTP) failed

PRVF-9652 : Cluster Time Synchronization Services check failed

Verification of Clock Synchronization across the cluster nodes was unsuccessful on all the specified nodes.

Aha! So CTSSD must have an NTPD server in slewing option mode or it will fail to synchronize the cluster nodes correctly.

The solution to this is to shutdown the Oracle RAC database environment as well as to shutdown ASM and the clusterware and then to restart ntpd on the Oracle RAC cluster nodes and app tier server host with the –x option

Previous to this, I had verified that ntpd was running however it was started by default mode and not with the -x option as shown below:

[root@rac1 ~]# service ntpd status
ntpd (pid 24396) is running...
[root@rac1 ~]# ps -ef|grep ntpd
root 15495 8369 0 09:52 pts/1 00:00:00 grep ntpd
ntp 24396 1 0 Jul11 ? 00:00:00 ntpd -u ntp:ntp -p /var/run/ntpd.pid -g

You can check to see if the -x flag has been set by examination of the /etc/sysconfig/ntpd file.

[root@rac1 ~]# grep OPTIONS /etc/sysconfig/ntpd
OPTIONS="-u ntp:ntp -p /var/run/ntpd.pid"
NTPDATE_OPTIONS=""

I found the following two My Oracle Support (http://support.oracle.com) notes useful while solving this issue with Oracle 11gR2 RAC and time synchronization issues.

MOS 1054006.1- CTSSD Runs in Observer Mode Even Though No Time Sync Software is Running
MOS 1056693.1- How to Configure NTP or Windows Time to Resolve CLUVFY Error PRVF-5436 PRV-9652

Logon failure to Oracle R12 EBS in 11gR2 RAC Environment and Solution

2011-07-12T14:15:00.000-07:00

Yesterday while working on a four node Oracle 11gR2 RAC environment with Oracle R12 eBusiness Suite, I encountered a weird login authentication error when I tried to open a few forms to manage concurrent processing as system administrator:

"Cannot Complete Application logon. You may have entered an invalid applications password or there may have been a database connect error."

I checked the password by logging into the database tier with the apps account and that worked. Then I searched on My Oracle Support and found more clues to help me solve the issue.

Starting Forms Intermittently Raises "Cannot Complete Applications Logon" and "APP-FND-01496" Error When Using RAC [ID 753028.1]

Sure enough, when I logged into the Oracle 11gR2 RAC cluster nodes and checked for their time, they were out of sync! So, we had to configure ntpd (Network Time Protocol Daemon) and restart the cluster nodes. Here is what is puzzling- with 11gR2, there is a new process called the CTSSD or Cluster Time Synchronization Service Daemon which is supposed to replace the functionality of NTPD. However, you still need to run NTPD to have RAC function in 11gR2 without complaining. Also, NTPD is required to some extent to maintain a time synchronization with the Oracle RAC nodes. Once we re-synched the clocks for the Oracle 11gR2 RAC hosts, we were able to resolve the forms login errors with our Oracle R12 eBusiness Suite and RAC environment.

Data Recovery Advisor Feature for 11g Not Supported with 11gR2 RAC

2011-07-12T14:08:00.000-07:00

Today while checking and validating our database backups for our Oracle 11gR2 RAC and R12 EBS Oracle environments, I wanted to test out the Data Recovery Advisor with 11gR2 RAC. Well, turns out, it is not supported! When you try to issue the DRA commands from RMAN, you get the following message:

[oracle@rac1 ~]$ rman target RAC1

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 12 14:09:39 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

target database Password:
connected to target database: VIS (DBID=296736794)

RMAN> list failure low;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/12/2011 14:09:50
RMAN-05533: LIST FAILURE is not supported on RAC database

RMAN> advise failure low;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of advise command at 07/12/2011 14:09:56
RMAN-05533: ADVISE FAILURE is not supported on RAC database

After checking this on My Oracle Support (http://support.oracle.com), I discovered that indeed, Oracle does not support their new feature for backup and recovery (Data Recovery Advisor) on RAC!

Now this is confusing because in the Oracle 11gR2 documentation, it specifies that DRA is supported by RAC when in fact, it is not supported and does not work!

Reference:
Documentation wrong with RAC and DRA - Data Recovery Advisor. (Oracle Database Administrator's Guide 11.2) [ID 1297071.1]

So folks, you cannot use the cool new 11g feature data recovery advisor with RAC! I was bummed to find this out as it is a cool feature and worked in 11gR1.

Oracle 11gR1/R2 RAC Essentials Book now available

2011-05-25T19:56:00.000-07:00

My new Oracle 11g RAC book is now available from PACKT Press:

http://www.packtpub.com/oracle-11g-r1-r2-real-application-clusters-essentials/book

If you are an Oracle DBA or technical manager looking to implement and design an Oracle 11g RAC environment, then you will find this book critical to successful implementation!

Upcoming Events- Spring/Summer 2011

2011-03-29T19:54:00.000-07:00

I will be presenting at a few conferences over the next several months:

Collaborate 11- April 12, 2011 for OAUG in Orlando, Florida
Lock Down! Secure your Oracle R12 E-Business Suite Environment in a week.
Event Type: White Paper / Standard Session

Session ID: 4908
Date: Tuesday, April 12
Time: 3:15 pm - 4:15 pm
Room: W304F
Speaker: Benjamin Prusinski

Description: This session will provide the audience with the technical guidance to implement security best practices for Oracle R12 E-Business Suite.

EMC World in May, 2011 in Las Vegas, NV

http://www.emcworld.com/
Oracle eBusiness Suite on RAC Sizing & Configuration on Vblocks Best Practice
This session will provide an overview of the Vblock Powered Solution for Oracle E-Business Suite and RAC. Participants will learn best practices for sizing and layout of Oracle E-Business and RAC database cluster on a Vblock Platform, how to dynamically scale out the Oracle E-Business application tier and RAC database cluster on a Vblock Platform, how to bi-directionally migrate an Oracle RAC database cluster from Physical to Virtual and how to build an inexpensive but effective high availability environment for Oracle E-Business stack on a Vblock Platform.

ODTUG Kaleidoscope in Long Beach, CA

http://www.kscope11.com/
Performance Tuning 101: Top Tips for Oracle 11gR2
Ben Prusinski
Session 17, 06/30/2011, 8:30 AM-9:30 AM
Learn how to use the new features in Oracle 11g Release 2 to optimize performance for data warehousing and transactional OLTP environments. We will show you some quick wins in terms of leveraging tools such as the new Results Cache and little known tools such as oradebug and lots more. Back to Top

I look forward to see you all at one of these events! Stay tuned for more exciting news coming soon.

Cheers,
Ben

Annoying interruption problem solved for Oracle R12 E-Business Suite

2011-03-03T00:07:00.000-08:00

I run most of my test environments using virtual servers with VMWare and Oracle Sun Virtualbox. As such the network connections use DHCP to access wireless internet access points. Recently a new issue came up that gave me hours of grief. After making some changes to my Oracle R12 context file, I was unable to get the middle tier application services started successfully. I received the following errors:

******************************************************
ERROR: Timed out (100000): Interrupted Exception
Your are running adoafmctl.sh version 120.6.12000000.2
Starting OPMN managed OAFM OC4J instance
*****************************************************

A quick search on My Oracle Support found similar issue in Note: 564536.1.
The note recommends changing the timeout parameters for oacore parameter
under the $ORA_CONFIG_HOME/10.1.3/opmn.xml file for the middle application tier server as well as changing the context file parameters s_oacoretimeout, s_formstimeout, and s_oafmtimeout for the middle tier application context file and running autoconfig on the middle tier. I tried this and unfortunately, it did not fix my problem.

So, upon further investigation, I found the solution given by My Oracle Support Note: 412980.1 Problem: oafm, forms, and oacore not starting after IP Address Change.

Aha! I thought since I use DHCP and virtual networks, this plays havoc on my Oracle R12 EBS virtual environments. The root cause of my problem and why the middle tier services failed to start is that the OC4J lock files store the old IP address which are incorrect after an IP address is changed by the DHCP and virtual network adapters whenever I travel to use different networks. To solve my problem, I had to perform the following tasks.

1. Take backup of current middle tier files
2. shutdown all current running middle app tier services with the adopmnctl.sh script
under the $INST_TOP/admin/scripts/home directory

3. delete the lock files under the $INST_TOP/ora/10.1.3/j2ee directories

[applmgr@tusebs scripts]$ echo $INST_TOP
/appstier12/CLONE/inst/apps/CLONE_tusebs
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/oacore/persistence/*
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/oafm/persistence/*
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/forms/persistence/*

4. Run Autoconfig on the apps middle tier:
[applmgr@tusebs scripts]$ ./adautocfg.sh
Enter the APPS user password:

The log file for this session is located at: /appstier12/CLONE/inst/apps/CLONE_tusebs/admin/log/02101314/adconfig.log

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.
Using CONFIG_HOME location : /appstier12/CLONE/inst/apps/CLONE_tusebs
Classpath : /appstier12/CLONE/comn/java/lib/appsborg2.zip:/appstier12/CLONE/comn/java/classes

Using Context file : /appstier12/CLONE/inst/apps/CLONE_tusebs/appl/admin/CLONE_tusebs.xml

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.

5. Start application middle tier services and verify all is working now

[applmgr@tusebs scripts]$ $ADMIN_SCRIPTS_HOME/adstrtal.sh apps/apps

You are running adstrtal.sh version 120.13.12000000.3

Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adopmnctl.sh start
script returned:
****************************************************

You are running adopmnctl.sh version 120.4.12000000.3

Starting Oracle Process Manager (OPMN) ...

adopmnctl.sh: exiting with status 0

adopmnctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adopmnctl.txt for more information ...

.end std out.

.end err out.

****************************************************

Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adapcctl.sh start
script returned:
****************************************************

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 /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adapcctl.txt for more information ...

.end std out.

.end err out.

****************************************************

Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adoacorectl.sh start
script returned:
****************************************************

You are running adoacorectl.sh version 120.11.12000000.2

Starting OPMN managed OACORE OC4J instance ...

adoacorectl.sh: exiting with status 0

adoacorectl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adoacorectl.txt for more information ...

.end std out.

.end err out.

****************************************************

Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adformsctl.sh start
script returned:
****************************************************

You are running adformsctl.sh version 120.12.12000000.3

Starting OPMN managed FORMS OC4J instance ...

adformsctl.sh: exiting with status 0

adformsctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adformsctl.txt for more information ...

.end std out.

.end err out.

****************************************************

Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adoafmctl.sh start
script returned:
****************************************************

You are running adoafmctl.sh version 120.6.12000000.2

Starting OPMN managed OAFM OC4J instance ...

adoafmctl.sh: exiting with status 0

adoafmctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adoafmctl.txt for more information ...

.end std out.

.end err out.

****************************************************

Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adalnctl.sh start
script returned:
****************************************************

adalnctl.sh version 120.3

Checking for FNDFS executable.
Listener APPS_CLONE has already been started.

adalnctl.sh: exiting with status 2

adalnctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adalnctl.txt for more information ...

.end std out.

.end err out.

****************************************************

Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adcmctl.sh start
script returned:
****************************************************

You are running adcmctl.sh version 120.14

Starting concurrent manager for CLONE ...
Starting CLONE_0210@CLONE Internal Concurrent Manager
Default printer is noprint

adcmctl.sh: exiting with status 0

adcmctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adcmctl.txt for more information ...

.end std out.

.end err out.

****************************************************

Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/jtffmctl.sh start
script returned:
****************************************************

You are running jtffmctl.sh version 120.3

Validating Fulfillment patch level via /appstier12/CLONE/comn/java/classes
Fulfillment patch level validated.
Starting Fulfillment Server for CLONE on port 9300 ...

jtffmctl.sh: exiting with status 0

.end std out.

.end err out.

****************************************************

All enabled services for this node are started.

adstrtal.sh: Exiting with status 0

adstrtal.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adstrtal.log for more information ...

[applmgr@tusebs scripts]$ $ADMIN_SCRIPTS_HOME/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: CLONE_tusebs.tusebs.localdomain
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
OC4J | oafm | 25028 | Alive
OC4J | forms | 24953 | Alive
OC4J | oacore | 24866 | Alive
HTTP_Server | HTTP_Server | 24812 | Alive

adapcctl.sh: exiting with status 0

And Voila! Issue has been resolved and no more annoying interrupted exception!

Kick off event for San Diego Oracle Applications User Group (SDOAUG) in March 2011

2011-02-25T17:17:00.000-08:00

As a new board member for the San Diego Oracle Applications User Group (SDOAUG) in San Diego, we are having a kickoff networking event in March.

On March 10th we are having our Annual Kickoff Networking Event at Karl Strauss Brewery from 4:30pm-7:30pm in Sorrento Valley. If you are not part of the SDOAUG membership list please send me your contact info or go to http://sdoaug.org/ to register yourself as a member to receive all future SDOAUG notifications.

Food, good beer and conversation will be flowing fast and steady so be sure to sign up and attend the premier kickoff event of the year for San Diego! Hope to see you all there!

Cheers,
Ben Prusinski, SDOAUG Events Chair, San Diego Oracle Applications User Group

How to list all default profiles for Oracle R12 EBS Account

2010-12-28T15:58:00.001-08:00

While checking profile options set for the SYSADMIN account, I ran across this useful SQL script from

My Oracle Support Note 201945.1
How to list E-Business Suite Profile Option values for all levels using SQLPlus

Let's run the script logged into Oracle R12 (12.1.1) in our test instance with APPS schema and run the SQL script:

set long 10000
set pagesize 500
set linesize 160
column SHORT_NAME format a30
column NAME format a40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format a40
select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, decode(to_char(v.level_value2), '-1', 'Responsibility',
decode(to_char(v.level_value), '-1', 'Server',
'Server+Resp')),
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,
decode(to_char(v.level_value), '-1',
(select node_name from fnd_nodes
where node_id = v.level_value2),
(select node_name from fnd_nodes
where node_id = v.level_value2)||'-'||rsp.responsibility_key)),
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(p.profile_option_name) in (
select profile_option_name from fnd_profile_options_tl
where upper(profile_option_name) like upper('%&profile_name%')
and upper(profile_option_name) in (select profile_option_name
from fnd_profile_options_tl
where upper(user_profile_option_name) like upper('%&user_profile_name%')))
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, user_profile_option_name, level_id, level_set;
/

The script will prompt you for the profile name which in our case is SYSADMIN to check for default profile settings.

Enter value for profile_name: SYSADMIN

SHORT_NAME NAME LEVEL_SET CONTEXT VALUE
------------------------------ ---------------------------------------- --------------- ------------------------------ ----------------------------------------
ADI_WHEN_SYSADMIN_OUTPUT_VIEW ADI: Allow Sysadmin to View all Output Site Y

The above script is very useful when reviewing security values and profile settings for Oracle R12 E-Business Suite accounts.

Some additional useful MOS notes and scripts:
Note: 201945.1 - How to list E-Business Suite Profile Option values for all levels using SQLPlus
Note: 282382.1 - How to Search all of the Profile Options for a Specific Value
Note: 367926.1 - How To Find All Users With A Particular Profile Option Set?

Changing Weblogic Admin Password

2010-12-21T15:59:00.000-08:00

Recently, I completed a new installation for Oracle 11g Weblogic server as part of the Oracle 11g SOA Suite configuration. Unfortunately I forgot the password. Here is how to reset the password for the weblogic admin account with Oracle 11g Weblogic (10.3.3).

Step 1: Set the environment for your Weblogic domains:

In our case, we are running Linux so we change to the Middleware directory set by the MIDDLEWARE_HOME variable

$ cd $MIDDLEWARE_HOME/user_projects/domains/{your_domain}/bin
where your_domain is the domain_name for your Weblogic domain as shown in below example:

$ cd user_projects/domains/fmw_domain/bin

Execute the setDomainEnv.sh script

. ./setDomainEnv.sh

Step 2:Run the copy command before we change the admin password for weblogic

$ java -cp /weblogic1033/oracle/wlserver_10.3/server/lib/weblogic.jar:$CLASSPATH weblogic.security.utils.AdminAccount weblogic mypass1 .

where weblogic is the admin username and mypass1 is the new password

Step 3: Reset the weblogic admin password with the following command:

$ java weblogic.security.utils.AdminAccount weblogic mypass1 .

where weblogic is the admin user account and mypass1 is the new password. Don't forget the period at the end of the command or this will not work.

In the future, if you do not want to set a password for the weblogic admin user, you can modify the boot.properties file as discussed in the following My Oracle Support note:

980292.1
Starting AdminServer Without Username/Password Prompt in WebLogic Server 10.3

Cheers,
Ben

SQL Developer

2010-12-21T15:08:00.001-08:00

In the past as a DBA and developer, I used Quest TOAD on a regular basis. Now with the freely available SQL Developer tool from Oracle, there is the perfect solution for a robust and easy to use development and DBA tool.

Oracle SQL Developer now has the following utilities:

1. Data Modeler- instead of ErWIN you can do it from SQL Developer
2. SQL and PL/SQL Development
3. Third Party Database Migration tool- migrate from MySQL and DB2 or Microsoft SQL
Server

4. DBA tools for monitoring user sessions, killing processes, etc.
5. Search feature for online Oracle Documentation

In my book on database migrations, Migrating to Oracle Expert Secrets for Migrating from SQL Server and MySQL from Rampant Tech Press, I discuss how to use the migration tools available in SQL Developer. Sue Harper of Oracle also has excellent discussions from the Oracle SQL Developer product management team. Let's take a quick peak into some of the new and useful goodies available in the latest version of SQL Developer - the swiss army knife of Oracle development tools!

The main window and menu has list of tools available:

I like the database copy utility when I need to clone a database fast:

Tools->Database Copy

Next up is the cool feature for data modeling. I will write more about this in a future blog post as the new data modeling tool merits a blog post of its own. You can generate new data models for physical or logical database designs or re-engineer a current database environment as well as gather DDL for the entire database or schemas.

The Unload Wizard is interesting tool which I plan to explore and test in the future. This allows you to dump data in various formats such as XML, PDF and flat file formats.

I also like the Diff Wizard tool to check for synchronization issues between database environments. I find this particularly useful after I perform a database refresh to a DEV environment from Production to ensure that the development database is in sync with production. SQL Developer provides a simple option as shown below to perform this task.

We can choose options for taking the diff in the databases

Once the report completes it shows the diff between the two databases:

Finally, with the DBA mindset, I like the monitoring capabilities to view sessions and trace expensive SQL statements. No longer must I use SQL scripts to view SQL statements and explain plans. Its so much easier as shown below.

And last but not least is the way cool search functionality for checking online Oracle documentation and even the Tom Kyte AskTom site:

This makes the latest version of the Oracle SQL Developer tool a one stop center for many development and DBA tasks and best of all, it is FREE!

Cheers,
Ben

Oracle R12 E-Business Suite video from OTN LAD Conference

2010-12-01T14:32:00.001-08:00

My Oracle R12 E-Business Suite Performance Tuning video from the OTN LAD Conference is now available in Spanish.

http://www.vimeo.com/16914910

Special thanks to fellow Oracle ACE in Peru- Miguel Palacios for the video.

Hope all Oracle users enjoy the video.

Cheers,
Ben

OTN LAD Tour: Peru, Chile and Uruguay

2010-11-07T10:31:00.000-08:00

Dear readers,

After an exciting trip to Peru, Chile, and Uruguay, I wanted to relay the wonderful experience that was the first ever OTN LAD Tour conference which spanned seven countries: Peru, Chile, Uruguay, Brazil, Colombia, Ecuador and Costa Rica. I want to extend my thanks to our most gracious hosts in these countries who not only worked hard behind the scenes to make this conference a success but welcome all of us Oracle ACEs to their countries with their hospitality!

Side trip before the conference.

The weekend before I arrived at the first conference, I went on a side trip to Cusco and Machu Picchu. Here is the map of the trip.

Needless to say, the adventure to Cusco and Machu Picchu was incredible! From stellar food at the Chicha restaurant in Cusco by famed Peruvian chef, Gaston Acurio to the magic of the Inka trail, there is no words to describe it.

Ceviche is a national dish of marinated seafood in lime juice and quite tasty!
Next up, I ordered the amazing anticuchos (beef hearts grilled to perfection) with various types of corn (maize) and potatos.

To reach the famed city of the Inca, you have to fly into Cusco and take a nice scenic four hour train ride to Aguas Calientes and then a bus ride to the base of Machu Picchu.

Once you arrive at Machu Picchu, its quite a hike up to the top! It was raining a lot and slippery which required a rain coat and extra care to avoid falling to one's death over the steep ledges. The views are simply fantastic and breathtaking!

To see this magic place requires several days to explore every incredible spot in the site!

I also went on a side tour around Cusco with fellow Oracle ACE Directors Hans Forbrich and Daniel Morgan who were gracious to arrange for the tour guide to show us around the fantastic wonders of Cusco. Below is a church that was built on an Incan site.

Peru

First, I met with fellow Oracle ACE Enrique Orbegozo who showed me a wonderful tour and genuine Peruvian hospitality by giving me a tour of Lima and excellent Peruvian cuisine.

Enrique was very gracious and took me to an incredible Peruvian folklore show that night.

PEOUG OTN Conference in Peru

After the amazing trip to Cusco and Machu Picchu, I flew back to Lima for the conference in Peru. Everyone was friendly, helpful and gracious.

Chile and Uruguay were wonderful experiences for me as well and from the bottom of my heart, thank you for your hospitality and it was a pleasure and honor to participate in the OTN LAD Tour.

Cheers,
Ben

Open World Conference Part Two

2010-09-25T15:09:00.001-07:00

Dear readers,

After my session for OOW, I had to run to a local clinic to receive my yellow fever vaccinations in preparation for my trip next month to South America for the Oracle OTN LAD conference Tour. It was painful to get these shots and my arm still hurts after it a few days later. I can only imagine how dreadful it would be to catch Yellow Fever and die from it. Anyways I went to some sessions, did work and networked with peers and new friends. On Wednesday night, there was a fantastic concert on Treasure Island with Berlin, Black Eyed Peas (BEP), Don Henley and Dave Miller band. I almost missed it because the queues for the bus were like 6 blocks long at the Hilton where I was staying! So after waiting for half an hour in futile desperation, I took a taxi to the concert on Treasure Island. Here is a good video of the Steve Miller Band:

Now Steve Miller is way beyond my time but he still rocks! I only wish that I had 1/10 of his guitar ability. It was crowded and food was near impossible to get without waiting for hours. So, since I ate already, I decided to grab some drinks and it was ironic how the food lines had thousands of people waiting but the lines for wine and beer were nonexistant! All in all a great time.

I did catch up with old friends like Charles Kim of Oracle MAA fame and Chet Justice.

I spent time camped out in the Zone for OTN lounge which this year was in a tent outside the Hilton. A couple years ago it was inside the Moscone Center. I liked the tent but wish it was inside the Moscone since making the trek from the tent on Mason to the Moscone was quite a walk and hard to make it to every session. Since I was multi-tasking in terms of doing work for a client, working on my presentations, and networking, the Zone Tent for OTN on Mason was my stomping grounds for most of OOW.

On Tuesday night, I went to the OTN night party at Yerba Buena gardens. It was ok but I almost was not able to get into the party as security guards were not educated on who to let in and keep out. Fortunately many of us bloggers had same problem and tweeted en masse and Justin saved the day and we were able to get into the OTN night party. Fortunately I wore a jacket as it was quite chilly that night.

I ran into Tim Hall quite a bit at the conference which was good since we both were not feeling that great and crashed a lot at the Zone OTN tent. Daniel Morgan and Hans Forbrich were around and we had some good chats in preparation for the OTN LAD conference trip. Ran into Richard Foote and Fritz Hoogland many times and these guys are great fun to chat with. Richard has the coolest Aussie accent and I always joke with him to bounce around like a kangaroo since he is from down under. Maybe I can index your foot sometime, Richard?

Of course no discussion would be complete without mentioning our new friend the Java Bean Bunny Guy! This cool dude was walking around the whole place waving and showing us the real power of J2EE.

My old iPhone died the weekend before OOW. So I bought a new iPhone4. Its great and does fantastic HD video and images. I look forward to using the Face time video chat once more folks have it.

Open World Conference 2010 Part One

2010-09-25T14:57:00.000-07:00

Dear readers,

Oracle OpenWorld was fantastic this year. With the addition of JavaOne and Oracle Develop conference more than 40,000 people attended this year! It seems like OOW keeps getting bigger and better each year. Some good photos of the first day of the conference.

Larry Ellison gave a very technical keynote to open the conference which was much more focused on technical details than what he usually presents. Below is good video that I took for Larry Ellison's keynote on Cloud Computing.

As a new Oracle ACE Director, I enjoyed the briefing that I participated in at Oracle HQ before the conference. My thanks go out to Lillian, Vikki, and Justin for a fantastic job in taking great care of us Oracle ACEs and Oracle ACE Directors for the conference!

We also had a fantastic Oracle ACE cruise on Sunday evening with lots of good food, drinks and discussion.

I presented my session on Monday afternoon and we had a good turnout over 150 folks came to learn about Oracle R12 E-Business Performance tuning. For me, the best part of the OOW conference was sharing my technical knowledge with fellow Oracle professionals as well as networking with peers, seeing old friends again and meeting new ones. When folks came to me after my session to gave me excellent feedback it makes me happy that I am helping out fellow Oracle professionals with their technical challenges.

Announcements- OpenWorld and Oracle ACE Director Status

2010-08-30T15:08:00.000-07:00

Dear Readers,

I have become an Oracle ACE Director!

Even more exciting news is that I will be presenting at Oracle OpenWorld this year. Below are the details:

Session ID: S319054

Title: Oracle E-Business Suite 12 Performance Tuning 101

Abstract:
In this session, learn how to approach performance tuning issues in both proactive and reactive modes for all components of the complex Oracle E-Business Suite 12 environment. Discover the latest performance tuning methods and tools for the application and database tiers with Oracle E-Business Suite. Learn how to tune the concurrent manager, application-tier components, the Java Virtual Machine (JVM), and database tier servers.

Speaker(s): Ben Prusinski, Ben Prusinski and Associates, President and Chief Architect

Stream(s): APPLICATIONS
Track(s): Applications Tools and Technology, Oracle E-Business Suite
Schedule: Monday, September 20, 12:30 | Marriott Marquis, Golden Gate C2

Hope to see everyone there! I will also be answering your technical Oracle questions at the Oracle OTN ACE Lounge as well.

Plagiarism and Oracle RAC book update

2010-08-19T15:57:00.000-07:00

It has been found that Guenadi Jilevski has plagiarized dozens of others work on Oracle blogs and websites as shown below:

From the September 2009 archive, virtually all of the content has been pasted-in from other sources and represented as his own work.

http://gjilevski.wordpress.com/2009/11/24/upgrade-oracle-crs-10-2-to-oracle-grid-11-2/

Stolen from Martin Casterbach:

http://martincarstenbach.wordpress.com/2010/01/21/upgrade-clusterware-10-2-to-11-2/

************************

http://gjilevski.wordpress.com/2009/09/29/edition-based-redefinition-in-oracle-database-11g-release-2/

Stolen from Dr. Hall:

http://www.oracle-base.com/articles/11g/EditionBasedRedefinition_11gR2.php

************************

http://gjilevski.wordpress.com/2009/09/25/install-oracle-11-2-rac-deploying-dns-dhcp-for-gns/

Stolen from Martin Casterbach:

http://martincarstenbach.wordpress.com/2009/10/02/build-your-own-11-2-rac-system-part-ii-dns-dhcp-for-gns/

************************

http://gjilevski.wordpress.com/2009/09/23/install-oracle-11-2-rac-using-dns-for-scan/

Stolen from Martin Casterbach:

http://martincarstenbach.wordpress.com/2009/09/26/build-your-own-11-2-rac-system-part-i-dns/

**********************************

http://gjilevski.wordpress.com/2009/09/04/oracle-11gr2-is-out/

Stolen from Arup Nanda:

http://arup.blogspot.com/2009/09/oracle-11g-r2-features.html

**********************************

http://gjilevski.wordpress.com/2009/10/22/dbms_parallel_execute/

Stolen from Hall:

http://www.oracle-base.com/articles/11g/dbms_parallel_execute_11gR2.php

**********************************

http://gjilevski.wordpress.com/2009/10/13/adding-node-in-oracle-11g-r2-grid-infrastructure/

Stolen from Martin Casterbach:

http://martincarstenbach.wordpress.com/tag/11-2/

Needless to say, I have ended all professional dealings with Jilevski. As for my new Oracle 11g RAC book with PACKT Press, my chapters are listed below:

Chapter 1: Introduction
Chapter 2: Architecture
Chapter 5: Managing and Troubleshooting Oracle 11g Clusterware
Chapter 14: Maximum Availability
Appendices

Recent issue of blogs and copyright

2010-08-17T15:24:00.001-07:00

Dear readers,

I recently had a major issue come to light when one of my co-authors for the newly released Oracle 11g Real Application Cluster Handbook with PACKT Press, Mr. Guenadi Jilevski, was taken to task for copyright infringement and plagiarism for Oracle content posted on his blog. This WRONG and against the law. Not only is plagiarism stealing, it is a violation of professional ethics. You cannot steal others work and call it your own original content! Furthermore, one must ALWAYS acknowledge and credit sources for published materials referenced as given by copyright laws and fair use laws unless it is one's original material.

Some background information on this matter came up when Jonathan Lewis approached me and Timur's blog discusses the matter here below:

http://timurakhmadeev.wordpress.com/2010/06/02/content-thief/

Don Burleson also brought Jilevski's web content to my prompt attention:

http://www.dba-oracle.com/n_oracle_certified_master_ocm_accused_content_theft_plagiarism.htm

Don Burleson then asked me if I was involved in the content posted on Jilevski's blog. My answer is this:

"No, I have no involvement whatsoever in the content posted by Guenadi Jilevski on his oracle blog. "

I just want to clear the air on this matter once and for all. US and international copyright law applies to both web and printed media as well as recorded media. You CANNOT take others content and cut and paste it to make it your own! This is stealing, plain and simple!

I have contacted Guenadi Jilevski to address this issue with all parties to make it correct if he has not credited sources of technical materials published. In addition, I have asked Jilveski to apologize for stealing others content. He needs to be taken to task for this behavior.

In summary, here is the fact about things with respect to writing and media usage of one's work and publications:

1) Please note that Copyright is NOT about copying without attribution, it is about NOT copying! That means, you cannot cut and paste others' diagrams, works and writings and claim it as your own work! You MUST use your own original work!

Cheers,
Ben

Webinar for Oracle R12 EBS Patching

2010-07-20T16:28:00.000-07:00

Recently I gave a webinar for the LAOUC (Latin America and New Zealand/Australia Oracle User Community)on best practices for patching the Oracle R12 E-Business Suite environment.

Below is a link to the webinar for those of you who would like to better understand how patching works for Oracle R12 E-Business Suite environment.

Enjoy! Ben

Bug in Oracle 12.1.1 EBS Installer (RapidWiz)

2010-07-20T15:58:00.001-07:00

While performing a fresh installation of the Oracle 12.1.1 E-Business Suite for the Linux platform, I discovered an interesting bug! The Rapid Wizard (rapidwiz) installer confuses the DBA by showing the incorrect version of the Oracle database software to be installed as part of the Oracle 12.1.1 EBS environment. Instead of showing the correct version for the Oracle 11gR1 database tier, it shows a 9i database!

I found this curious as I continued the installation for 12.1.1 EBS as it later on shows the correct database server version (11.1.0.6) that will be installed for the database tier:

I wonder if Oracle EBS team has caught this bug or not? I will open a bug fix request to product development for the Oracle EBS team.

Cheers,
Ben

Part II: Setup Printers for Oracle E-Business Suite Release 12

2010-06-29T13:00:00.000-07:00

In this post, we finish our printer configuration for the Oracle Applications R12 E-Business Suite environment. In the previous blog post, we discussed how to use the LINUX tools to configure printers at the operating system level. In this post, we finish the printer configuration by showing you how to setup the printer in the Oracle R12 E-Business Suite environment within the Oracle Applications Manager (OAM) graphical interface.

The first step is to login to the OAM environment as the SYSADMIN user and to navigate to the System Administrator responsibility.

Now we need to setup the printer by register the device.

We enter the settings for the printer in the flexfields above.
As such you need to enter the values for the printer driver and print type.

We enter the values for the HP Laserprinter below

Next we need to configure the HP printer with the correct driver as shown below.

As part of the print driver configuration for the Oracle R12 EBS environment,
we have additional print considerations. For example, the Driver Method comes into play
depending on the command, program or subroutine used with the printer.

We also have Driver Method Parameters that allow us to spool file or generate
print output to standard input as shown below.

OK so now that we have configured our printer, we need to run some post-configuration tests to verify that it works. Fortunately, Oracle EBS provides a suite of utilities and diagnostic tests for us with Release 12.

To run these tests, you need to switch to the System Administration responsibility for OAM and navigate to Diagnostics-> Diagnostic Tests as shown below.

Under the basic tab for the Application Object Library, select RunALL
to generate diagnostics for the environment.

We have covered the basics of printer setup and for future reference, Oracle provides many excellent tips on advanced configuration such as PASTA setup with Oracle R12 EBS.

Below are some excellent references from My Oracle Support (MOS) formerly known as Metalink (http://support.oracle.com)

Pasta Overview Note: 420019.1

Note 356501.1 "How to Setup Pasta Quickly and Effectively".

Note 269129.1 "How to Implement Printing for Oracle Applications: Getting Started" for general driver information.

Note 365111.1 "How to Setup Pasta for PCL Based Printers "

Note 356501.1 "How to Setup Pasta Quickly and Effectively",

Once you run the test for the pasta printer, Oracle Diagnostics will generate an HTML file report to display the configuration details and status report for the printers.

My Oracle Support (MOS) Technical References

NOTE:356501.1 - How to Setup Pasta Quickly and Effectively
NOTE:728077.1 - How to Setup IX Library Quickly and Effectively
NOTE:365111.1 - How to Setup Pasta for PCL Based Printers
NOTE:189708.1 - Oracle Reports 6i Setup Guide for Oracle Applications 11i
NOTE:240864.1 - Activating and Configuring IX Library
Note 60936.1 Step By Step Guide to Set Up a Printer in Oracle Applications
Note 99495.1 Oracle Applications Postscript Printing
Note 112172.1 Oracle Applications Character Printing
Note 152285.1 Building a Printer Initialization String for Oracle Applications
Note 106186.1 How to Test an Initialization String Outside of Oracle Applications
Note 1014599.102 How to Test Printer Initialization Strings in Unix

Printer Configuration for Oracle R12 E-Business Part I: Preparation

2010-06-29T12:22:00.000-07:00

In this blog series, we will cover how to setup and configure printers with the Oracle R12 E-Business Suite. As a systems applications DBA, we are tasked with the challenge of printer configuration. Often this is an overlooked item before Go-Live which severely impacts the project success because if the financial analyst or CFO cannot print invoices or financial reports, they will be unable to perform essential tasks even in this Green Day of paperwork reduction.

Part 1: Printer Setup Preparation
Part 2: Configure Printers with R12 E-Business Suite

Ok lets get started. We need to first prepare the operating system to accept our printers for usage before R12 EBS will be able to use the new printer. We will cover how to configure the Linux and UNIX operating system by using the lp facility with CUPS for printing. For reference with HP Printers for Linux, we download the software from

http://hplipopensource.com/hplip-web/install_wizard/index.html

Another good reference site for Linux drivers is the open source site

http://www.openprinting.org/printers/manufacturers/HP

We go to site below

https://launchpad.net/hplip

Download printer drivers for Linux

Our first step is to download the correct printer driver which in our case happens to be an HP Laserjet printer. We download this from the HP site listed below.

We follow the prompts and download the driver.

Once we have downloaded the print driver, we are ready to install it.
HP uses a download manager for Linux so we choose the Linux version and download it.

Good now we just need to install the software for Linux to configure our HP Laserjet printer. Once we have installed the driver we can run the CUPS utility for Linux to setup our new printer.

Next we need to check the status for the Linux/UNIX based CUPS print server

[root@tusebs etc]# /etc/init.d/cups status
cupsd (pid 5398) is running...

To add the new printer to the LINUX environment, you can use the web gui tool to setup new printer device for LINUX. Open a browser to:

http://localhost:631/admin

The CUPS printer utility will show up as shown below.

We use this excellent browser based tool to add, remove, adminster and monitor printer configurations for LINUX.

So now we need to add the printer to our environment with the tool.

Click the button in the tool for Add New Printer

Then click on the continue button.

After entering the printer name, you need to enter the device mapping for it.
For example, we used LPD/LPH host or Printer Jet Direct driver but you can also choose from other device mappings such as IP address for the device configuration and
then click continue.

Now we need to enter the device URI for our printer which
in our case, since our HP printer has a Jet Direct card installed so we use this option and enter the socket URI mapping.

Next we click on the continue button. Now you will need to enter the make which is HP in our case and then continue. You can also use a PPD file if you one.

Enter the model/driver for your printer and continue.
Then click on the button to Add the printer.
Once the printer has been added to your LINUX configuration, you can verify status in the printers section of the tool. Make sure to use the concurrent processing server as the focal point for the printer setup and configuration.

Now we are ready to add our printer to R12 EBS environment!
Stay tuned for part II where we use the Oracle Application Manager (OAM) to install and configure our new printer for Oracle R12 EBS.

Finishing the Oracle 11gR2 Grid Infrastructure setup

2010-05-18T20:45:00.000-07:00

In the previous post, we discussed how to resolve installation issues for time synchronization (NTP) between our cluster nodes as well as how to configure shared disks for 11gR2 RAC by using iSCSI. This post concludes a summary of the steps with our 11gR2 Grid setup. We need to run two scripts, oraInstRoot.sh and root.sh on both cluster nodes.

Open a new terminal shell window on the first 11gR2 cluster node and as root user,
execute the script called orainstRoot.sh located under the /u01/app/oraInventory directory.

This script will set the correct permissions for the configuration.

Next as root user, execute the script called root.sh located under the
/u02/app/11.2.0/grid directory as shown below.

This will take some time to run and will startup the clusterware processes including
CRS and OHASD (Oracle HA service).

My advice is to allocate at least 2.5Gb of RAM memory per VM (Virtual Machine) cluster node if you are using virtualization to setup 11gR2 RAC. The big change with 11gR2 RAC is that consumes at least 10x more memory and is a memory pig so you need to account for this major change. This is big difference from 11gR1 which does consume as much memory.

Shared disks not accessible during 11gR2 RAC Grid Infrastructure Installation

2010-05-16T11:24:00.000-07:00

During the initial setup of the shared disks on my iSCSI SAN for the 11gR2 RAC cluster nodes,
the Oracle 11gR2 Grid Infrastructure complained that it could not access the LUNS for the iSCSI storage. To verify and resolve this issue, I had to run the discovery commands for iSCSI as shown below.

First, on the iSCSI SAN we need to check the status for the LUNS:

To do so, we issue the Linux iSCSI command tgtadm --lld iscsi --op show --mode target

root@san ~]# tgtadm --lld iscsi --op show --mode target
Target 1: iqm.mgmt.volumes-san
System information:
Driver: iscsi
State: ready
I_T nexus information:
I_T nexus: 2
Initiator: iqn.1994-05.com.redhat:7d8594f48e2a
Connection: 0
IP Address: 1.99.1.1
I_T nexus: 3
Initiator: iqn.1994-05.com.redhat:6cc883d783b6
Connection: 0
IP Address: 1.99.1.2
LUN information:
LUN: 0
Type: controller
SCSI ID: IET 00010000
SCSI SN: beaf10
Size: 0 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: None
LUN: 1
Type: disk
SCSI ID: IET 00010001
SCSI SN: beaf11
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk1.dat
LUN: 2
Type: disk
SCSI ID: IET 00010002
SCSI SN: beaf12
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk2.dat
LUN: 3
Type: disk
SCSI ID: IET 00010003
SCSI SN: beaf13
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk3.dat
LUN: 4
Type: disk
SCSI ID: IET 00010004
SCSI SN: beaf14
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk4.dat
LUN: 5
Type: disk
SCSI ID: IET 00010005
SCSI SN: beaf15
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk5.dat
LUN: 6
Type: disk
SCSI ID: IET 00010006
SCSI SN: beaf16
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk6.dat
LUN: 7
Type: disk
SCSI ID: IET 00010007
SCSI SN: beaf17
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk7.dat
LUN: 8
Type: disk
SCSI ID: IET 00010008
SCSI SN: beaf18
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk8.dat
LUN: 9
Type: disk
SCSI ID: IET 00010009
SCSI SN: beaf19
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk9.dat
LUN: 10
Type: disk
SCSI ID: IET 0001000a
SCSI SN: beaf110
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk10.dat
LUN: 11
Type: disk
SCSI ID: IET 0001000b
SCSI SN: beaf111
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk11.dat
LUN: 12
Type: disk
SCSI ID: IET 0001000c
SCSI SN: beaf112
Size: 2147 MB
Online: Yes
Removable media: No
Backing store type: rdwr
Backing store path: /disks/disk12.dat
Account information:
ACL information:
ALL
[root@san ~]#

Good, so on my iSCSI SAN I can see the LUNs. Next step is to initiate discovery on the second RAC node that cannot see the shared disks.

[root@vxnode2 ~]# iscsiadm -m discovery -t sendtargets -p san.mgmt.example.com
1.99.1.254:3260,1 iqm.mgmt.volumes-san

You should see the status as shown above with details for the SAN. If not you need to run the rediscovery process and check to ensure that the iscsi services are running on the cluster node. Next verify that the disks are accessible and have the correct ownership and privileges granted:

[root@vxnode2 ~]# ls -l /dev/sd*
brw-r----- 1 root disk 8, 0 May 16 03:28 /dev/sda
brw-r----- 1 root disk 8, 1 May 16 03:29 /dev/sda1
brw-r----- 1 root disk 8, 2 May 16 03:28 /dev/sda2
brw-r----- 1 oracle oinstall 8, 16 May 16 03:30 /dev/sdb
brw-r----- 1 oracle oinstall 8, 32 May 16 03:30 /dev/sdc
brw-r----- 1 oracle oinstall 8, 48 May 16 03:30 /dev/sdd
brw-r----- 1 oracle oinstall 8, 64 May 16 03:30 /dev/sde
brw-r----- 1 oracle oinstall 8, 80 May 16 03:30 /dev/sdf
brw-r----- 1 oracle oinstall 8, 96 May 16 03:30 /dev/sdg
brw-r----- 1 oracle oinstall 8, 112 May 16 03:30 /dev/sdh
brw-r----- 1 oracle oinstall 8, 128 May 16 03:30 /dev/sdi
brw-r----- 1 oracle oinstall 8, 144 May 16 03:30 /dev/sdj
brw-r----- 1 oracle oinstall 8, 160 May 16 03:30 /dev/sdk
brw-r----- 1 oracle oinstall 8, 176 May 16 03:30 /dev/sdl
brw-r----- 1 oracle oinstall 8, 192 May 16 03:30 /dev/sdm

These should match for both cluster nodes.

Now we can kick off the installation for 11gR2 RAC as shown below.

Are you ready for a coffee break? I sure am! Ok so this will take a LONG time to run since I am using VMWare Fusion on my 4Gb Macbook Pro and it will run for half hour or so.

I will cover how to setup an iSCSI SAN for Oracle 11gR2 RAC in a future blog post.
My Oracle Support has a nice walk through guide on how to configure Open Filer iSCSI for Oracle RAC environments.

Using Openfiler iSCSI with an Oracle RAC database on Linux [ID 371434.1]

NTP configuration issues with Oracle 11gR2 RAC setup

2010-05-16T03:52:00.001-07:00

During the Grid infrastructure setup for Oracle 11gR2 RAC, I ran into a weird error with ntp not working with the Grid
infrastructure installer.

The solution thanks to he Grumpy DBA, better known as Jay Caviness

http://grumpy-dba.com/2009/09/04/slewing-the-11gr2-dragon/

is to edit ntp configuration on both cluster nodes and restart ntp services.

To do so, logon to each node as root and edit the /etc/sysconfig/ntpd configuration file to make change for options settings as shown below:

[root@vxnode2 ntp]# cat /etc/sysconfig/ntpd
# Drop root to id 'ntp:ntp' by default.
OPTIONS="-u ntp:ntp -p /var/run/ntpd.pid"

# Set to 'yes' to sync hw clock after successful ntpdate
SYNC_HWCLOCK=no

# Additional options for ntpdate
NTPDATE_OPTIONS=""
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

You will need to restart ntp services for this to take affect:

root@vxnode2 ntp]# service ntpd stop
Shutting down ntpd: [ OK ]
[root@vxnode2 ntp]# service ntpd start
ntpd: Synchronizing with time server: [ OK ]
Starting ntpd: [ OK ]
[root@vxnode2 ntp]# ps -ef|grep ntp
root 7363 1 0 03:51 ? 00:00:00 ntpd -x -u ntp:ntp -p /var/run/ntpd.pid

Now we can move forward in the 11gR2 RAC Grid Infrastructure setup.

Be aware that since we are using virtualization for 11gR2 RAC with VMware, you may need to restart the ntp services each time that you power on the VMs. Another option around this issue with ntp is to use the Oracle 11gR2 RAC cluster synchronization services (CSS) instead of ntp to let Oracle handle the network synchronization services for the cluster.

Configure Cluster Time Synchronization Service - (CTSS)

If you want to use Cluster Time Synchronization Service to do this, you need to remove your ntp configuration and shutdown the ntp services on both cluster nodes.

To deactivate the NTP service, you need to shutdown and disable the existing ntpd service, and remove the ntp.conf file. To complete these steps on CentOS Linux, run the following commands as the root user on both cluster nodes:

1) /sbin/service ntpd stop
2) chkconfig ntpd off
3) mv /etc/ntp.conf /etc/ntp.conf.original
4) Delete the following file:

rm /var/run/ntpd.pid
This file maintains the pid for the NTP daemon.

When the installer finds that the NTP protocol is not active, the Cluster Time Synchronization Service is automatically installed in active mode and synchronizes the time across the nodes. If NTP is found configured, then the Cluster Time Synchronization Service is started in observer mode, and no active time synchronization is performed by Oracle Clusterware within the cluster.

To confirm that ctssd is active after installation, enter the following command as the Grid installation owner (grid):

1) crsctl check ctss
CRS-4701: The Cluster Time Synchronization Service is in Active mode.
CRS-4702: Offset (in msec): 0

Thanks to Jeffrey Hunter for these tips. He has an excellent writeup for 11gR2 RAC with iSCSI:

http://www.oracle.com/technology/pub/articles/hunter-rac11gr2-iscsi.html#10

Next we will take a look at how to resolve issues with iSCSI disks not accessed by the 11gR2 RAC Grid infrastructure setup program.

Oracle 11g Release 2 RAC Setup on VMWare on laptop

2010-05-16T02:07:00.000-07:00

This weekend, I decided to setup Oracle 11g Release 2 RAC and ASM with the new Grid infrastructure on Macbook laptop with 4Gb of RAM. There is a way to setup a basic two node sandbox cluster with 4Gb of RAM. Some good references listed below discuss the detailed steps involved to configure the cluster with Oracle 11gR2 so I will not bore you with details.

Marly's Blog discusses the setup steps in great detail:

http://wiki.oracle.com/page/11gR2+RAC+on+a+Mac+-+Part+1
http://wiki.oracle.com/page/11gR2+RAC+on+a+Mac+-+Part+2
http://wiki.oracle.com/page/11gR2+RAC+on+a+Mac+-+Part+3
http://wiki.oracle.com/page/11gR2+RAC+on+a+Mac+-+Part+4

Masterschema has an excellent video series on how to do this as well:

http://www.masterschema.com/2009/09/install-oracle-rac-11g-r2-in-vmware-3-nodes/

The biggest decision you need to make is that with respect to shared storage. With 11g Release 2 RAC, you now have the option to store both the clusterware, OCR and ASM in ASM. Also, since VMWare Fusion and Oracle Sun VirtualBox do not have the ability to share disks which is required for RAC, you need to either setup NFS mounts between the cluster node VMs or use
an iSCSI server such as OpenFiler. However, on a laptop with 4Gb of memory, you need to conserve resources and Open Filer is a pig so I recommend using a third VM with Linux to host the iSCSI shared disks. There are also some excellent white papers on how to setup 11gR2 RAC with iSCSI and OpenFiler if you decide to buy a few low cost servers listed below:

http://www.oracle.com/technology/pub/articles/hunter-rac11gr2-iscsi.html#10

OpenFiler is a free iSCSI appliance that offers many similar features to that with NetApp filer iSCSI servers for free!
Since its easier to setup iSCSI server on a bare bones Linux VM, I decided to bypass using OpenFiler since it takes up too much memory that I cannot afford to waste on my 4Gb Macbook Pro laptop computer and the bare bones Linux VM uses only 350Mb of RAM compared to the 1-2Gb of RAM memory required for a decent OpenFiler server. Maybe if I get a laptop with 16Gb then I will test 11gR2 RAC with OpenFiler but for now, I just did a poor man's iSCSI virtual machine with CentOS Linux.

In the past, I have used Red Hat Linux and Oracle Enterprise Linux. However, this time I downloaded the ISO image for CentOS from http://centos.org/ and I must admit I am impressed! Its friendly, free and powerful and easier to use than Oracle Enterprise Linux.

Ok, So far, I am half way through the 11gR2 RAC install and finishing the Grid Infrastructure setup. There are a couple of cool new features that I like:

1) SSH setup- now you can have Oracle set this up and verify that ssh equivalency is working before the install
2) Validation of IP addresses- during the Grid Infrastructure installation process, Oracle verifies that the IP addresses for the cluster nodes are correct as well as that of the VIP (Virtual IP addresses).

Another note: Oracle 11gR2 RAC is a serious memory PIG compared to even 11gR1 and earlier releases. Ideally you want a server with at least 8GB or more RAM to setup the VMs. There is a workaround to use less memory resources and to prevent the OHASD and clusterware processes from using as much memory.

Alex Gorbachev of Pythian has a good writeup on this:

http://www.pythian.com/news/9179/oracle-11gr2-grid-infrastructure-memory-footprint/

There is a workaround to decrease the memory usage.

After installation update the ohasd in each of the GRID homes. Add: ulimit -Ss like this:

[root@server1 ~]# vi /u01/app/11.2.0/grid/bin/ohasd

# forcibly eliminate LD_ASSUME_KERNEL to ensure NPTL where available
ulimit -Ss
LD_ASSUME_KERNEL=
export LD_ASSUME_KERNEL

The result, it will same around 500MB of memory.

You still need more memory in compare to 11gR1, but at least it will same 1G for 2 nodes.

Thanks to Bernhard de Cock Buning for this tip. I have not tested it yet but will see if it helps me when I finish the Grid Infrastructure setup with both VMs.

Stay tuned for a lessons learned discussion of what 11gR2 RAC means for the enterprise. I also plan to discuss how to RAC enable Oracle Release 12 E-Business Suite in a future blog post.

I do prefer the Mac OS X platform compared to setup of RAC virtualization on Windows because the Mac OS is based on BSD UNIX so I can use most of the same UNIX commands that I am happy with as a DBA.

Cheers,
Ben

Collaborate 10 Conference Experiences in Vegas

2010-05-01T10:09:00.000-07:00

Collaborate was a wonderful conference. Plus, it was in Vegas and since I had never spent time before in Las Vegas, a new experience for me as well! Here is where I stayed during the conference.

The conference was at the Mandalay Bay casino/resort center and also where I stayed for the week to make it convenient to get to and from the sessions and events. Besides, the Mandalay has probably one of the best pools/beaches in Vegas which made it pleasant for those scorching hot days while I was there.

The cool thing about the main pool was the concert stage above it with live shows in the evenings with bands playing good music and the waves to simulate surfing!

Of course the coolest looking hotel would be the Luxor fashioned after the pyramids of Egypt along with the Disneyland like Castles of the Excalibur.

Next door to the Luxor is the medieval themed Excalibur.

I had the chance to walk the strip a few times which was fun in spite of the heat and need to drink massive quantities of water and the pain of the blisters that I received- next time need to find better walking shoes! So I liked the lights on the strip as well as the cool view from the top of the Mandalay as well.

I am not much of a gambler but I did manage to break even and win $50 on blackjack, $30 on craps and lose $20 on slot machines. Met an older gentleman who won $11,000 that day on Craps! And saw a guy lose several thousand dollars in one hand of blackjack as well. The Oracle ACE dinner on Sunday night was fantastic and I give special thanks to folks at Oracle ACE program who planned a fun evening and dinner- Vikki and others, thanks! Rick Moonen's RM Seafood has excellent seafood and a hip decor as well. For the conference a nice snapshot of the event center.

Oracle Magazine had a party at Red Square in Vegas which was fun chance to meet new people and try some speciality vodkas. Ran into Steven Feuerstein of Quest and PL/SQL fame and he is a very nice guy.

I feel blessed to have met so many new friends and many of the top Oracle experts in the world there as well. I especially enjoyed meeting Ahmed Alomari, Mark Farnham, Rich Niemec and Brad Brown from TUSC, Ian Abramson, John Stoufer, Craig Shallahamer, Iggy Fernandez and many others at the conference. I also has a chance to meet founder and chairman of Pythian, Paul Vallee which was nice since Pythian is one of the most well known Oracle database consulting providers with many bright Oracle DBA folks working there.
Had chance to connect with Arup Nanda several times at the conference as well as meet John Garmany from Burleson Consulting. So how did my session go? It was ok, I did not have a lot of people in my session probably because it was on Thursday the last day of the conference and most of the attendees left on Wednesday evening.

So what was the highlight of the conference for me? I have to say that what made my day was when one of the senior Oracle Applications DBAs who attended my session on Thursday praised the quality and let me know she really enjoyed my session! Thank you! When a DBA tells you that he/she learned new tips and liked the presentation better than most at the conference that gives you a fantastic feeling that it was all worth it for the hard work you put into the paper and preparation!

Hope to see more folks at next conference. With the US and world economy in the worst shape since the Great Depression, my prayer is that things come back so I can have more folks attend to learn new things on Oracle technology.

Cheers,
Ben

Collaborate 10 Announcement

2010-04-07T12:04:00.000-07:00

Dear readers,

Yours truly will be presenting at the Collaborate 10 conference in Las Vegas this month on best practices for Oracle Release 12 E-Business Suite patching. Below are the details:

Thursday, April 22
11:00 am - 12:00 pm 3303 Tip and Tricks for Patching R12 Oracle Applications E-Business Suite OAUG Infrastructure Technologies (IT)
Database (DB)
Application Strategy and Services (AS)

Tip and Tricks for Patching R12 Oracle Applications E-Business Suite
Session ID: 3303
Date: Thursday, April 22
Time: 11:00 am - 12:00 pm
Location: South Seas G
Speaker: Benjamin Prusinski

Product Line(s): Oracle E-Business Suite (EBS)

Track: Application Strategy and Services (AS); Database (DB); Infrastructure Technologies (IT)

Description: This session will provide best practices to patch your Oracle R12 EBS environments to ensure maximum availability and performance. Learn best practices on patching Oracle R12 EBS environments to avoid downtime. Learn tools and techniques for patching R12 EBS Oracle Applications. Learn new features in R12 EBS for patching Oracle Applications such as Patch Wizard.

Speaker:

Benjamin Prusinski
Ben Prusinski and Associates

I will also be available in the Oracle ACE lounge so hope to see you all there! My session will provide tips plus real world demos with Oracle Release 12 E-Business Suite.

Cloning Release 12: Oracle Applications Part II: Rapid Clone techniques

2010-03-18T18:08:00.000-07:00

In the previous blog post, I walked you through the pre-clone steps for Release 12 with Oracle E-Business Suite. Now that our pre-clone is complete, its time to do the actual cloning operation. With Release 12 for Oracle Applications, Oracle provides
a useful perl script called adcfgclone.pl as part of Rapid Clone that performs most of the heavy lifting for cloning the E-Business Suite. In addition to years of experience an an Oracle Apps DBA, I referenced the following MyOracleSupport (formerly Metalink) notes listed below to guide me in the cloning for the E-Business Suite:

Note: 406982.1 - Cloning Oracle Applications Release 12 with Rapid Clone

Note: 603104.1 - Troubleshooting RapidClone issues with Oracle Applications R12

In a nutshell the cloning process is as follows:
========================================================

I. Clone database tier from source to target environment
II. Clone applications tier from source to target

Cloning Release 12 Oracle E-Business Suite with Rapid Clone

Ok so we ran the adpreclone.pl scripts from last time and finished the prep work for the cloning. So how do we do the actual cloning or refresh for Release 12?
Actually the answer is that it depends on your environment. You can do a hot clone using the Oracle Applications Management Pack (AMP) with Oracle Enterprise Manager (OEM) Grid Control to cut down on downtime window. However that requires additional licensing and installation time. Or you could use RMAN or an Oracle hot backup to take the copy of your source database tier and clone to the target environment. However, for our example, we will use a simple cold backup to copy over the apps tier and database tier from our Source EBS environment to our target system.

As I mentioned above, for cloning an Oracle Applications environment, you would use your backup as well as copy commands manually or via a series of UNIX or Linux shell scripts to copy over the apps and db tier as part of the cloning.

However, in my test environment, I do not have an extra 300Gb of disk space
so we will use the Unix/Linux mv (move) commands to simulate the clone procedures.

If you decide to use the Oracle Recovery Manager (RMAN) utility to clone the database the procedure will be different.

Reference the Oracle documentation on usage for RMAN for details.

Some details on how to use RMAN to do this are listed below:

Note: 362473.1 - Cloning E-Business Suite Using Hot Backup for Minimal Downtime of Source Environment

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm

Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2)
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/toc.htm

In my opinion, you should use the RMAN Duplicate database command to take a hot backup of the database tier.

This works well and details are provided in

Note: 228257.1 RMAN 'Duplicate Database' Feature in Oracle9i / 10G and 11G

Note: If your database tier server is on 11gR1 or 11gR2 the new features for RMAN such as encrypted backups, Data Recovery Advisor (DRA), et al may also be of use to your backup and recovery strategy at future date. I have a blog
post on 11g RMAN new features in a previous blog post if you have questions on how this works with RMAN and 11g.

Ok so let's get started and cloning! First, we start with cloning the database tier.
On the database tier, we login as the oracle OS user and shutdown both the listener and database:

I. Clone Source database tier to target database tier server

Since we are simulating a clone procedure we create the new clone directory for database and applications tiers as follows:

mkdir -p /dbtier12/CLONE
mkdir -p /appstier12/CLONE

Before we use Rapid Clone script (adcfgclone.pl) we need to copy over the database and application tier files.

First we copy the application tier file system
Log on to the source system application tier nodes as the APPLMGR user and shut down the application tier server processes. Copy the following application tier directories from the source node to the target application tier node:

$APPL_TOP
$COMMON_TOP
Applications Technology Stack:
Oracle 10gAS 10.1.2 Tools ORACLE_HOME
Oracle 10gAS 10.1.3 Web ($IAS_ORACLE_HOME)

So this means that for the Apps tier you need to copy over the $INST_TOP, $COMMON_TOP, $APPL_TOP, 10.1.2 ORACLE_HOME, and 10.1.3 ORACLE_HOME directories and files

Copy the database node file system

Log on to the source system database node as the ORACLE user:

1. Perform a normal shutdown of the source system database
2. Copy the database (.dbf) files from the source system to the target system
3. Copy the source database ORACLE_HOME to the target system
4. Start the source Applications system database and application tier processes

For the database tier this includes the datafiles, parameter file (init.ora, spfile) and tnsnames.ora files

In this test environment, I do not have 300+ Gb of disk space so we simulated the copy with the UNIX/Linux mv command. However in a real world environment you would use the cp or scp command to transfer files from source to target systems. You would need to shutdown the database and take a cold backup of the database and apps tiers unless you do hot cloning and hot backup.

So now on the target we have the following clone directories:

1) Apps tier for CLONE

/apptier12/CLONE base directory for apps tier

-> 10.1.2 for CLONE ORACLE_HOME 10.1.2
-> 10.1.3 for CLONE ORACLE_HOME 10.1.3
-> appl for CLONE APPL_TOP
-> comn for CLONE COMMON_TOP

2) db tier for CLONE

/dbtier12/CLONE
CLONE 10g ORACLE_HOME -> /dbtier12/CLONE/10.2.0
CLONE datafiles /dbtier12/CLONE/db/apps_st/data

/dbtier12/CLONE/db/tech_st

We will need to keep track of this information for the upcoming step when we run the clone scripts!

Do not set the environment files for the database tier! We need a clean slate for
the database tier clone procedure.

Note: if you decide to do a copy of the db and apps tier files from your source to target system, you will need to start the database tier and apps tier services on the source system. In this example we did a Unix/Linux move (mv) to simulate the copy
so we do not start the apps tier and db tier since both source and target are on
the same virtual machine.

Run the clone script for db tier this is called adcfgclone.pl we can take a sneak peak at it with vi or emacs or whatever your favorite editor

perl adcfgclone.pl dbTier

How the clone script flows

adcfgclone.pl calls the script adchkutil.sh which then calls forth the script adclone.pl and adclonectx.pl

Note: if you do not have PD ksh installed the work around to get adcfgclone.pl working is to edit the adchkutil.sh script to comment out the reference in the script to look for the PD ksh shell.

Clone source database tier to target database tier

cd /dbtier12/CLONE/10.2.0/appsutil/clone/bin

perl adcfgclone.pl dbTier

enter the password for APPS then hit the key

enter the details for target system at the prompts

In our case we take the defaults since we are cloning from source to target on same system as shown in the example below

Once we enter the choices for the prompt settings the clone will begin for the db tier. You will need to enter settings for your target clone datafile location (DATA_TOP) as well as database tier ORACLE_HOME location, and other settings such as port pool.

Time to go for a coffee break! It will take some time and depends on the size of your database. I have scripts for cloning that I usually fire off at night time and babysit to make sure all runs smoothly.

You can tail the log file to keep track of your cloning operation. I like to dump the clone output to a logfile as part of a script to verify things.

When it finishes you should see a successful screen like below:

Rapid Clone starts the listener and database back up once the clone is created. We can run a few simple commands to verify that our new database clone is online and available:

My clone took about 90 minutes for the database tier since I have a very slow server running in a virtual machine. Your mileage may vary based on hardware, OS and available resources. Now lets clone the apps tier and finish our cloning!

Clone Source applications tier to target applications tier

Now that our database tier has been cloned its time to clone the apps tier to the new target! We run the adfgclone.pl script on the source apps tier as shown below:

For our example, The cloning script for the apps tier lives under the
following directory:

$ cd $INST_TOP/clone/bin

Login as the applmgr OS user and we run it with the following syntax:

perl adcfgclone.pl appsTier

Cloning the Applications Tier

Since I do not have the PD ksh installed it errors and then I need to edit the adchkutl.sh script which is called by adcfgclone.pl like last time so that it does not try to search for the PD ksh.

Rapid Clone uses a questionaire to prompt us for the details for the source to target cloning for the applications tier similar to the questionaire in the cloning script (adcfgclone.pl) that we ran earlier to clone the database tier.

We enter the following for the target apps tier system prompts:

target system base directory: /appstier12/CLONE
target system Forms ORACLE_HOME /appstier12/CLONE/10.1.2
target system Web ORACLE_HOME /appstier12/CLONE/10.1.3

choose 0 for port pool like we did earlier for db tier clone

A complete list is given below:

Now we want to keep a close watch on the logs to make sure no errors occur during the cloning for the apps tier in which we tail the logfile:

Are you ready for another coffee break? I sure hope so because I am! So lets grab a mocha and maybe a donut while this clone runs for the next few hours. I usually run a clone script at night time and check it every half hour until its done.

In between the coffee and donut break, I check the status and its at 50% and before I can drink the java or eat my donut a frantic Oracle project manager and functional analyst are waiting for me in my DBA cube!

I calm the functional team down and fix their performance issue. Was resource problem with JVM for the apps tier for another environment. I will blog on a solution to this in future post. OK so we check our clone and enter additional prompts after we had to restart it after it failed due to port issue that I fixed and re-started the clone.

Our clone finishes and all of the app tier services are started automatically by Rapid Clone (adcfgclone.pl) script.

And we have verification without errors.

Troubleshooting Errors during Rapid Clone - Appstier

Recall that earlier our clone failed at 74% during the cloning for the applications tier when we ran adcfgclone.pl appsTier so I checked
the logfile under

/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/log to find the
specific failure since it failed during the ApplyAppsTier phase

After looking at the logfile ApplyAppsTier_02251717.log I found the root cause
why my clone failed was due to unable to connect to the listener and database.

The port pool needs to be same on database and applications tier
so if you set 0 as port pool during adcfgclone.pl dbTier for db tier you MUST USE same port pool for clone with apps tier or the clone will fail during Appstier apply phase!!!

For Linux, you can use lsof command to find ports in use and use kill command to free up these ports.

After the cloning scripts have run successfully, the apps and db tiers will be started automatically by the Rapid Clone scripts.

I then reviewed the logs under

/appstier12/CLONE/comn/clone/bin

for both the source database and applications tiers and verified that all application and database services are available.

Next we need to verify that, users can login to OAM as cloned environment.

Sure enough, I hit the dreaded 500 Internal Error when I tried to login to the OAM website.

Here are some good troubleshooting notes if you have 500 Internal errors when attempt to access the OAM login page

Note: 761869.1 500 Internal Server Error
Note: 813523.1 500 Internal Server
Note: 443353.1 Guest Password mismatch
Note: 564536.1 ADSTRTAL.SH returns error TIMEOUT OUT Interrupted Exception
Note: 331322.1

Note: 391406.1 How to get a clean Autoconfig
Note: 387859.1 Autoconfig R12

I used these to resolve the issue which involved changing settings for the context file to allow JVM to function correctly without consuming all server resources as well as bouncing the app and database tiers on the target clone server.

After all was said and done, I verified that things worked for my new clone.

As a final sanity check, I checked the database and application tier servers for the new cloned environment.

Good the cloned database is functional and available. Lastly, I checked that all of the application tier services were online.

Finishing Tasks for Cloning Operations

1. Workflow settings for new clone
2. Verify login and accounts work correctly
3. Check status for SSO and OID

While we did not check on workflow, this is one area that you must address in your cloned environments. Also, run tests to check that the authentication is working correctly with accounts that use SSO and OID. If you do not perform these checks then users will have problems logging into the cloned systems after a refresh.

Congratulations! You now can perform the cloning and refresh tasks that are critical skills to have as a hardcore Oracle Apps DBA!

Cloning Release 12 Oracle Applications E-Business Suite: Part I pre-clone steps

2010-03-16T14:09:00.000-07:00

Dear readers,

Today we begin a new series on cloning the E-Business Suite for Release 12 with Oracle Applications. In this post, we cover the pre-clone steps required to perform a successful clone for Oracle Applications R12. As an Apps DBA, the ability to quickly produce clones is mandatory to be a successful EBS DBA. The following post will show details on how to clone. First lets get started with the prep work.

Part I: Pre-Clone Steps

Unlike a regular Oracle database environment, cloning the E-Business Suite is a complex affair. You need to prepare the source and target environments before a clone operation can be performed.

Part IA: Prepare clone environments

As a precaution, I advise you to follow any pre-reqs on My Oracle Support (formerly Metalink) and to download and apply the latest AD, Autoconfig template, and Rapid Clone patches before we begin. This will save you the potential headache and risk of failed clone operations due to a patch that should have been applied before doing the cloning steps.

1. First make sure to source the environments for oracle and applmgr environments for the database and applications tiers.

2. Shutdown apps tier and run autoconfig on apps tier

$ADMIN_SCRIPTS_HOME/adstpall.sh or $INST_TOP/admin/scripts/adstpall.sh
$ADMIN_SCRIPTS_HOME/adautocfg.sh or $INST_TOP/admin/scripts/adautocfg.sh

Note: you will need to enter your password for APPS for these scripts

As a precaution, I advise you to first backup context file before cloning.

3. as applmgr user on apps tier, create the appsutil file for the pre-clone steps
Oracle supplies a script called admkappsutil.pl to generate the new appsutil file which you will need to do the pre-clone. On the applications tier as the applmgr OS user run:

perl $AD_TOP/bin/admkappsutil.pl

Review the log files from the script to ensure that no errors have occurred before taking the next step.

Scroll down and verify it worked:

Open another window or shell prompt and
as the oracle user, copy the file via ftp from the $INST_TOP/admin/out
directory to the database tier server to the $ORACLE_HOME directory

cd $ORACLE_HOME; unzip -o appsutil.zip

as oracle user, run autoconfig on the database tier

cd $ORACLE_HOME/appsutil/scripts/ where indicates _ where SID is
the database SID and hostname the database tier hostname

in our case, this SBOX_tusebs would be as follows:

Now that we have run auto config, we are ready for the pre-clone steps!

Part IB: run pre-clone scripts for database tier

Now that we have completed the required prep work for our cloning operations, we need to run the pre-clone scripts for the database and applications tiers before we can clone the environment.

Oracle Release 12 actually starting with release 11i for E-Business Suite has a suite of perl scripts that perform the heavy lifting for us in terms of these cloning scripts. First, we need to pre-clone the database tier
and then we run the pre-clone script for the apps tier. Both the Sequence and syntax are critical here or the clone will fail.

The pre-clone script for database and apps tier are called adpreclone.pl. It lives under the
$ORACLE_HOME/appsutil/scripts/ directory on the database tier.

If you are a curious DBA like me,
you can look at the script with your favorite editor such as VI or EMACS.
Here is a sneak peak at the adpreclone.pl script:

Warning (Achtung!): Do not play around and make changes
to any of these scripts without the advice of Oracle Support!

Of course, one test that I may try at a future date on my
sandbox play environment is to tweak the adpreclone.pl scripts to see if I can do something funny like run my preclone
with silly names like
perl adpreclone.pl mickeymouseDBtier
perl adpreclone.pl plutoAppsTier

Then I could call it Disney EBS Rapid Cloning!

Warning: Kids do not try changing these scripts on a production system! Tricks are for kids not silly rabbits to paraphrase my favorite cereal commercial.

OK Let's run it!

you will need to enter the APPS password for the script to execute

it runs for a few minutes and then completes without errors

we verify the status in the logfile

scroll down and check to see that all ran fine without errors

Ok so now we need to run the pre-clone script on the apps tier as the applmgr OS user. Since we are running this on Linux platform the script is a shell script.

Part IC: Run the pre-clone scripts for apps tier

The pre-clone script for apps tier is located under the $INST_TOP/admin/scripts or $ADMIN_SCRIPTS_HOME directory and is called adpreclone.sh. To execute it we logon as applmgr user and run

perl $INST_TOP/admin/scripts/adpreclone.sh appsTier as shown below and you need to enter the APPS password
at the prompt.

Be sure to use the syntax EXACTLY as shown above or the preclone will FAIL!

This will run for a short time maybe 5-10 minutes and then complete. Verify that your preclone ran without errors. Check the log file generated by the adpreclone.sh script for the apps tier to ensure no errors have occurred as shown in the examples below.

Scroll down the remainder of the log file:

Now we are finally ready to clone! In the next blog post, I will show you the actual cloning steps to clone R12 EBS. Stay tuned!

Cheers,
Ben Prusinski

Fusion Middleware Integration with Oracle E-Business Suite: Part II: SOA Suite and Release 12

2010-03-06T21:21:00.000-08:00

In the last post, we discussed how to install SOA Suite for Oracle Fusion Middleware. This post will provide some quick tips on how to configure Release 12 of the Oracle Applications E-Business Suite with SOA Fusion Middleware. As a caveat, I want to advise you all that all versions of Oracle EBS prior to 12.1 are not supported for SOA Fusion Middleware based on discussions with Oracle support. With that said, I will summarize the details. Since my test environment is 12.0.4 and SOA requires 12.1, I do not have actual screen shots yet. What I will do is to revisit the specifics in a future post once I have upgraded my test 12.0.4 EBS environment to 12.1.2 ( a lengthy process!). OK so lets get started.

The reference details are further contained and hidden away in a few key notes from MyOracleSupport (formerly Metalink):

Oracle Metalink Note: 755067.1 - Using Oracle BPEL 10g with E-Business Suite Release 12.1.1

Oracle Metalink Note 556540.1 Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.1

MetaLink Note 755069.1 -EBS R12 Packager and Adapters for External Application Servers

Step A: Perform the following steps for enabling Oracle E-Business Suite Integrated SOA Gateway Release 12.1:

1. Apply the following Oracle E-Business Suite patch:
Patch 9060361:R12.TXK.B: FSG: Log Location and Level Properties Not Accessible from OAFM

As with all EBS patches, you will need to verify that all pre-requisite and co-requisite patches have been applied successfully to the 12.1.x E-Business Suite via Auto Patch without errors. Be sure to review the readme instructions!

2. Set applications environment and stop all application tier processes.
From the applications instance $APPL_TOP, set the environment by running the APPS.env script

3. Shutdown the application tier processes for your EBS instance by running $ADMIN_SCRIPTS_HOME/adstpall

4. Run TXK development script to install Oracle Application Server Adapter for Oracle Applications as shown below:
$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp -applicationname=pcapps -oracleinternal=Yes -oc4jpass=welcome -runautoconfig=No

5. Run TXK deployment script to install forms-c4ws.ear and configure container Forms-c4ws J2EE group correctly, as shown below:

$FND_TOP/bin/txkrun.pl -script=DeployForms-c4ws

6. Enable the new container forms-c4ws. (By default, the new OC4J container forms-c4ws will be disabled.)
To enable the new container forms-c4ws, modify the following values of context variables in the $CONTEXT_FILE:

Set 's_forms-c4wsstatus' to "enabled".
Set 's_forms-c4ws_nprocs' to "1".

7. Run AutoConfig. For information on how to run AutoConfig, see "Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12", My Oracle Support Knowledge Document 387859.1 for details.

8. Start all application tier processes for the instance by running the script $ADMIN_SCRIPTS_HOME/adstrtal

9. Enable ASADMIN user per details in the Oracle Metalink suport notes

10. Log on to Oracle E-Business Suite using sysadmin

11. Bounce the middle tier for R12 EBS environment

Part B: Configure SOA Suite with Release 12.1.x for Oracle Applications E-Business Suite

I will provide a summary of steps for the required tasks to configure the Oracle SOA Suite with Release 12 for Oracle EBS:

1. Register your installation of SOA Suite with R12 EBS instance per details from Oracle MetaLink Note 755069.1
This involves generating a new DBC file to use with SOA Suite and your EBS instance.

2. Create BPEL domains in SOA environment

3. Create JDBC connection pools in SOA instance for OC4J to access each EBS 12.1 instance

4. Define data sources between SOA instance and EBS 12.1 instances

5. Create Oracle adapter connection between SOA and 12.1 EBS instances

Hope this helps you to get started on your quest to configure Oracle Release 12.1 with SOA Suite Fusion Middleware!
Cheers,
Ben

Fusion Middleware Integration with Oracle E-Business Suite: Part I: SOA Suite and Release 12

2010-02-16T20:50:00.000-08:00

In this series, I will explain in detail how to configure Oracle Fusion Middleware SOA Suite with the Oracle E-Business Suite for Release 12 Oracle applications. This post will cover how to install the Oracle 10g SOA Suite for Fusion Middleware. Part II will provide details on the actual tasks required to integrate Fusion Middleware components for the SOA Suite with E-Business Release 12.

First, download the SOA Suite files from OTN (http://otn.oracle.com). For this series, we will use supported configurations, as such SOA Suite 10.1.3.5.0 with Oracle Release 12.

Oracle Enterprise Linux 5.2 will be used as the platform for SOA Suite 10g with Oracle EBS 12.0.1.

Preparation

1a. Download SOA 10.1.3.1.0 from OTN or Oracle Edelivery
I recommend that you first review the Deployment Guide for Oracle Application Server 10g Release 3 (10.1.3.1.0) available from:

http://download.oracle.com/docs/cd/B31017_01/core.1013/b28939.pdf

Oracle® Application Server
Enterprise Deployment Guide
10g Release 3 (10.1.3.1.0)
B28939-03

In order to save you time and the 200+ pages of documentation, we can simplify the steps for installation of the Oracle 10g SOA Suite.

First we need to install and configure the schemas for the SOA suite. These schemas are deployed via the irca.sh script located under the ORACLE_HOME directory for SOA Suite after you unzip the files for SOA Suite.

1. Install schemas for SOA

$ cd /install/soa_schemas/irca
./irca.sh

Verify that the SOA schemas have been created without errors.

The irca.sh script will generate a log file that outputs schema object creation

For example: my installation created a log file called irca2010-02-10_07-19-43PM.log

Now that we have created the schemas for SOA Suite and populated the schemas with required metadata, we are ready to install the SOA Suite. Unzip the files that you downloaded earlier and start the installation by executing the runInstaller script.

2. Install SOA suite

The splash screen will begin as long as all prerequisites have been met.

For our test SOA Suite deployment, I used the following entries for the 10g AS instance and administrator account (oc4jadmin):

AS Instance name: soa10gAS
AS administrator: oc4jadmin

Schemas

orabpel
oraesb
orawsm

Oracle:

sys
system/manager

EBS R12:
apps/apps
sysadmin/sysadmin

Next we need to enter details for the orainventory location:

After this runs, we need to run the orainstRoot.sh script as root user in another terminal window.

After the orainstRoot.sh script performs the oraInventory pointer file setup, we are ready to kickoff the installation for SOA Suite.

Now the Oracle installer (OUI) for SOA Suite runs the pre-requisite checks before installation begins. Since we are running an instance of EBS R12 already on the same server for test purposes, these already have been met to comply with kernel and system requirements for SOA Suite.

The installation begins after we confirm these are satisfied and on my test environment ran for a few hours since I have a slow 2Gb server with R12 EBS competing for resources with SOA Suite.

So after we take a break or lunch, we continue the installation.
Now we need to run the root.sh script in another terminal session as root.

We enter the prompts for directory and take defaults.

After a few hours, the installation process completes.
However, we have it fail at the end on configuration assistants during opmn (Oracle Process Manager) on a timeout process error. The solution to this issue which most likely is a bug in 10gAS (Oracle Application Server) is to stop opmn gracefully and kill any open opmn processes.

For reference, our test SOA Suite has the following details:

URL welcome page

http://tusebs.localdomain:8889

OEM 10g AS for Oracle 10g Application Server control and management

http://tusebs.localdomain:8889/em

Instance name: soa10gAS.tusebs.localdomain

Like most Fusion Middleware products, Oracle 10g Application Server is a cornerstone. However, future direction from Oracle appears to be that Oracle 10g Application Server will most likely be replaced eventually by Web Logic server.

Now that the installation has completed for SOA Suite, we need to apply a patch.

3. Install patch for SOA Suite

Download patch from OTN (http://otn.oracle.com)

$ unzip ias_linux_x86_101350.zip

$ cd Disk1
./runInstaller &

So after an hour or so, the patch for SOA Suite 10g completes and fails due to a bug most likely with SOA Suite.

Note: there is a bug in SOA suite due to issue with 10gAS with the opmnctl utility.
What happens is that the install will fail at end of the setup.

First we need to check status for the SOA processes.

Some tips on how to use OPMNCTL :

Uh oh, looks like SOA Suite processes are down.

The solution is to finish install for the patch and to manually kill the opmnctl processes for SOA suite.

Note: be careful to kill ONLY SOA suite opmnctl processes and NOT those for EBS Suite!

Use ps -ef|grep opmnctl to find the process ids and kill these. Then start opmnctl processes manually from the SOA ORACLE_HOME directory.

Run the following opmnctl commands from SOA ORACLE_HOME/opmn/bin directory:

$ opmnctl startall
$ opmnctl startproc ias-component=ASG

Now we need to restart the patch installer from SOA ORACLE_HOME directory and enter password for oc4jadmin screen

Next we need to enter password for OWSM schema

Click ok because it will shutdown the application middle tier for the SOA suite as part of the patch configuration process.

Now you will see the preview screen listing details for the patch with SOA Suite.

For the summary page, you just need to review and click ok to install the patch for SOA 10g Suite.

The patch takes about 30-90 minutes to run based on server performance. With my test server on VMWare with 2Gb of memory allocated to the VM, it ran about 90 minutes and was very slow because I also have a full blown R12 EBS environment installed.

As part of the final configuration for the SOA patch, we need to run the root.sh script as root user to finish the patch for SOA suite.

# cd /soa
# ./root.sh

The root.sh script configures privileges required for the SOA Suite installation.
The patch installation completes successfully.

However we need to check and if required, start the processes for SOA Suite for 10g Application Server.

We use opmnctl to start and validate all processes for SOA suite are back online

Verify that SOA Suite is available and login to welcome page

Oracle 10g SOA Suite uses the following URL for default installation:

http://hostname:port

Example: http://tusebs:8889 will show 10gAS site
You can access BPEL and other SOA tools from this page

We need to verify that SSO is working for Single Sign on with SOA Suite

Navigate to http://tusebs:8889/BPELConsole

After we login to BPEL we can check the settings for the SOA Suite:

These settings will be used at a later point to configure SOA Suite for BPEL with Oracle Applications Release 12.

Now we also want to verify that all is functioning with the Oracle 10g AS environment since this plays an key role in SOA Suite operations. We navigate to the main 10g AS console site.

By default this is under the URL:
http://host:port/em

For example, our test server uses http://tusebs:8889/em

Stay tuned for the next installment when we complete the steps to show you how to integrate the Oracle 10g SOA Suite with Oracle E-Business Suite Release 12!

Internet Browser Problems with R12 E-Business Suite

2010-02-10T18:15:00.000-08:00

I recently performed a new fresh install of the Oracle R12 E-Business Suite and all went smooth sailing until I opened web browser to launch Concurrent Manager administration screen in which R12 complained that it cannot find the J2SE plugin which is required for most administration screens on the client desktop. Since I am using Firefox for Linux, I tried the initial workarounds mentioned in MyOracleSupport (Metalink) note 414151.1

403 Forbidden Error When Download J2SE Plugin From Firefox In R12 to resolve the problem with Firefox and my R12 installation. Only problem is that since I am running client on a Linux machine, it did not open the oaj2se.exe file successfully.

So, turned to another Metalink Note 389422.1 Recommended Browsers for Oracle E-Business Suite Release 12 to see what browsers will work and allow me to install the plugin for J2SE with E-Business Suite for Release 12.

However, I wanted to use Mozilla Firefox as I prefer open source solutions for Linux.
So what to do? Since the required plugin is a windows file, you need to download the WINE package from http://www.winehq.org for Linux. Then you can install the plugin oaj2se.exe to fix the browser issues with R12 EBS. Even better, if you must use Microsoft Windows products, WINE allows you to do so with Linux!

Hope this helps.

Cheers,
Ben

Scheduling Downtime in R12 EBS

2010-01-27T21:51:00.000-08:00

As busy DBAs, we often need to schedule maintenance activities outside of business hours to perform critical tasks such as patching and server moves. It is wise and crucial to deliver the news well in advance if possible to the user and management community before performing any event that causes downtime. With the Oracle Applications EBS Suite for Release 12, the maintenance schedule for downtime is an excellent way to notify users and management of scheduled downtime events. Let's take a look at how to set this feature up for R12 EBS.

The navigation path to the downtime feature is to first login with system administrator responsibility and then navigate to site map as follows:

System Administrator-> Oracle Applications Manager Dashboard-> Site Map

At the site map you have features to schedule various system and DBA functions and tasks.

Click on the Maintenance tab. Under Patching and Utilities go to Manage Downtime Schedules.

Now you can setup the downtime message and schedule.

You have a choice to either use the default notification message or to create your own custom message as well as time and date for the downtime message and contact information. In addition, you need to setup a warning message prior to the downtime.

Once we have created our downtime schedule, we confirm this.

Now we can review our downtime schedules on the main screen listed below.

You have the option to update the downtime schedule as well as to delete it.
Another nice added bonus is the notification webpage that will advise users of the downtime prior to the event as well as status of the maintenance activity. You can setup the URL for this maintenance page and direct users to it before and during the scheduled maintenance activity.

By using this tool with Release 12, you can add an extra measure of communication that will make your life easier as a busy Oracle Apps DBA as well as build trust with the management and user teams in your business environment.

Cheers,
Ben

eTRM- e-Business Electronic Manual

2009-12-10T16:05:00.000-08:00

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

2009-12-07T15:00:00.000-08:00

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

2009-12-06T17:49:00.000-08:00

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

2009-12-03T18:43:00.000-08:00

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

2009-12-03T16:05:00.000-08:00

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

2009-09-23T22:12:00.000-07:00

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

2009-09-12T21:26:00.001-07:00

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

2009-09-07T11:28:00.001-07:00

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

2009-09-01T11:26:00.000-07:00

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

2009-08-07T18:19:00.000-07:00

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

2009-06-12T11:39:00.000-07:00

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

2009-05-24T21:19:00.000-07:00

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

2009-05-22T21:42:00.000-07:00

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

Part 3: SSL Configuration with Oracle HTTP Server (OHS)

2009-05-16T17:46:00.000-07:00

Dear readers,

In the first two parts, we illustrated how to configure the core functions for Identity Management (IdM) with Oracle 10gAS Fusion Middleware. In this section, we will explain how to setup the Oracle HTTP Server or OHS for SSL with Oracle 10g Application Server. OHS is Oracle's implementation of the Apache 1.3 webserver which is an integral component of the Oracle Fusion Middleware technology. In addition to playing a pivotal role in the Oracle 10gAS, OHS is a key ingredient in the Oracle Applications EBS technology stack. With SSL, we can secure HTTP communication between the user's web browser and OHS web server. OHS uses the mod_ossl module or plugin to implement SSL within the web server which fully supports SSL 3.0 standards.

We also have a useful SSL configuration tool (SSLConfigTool) that we will see later on in our discussion which automates many of the steps for us to secure the HTTP protocol with OHS. The SSLConfigTool is located under $ORACLE_HOME/bin directory.

How to Configure SSL with OHS

To configure OHS with SSL, we need to modify the httpd.conf configuration file for OHS to tell Apache 1.3 ahem, OHS server
that it will use SSL for security settings. The httpd.conf file is located under the $ORACLE_HOME/Apache/Apache/conf directory.
The following parameters are key to understanding SSL configurations with OHS:

- ServerName the server which will be enabled for SSL
- SSLWallet File directory path location for the server wallet
- SSLEngine setting to either enable or disable SSL
- SSLVerifyClient verification type for client certificates
we have three options here: none which means SSL without certificates
Optional- only user server certificate
Require: use both server and client certificates

Once we have configured OHS for SSL, we can access the OHS server from secure HTTPS protocol
from the host with https://hostname.domain:4443

Oracle OHS uses the port 4443 as the default port for SSL communications

Now, lets take a look at our current OHS httpd.conf configuration file settings

##
## httpd.conf -- Apache HTTP server configuration file
##

#
# Based upon the NCSA server configuration files originally by Rob McCool.
#
# This is the main Apache server configuration file. It contains the
# configuration directives that give the server its instructions.
# See for detailed information about
# the directives.
#
# Do NOT simply read the instructions in here without understanding
# what they do. They're here only as hints or reminders. If you are unsure
# consult the online docs. You have been warned.
#
# After this file is processed, the server will look for and process
# /home/oracle/infra/Apache/Apache/conf/srm.conf and then /home/oracle/infra/Apache/Apache/conf/access.conf
# unless you have overridden these with ResourceConfig and/or
# AccessConfig directives here.
#
# The configuration directives are grouped into three basic sections:
# 1. Directives that control the operation of the Apache server process as a
# whole (the 'global environment').
# 2. Directives that define the parameters of the 'main' or 'default' server,
# which responds to requests that aren't handled by a virtual host.
# These directives also provide default values for the settings
### Section 1: Global Environment
#
# The directives in this section affect the overall operation of Apache,
# such as the number of concurrent requests it can handle or where it
# can find its configuration files.
#

#
# ServerType is either inetd, or standalone. Inetd mode is only supported on
# Unix platforms.
#
ServerType standalone
#
# ServerRoot: The top of the directory tree under which the server's
# configuration, error, and log files are kept.
#
# NOTE! If you intend to place this on an NFS (or otherwise network)
# mounted filesystem then please read the LockFile documentation
# (available at );
# you will save yourself a lot of trouble.
#
# Do NOT add a slash at the end of the directory path.
#
ServerRoot "/home/oracle/infra/Apache/Apache"
#
# The LockFile directive sets the path to the lockfile used when Apache
# is compiled with either USE_FCNTL_SERIALIZED_ACCEPT or
# USE_FLOCK_SERIALIZED_ACCEPT. This directive should normally be left at
# Port: The port to which the standalone server listens. For
# ports < 1024, you will need httpd to be run as root initially.
#
# This port is used when starting without SSL
Port 7777
Listen 7777
#
# If you wish httpd to run as a different user or group, you must run
# httpd as root initially and it will switch.
#
# User/Group: The name (or #number) of the user/group to run httpd as.
# . On SCO (ODT 3) use "User nouser" and "Group nogroup".
# . On HPUX you may not be able to use shared memory as nobody, and the
# suggested workaround is to create a user www and use that user.
# NOTE that some kernels refuse to setgid(Group) or semctl(IPC_SET)
# when the value of (unsigned)Group is above 60000;
# don't use Group nobody on these systems!
#
User oracle
Group oinstall
#
ServerName sandiego.localdomain
#
# DocumentRoot: The directory out of which you will serve your
# documents. By default, all requests are taken from this directory, but
# symbolic links and aliases may be used to point to other locations.
#
DocumentRoot "/home/oracle/infra/Apache/Apache/htdocs"

#
# Each directory to which Apache has access, can be configured with respect
# to which services and features are allowed and/or disabled in that
# directory (and its subdirectories).
#
# First, we configure the "default" to be a very restrictive set of
# permissions.
#

Options FollowSymLinks MultiViews
AllowOverride None

We also need to update settings for the Oracle Process Manager and Notification (OPMN) for our Portal 10gAS instance
by changing settings for the opmn.xml configuration file located under the $PORTAL_HOME/opmn/conf directory. We can either edit this configuration file for Portal directly or more conveniently, to use the Oracle 10gAS Application Server Control for the Portal instance under Process Management tab. Below is our sample opmn.xml config file for Portal:

Of note here for our purposes, is that we need to enable SSL communication for the OHS HTTP Apache 1.3.x web server. By default, SSL is NOT enabled! So we need to find the section in the opmn.xml file that references the HTTP server. Below is the relevant section:

ias-component id="HTTP_Server"
process-type id="HTTP_Server" module-id="OHS"
module-data
category id="start-parameters"
data id="start-mode" value="ssl-disabled"

We need to change this to "ssl-enabled" and resync OHS to allow SSL settings to take affect.

I prefer to use Application Server control because if we edit the opmn.xml file directly, we need to resync it manually which can be a pain. Application Control Server does this automatically for us. Under the main Portal instance page for 10gAS, we navigate to Process Management as shown below

This brings us directly to the editor for the opmn.xml file and we update it to enable the SSL option

We apply the changes.

Now we need to verify that our certificates can be accepted and then apply final configuration changes to the OHS httpd.conf file to ensure SSL communications function correctly. First, we need to navigate to the Oracle Certificate Authority (OCA) user page. We also need to clear out our web browser and remove old certificates since we are using the same server for our testing purposes.

We use our administrator account and password to login via SSO

Once we login, we can view certificates that we created earlier

We choose to get our certificate by choosing the option Get Certificate which will be used for future SSL communications

Details for the certificates are shown below.

Now we need to add the directives to the Portal Instance OHS web server to complete our setup for enabling SSL with Oracle
10g Application Server.

To complete our SSL tasks for OHS, we need to select Advanced Server Properties tab in the HTTP Server administration web page.

Since the httpd.conf configuration file for OHS is what need to modify to complete our SSL setup for Oracle HTTP Server, we click on the link.

We need to add a couple of directives to our Apache (OHS) httpd.conf configuration file to enable SSL.

SSLVerifyClient require

We then click the Apply button and OHS will need to restart the web server to apply our changes for SSL. Be careful editing this file since any typos will cause the OHS web server to fail to restart.

Now hopefully, our Apache web server OHS restarts with the enabled SSL changes.

A good reference for Apache and OHS since OHS really is Oracle's rebranded version of the Apache 1.3 web server is to consult the Apache main site http://apache.org

For reference on the Apache and OHS SSL directives the link
http://httpd.apache.org/docs/2.2/mod/mod_ssl.html has excellent supplemental details.