11.5.10.2 technical upgrade to r12 by the numbers

12 in 12

I’d recently heard that this was the new sales line for release 12, as in “go to R12 in 2012”.  I know it’s inevitable, and I wanted to see what difference a year and a half made (since I’d last done an r12 technical upgrade) on how smooth the process went.

Compelling Reasons to upgrade?

Support costs will force even the most battle-wary 11i users to r12.  I don’t know that this fits the definition of a compelling business reason, but feels more like blackmail.

We have begun to see some proverbial cracks on the surface of our own installation.  The most glaring example is that under 32-bit front end architecture (not wishing to pursue load balancing, multiple front ends and the evils which come with that) we’re begun to hit the limits of memory as more users connect.  This has a “ceiling” set by Red Hat’s (supported) 32-bit memory limit of 16gb.   Going to an r12, Linux x86_64 front end would immediately free the front end from its memory constraints.

In the interest of clarity and full disclosure

This is what my 11i environment looks like:

• RH5 64-bit database (only) tier, running 11.1.0.7 with 32gb RAM
• RH5 32-bit applications tier, running forms (servlet), reports, concurrent managers and iAS (1.0.2.2.2) on techstack 6.0.8.27
• Using native JRE 6 update 10 instead of Jinitiator
• As to family packs, I’m basically on the 11.5.10.2 extended support level
• We have ADI, Discoverer 11g, and a 3rd party bolt on called Highjump pointed at the production system

The First Hurdle

Apparently, when software is delivered via media to your office (in my case, Oracle software), it costs significantly more for your annual license than when some executive decides that you will opt for E-Delivery.  I understand it, save a buck where you can.  The true problem is figuring out how to download about 45 DVDs from E-Delivery.   There’s no error correction or download resume with an http download.  It just fails, silently, and you don’t know your .zip file is bad until you try to unzip it.  It’s maddening.  It literally took me 3 weeks to download all the files, and I still had to take my notebook home and use my private ISP to get one or two of the DVDs which simply never worked on our (busy) work internet.  Once I finally had the install set staged on a network share, I was ready to start the upgrade in earnest.

Reference

The reference I used for this upgrade was Oracle E-Business Suite Upgrade Guide Release 11i to 12.1.1 Part No. E16342-01 (Dec 2009) and Oracle Applications Install Guide: Using Rapid Install Release 12.1 (12.1.1) Part No. E12842-02 (Mar 2009).   Really what I’m discussing in this entry begins in Chapter 3.  I ran TUMS, and looked at all the functional steps (most of which TUMS said to skip) but the heart of what I’m aiming to document here is the technical upgrade, not the functional.

Cleaning up the environment

The first step I took after shutting everything down on my 11i front end was to remove the ERP mount point.  I literally wiped it out.  This means things like CUSTOM.pll and any other tweaks (except our own custom schemas, which I did preserve) are gone.  It’s a vanilla desert ready for install.

Rapid Install  (12.1.1)

/startCD/Disk1/rapidwiz

The journey of a thousand bugs..

Obviously, I chose the upgrade path

Is there somebody at Oracle whose sole job it is to ensure they ask for my email address every time I run any product they make?

larry.ellison@oracle.com

Create Upgrade File System

Pick the port pool you were (hopefully!) already using

This is the first really interesting configuration question.  Basically, you’re telling it how to find your (existing) database

Connection descriptor to your database

All Default information here, I’d imagine, in most cases

This is interesting, internationalization settings.  Back in the old days, the database characterset didn’t default to UTF-8, it defaulted to WE8ISO8859P1.  To make a long story short, we migrated to WE8MSWIN1252 in order to pick up the Euro character when we opened an overseas facility.  I wish we were on UTF-8, but converting the characterset is a major endeavor which I’d rather avoid, thank you very much.  Put in what you’re using here, obviously.

Define your apps node

Define your database node (again!)

Confirm your nodes look right

System Checks

And we’re ready to go… disclaimer, it says UTF-8, I must have taken this screenshot then realized that was wrong.. I believe it should read what you entered under internationalization settings

Last chance to bow out

These blue bars race for a few hours

Post Install

We’re done, right?

Um no.  Now the real work begins.  For some crazy reason, autoconfig runs after the rapid install.  I have no idea why, it could never possibly work.  Just ignore the errors, and on the OS, go do a $SCRIPT_TOP/adstpall.sh apps/password.  There’s no way anything could be running right, the database is still “11i”.

Finishing up 12.1.1

