Kevin Kempf's Blog

March 9, 2015

R12.2 Apache won’t start up

Filed under: Linux, Oracle Linux, R12.2, Weblogic — kkempf @ 9:45 am



I had to bounce my R12.2 non-production front end this weekend to switch kernels around.  I restarted services, but admit I didn’t pay much attention to them as it wasn’t PROD.  We run multiple instances of R12.2 on different port pools but on one host (well 2, one for the databases, one for the application servers). We do this not because I like it, but because we don’t have infinite budgets.  Regardless, I get a complaint that our audit environment isn’t working.  Sure enough, the login is unavailable, and Weblogic says the web tier process EBS_web_AUDIT is down.

Identify the problem: To the command line!

I try to simply start it in Weblogic but it errors out, and there’s far too many log links to click for this admin to stay in a GUI.   I run start and get:

You are running version 120.0.12020000.6

Starting OPMN managed Oracle HTTP Server (OHS) instance … exiting with status 204

Follow the bouncing ball tells me to check my log file: $INST_TOP/logs/appl/admin/log/adapcctl.txt, and here’s what it tells me:

ias-instance id=EBS_web_AUDIT_OHS1

--> Process (index=1,uid=1810789619,pid=11169)
  failed to start a managed process after the maximum retry limit

03/09/15-09:52:40 :: exiting with status 204
[2015-03-09T09:38:34.0590-04:00] [OHS] [INCIDENT_ERROR:32] [OHS-9999] [worker.c] [host_id: (myhostname).com] [host_addr: (myipaddr)] [pid: 10527] 
[tid: 140534409107264] [user: oraaudit] [VirtualHost: main] (98)Address already in use: make_sock: could not bind to address [::]:10059
[2015-03-09T09:38:34.0590-04:00] [OHS] [INCIDENT_ERROR:32] [OHS-9999] [worker.c] [host_id: (myhostname).com] [host_addr: (myipaddr)] [pid: 10527] 
[tid: 140534409107264] [user: oraaudit] [VirtualHost: main] (98)Address already in use: make_sock: could not bind to address
[2015-03-09T09:38:34.0590-04:00] [OHS] [INCIDENT_ERROR:20] [OHS-9999] [worker.c] [host_id: (myhostname).com] [host_addr: (myipaddr)] [pid: 10527] 
[tid: 140534409107264] [user: oraaudit] [VirtualHost: main] no listening sockets available, shutting down
[2015-03-09T09:38:34.0591-04:00] [OHS] [ERROR:32] [OHS-9999] [core.c] [host_id: (myhostname).com] [host_addr: (myipaddr)] [pid: 10527] 
[tid: 140534409107264] [user: oraaudit] [VirtualHost: main] Unable to open logs

Closing in

So it appears that my user (oraaudit) can’t get a port it wants (10059), and after a little hunting on Google I find the right syntax. I wanted to use netstat, but I couldn’t figure out the PID and lsof made it really easy:

# lsof -i:10059
java    13815 oratrain  305u  IPv6  82113      0t0  TCP (myhostname).com:10059->(myhostname).com:rds2 (ESTABLISHED)

Take down the suspect

It’s some random java process from another instance (Train). See ya.

# kill -9 13815
# lsof -i:10059

Restart Apache start

You are running version 120.0.12020000.6

Starting OPMN managed Oracle HTTP Server (OHS) instance ... exiting with status 0

Nothing to see here

This is something most apps DBAs have seen at some time or in some form. The path to follow changes, but in the end the fix is usually similar to this. Knowing with certainty that it’s OK to kill the blocking PID is always a delicate task. There’s absolutely no way you could have fixed this from within the Weblogic admin console that I can think of, short of going into the other environment and shutting it down completely.

February 25, 2015

R12 Take Aways and Warnings, Part 3 (Java Clients, Dataguard with editioning, logs, crons and the Workflow Notification Mailer)

Filed under: 11g, Dataguard, R12.2 — kkempf @ 1:58 pm


More R12.2 War Stories

I thought I’d go back to my R12.2 war stories today.  To be honest, I still have plenty to talk about even after this post, but a journey of a thousand miles begins with one step, right?

Java Clients

What a pain in the 4th point of contact.  We went into this upgrade knowing we wanted to deploy the most advanced Java client to the desktop (for forms) we could, so we didn’t have to deal with it.  Wow it’s a terrible hot mess.  We settled on 1.7.67.  Oracle, could we just call it 7.67?  Why do we need a 1 in front?  Sorry back to the topic at hand.  Java 7 is smart, and won’t run unsigned Jar files without much ado and many, many user clicks.  This is all good in the name of security, I suppose, but it sucks to administer.  At one point here I talked about the problems that arise.  Oracle tries to help mitigate this with a new product called Java Advanced Managment Console but it’s really new and really doesn’t seem to fully meet our needs.  So bottom line here: figure out how to get a java code signing certificate from your favorite CA (Thawte worked great for us) and what the new rules are for signing jar files.  You can search Enhanced Jar Signing on MOS, really you just need to understand adjkey to build the key, pick your CA, and use adjkey again to import the certificate.  Then you run adadmin (1,4) to regenerate jar files.


We utilize active dataguard with a physical standby against our Linux x86_64 database.  You may think Dataguard is a curious topic for an R12 upgrade.  The truth is that the upgrade itself has little to do with dataguard; in fact I disabled it before starting the upgrade because a) I didn’t want to ship hundreds of gigabytes of data down the pipe and b) I had no faith it would actually work.  After I was done with the upgrade I rebuilt the dataguard environment using RMAN, and it crashed every week for 6 weeks.  There’s a few known issues out there, but the error manifested itself like this:

