SOA Upgrade ->

We upgraded our SOA install from to and hit a few issues.   One of the main issues was that after upgrading DEV our inflight processes disappeared.    We talked back and forth with Oracle and it was supposed to be supported but for some reason it wasn't working for us.    I know that from SOA 10g to SOA 11g in-flight processes are not supported as part of the upgrade.

One of the ways we use BPEL is workflow for a custom application.  It could take months for a workflow to complete and there are always in-flight processes.    So there is never a quiet time, or a point at which all workflows are complete to do the upgrade.

We put a plan in place to manually re-place all items back in the proper stage.    It seems like it was going to be a fair amount of effort so for DEV we only re-placed a subset of items to make sure we had the process correct.

However, once we upgrade TEST the in-flight processes somehow magically survived.    We aren't sure why, there could be some small inconsistencies between DEV and TEST which caused it.  Another reason could be that in DEV we upgraded to first, then at a later point upgraded to   In TEST we went directly to

The SOA upgrade itself isn't a very resilient process.   If the PSA (Patch Set Assistant) gets interrupted for some reason its not smart enough to recover.  In DEV it wasn't a big issue, I had an export of the MDS and SOAINFRA schemas and I just dropped and recreated.    For PROD tho, the amount of data, thus downtime, would prevent us from doing this.    We only hit an issue once in DEV and TEST was smooth,  so for PROD we all had our fingers cross.

The PROD install was going smoothly until I had to run the PSA.     Shortly after I started the schema upgrade it failed and a dreaded error appeared.  In the $FMW_HOME/oracle_common/upgrade/logs directory I found:

[2013-08-21T19:28:09.239-04:00] [SOA] [ERROR] [] [upgrade.SOA.SOA1] [tid: 13] [ecid: 0000K2ZD5BJ9lZWpPws1yd1I5Klw000004,0] [[
oracle.sysman.assistants.common.dbutil.SQLFatalErrorException: java.sql.SQLException: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The problem was a database session holding a lock in the SOAINFRA schema.   We hit this problem in DEV and the solution was to shutdown applications that access SOAINFRA tables and change the schema password.   However, I didn't notice a hung database session by one of our custom applications.

If you try to run the PSA again it says the SOAINFRA schema isn't valid and won't let you continue with the upgrade.   So I tried manually updating the registry to state the schema is valid.

update schema_version_registry set status='VALID' where mr_name='SOAINFRA';

Great!  The installer started up again but it quickly failed.  Checking the logs:

2013-08-21 19:36:19.378 rcu:Extracted SQL Statement: [ALTER TABLE BPM_CUBE_PROCESS ADD (SubType VARCHAR2(200), DeploymentInfo BLOB)]
2013-08-21 19:36:19.378 rcu:Statement Type: 'DDL Statement'
JDBC SQLException - ErrorCode: 1430SQLState:72000 Message: ORA-01430: column being added already exists in table

Since this was production, I opened a P1 SR with Oracle right away.   In these situations I try to get Oracle involved as quickly as possible.   I'll still continue to do research on my end.  Sometimes I find the solution quicker, sometimes Oracle Support does.   

This time I found the following note:

Patch Set Assistant Failed for SOA when Patch 13606871 is Applied on SOA (Doc ID 1517404.1)

It provided a set of SQL statements to help rollback the schema upgrade.  Only problem was my upgrade was from, not    Knowing that Oracle now supported manually rolling back the upgrade, then I looked at the script that was failing:


After a few trial and errors I managed to write a script to undo all the changes.     Unfortunately if you missed one, which was easy to do as the script is a few thousand lines, you had to start over.   Finally I managed to find all updated objects and continued with the upgrade.

Another upgrade issue I encountered showed itself in the startup logs for soa_server1:

java.sql.SQLException: ORA-25226: dequeue failed, queue UPG_SOAINFRA.EDN_OAOO_QUEUE is not enabled for dequeue
The solution to this problem was to manually restart the queues:

SQL> show user
SQL> exec dbms_aqadm.start_queue('IP_OUT_QUEUE',true,true);
PL/SQL procedure successfully completed.
SQL>  exec dbms_aqadm.start_queue('EDN_OAOO_QUEUE',true,true);
PL/SQL procedure successfully completed.
After the startup we waited anxiously to hear from the superusers to let us know the status of in-flight processes.    To much celebration (more so from them than us since they had alot of potential work to do) in-flight processes did not disappear.


Openworld 2013

Another Openworld has come and gone.  With all the keynotes, sessions and parti^H^H^H networking events yet to happen, its almost unimmaginable that it could go by so quickly.  Yet here I am typing up the summary of my trip.

I arrived the Saturday before the conference and shortly after checking in I went down to Moscone West to register.  This is the first time in a couple of years that I didn't attend under the Blogger program.   Hopefully i'll be able to blog enough this year to be eligible for the program again.

Lately i've had a few issues with query performance so I attended a number of sessions on the optimizer and sql tuning.   I also attended a couple on Grid Control so I could start planning our upgrade from 11g to 12c.  With the new 12.2 E-Business Suite just a short time before the conference I attended some sessions about that product as well

The last few years I did a day by day break down but this year i'll just mention some of the sessions  I attended and are worth downloading if your interested in the topic.

"UGF9740  --  RDBMS Forensics: Troubleshooting with Active Session History"

"UGF3062  --  The Query Optimizer in Oracle Database 12c: What’s New?"

"UGF5498  --  Solving Critical Customer Issues with the Oracle Database 12c Optimizer"

"UGF9790  --  Where Did My CPU Go?"

"CON8482  --  Oracle E-Business Suite Technology: Latest Features and Roadmap"

"CON8707  --  Consolidating Databases with Oracle Database 12c"

"CON8711  --  Oracle RMAN in Oracle Database 12c: New Features and Best Practices"

"CON8460  --  Deployment and System Administration of Oracle E-Business Suite 12.2"

"CON8725  --  Behind the Scenes of Oracle Multitenant"

"CON8492  --  Oracle E-Business Suite Technology Certification Primer and Roadmap"

"CON8643  --  Oracle Optimizer Boot Camp: 10 Optimizer Tips You Can’t Do Without"

"CON9458  --  Oracle Linux Troubleshooting: Diagnostics and Best Practices"

"CON8883  --  Cloning and Snapshots with Oracle Database 12c"

"CON11637  --  What’s New in Oracle Database 12c"

"CON7457  --  SQL Tuning 101"

"CON8252  --  Best Practices for Maintaining Oracle Fusion Middleware"

"CON7752  --  Oracle Enterprise Manager Cloud Control 12c: Top 10 Features for DBAs"

A couple of dampers on the trip.  Baggage handlers left my suitcase in a puddle somewhere at SFO or the Toronto airport.   So half of my clothes were soaking wet.  Not a huge deal, I just hung them up to dry but luckily I had opened my suitcase when I arrived, instead of the next morning when I would have needed more clothes.

Larry Ellison didn't show up for his keynote.  I had skipped a session and lined up early so I could get a good seat.   Looks like I wasn't the only unhappy person there because as soon as he was announced as a no show quite a few people left.    Another first is that I didn't know very many people attending the conference.  I managed to met up with a former co-worker a few times which was great.

From a session/networking perspective tho I had a great time.   I learned a lot and met some great people.    Hopefully I will be able to return again next year.

I liked how they didn't totally enclose Howard Street this year.    If it rains its nice to have the tents but I found them to be pretty hot and stuff if the weather is nice.    It also opened up the space and made it more enjoyable to meet up with people.

I didn't take alot of pictures but ones I did take can be found on flickr:


runInstaller java.lang.reflect.InvocationTargetException

Well this one stumped me for a little bit today:

Not much information on Metalink or Google.    In the past one issue that would crop up every now and then was /tmp mounted with noexec.   I checked for that but it was ok... On a whim I decided to set my tmp dir to another location and it worked:

TMP=/u01/tmp; export TMP

Update: I recall why changing the TMP directory worked.    The problem was with noexec as one of the mount options of /tmp.   I had changed it in fstab but couldn't arrange downtime so I used environment variables to repoint tmp.   The server still hasn't been rebooted, so the change hasn't come into affect yet.  


error on line 1 at column 1: Document is empty

I'm not sure why this happens but every now and then the invalidator password gets corrupted.  When that happens, the following error will be seen when you try to access portal:

This page contains the following errors:
error on line 1 at column 1: Document is empty
Below is a rendering of the page up to the first error.

Re-enter the invalidator password for Webcache and Portal.




OAM and EBS Breaks after Cloning

We are in the process of integrating E-Business Suite with Oracle Access Manager (OAM). Its setup in DEV and TEST but not in PROD yet.    There was a requirement to reclone DEV and as a result the integration with OAM broke.

We ran all the configuration steps on the EBS side, such as:

  • $FND_TOP/bin/ -script=SetSSOReg -registerinstance=yes
  • Verifying ASADMIN was active and the correct password.
  • Profile options to configure authentation:
    • Applications SSO Type
    • Applications SSO Login types
    • etc...
  • Business Events to make sure account creation and modifications synchronizes correctly.
  • We skipped all of the tasks to be performed on the OAM side since they were already done.

However, when restarting Access Gate we would see the following error in the logs:

        at weblogic.jdbc.module.JDBCModule.prepare(
        at weblogic.application.internal.flow.ModuleListenerInvoker.prepare(
        at weblogic.application.internal.flow.DeploymentCallbackFlow$
        at weblogic.application.utils.StateMachineDriver.nextState(
        at weblogic.application.internal.flow.DeploymentCallbackFlow.prepare(
        Truncated. see log file for complete stacktrace
Caused By: weblogic.common.resourcepool.ResourceSystemException:
Could not connect to 'oracle.apps.fnd.ext.jdbc.datasource.AppsDataSource'.

The returned message is: ORA-01017: invalid username/password; logon denied

We triple checked the datasource credentials defined in the Access Gate server but still nothing worked.  It turns one of the steps to deploy Access Gate was to copy the dbc file from $FND_SECURE over to the $MW_HOME/appsutil/accessgate/dev directory.   Since DEV was recloned the APPL_SERVER_ID value had changed.    We re-copied this file, bounced the Access Gate server and everything started working again.

Since we don't have EBS integrated with OAM in PROD yet i'm not sure if we will need to do any additional steps after a clone.   I'm thinking all the steps above will apply except for maybe business events as they will already be configured properly in PROD.   I'll update this post if I have to do anything else.


Web Cache 11g as a Reverse Proxy for E-Business Suite Virtual Tier

We are currently in the process of implementing a virtual tier setup for our E-Business Suite environment.    The reason is because we are planning on exposing some modules to the internet.   Since we are a small shop a separate physical tier isn't necessary.  

A complicating factor tho is that our DEV environment is in house while our TEST and PROD servers are located at a hosting provider.     TEST and PROD will be able to take advantage of an F5 Loadbalancer, while DEV doesn't have one available.

To setup the virtual tier i'm following the steps in note Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration - R12 [ID 726953.1]

The note suggests using Web Cache 10g to simulate a reverse proxy:  Installing and Configuring Web Cache 10g and Oracle E-Business Suite 12 [ID 380486.1]

We don't have any 10g webcache environments, but we use 11g for our Fusion Middleware environment.  I couldn't find any notes on Metalink which described using WC 11g.  Technically tho, there should be no reason why it wouldn't work with 11g, so I decided to give it a try.

NOTE: I am not sure if 11g Web Cache is certified with R12, I don't believe it is.   So you should confirm with Oracle Support before using this in a production environment.   Since this is just a POC and in TEST and PROD I will be using an F5 Load Balancer I decided to give 11g WC a try.

For each under Section 5: Configuration details, I will show the 11g setup.

5.1.1 - Login to the Web Cache Admin page

  • http://server:8091/webcacheadmin

5.1.2 - Listen Port

  • Add a Listen Port.
    • On the left hand side, under Ports click on Listen Ports.   Once the Listen Ports page has loaded click on the Create... button.
    • IP Address Field -> ANY
    • Port Number Field -> 7777 (or whichever port your EBS Virtual Tier is listening on.)
    • Since this is a DEV environment I am not using https so I left Protocol as default.
    • Click on Submit.

5.1.3 - Configure Web Cache Site definitions for E-Business Suite.
  • On the left side under  Origin Servers, Sites, and Load Balancing click on Site Definitions.
    • No changes from the 10g document.   Here is a screenshot of my setup:
    • myserver  is your WebCache server.
    • If your not using HTTPS, don't enter / for HTTPS Only Prefix, leave it blank or it will not work.
5.1.4 - Configure E-Business Suite as an Origin Server
  • On the left hand side click on Origin Servers and Add... on the right hand side once the page loads.  The setup here is almost identical to the 10g document.   The only thing missing is the option to select the protocol.

    Here is a screenshot of my setup.  Remember, the Origin Server is the server where your EBS virtual tier is running.  In my case its configured to listen on port 7777.

  • On the left hand side click on Site-to-Server Mapping 
    • Click on the Add...  button once the page loads.   
    • Below is a screenshot of my configuration.  Host Name (in this case server) is your Web Cache server.   Under Origin Servers select the one you created above for your EBS environment.  (Sorry for blanking out some of the server names, i'm paranoid. ;)   This is  a copy of the default settings for Web Cache 11g for our fusion middleware environment.

    • The above configuration seems to work fine but its quite different that what your instructed to do in 10g.   In 10g your told to create the following mappings: 
      • After seeing this I configured my Site-To-Server Mappings as following:
      • The file site-to-site mapping above was the default one.   The next 3 are the ones I added for this EBS configuration.  The second one is the same as the screen shot above.   The next two are the same as that one but hostname is set to * for both, and for the last the port number is a *.   ESI Content Policy is set to Exclude fragments for both.

As you can see, the 11g setup is pretty much identical.  You can follow the rest of Installing and Configuring Web Cache 10g and Oracle E-Business Suite 12 [ID 380486.1] as is.   If your trying this configuration and running into issues with the configuration on the EBS side for the Virtual Tier let me know.  


R12 Cloning: missing from file system.

This weekend I had to build an R12 environment.   We use Oracle VM in our development environment so this made the who process much easier.    Before I would have to setup a new server, install the OS, patch it, put in the Oracle pre-req's then clone.   Now I just need to run pre-clone, shutdown the environment, copy it, rename/reconfigure the vm and run post clone.

When I ran dbTier tho I hit the error described in the following note:

On Release 12 adcfgclone is failing with AC-00423, RC-50014 missing from file system [ID 549872.1]

The solution is to copy from the source system and place it under $ORACLE_HOME/appsutil/template

However, when I executed again I hit the same error. would get deleted from the template directory.

The solution was to run autoconfig, preclone and recopy the db files and software over.

Well, at least I didn't have to build a new server.. Saved me a little time anyways.

If your having cloning issues you may want to take a look at the other issues I encountered as well.  Just click on the EBS Cloning label on the right side.


R12 Cloning: Can't login as apps

This past week I was cloning a DEV environment from PROD.   I noticed that the script appeared to be hanging.  Looking at the logs it was stuck on:

Attempting to connect to database using :

        DbSid     :DEV
        DbHost    :myserver
        DbPort    :1553

So the first thing I tried was logging into the database using tns, ex:  sqlplus apps/apps@DEV.   This also hung.   However, other accounts such as dbsnmp, etc were fine.

Using Grid Control I noticed there was a lot of contention in the database:

Drilling down into concurrency it was very easy to see that the issue was related to library cache locks.

Looking at the sessions involved I found that they were related to our SOA server and other integrated applications.    Since I am in the process of cloning this database from PROD, the databases are identical, down to the account passwords.   So all of these connection attempts were using the incorrect password.   I reset the passwords to their appropriate DEV values a little later in the cloning process.

I did a quick search on Metalink and this note jumped out at me:

The recommendation is to set the following event:


I set this parameter, bounced the database, reran and it ran with no issues.   

So, just an FYI.  If your cloning an EBS environment and have alot of integrated applications/processes that you may run into this issue until you either reset the account passwords or set this event.


Mac OSX: Disable Java 7

As most of you are probably aware if you are running Java 7 on your desktop there is a bug which will prevent forms from launching:

Steven Chan covers the certifications and the workarounds on his blog.

My Mac was updated to Java 7 a short period of time ago and of course I had issues opening forms.    I'm late to the game so i'm sure most people have already hit this issue.  I'm just documenting it here as a personal reference for when I need to back the fix out.

There are two solutions to this problem:

1.  Patch your EBS environment as per Steven's article above.
2.  Disable Java 7 on your Mac.

I choose to do number 2 as I needed to login ASAP and didn't have time to wait for patches to get through QA.

To disable Java 7 and re-enable Java 6 follow the steps on Apple's Support site:

While I was trying* to track down when my system was updated to Java 7, I found the following site from Oracle which provides alot of information for Mac users of Java:

* I'm not sure how my Mac was updated to 7.   I'm assuming it was when our tech support upgraded my Mac to Mountain Lion and updated it.


Portal Upgrade to

A few months ago we upgraded our Fusion Middleware Portal environment from to   The upgrade went pretty smoothly, especially compared to our to upgrade.   It seems that Oracle has replaced alot of the manual post-upgrade tasks with scripts.   It seemed much easier this time around.

Below is the upgrade.   I'll post a followup shortly with some of the issues I encountered and how they were resolved.

1.  Shutdown Portal
   a.  $FUSION_HOME/PORTALasinst_1/bin/opmnctl stopall;

2.  Backup application and Database

3.  If you haven't already download the patchset from Oracle and extract it.
       WebLogic 10.3.6:


4.  Update WebLogic
    $JAVA_HOME/bin/java -jar wls1036_upgrade_generic.jar


Prompted for Configuration manager, Bypassed…


5.  Upgrade Portal
   a. $STAGEDIR/Disk1/


 Upgrade Portal Schema using the Patch Set Assistant

   1. Recompile any INVALID objects:

   2. Verify aq_tm_proccesses > 0

        show parameter aq_tm_processes;
        alter system set aq_tm_processes=1 scope=both;

  3.  Run the PSA utility


After you put in the database credentials click on the Connect button to select the Portal schema, enter the password and click on Next:



4.  Recompile any INVALID objects following same steps as above.
5 . Verify upgrade was successful

Post-Patching Tasks If Your Starting Point is Release
1 . Upgrading System Components
a.  Startup the Weblogic Admin server for the Portal Domain
Check to see if Config is locked
b.   ORACLE_HOME=/u01/app/oracle/product/fmw11g/PORTALas_1
c.  Start OPMN Instance
$FUSION_HOME/PORTALasinst_1/bin/opmnctl start
d.   Execute from $ORACLE_HOME/opmn/bin
[oracle@myserver bin]$ ./ -oracleInstance /u01/app/oracle/product/fmw11g/PORTALasinst_1 -adminHost myserver -adminPort 7001 -adminUsername weblogic
Unregister instance...
Command requires login to weblogic admin server (myserver):
  Username: weblogic
Unregistering instance
Command succeeded.
redeploy NonJ2EEManagement.ear...
Command requires login to weblogic admin server (myserver):
  Username: weblogic
Redeploying NonJ2EEManagement Application...weblogic.Deployer invoked with options:  -adminurl myserver:7001 -username weblogic -name NonJ2EEManagement -source /u01/app/oracle/product/fmw11g/PORTALas_1/opmn/applications/NonJ2EEManagement.ear -redeploy -upload -noexit
Task 0 initiated: [Deployer:149026]deploy application NonJ2EEManagement [Version=11.1.1] on AdminServer.
Task 0 completed: [Deployer:149026]deploy application NonJ2EEManagement [Version=11.1.1] on AdminServer.
Target state: redeploy completed on Server AdminServer
Command succeeded.
re-register instance...
Command requires login to weblogic admin server (myserver):
  Username: weblogic
Registering instance
Command succeeded.
Successfully upgraded NonJ2EEManagement.ear and the registeration.
e.   Resolving JDBC Errors in Oracle Reports and Oracle Portal
You must perform the following in order to avoid these JDBC error messages:
Go to the bin directory inside your DOMAIN_HOME.
Edit the (on UNIX operating systems) or setDomainEnv.cmd (on Windows operating systems) file and search for EXT_PRE_CLASSPATH.
Change the definition to match the following:
On UNIX operating systems:
For example:
if [ "${EXT_PRE_CLASSPATH}" != "" ] ; then
   if [ "${PRE_CLASSPATH}" != "" ] ; then
      EXT_PRE_CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc6.jar // add this line
      export EXT_PRE_CLASSPATH                           // add this line
      export PRE_CLASSPATH
      EXT_PRE_CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc6.jar  // add this line
      export EXT_PRE_CLASSPATH                            // add this line
      export PRE_CLASSPATH
f.   Updating Fusion Middleware Shared Libraries
Run the upgradeJRF() command on the node or system where the Administration Server is located for each domain you want to update. Your domain location is passed as a parameter:
wls:/offline> upgradeJRF('/u01/app/oracle/product/fmw11g/user_projects/domains/PortalDomain')

g . Updating Configurations and Stores
wls:/offline> upgradeOpss(jpsConfig="/u01/app/oracle/product/fmw11g/user_projects/domains/PortalDomain/config/fmwconfig/jps-config.xml", jaznData="/u01/app/oracle/product/fmw11g/oracle_common/modules/oracle.jps_11.1.1/domain_config/system-jazn-data.xml")  
{auditStore=null, password=null, user=null, url=null, jdbcDriver=null, jaznData=/u01/app/oracle/product/fmw11g/oracle_common/modules/oracle.jps_11.1.1/domain_config/system-jazn-data.xml, jpsConfig=/u01/app/oracle/product/fmw11g/user_projects/domains/PortalDomain/config/fmwconfig/jps-config.xml}
Starting upgrade of opss configuration and security stores.
Aug 23, 2012 11:55:38 AM getInstance
WARNING: file /u01/app/oracle/product/fmw11g/user_projects/domains/PortalDomain/config/fmwconfig/audit-store.xml was not found
WLS ManagedService is not up running. Fall back to use system properties for configuration.
Upgrade of opss configuration and security stores is done.
NOTE: Ignore Warning above.  See Note:  1435333.1
h . Optimizing Performance for WebLogic Server Domains on 64-Bit Platforms
Modify $DOMAIN_HOME/bin/ and add:
before line:

# Had to have a separate test here BECAUSE of immediate variable expansion on windows

if [ "${JAVA_USE_64BIT}" = "true" ] ; then
        if [ "${JAVA_VENDOR}" = "Oracle" ] ; then
                export EXTRA_JAVA_PROPERTIES
if [ "${JAVA_VENDOR}" = "Oracle" ] ; then