• cp admin/adgrants.sql out of the 7461070 directory and stage it to $ORACLE_HOME/appsutil/admin on the RDBMS tier
• sqlplus / as sysdba
• @adgrants apps
• Now apply AD 12.1.1 upgrade driver.  Basically, this means apply patch 7461070 via adpatch, which worked without incident

The Big Cahuna

Now you will run adpatch to apply the 6678700 patch.  This patch isn’t downloaded; it’s delivered by rapid install:

adpatch $AU_TOP/patch/115/driver/u6678700.drv options=nocopyportion,nogenerateportion

Problems with the Big Cahuna

• Issue: 4 hours into the patch, all my workers failed:

FAILED: file csprecm.odf  on worker  1.
FAILED: file csppl.odf    on worker  2.
FAILED: file csmcsma.odf  on worker  3.
FAILED: file cugrunt.odf  on worker  4.

• Fix: I can’t easily explain why, but the fix was to shutdown and restart the database, then use adctrl to restart the workers

• Isuse: with 60k jobs remaining, I ran into this

FAILED: file asrmorgdef.sql on worker  1.

note 1263744.1 is a 100% hit, the fix is:

If you are not using Sales products you can ignore the error and continue with the upgrade.  If you are using the Sales products you can comment out these tables and continue.  Development will be removing these from the upgrade.

• • Fix:
    • vi /u02/appmis/apps/apps_st/appl/as/12.0.0/patch/115/sql/asrmorgdef.sql
    • — out references to Tables AS_INTERACTIONS_ALL and AS_NOTES_ALL (and the table counts)

• Issue: With 56k jobs remaining:

FAILED: file iecadmmn.ldt on worker  1.

• • Fix: Per Note 452082.1

SQL> select function_id from fnd_form_functions where function_name=’IEC_SETUP_SSS’;

FUNCTION_ID
———–
11777

SQL> select count(*) from fnd_form_functions_tl where function_id= 11777;

COUNT(*)
———-
0

SQL> delete from FND_FORM_FUNCTIONS where FUNCTION_ID =11777;

1 row deleted.

SQL> commit;

Commit complete.

• Issue: FAILED: file asospar.ldt  on worker
  • Fix: ID 452198.1
  • Get rid of “begin mo_global.init; end;” Profile option value
  • UPDATE fnd_profile_option_values
    SET profile_option_value = NULL
    WHERE profile_option_id = 3157
    AND profile_option_value is not NULL;

• Issue: With 38k jobs remaining

FAILED: file peaeiasd.sql on worker  1.
FAILED: file peaeirsd.sql on worker  2.
FAILED: file pereirsd.sql on worker  4.

ID 1072538.1

• Fix: @/scratch/stage/r12/r12.1.1/8845395/per/patch/115/sql/peasgasddrop.sql (part of patch, which you can’t easily start with the other huge patch running, run patch or possible sql twice)

• Issue: FAILED: file MSDODPCODE.sql on worker  3.
  • Fix: ID 977196.1exec dbms_aw.AW_ATTACH(‘ODPCODE’,true,true,’wait’)
• Issue: aptppupg1.sql updated for about 24 hours
  • Fix: I killed the 4 sql threads; after that it restarted all 4 and ran fine.
• Issue: apilnupg.sql hung at 100% cpu no disk for 12 hours

• Fix: killed sql, gathered stats on AP.AP_INVOICE_DISTRIBUTIONS_ALL and AP_TAX_ALLOC_AMMOUNT

ID 942694.1

The Index AP_INVOICE_DISTRIBUTIONS_N25 is missing.

Note:  apsin.odf version 120.49.12010000.7  should have changed the definition for creating the index so it was not deferred.

create index AP_INVOICE_DISTRIBUTIONS_N25 on AP_INVOICE_DISTRIBUTIONS_ALL(PARENT_REVERSAL_ID);

Possibly patch 10096115?  Bounced database and the issue went away.