Sat Dec 13 14:17:30 2014
Errors in file /u01/appprod/oracle/proddb/diag/rdbms/proddg/PROD/trace/PROD_lgwr_24265.trc:
ORA-04021: timeout occurred while waiting to lock object
LGWR (ospid: 24265): terminating the instance due to error 4021
Sat Dec 13 14:17:30 2014
System state dump requested by (instance=1, osid=24265 (LGWR)), summary=[abnormal instance termination].
System State dumped to trace file /u01/appprod/oracle/proddb/diag/rdbms/proddg/PROD/trace/PROD_diag_24236_20141213141730.trc
Dumping diagnostic data in directory=[cdmp_20141213141730], requested by (instance=1, osid=24265 (LGWR)), summary=[abnormal instance termination].
Instance terminated by LGWR, pid = 24265

And now you have no dataguard!  After trying various patches (17588480, 19631234, and 16299727), it appears that the real problem was that dataguard didn’t handle editioning well and the answer was patch 16299727 and adding

_adg_parselock_timeout=500  # dataguard editioning fix
event=”16717701 trace name context forever, level 104887600″ # dataguard fix

to my pfile/spfile fixed the issue.


There’s lots of logs in R12.2.  Pretty safe, obvious statement, right?  Well you need to go figure out where they are, because they all moved in 12.2.  If you don’t, well you’re gonna have a mess or possibly run out of disk.  I cron’d up a bunch of find commands to get rid of them; obviously don’t paste blindly, you need to tweak the retentions to your needs.  Some of these are supposed to be covered by cleanup jobs, but I find this is a nice insurance policy:

# Remove old outputs $APPLCSF/out or /u02/appprod/fs_ne/inst/PROD_hostname/logs/appl/conc/out
0 8 * * 1 /usr/bin/find /u02/appprod/fs_ne/inst/PROD_hostname/logs/appl/conc/out -mtime +30 -type f -exec rm -rf {} \;
# Remove old reqs $APPLCSF/log or /u02/appprod/fs_ne/inst/PROD_hostname1/logs/appl/conc/log
0 8 * * 1 /usr/bin/find /u02/appprod/fs_ne/inst/PROD_hostname/logs/appl/conc/log -mtime +30 -type f -exec rm -rf {} \;
# Remove Weblogic oacore Server Logs
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/oacore_server1/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/oacore_server1/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/oacore_server2/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/oacore_server2/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/oacore_server3/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/oacore_server3/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/oacore_server4/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/oacore_server4/logs -mtime +7 -type f -exec rm -rf {} \;
# Remove Weblogic Admin Server Logs
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/AdminServer/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/AdminServer/logs -mtime +7 -type f -exec rm -rf {} \;
# Remove Weblogic Forms-c4ws Server Logs
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/forms-c4ws_server1/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/forms-c4ws_server1/logs -mtime +7 -type f -exec rm -rf {} \;
# Remove Weblogic oafm Server Logs
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/oafm_server1/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/oafm_server1/logs -mtime +7 -type f -exec rm -rf {} \;
# Remove Weblogic forms Server Logs
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/forms_server1/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/forms_server1/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/forms_server2/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/forms_server2/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/forms_server3/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/forms_server3/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/forms_server4/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/user_projects/domains/EBS_domain_PROD/servers/forms_server4/logs -mtime +7 -type f -exec rm -rf {} \;
# Remove Weblogic wlst Server Logs
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/logs -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/logs -mtime +7 -type f -exec rm -rf {} \;
# Remove Weblogic OHS Logs
0 8 * * 1 /usr/bin/find /u02/appprod/fs1/FMW_Home/webtier/instances/EBS_web_PROD_OHS1/diagnostics/logs/OHS/EBS_web_PROD -mtime +7 -type f -exec rm -rf {} \;
0 8 * * 1 /usr/bin/find /u02/appprod/fs2/FMW_Home/webtier/instances/EBS_web_PROD_OHS1/diagnostics/logs/OHS/EBS_web_PROD -mtime +7 -type f -exec rm -rf {} \;


While we’re on the subject of cleaning up logs, let’s talk about crons.  If it weren’t for cron, my R12.2 environment would not run.  Period.  Besides cleaning up logs, I have crons to do things like

  • rotate MSCA ports (telnet server)
  • bounce MSCA (daily!  it’s horribly unstable)
  • monitor various R12 related things from the OS (for example, use curl in a shell script to ensure the front end login page is up)
  • bounce JVMs every week (really, you need to do this)
  • cleanup apps sessions which otherwise won’t timeout or die (more on this later, worst surprise of the upgrade)
  • preclone the apps tier

Workflow Notification Mailer

If you’re using this, especially if your mailer accepts IMAP/inbound email responses as a part of the workflow, make sure you understand how it looks in 12.2.  It does not survive the upgrade from 11i, and the screens are totally different.  As far as I can tell, they don’t require any new or additional information to work, they just changed around the configuration screens so as to confuse you.  Bottom line is this: it mostly works once you get it configured right, but you should be aware that there’s a patch 18842914 for the mailer which greatly improved its reliability.  I put that in because it kept crashing when some 3rd party server was spamming it with email bounces (yeah, I’m not going into that any further).

Coming Soon

I still haven’t covered the most annoying things… like adop, the techstack, custom tops, MSCA in general, report manger replacing the ADI desktop client, and having to manually kill sessions to keep the database from dying…

January 30, 2015

Determine Weblogic Server Version in EBS R12.2

Filed under: R12.2, Weblogic — kkempf @ 10:08 am

A diversion from my topic in progress