• Issue: FAILED: file bomstrc.odf  on worker  2
  • Fix: The table is missing the index BOM_STRUCTURES_B_U1
    or index BOM_STRUCTURES_B_U1 exists on another table.
    Create it with the statement: 

    Start time for statement below is: Wed Mar 09 2011 09:19:13

    CREATE UNIQUE INDEX BOM.BOM_STRUCTURES_B_U1 ON BOM.BOM_STRUCTURES_B
    (OBJ_NAME, PK1_VALUE, PK2_VALUE, ALTERNATE_BOM_DESIGNATOR) LOGGING STORAGE
    (FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 10 INITRANS 11 MAXTRANS 255
    COMPUTE STATISTICS  TABLESPACE APPS_TS_TX_IDX (error)

    Statement executed.

    AD Worker error:
    The index cannot be created as the table has duplicate keys.

    Use the following SQL statement to identify the duplicate keys:

    SELECT OBJ_NAME, PK1_VALUE, PK2_VALUE, ALTERNATE_BOM_DESIGNATOR, count(*)
    FROM BOM.BOM_STRUCTURES_B
    GROUP BY OBJ_NAME, PK1_VALUE, PK2_VALUE, ALTERNATE_BOM_DESIGNATOR
    HAVING count(*)>1

    ORGANIZATION_ID ASSEMBLY_ITEM_ID ALTERNATE_BOM_DESIGNATOR      COUNT(*)
    ————— —————- —————————— ———-
    363       289065                     2

    (deleted one row from view bom_bills_of_materials but patch 4901614 and Lamb fixing the issue in PROD should stop this from ocurring again)

    AD Worker error:
    Unable to compare or correct tables or indexes or keys
    because of the error above

• Issue: FAILED: file apsin.odf    on worker  1
  • Fix: drop index AP_INVOICE_DISTRIBUTIONS_N25;since it was created in H above and didn’t fix anything, I dropped it.
• Issue: FAILED: file czhist.sql   on worker  3
  • Fix: BEGIN FND_STATS.GATHER_COLUMN_STATS(‘CZ’,’CZ_DEVL_PROJECTS’,’DELETED_FLAG’); END;*

  Issue: ERROR at line 1:ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old.  Please
  try upgrading it with dbms_stats.upgrade_stat_table
  ORA-06512: at “APPS.FND_STATS”, line 664
  ORA-06512: at “APPS.FND_STATS”, line 2497
  ORA-06512: at line 1

  DOC: 1281478.1

  • FIX: EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘APPLSYS’,’FND_STATTAB’);

12.1.1 is complete, on to 12.1.3

I hope the above issues weren’t awful to read; they simply don’t format well and I’ll try to clean them up better later.  The point is, you are going to hit unexpected errors all through the main driver.  The most disconcerting is that I had to bounce the database a few times, and that was the fix.  I can’t explain it, I don’t like it, but it fixed the problem.

12.1.3

First, apply patch 9239089, which is AD.B Delta 3 and a prerequisite to 12.1.3

• perl /u02/appmis/apps/apps_st/appl/ad/12.0.0/bin/admsi.pl
  –tells you basically to copy 9239089/admin/adgrants.sql to $OH/appsutil/admin and run it.  Then it tells you to run HRMS Legislative updates when you’re done.
• there were problems with my binaries also which caused all MSC relinks to fail

[ID 1128486.1]
I had an advanced version of binutils and no version of xorg installed

http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/

had the binaries, and that fixed it

Next, apply patch 9239090 via adpatch, which is the major 12.1.3 patch