Stumbled across this via an analyst on an SR I was working, thought it might be useful for others.

$ cd $FMW_HOME/wlserver_10.3/server/lib

$ java -cp weblogic.jar weblogic.version

WebLogic Server Tue Nov 15 08:52:36 PST 2011 1441050

Use ‘weblogic.version -verbose’ to get subsystem information

Use ‘weblogic.utils.Versions’ to get version information for all modules

January 13, 2015

R12 Take Aways and Warnings, Part 2 (AD Changes and your new Techstack)

Filed under: R12.2, Techstack — kkempf @ 9:53 am


ADOP and your new Techstack

As promised, I’m going to continue the discussion about about my R12.2 upgrade and the lessons learned as a part of it by digging into the techstack.  There’s a lot of ground to cover here!

General Upgrade Flow and Layout

For a general understanding, I thought I’d start with a basic explanation of the upgrade process.  Let’s assume a simple installation, where you have a database tier and an applications tier.  As usual, I’m tossing out my disclaimer that this list is NOT complete.  It’s a high level look at the basic tasks.  Follow the official doc!

  • 11i Pre-Patching/Prep Work

You will need to first prep your old environment (in my case 11i, though I suspect this will hold true if you’re on 12.0/12.1) for the upgrade.  This means under 11i, you need to apply database patches, run checkers, clean up your customizations, etc.  Since R12.2 allows for a 64-bit front end, we chose to create a new front end host and leave the database where it was (since it ran fine).  Compared to 11i, these new certifications allow you all kinds of nice things, such as running Linux 6, and allocating huge sums of memory (with a 64-bit OS) to the host without running a PAE (physical address extension for memory) kernel.  This in turn allows you to easily accommodate many, large JVMs on the front end.  Regardless, you need to be at the appropriate baseline before you begin the upgrade.

  • 11i Database Patching

Pretty self explanatory; there’s a bunch of mandatory patches to the RDBMS tier.

  • 12.2 Rapid Install

You first lay down the file system(s) on whatever will be your new R12 application server.  Note: You can’t do this ahead of time in production.  At this point you’re touching the database and affecting 11i

  • Application DBA Tasks

In the middle of your upgrade, you need to perform a bunch of AD tasks.  Such as getting on the right version of the techstack, extending tablespaces, fixing customizations, etc.

  • Adpatch

This is the last time you’ll run adpatch, and it’s a monster: the largest, longest bit of work involved.  Lots of worker fails with errors which you have to go research.

  • Another 12.2 Rapid Install pass

Short pass here, against your new R12 filesystem.  Congratulations, after this you’re on 12.2 (not a production release, but you can login after this).

  • Enabling 12.2 Online Patching

This is why you decided to install 12.2 right?  This is a long, database intensive task.

  • Applying Techstack Patches

You can mitigate this by using the latest install CD for 12.2.X, but you’ll be installing patches against Weblogic, forms and reports, http server, FMW etc

  • Applying 12.2.3 (or 12.2.4, presumably!)

This is it’s own patch, applied with adop

  • Post-install Tasks

The devil is in these details, which are specific to your installation.  An example is configuring Vertex (tax) for your financials

JVM Sizing

If you’re serious about this upgrade, please Google up Oclay Sariouglu and Dimas Chbane’s (both from Oracle) presentation from East Coast Oracle Users Group conference titled “E-Business Suite 12.2: Architecture and New Features”.  Specifically, a PowerPoint called Chbane 12.2 Architecture.pdf is worth reviewing; I’m sorry I don’t know with certainty that you can get it easily if you didn’t attend the conference, you can probably get it through official channels.  Here’s the bottom line: Oracle has a scaling recommendation (and a revised scaling recommendation!) for adding oacore and forms JVM’s based on the number of users.  The default installation for R12.2 gives you one (1) JVM for forms (forms_server1) and one (1) JVM for oacore (oacore_server1), and each take maybe 256MB or 512MB (I forget which).  This is ludicrously small, for any real deployment, and I’m glad I went to this conference and attended this session to at least get the references on how to size them and how to add them.  These JVMs live inside of Weblogic, and you need to size them correctly.  Because if you go live with the defaults and more than a few dozen users, your JVMs will crash.  Check out 1905593.1.  We went live with 4 of each (forms, oacore) at 2GB per for an installation footprint of about 1000 users (about a third of whom are in professional forms) and it’s served us well.  The truth is our forms footprint is probably too big, but we haven’t cut it back yet.

Adding JVMs is a somewhat manual, command line process, or at least was when we did it, and for being rather essential to a go-live, it stands out as an after-thought to the whole process.  This task also includes changes to the context files, which also has hooks to your start/stop scripts (  Consequently, this has the side effect of making an application server startup (or a maintenance adop cutover) even slower (if it will go down cleanly at all, but I digress).  Another bit of fallout is that when you clone, you may need to cut back these JVMs, depending on the memory resources you have available on your non-production host(s).  If you find yourself in this situation, I’ll offer this tidbit: this can be accomplished by changing the s_oacorename and s_formsname parms in the context file to eliminate, for example, oacore_server2, oacore_server3, etc. until you get it sized appropriately.

AD/TXK Patching

As you probably know, officially, AD is the product abbreviation for Applications DBA, and TXK corresponds to Teckstack.  The two are tightly interwoven, to the point that in R12.2 (perhaps in earlier editions, unsure) they must always be on the exact same version, and patched together.  It raises an interesting aside, in that to me, as the DBA, as they’re now indistinguishable why bother to make any distinction and not just roll them up under one product… but I digress.

In the middle of your upgrade, you apply AD.C.  In my original plan, I think I had the choice of applying AD/TXK delta 3 or delta 4.  Since I had nothing to lose, I went with delta 4, and during the course of our upgrade testing delta 5 was released.  Delta 5 fixed so many critical bugs in the AD stack that I had to beg the project manager to let me put it in late in the testing phase.  For example, we use mobile supply chain (MSCA) and under delta 4 the telnet ports would change during an adop cutover phase (imagine if you will telling hundreds of manufacturing operators to change their telnet ports on PC’s and hand-held devices after every maintenance weekend).  All of this gets rather confusing, because officially I’m on AD/TXK delta 5, but that’s not all.  Now there’s essential updates on top of AD/TXK which get released, seemingly at random.  For example, patches 19445058/19581770 (now superseded by 20034256: additional fixes that were not in the first bundle/November bundle patches for AD Delta 5!) are called “BUNDLE FIXES FOR R12.TXK.C.DELTA.5” and “BUNDLE FIXES FOR R12.AD.C.DELTA.5”.  This is a mess.  To me, it would be much simpler to consolidate the AD/TXK patches and put some meaning to the versions.  Why can’t AD/TXK delta 5 with bundle fix 1 be called, simply, AD.C.5.1?  No TXK, I don’t care about the distinction since I always have to apply them together, why are they distinct products to the customer?  It’s like selling a cup and the water separately at a restaurant.

Regardless, my point about the AD/TXK C, Delta 3, 4 and 5, additional fixes, essential bundles, mandatory mess ups, fantastic fixes, or whatever else they call it: they change a lot.  Research what the latest, greatest version is and install it.  Now welcome to another new job task: you need to keep up with this in 12.2 (subscribe to Steven Chan’s blog, at least you’ll get an email when these are released, among other things).

I’ve rambled enough for today, I have plenty more to cover.  Up next time: more AD changes and your new techstack!

January 9, 2015

Online Patching

Filed under: Online Patching, R12.2 — kkempf @ 11:11 am

ADOP my friend (today)

So I took the plunge and did the main thing we chose R12.2 for, as opposed to R12.1.  I applied a few patches prior to a downtime/maintenance cycle this weekend in my Production system.  Kind of scary, but everything went OK.  Here’s what’s interesting:

online patching load

Guess when I ran my first adop phase=apply (…) ?   <spoiler: 11:25am>

It was a curiously intense bit of work for the database to apply this patch (19523116 for the record) in online mode.  Oracle never advertised that online patching did its work with 0 impact, but it was a bit of a surprise to see the database system CPU maxed out as a result of it.

I should add that I did this yesterday, and didn’t get any calls or complaints so this in no way affected Production that I ‘m aware of.  My point is only that you should be aware that online patching has the potential to impact your server in significant ways.  Maybe don’t run it during month end close.

January 7, 2015

R12 Take Aways and Warnings Part 1 (Overview)

Filed under: Oracle, R12.2 — kkempf @ 11:30 am


What could possibly go wrong?

I put my thoughts together, and figured I could cite some specifics for the benefit of anyone who cares to listen.  I’ll be honest, functionally, R12.2 behaves pretty similarly to 11i.  The most significant impact for us was in the Financials with subledger accounting, but our WIP, Sales, HR, Purchasing, Order Management, Inventory, and Quality mostly just worked out of the box.  Sure we had to tweak all our custom reports, but the end-user experience was largely unchanged.

Most of the problems encountered lie in the techstack, and really, that’s why you’re here reading this, I suppose.  So without further ado…

Documentation: Before you Start

Wow, it’s ever-changing under your feet.  In the real world, you pick a baseline and have to dance with that partner all the way through the upgrade process.  In our case, this was 6 months.  Many, many things changed during that span, and it’s not easy to stay on top of.  If you’re like me by the time you get through your first pass against 12.2.3 you’ll have a stacks and piles of documents all over your desk.  This post is by no means meant to be all-encompassing, but the following are sure bets:

Here’s your first stop for information for 12.2.3 (it stands to reason there’s an equivalent on for 12.2.4): Doc ID 1586214.1  If you look at the end of the document, there’s a change log.  During the relevant span of my upgrade (1-May-14 through 1-Dec-14) there were many changes, some really quite critical.  This is really important to note: you’re on the bleeding edge here, I don’t care what Oracle says.  This document is shifting under your feet in critical ways and you need to throw out your printed copy and reprint it every time it changes.

For your database and the new techstack, I followed Doc ID 1594274.1 Again, you’ll notice a massive change log.  This is partially due to the changes as a result of new releases of the startCD.  When I began working the project, I chose the most advanced version I could (47) and I see now there’s a 48 and 49.  I strongly urge you to use the latest version.  With each one you get newer pieces and fixes for your techstack, which reduces your upgrade time in the long run because you don’t have to manually apply so many patches.

Finally, subscribe to and follow the updates from Steven Chan’s blog.  There’s nothing else out there like it that I’m aware of, and there were many relevant, timely updates which helped me through the process of the upgrade.

Timing is Everything

It probably goes without saying, but choose whatever the latest version is you can.  In our case, we started on 12.2.3 in May, and sometime before December 12.2.4 was released.  Of course, 12.2.4 contains fixes and enhancements we’d like to have, but as Don Rumsfield said, “you go to war with the Army you have” so we stuck with 12.2.3.  I have to admit, it was tempting to consider 12.2.4 but it would have meant a project re-baseline, and nobody wanted that.

Education & Training

Go ahead and check out Oracle education, and see how many classes there are on Ebusiness Suite 12.2.  In May, there were 0.  The first run of an online patching virtual (online) class was right around July 4th, and I took it.  Good class, by the way.  My point is this: Oracle is light years behind on providing training on this release.  I don’t know what the hold up is, but you’re pretty much on your own.  Go to conferences, read blogs, attend webinars, do whatever you can, because at this time, Oracle Education isn’t going to help you much.


This was an unexpected surprise.  Perhaps I’d just gotten used to response times for 11i (which was basically on life support) but WOW!  Oracle really responds quickly to R12.2 issues.  It’s refreshing, and to that I say kudos to support.


If you’re running financials, you probably already know that there are significant changes to the tax engine in R12.  I’m not a functional guy, but I have done monthly Vertex updates, and now that I’ve gone through the upgrade I also had to do the installation of the latest compatible Vertex version.  And that’s not the half of it, there’s something called a Tax Regime which had to be recreated or adjusted, and all the while Oracle is pushing you to use EBTax which is their version of tax.  We skipped EBTax because it didn’t replace Vertex completely, and to my understanding still required us to source tax changes from elsewhere.  Regardless, know that you need to address tax, and from a functional standpoint there may not be a lot of knowledge about R12.2 tax.  In our case, we engaged Perficient to assist us and had a good experience with it.

Consulting Partner

Choose your consulting partner wisely, if you’re outsourcing some of the work.  We are a small shop, and essentially had no choice.  It’s frustrating to know that the consultants are learning the product on your dime.  In the end, as a DBA I strongly recommend you do your own heavy lifting, or you’re unlikely to like the final result, much less understand it.  I also recommend you approach the upgrade to 12.2 as a technical upgrade, and ensure your consulting partner’s strength lies in core database and applications DBA tasks.  If not, you might as well do it yourself.

Up next time: AD changes and your new techstack

January 6, 2015

Oracle R12.2 Post-Upgrade Ramblings

Filed under: R12.2 — kkempf @ 4:55 pm


Live on 12.2

Over Thanksgiving weekend, we upgraded from to 12.2.3.  It was a monumentally big project, at least for our organization, and I’m very happy it’s in the rear-view mirror.  There was only one “if this doesn’t get fixed we’re rolling back” moment, and for the most part the upgrade and transition has gone smoothly.  After being live for over a month, and surviving month end closes, year end closes, and multiple clones, I feel confident saying R12.2 behaves better than once properly tweaked.

What the Upgrade Looks Like

To be honest, the upgrade is a ton of work.  There’s 11i pre-patching and prep-work (some of which cannot be done prior to down time for go-live), followed by a rapid install of 12.2, application dba tasks, running good old adpatch against a gigantic merged driver, another rapid install pass on 12.2, enabling online patching, apply lots of techstack patches, applying 12.2.3 with adop (your new best friend/worst enemy), and enough post-install tasks to make any DBA happy.  In real terms, this meant cramming about 50 hours worth of wall time into 62 hours of a weekend window.  Not much sleep.

Next Post

While the task is still fresh in my mind, I’m going to express some of the (many) pain points R12.2 brought with it, and how we coped with them.  It’s a whole new ballgame.

October 23, 2014

Running a shell script via concurrent request

Filed under: 11i, Linux, Mobile Supply Chain, R12.2 — kkempf @ 8:55 am


The Problem

Mobile Supply Chain (MSCA) in 11i and R12 is a rather unreliable product as I discussed here.  It tends to cease working for no reason; I’ve blogged about this before so I won’t dwell on how ridiculous that is.  While I have Linux/OS cron jobs to bounce the service entirely once per day, and rotate the ports twice per day, it’s still not enough.  Invariably, the dispatcher will stop fielding connections at 2am on Sunday (2 hours after the bounce), when I get a phone call and I have to go VPN in and unscrew it.  It is in my own self-interests, then, that I set out to create a concurrent request which would cleanly kill/restart MSCA from inside the Ebusiness Suite, and the reason behind this post.  Incidentally, one added benefit of using a concurrent process is that I can leverage Oracle responsibilities and security, ensuring not just anybody can bounce the MSCA telnet server.

Nothing New Here

I suppose I’ve always known there was a host option when you defined a concurrent program, I just never knew if it worked.  I’m in a weird sort of down-time before our R12.2 go-live and find myself without much to work on for a few days, so I’m “playing” in R12.2.  There’s nothing revolutionary about this post, it’s just my goal to walk from beginning to end through the process of running a host script as a concurrent process, and provide some in depth/meaningful context as to why you’d like to do that.  In fact I’d like to acknowledge the Oracle Ebizsuite blog here which helped me understand how to go about setting this up, as well as Doc ID 1594853.1.

From the Apps to the OS

I’ll walk the setup starting within the apps.  This bit hasn’t changed any from 11i to R12.

Concurrent Program Executable

As sysadmin, define the Executable via Concurrent->Program->Executable

CP Executable

Incidentally, you need to define an application in the 3rd line; we used our custom schema.

Concurrent Program Definition

Now define the concurrent program via Concurrent->Program->Define

Incidentally, you need to define an application in the 3rd line; we used our custom schema.  The Executable in the 5th field is the one you defined in the prior step.  Note I have no parameters, but if you wanted to you could add them here.  We’ll go over how the shell sees these in a minute.

CP Define

Assign a Request Group

Go to Security->Responsibility->Request and figure out what group can run this concurrent request.  Or create a group, I suppose.

Sec Respons Request

Write Your Script

Things to know about the script

  • appsuser is the OS user who started MSCA out of $ADMIN_SCRIPTS_HOME via or the like
  • references: I want to know when someone runs this request, or when the dispatcher port isn’t free for some reason to restart the dispatcher (in essence, meaning the script failed)
  • System vs. User parameters: I didn’t see any way to make it cleaner than was done over here, so I pulled it in so you can see the parms
    • $0: The shell script being run
    • $1: apps/{your apps password in plain text!!!}  HINT: Don’t put this in the concurrent request, or the person who just ran this CR can see the apps password
    • $2: unsure; possibly OS PID?
    • $3: Apps userid who ran the job
    • $4: Concurrent Request ID
    • $5: First User parameter (in my case, there are none)
    • $6-$n: Additional User parameters
  • 11i vs. R12 parms: I had two versions because they moved where the port number of the individual telnet servers were between 11i and 12 so awk changed slightly to get the right spot
    •     #DTL=`ps -fu appsuser|grep telnet|grep -v "grep"|awk '{print $15}'`   # 11i
          DTL=`ps -fu appsuser|grep telnet|grep -v "grep"|awk '{print $17}'`   #  R12.2
  • Seems like it need the .prog extension to be called e.g. scriptname.prog
  • If you try to use this in 11i you will need to define $ADMIN_SCRIPTS_HOME variable via .bash_profile or something as it’s a new one in R12.  Or you can just hardcode the path to the script
  • I put the script in $CUSTOM_TOP/bin
  • Script walk through
    • It first echoes back your parameters
    • Next it issues the command stop to attempt to gracefully shut down the MSCA telnet server.  This will fail to stop the dispatcher.
    • It emails me that the concurrent request was run, so I can be aware of how often this is needed, and who is doing it
    • Sleep 30 seconds so the command can try to do it’s job.  Then figure out the PID for MWADIS and kill it
    • Next figure out the remaining telnet processes and kill them.  This doesn’t normally execute
    • Sleep 30 seconds and then issue start.  Tee the output of this command to a temp file and scan for the words “is not free to start the dispatcher”
      • tee /tmp/mwa_alert.txt
    • If you see that it’s not free to start the dispatcher, email me again, this means there’s a bigger problem which needs my attention


#   06/24/13 kmk

echo "Following are System Parameters"
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
echo "1st System Parameter    :"$p0
#echo "2nd System Parameter    :"$p1
echo "3rd System Parameter    :"$p2
echo "4th System Parameter    :"$p3
echo "5th System Parameter    :"$p4
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
echo "Following are User Parameters  "
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
#echo "1st User Parameter    :"$u1
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"

echo "Stopping MWA Server via "$p0
nohup $ADMIN_SCRIPTS_HOME/ stop $p1
echo $ADMIN_SCRIPTS_HOME/ stop APPS/password

mail -s "Concurrent Request "$p4" Submitted to Bounce MWA by "$p3 < /dev/null

sleep 30


for pids in `ps -fu appsuser|grep MWADIS|grep -v "grep"|awk '{print $2}'`
    echo "forcing kill on MWADIS PID ${pids}"
    kill -9 ${pids}

# Kill telnet
for pids in `ps -fu appsuser|grep telnet|grep -v "grep"|awk '{print $2}'`
    #DTL=`ps -fu appsuser|grep telnet|grep -v "grep"|awk '{print $15}'`   # 11i
    DTL=`ps -fu appsuser|grep telnet|grep -v "grep"|awk '{print $17}'`   #  R12.2
    echo "forcing kill on PID ${pids} for ${DTL}"
    kill -9 ${pids}

sleep 30

echo "Starting MWA Server via "$p0
echo $ADMIN_SCRIPTS_HOME/ start APPS/password
rm -rf /tmp/mwa_alert.txt
touch /tmp/mwa_alert.txt
nohup $ADMIN_SCRIPTS_HOME/ start $p1 |tee /tmp/mwa_alert.txt
# if the dispatcher isn't free, email me

MWAERROR=`cat /tmp/mwa_alert.txt | grep "is not free to start the dispatcher"`

if [ ! -z "$MWAERROR" ]
  echo $MWAERROR | mail -s "MWA Error"

Why Didn’t I Just Use Oracle’s Vanilla Scripts?

In short, because they don’t work.  They start MSCA fine, but in 12.2 (at least as of the AD delta 5) they don’t actually stop the processes correctly.  It stops the individual ports, but not the dispatcher.  The dispatcher is like some bad villain in an action movie that just refuses to die:

$ ps -ef|grep oratest
oratest  29516     1  0 Oct21 ?        00:01:26 /u02/apptest/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=29516 -Doracle.apps.mwa=/u02/apptest/fs2/EBSapps/appl/mwa/12.0.0 -Doracle.apps.jrad.mmd=/u02/apptest/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/html/jrad -Doracle.apps.inst=/u02/apptest/fs2/inst/apps/TEST_host -mx512m -ms128m oracle.apps.mwa.presentation.telnet.Listener 10260
oratest  29540     1  0 Oct21 ?        00:01:24 /u02/apptest/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=29540 -Doracle.apps.mwa=/u02/apptest/fs2/EBSapps/appl/mwa/12.0.0 -Doracle.apps.jrad.mmd=/u02/apptest/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/html/jrad -Doracle.apps.inst=/u02/apptest/fs2/inst/apps/TEST_host -mx512m -ms128m oracle.apps.mwa.presentation.telnet.Listener 10262
oratest  29567     1  0 Oct21 ?        00:01:25 /u02/apptest/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=29567 -Doracle.apps.mwa=/u02/apptest/fs2/EBSapps/appl/mwa/12.0.0 -Doracle.apps.jrad.mmd=/u02/apptest/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/html/jrad -Doracle.apps.inst=/u02/apptest/fs2/inst/apps/TEST_host -mx512m -ms128m oracle.apps.mwa.presentation.telnet.Listener 10264
oratest  29584     1  0 Oct21 ?        00:00:00 /bin/sh -f /u02/apptest/fs2/inst/apps/TEST_host/admin/scripts/ start_dispatcher
oratest  29589 29584  0 Oct21 ?        00:00:01 /u02/apptest/fs2/EBSapps/appl/mwa/12.0.0/bin/MWADIS
$ stop apps/apps

Stopping MWA Servers and the dispatcher ....
Stopping MWA Server on Port number: 10260
Stopping MWA Server on Port number: 10262
Stopping MWA Server on Port number: 10264
Stopping MWA Dispatcher on Port number: 10830
$ ps -ef|grep oratest
oratest  29584     1  0 Oct21 ?        00:00:00 /bin/sh -f /u02/apptest/fs2/inst/apps/TEST_host/admin/scripts/ start_dispatcher
oratest  29589 29584  0 Oct21 ?        00:00:01 /u02/apptest/fs2/EBSapps/appl/mwa/12.0.0/bin/MWADIS

$ stop_dispatcher apps/apps
MWA Telnet Server Release: [December 12th 2002]
Telnet dispatcher shut down successfully.
$ ps -ef|grep oratest
oratest  29584     1  0 Oct21 ?        00:00:00 /bin/sh -f /u02/apptest/fs2/inst/apps/TEST_host/admin/scripts/ start_dispatcher
oratest  29589 29584  0 Oct21 ?        00:00:01 /u02/apptest/fs2/EBSapps/appl/mwa/12.0.0/bin/MWADIS

Final Steps

Go to $CUSTOM_TOP/bin or wherever you have your shell script

Sym link fndcpesr to the name of the shell script minus the .prog extension:


Confession: I have no idea how you’re supposed to do this in R12 and what I could find on My Oracle Support only covered through R12.1 (where there is only one filesystem).  I don’t know how you’re supposed to handle the dual filesystems, and you can clearly see my sym link is hard coded to fs2.  There has to be a more elegant way to do this, just not sure what it is at the moment.

If I do an ls -ltr on my directory, here’s what it looks like:

ls ltr

Concurrent Request

Here’s the log (there is no output) from my concurrent request.

CR Log


I found this to be a fun diversion, but I like writing shell scripts as much as SQL, and I like figuring out ways to do things which are more “correct”.  All that said, shame on the Oracle MSCA team for delivering such a half-baked product.  The R12.2 version of MSCA is plagued by a host of problems, most of which I have active SR’s open for:

  • Constant issues with tabbing not working in the MSCA GUI
  • Really, your stop script doesn’t stop your process?  Even when I specify stop_dispatcher?
  • How is it that doesn’t start MSCA, but it used to in 11i, and R12 has been out for some time?  I could say the same for…
  • Major, quirky Java compatibility issues with the MSCA GUI
  • For the love of Pete, please don’t tell me that the tabbing issues are known to be resolved in JRE 1.7.23.  While your MSCA might work with 7.23, that’s not a real-world option as everyone else at Oracle is telling me to be on the latest version of Java and this PC also has to connect to Oracle forms.

October 17, 2014

Querying your 11i/R12 Context File from Oracle SQL*Plus

Filed under: 11i, R12.2 — kkempf @ 8:53 am



Peeling back the layers of the context file, precipitated by MSCA

We all know that the context file holds information about virtually everything on the applications tier, and how important it is.  As a result of our upgrade effort from 11i to R12.2, I started getting incessant calls wanting to know that current dispatcher port for MSCA. While this post will be specific to MSCA as the reason for having to figure out how to do this query, the principles here can be used to obtain any value from the context file via SQL*Plus, for any reason.


I should note a few things about the MSCA product for those (presumably the majority of you) who are unfamiliar with it.  The MSCA product is essentially a telnet front end, which runs on the application server on a specific port.  Users can either use telnet to connect to it, or the MSCA GUI client.  Either one requires a hostname and port.  MSCA actually runs on many ports, managed by a dispatcher to reduce the load on any one port.  So ideally, users connect to the dispatcher.  Thus one would think that when I created a new environment, it would be easy to just inform everyone of the port to connect to and the calls would end.  In my case, there’s two problems with that.  First, there’s a known R12.2 bug which switches the dispatcher port during an online patch session.  So if you’re running on filesystem1, it might be 10300, but if you’re on filesystem2, 10302.  Second, MSCA doesn’t like to stay running.  So it’s helpful for a user to confirm they’re trying to connect to the correct port before calling.

The good part

The bottom line is the query to accomplish this is as follows; the table fnd_oam_context_values stores entire context files as clobs in a field called text.

extractvalue( xmltype( text ),’//mwaDispatcherPort‘ )
status = ‘S’
and name = ‘SID_hostname
and last_synchronized =
max( last_synchronized )
status = ‘S’
and name = ‘SID_hostname
) ;

Obviously, when you do it, you’re going to change a few things:

  • mwaDispatcherPort is the tag from the $CONTEXT_FILE you’re looking for the value of.  Examples include oa_context_name, jinit_ver_dot, platform, or whatever you’re looking for.
  • SID_hostname is the database SID followed by an underscore and the applications tier hostname.

Why the subquery?  There are many context files stored in this field as clobs.  You want the most recent one (presumably, tweak as desired, based on last_synched) and you want one which has a value of S (synched? not 100% sure about this column, just figured it out through trial and error)

Incidentally, there is a table called fnd_env_context which holds some, but not all of the values contained in the context file.

End Result

We’re gunning for a centralized APEX report which can query the databases and return the relevant port numbers for users, as appropriate, depending on their apps responsibility.  So they can stop calling me so I can blog more.

October 16, 2014

R12.2 Apps Password Changing

Filed under: Linux, R12.2 — kkempf @ 10:02 am


Staring down the barrel of an R12.2 upgrade

So we’re getting really, really close to pulling the trigger on this release.  I’m tying up loose ends from an admin perspective, and one of them was confirming I could clone under 12.2.

R12.2 Cloning

In general, I’d say cloning is easier than 11i.  That said, I currently have an SR open because after completing the clone in accordance with 1383621.1 I can’t run a patch cycle because of an issue with appltop_id.  I’m really hoping I missed something simple and it will be resolved soon.  Other than that, my clone worked great, I could log in and things were running right.  Although the cloning document isn’t what I would call a “roadmap” to a clone, it’s enough that if you used to know how to clone you can figure it out.  My favorite part is for the poor people running R12.2 on Windows (hopefully, nobody) where in order to prepare the source system, you have to shut down production to clone.

Password Changing

There’s a lot of moving parts in the E-Business suite, and Oracle did us a favor by providing the FNDCPASS utility again in R12.2 to facilitate apps password changes.  Well really any schema changes.  I grabbed Doc 1674462.1 and I have to say it’s well written and accurate!  In the old days of 11i, you had to run autoconfig to propogate the apps password change to a human-readable text file under the Apache install (among other reasons, I’m sure).  No, really, it’s out there source an 11i environment and type this:

cat ($IAS_HOME)/Apache/modplsql/cfg/|grep -m1 password|sed -re ‘s/(^.+= )//’

Well now that bit is buried under Weblogic, so it takes longer in that you have to log in to the admin server and use the web-based GUI to make the JDBC connection change.

My Gift to you

Although I feel the document is accurate, I don’t want to have to go dig it out every time I do a clone (or rely on Metalink being up/accessible) and need to change my non-production system password to something different than production.  Besides, a working shell script means I don’t have to worry about standardizing my process.  In my cloning notes I simply say “change apps password with”.

So here’s my shell scripted version; I could have automated it more but I feel this script allows for you to walk through the steps without making it a complete “black box” in case it changes in the future.  I think the only caveat is that the directory you run it in needs to be writable.  You can chmod +x the script and just run it without arguments to get the “help” information.

# apps password change #

if [ $# -ne 6 ]
  echo "Usage:"            
  echo "        -appspass    password             Current apps Password"
  echo "        -syspass     password             Current system Password"
  echo "        -newpass     password             New apps, apps schema's, non-apps and non-apps schema's Password"
  echo "example: -appspass apps -syspass manager -newpass tayl0r"
  echo "General order of password changes IAW DOC ID 1674462.1:"
  echo "0) export fnd_users"
  echo "1) Stop everything with and ensure it's down"
  echo "2) Run this script"
  echo "3) Start only the admin server with"
  echo "4) Change the apps password in WLS Datasource" 
  echo "   a. Log in to WLS Administration Console"
  echo "   b. Click Lock & Edit in Change Center"
  echo "   c. In the Domain Structure tree, expand Services, then select Data Sources"
  echo "   d. On the Summary of JDBC Data Sources page, select EBSDataSource"
  echo "   e. On the Settings for EBSDataSource page, select the Connection Pool tab"
  echo "   f. Enter the new password in the Password field"
  echo "   g. Enter the new password in the Confirm Password field"
  echo "   h. Click Save"
  echo "   i. Click Activate Changes in Change Center"
  echo "5) Start everything with"
  echo "6) Check in the apps and WLS"
  echo "   a. Log in to WLS Administration Console"
  echo "   b. In the Domain Structure tree, expand Services, then select Data Sources"
  echo "   c. On the Summary of JDBC Data Sources page, select EBSDataSource"
  echo "   d. On the Settings for EBSDataSource page, select Monitoring > Testing"
  echo "   e. Select oacore_server1"
  echo "   f. Click Test DataSource"
  echo "   g. Look for the message Test of EBSDataSource on server oacore_server1 was successful."
  exit 1

for parm in `echo $*`
  if [ $1 = '-appspass' ]
  elif [ $1 = '-syspass' ]
  elif [ $1 = '-newpass' ]

  if [ $2 ]

echo "changing non apps passwords..."

echo "changing apps password..."
  echo "Remaining Steps:"
  echo "3) Start only the admin server with"
  echo "4) Change the apps password in WLS Datasource" 
  echo "   a. Log in to WLS Administration Console"
  echo "   b. Click Lock & Edit in Change Center"
  echo "   c. In the Domain Structure tree, expand Services, then select Data Sources"
  echo "   d. On the Summary of JDBC Data Sources page, select EBSDataSource"
  echo "   e. On the Settings for EBSDataSource page, select the Connection Pool tab"
  echo "   f. Enter the new password in the Password field"
  echo "   g. Enter the new password in the Confirm Password field"
  echo "   h. Click Save"
  echo "   i. Click Activate Changes in Change Center"
  echo "5) Start everything with"
  echo "6) Check in the apps and WLS"
  echo "   a. Log in to WLS Administration Console"
  echo "   b. In the Domain Structure tree, expand Services, then select Data Sources"
  echo "   c. On the Summary of JDBC Data Sources page, select EBSDataSource"
  echo "   d. On the Settings for EBSDataSource page, select Monitoring > Testing"
  echo "   e. Select oacore_server1"
  echo "   f. Click Test DataSource"
  echo "   g. Look for the message Test of EBSDataSource on server oacore_server1 was successful."

« Newer PostsOlder Posts »

Create a free website or blog at