• Issue: FAILED: file jeesmodupg.sql on worker  1.
  FAILED: file jeesmodupg.sql on worker  2.
  FAILED: file jeesmodupg.sql on worker  3.
  FAILED: file jeesmodupg.sql on worker  4.sqlplus -s APPS/***** @/u02/appmis/apps/apps_st/appl/je/12.0.0/patch/115/sql/jeesmodupg.sql &un_ar &batchsize 1 4
  Connected.
  DECLARE
  *
  ERROR at line 1:
  ORA-20004:
  ORA-06512: at line 77 

  ERROR at line 1:
  ORA-04098: trigger ‘APPS.JL_BR_AR_OCC_DOCS_DIS_N_OTHER’ is invalid and failed
  re-validation

  • Fix: Basically the trigger was invalid, and although everything it called seemed valid, it would not compile.  In the end, I bounced the database again and did an alter trigger APPS.JL_BR_AR_OCC_DOCS_DIS_N_OTHER compile;
• Issue: IGIPSIAP.rdf failed during recompile
  • Fix: Could not find anything obvious, skipped it as it’s irrelevant to me

Ready to Fire it Up?

At this point, 12.1.3 was in place, and there were really minimal errors installing it (compared to 12.1.1).  I had to do a few administrative tasks:

• Fire up an Xvnc server (so an X display is running, which allows forms/reports to run)
• on the applications tier,  perl $AD_TOP/bin/admakappsutil.pl, copy to  database tier $ORACLE_HOME and unzip -o it
• make changes to my context file to adjust workflow server settings
  • The context file is in /u02/appmis/inst/apps/MIS_zso-oramis-02/appl/admin/MIS_zso-oramis-02.xml
  • Adjust workflow server settings related to hostname, domain, and email addresses.  Also my Database was incorrectly tagged as UTF-8
    • <oa_workflow_server>
      <hostname oa_var=”s_javamailer_imaphost”>NoImapHost</hostname>
      <domain oa_var=”s_javamailer_imapdomainname”>NoImapDomain</domain>
      <username oa_var=”s_wf_admin_role”>SYSADMIN</username>
      <username oa_var=”s_javamailer_reply_to”>NoReplyTo</username>
      <username oa_var=”s_javamailer_imap_user”>NoImapUser</username>
      <username oa_var=”s_javamailer_outbound_user”>changeOnJavaMailerInstall</username>
    • <hostname oa_var=”s_smtphost”>smtp</hostname>
      <domain oa_var=”s_smtpdomainname”>company.com</domain>
    • <dbcset oa_var=”s_dbcset”>UTF8</dbcset>
• Customizations
  • Per 552010.1,  the file formservlet.ini used in 11i has been replaced by default.env in R12.
  • The $ORA_CONFIG_HOME/10.1.2/forms/server/default.env file did not contain an entry for the custom top.  Added
    # Begin customization
    HR_CUSTOM=/u02/appmis/miscust/hri/12.0.0
    AP_CUSTOM=/u02/appmis/miscust/ap/12.0.0
    AR_CUSTOM=/u02/appmis/miscust/ar/12.0.0
    AST_CUSTOM=/u02/appmis/miscust/ast/12.0.0
    BOM_CUSTOM=/u02/appmis/miscust/bom/12.0.0
    INV_CUSTOM=/u02/appmis/miscust/inv/12.0.0
    OE_CUSTOM=/u02/appmis/miscust/oe/12.0.0
    PO_CUSTOM=/u02/appmis/miscust/po/12.0.0
    WIP_CUSTOM=/u02/appmis/miscust/wip/12.0.0
    XXX_TOP=/u02/appmis/misappl/xxx/12.0.0
    # End customization
  • cd $APPL_TOP
    ln -s /u02/appmis/miscust/xxxx
  • echo “xxxx    $APPL_TOP” >> $APPL_TOP/admin/topfile.txt
  • adovars.env has no custom section, does not survive autoconfig.  Unsure if it has a new equivalent
• Discoverer 11g
  • Could not connect, followed ID 1243866.1
  • Confirm whether the same occurs with s_appserverid_authentication set to OFF:
    Make a backup copy of the $APPL_TOP/admin/<SID>_<hostname>.xml context file.
    Edit file and locate the following context variable: 

    s_appserverid_authentication

    Set the parameter equal to OFF
    Run Oracle Applications Autoconfig to instantiate the changes.

  • After doing this, it worked fine
• Installed online help as recommended
  • cd $AU_TOP/patch/115/driver
  • adpatch options=hotpatch, nocopyportion,nogenerateportion
• SSL: Still trying to figure this out!  Following Doc 376700.1
  • Turns out if you had wildcard certificates (*.domain.com) as opposed to exact server certificates (host1.domain.com) you’re screwed.
  • Oracle insists this is some kind of security issue, and won’t support importing my old certs.  Guess I’ll figure out another way and hack them in.
  • Sometimes being the customer sucks, as in when the company selling you the product is inflexible.  I’m the one willing to take the risk, not Oracle, so why are they dictating this?
• Recreate Custom Concurrent Manager Queues Turns out this is not required if you go through the steps I’ve outlines; being stored in the database they survive this.
• Run autoconfig and then adstrtal.sh and you should be good to go

Parting Thoughts

I’m really bummed that Chrome doesn’t work natively like it did in 11i while in R12.  It throws the worlds worst error: FRM-92120: Registry file http://myhost:8030/forms/oracle/forms/registry/Registry.dat is missing.  Firefox seems fine, and I’m able to hit it from my (uncertified!) Ubuntu machine, so that’s something, I guess.  Not much has appeared to change from the forms side.  Obviously the majority of the change will be functional, and we haven’t even delved into that.

I used the login screen to reset my password once the workflow mailer was running.  That was really cool.  I never got this to work on 11i.

Hold questions to the end, please!

I’d ask that you don’t hit me with specific problems about r12 you may be having.  I simply don’t know enough about it yet to speak intelligently.  While I’m happy to share information/opinions about my upgrade and environment, I recommend using My Oracle Support to answer specifics.  I have to admit, it was surprisingly helpful throughout the whole upgrade process.  Most of the bugs I hit were known, and those which weren’t seemed to be fixed by bouncing the database (ugh!).

Parting Shots

Default login page

Proof of Life