Kevin Kempf's Blog

December 9, 2015

FND_LOBS and Securefiles (Part II)

Filed under: 11g, 12c, R12, R12.2 — kkempf @ 3:03 pm

An Actionable Plan

Last post, I talked about how I was playing with securefiles, fnd_lobs, deduplication and advanced compression.  It took a bit of tweaking, but I think I can safely share my success at reigning in FND_LOBS.  I’m going to outline the process of an online conversion from FND_LOBS defined as a basicfile (the default) to securefiles.

Jumping off a Bridge

Ever see Oracle do a presentation and the first slide is legalese called a Safe Harbor which basically says everything you are about to see and hear is subject to change and no business plans should be made on assumptions or potential future releases?  I feel like I should make a similar warning, let’s call it a bridge jumper.  Remember the old saying, “If all your friends jumped off a bridge, would you do it too?”.  Here’s what I’ll say: This works in DEV on EBS version R12.2, and I’ve done some minimal functional testing.  Don’t do it in PROD until you prove that it doesn’t impact something I forgot about or can’t foresee.  I recommend a regression pass.  Also don’t use products you’re not licensed for, such as advanced compression, even in non-production environments.

References

http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_smart.htm

http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html

Just the Highlights

You can convert your FND_LOBS table via online redefintion from basicfiles to securefiles.  In doing so, you will define the new lob table as securefile storage, and if you use deduplication and compression clauses, you will probably save a lot of space.  I did, dropping from 150GB to about 50GB.

select a.owner, a.table_name, a.column_name, a.segment_name , b.bytes/1024/1024
    from dba_lobs a, dba_segments b
    where a.segment_name = b.segment_name
    and a.owner = b.owner
    and a.table_name like 'FND_LOBS%'
   order by b.bytes/1024/1024 desc
SQL> /
OWNER                   TABLE_NAME              COLUMN_NAME                     SEGMENT_NAME            B.BYTES/1024/1024
------------------------------ ------------------------------ --------------------------------------------------
APPLSYS                FND_LOBS               FILE_DATA                      SYS_LOB0000034032C00004$$        149115.25

By the Numbers

I really wish WordPress would let me paste code in better, as I could make this a lot easier on the eyes.  Well you get what you get.

1. Increase the size of your APPS_TS_MEDIA tablespace until you can hold at least another table some percentage of the size of your FND_LOBs lob segment.  See my prior post to figure out how to determine this.

2. Create FND_LOBS_NEW (yeah, creative name right?) using the same syntax as your old table.  Note deduplicate, compress high clauses.

create
  table applsys.fnd_lobs_new
  (
    file_id          number
  ,file_name         varchar2( 256)
  ,file_content_type varchar2( 256) not null
  ,file_data blob
  ,upload_date     date
  ,expiration_date date
  ,program_name    varchar2( 32)
  ,program_tag     varchar2( 32)
  ,language        varchar2( 4) default( userenv( 'LANG') )
  ,oracle_charset  varchar2( 30) default( substr( userenv( 'LANGUAGE'),instr( userenv( 'LANGUAGE'),'.') + 1) )
  ,file_format     varchar2( 10) not null
  ,primary key( file_id ) validate
  )
  tablespace apps_ts_media pctfree 10initrans 10maxtrans 255storage
  (
    initial 128k buffer_pool default
  )
  logging nocompress lob
  (
    file_data
  )
  store as securefile
  (
    deduplicate compress high tablespace apps_ts_media chunk 8192storage( initial 128k buffer_pool default) pctversion 0nocache logging
  ) ;

3. Take your new table out of logging and enable parallel dml in an effort to save time:

  • alter table applsys.fnd_lobs_new nologging;
  • alter session force parallel dml parallel 4;
  • set timing on

4. Map the columns to the new table (this ran about 12 hours for me)

declare
  l_col_mapping varchar2(1000);
begin
  l_col_mapping :=
     'file_id file_id, '
   ||'file_name file_name, '
   ||'file_content_type file_content_type, '
   ||'file_data file_data, '
   ||'upload_date upload_date, '
   ||'expiration_date expiration_date, '
   ||'program_name program_name, '
   ||'program_tag program_tag, '
   ||'language language, '
   ||'oracle_charset oracle_charset, '
   ||'file_format file_format'
   ;
   dbms_redefinition.start_redef_table ('APPLSYS', 'FND_LOBS', 'FND_LOBS_NEW', l_col_mapping)
   ;
end;
/

5. Drop the existing index on APPLSYS.FND_LOBS_CTX (intermedia index). At this point, you may experience adverse performance on FND_LOBS for some users, but this index is of the insides of the LOBs as I understand it, so I doubt anyone will notice (your primary key remains available).  If you don’t do this, the next step fails spectacularly

drop index applsys.fnd_lobs_ctx;

6. Begin the redefinition process.  This ran really fast for me.

declare
   l_error_count pls_integer := 0;
begin
   dbms_redefinition.copy_table_dependents('APPLSYS', 'FND_LOBS', 'FND_LOBS_NEW', 1, TRUE, TRUE, TRUE, FALSE, l_error_count)
   ;
   dbms_output.put_line('Errors Occurred := '|| to_char(l_error_count))
   ;
end;
/

7. Finish the redefinition process.  Also very fast.

begin 
    dbms_redefinition.finish_redef_table
       ('ARUP', 'FND_LOBS', 'FND_LOBS_NEW');
end;
/

8. Confirm it worked

select securefile
from dba_lobs
where table_name = 'FND_LOBS'
/
SEC
---
YES 

9. Space check

select a.owner, a.table_name, a.column_name, a.segment_name , b.bytes/1024/1024
    from dba_lobs a, dba_segments b
    where a.segment_name = b.segment_name
    and a.owner = b.owner
    and a.table_name like 'FND_LOBS%'
   order by b.bytes/1024/1024 desc
OWNER                   TABLE_NAME              COLUMN_NAME             SEGMENT_NAME            B.BYTES/1024/1024
------------------------------ ------------------------------ ------------------------------ ------------------------------
APPLSYS                FND_LOBS               FILE_DATA              SYS_LOB0008283556C00004$$            46987

Elapsed: 00:00:11.64

10. Drop the temp table

drop table fnd_lobs_new;

11. Recreate the index APPLSYS.FND_LOBS_CTX from the EBS script.  This ran about 5 hours for me.

cd $FND_TOP/sql
sqlplus apps
SQL> @aflobbld.sql APPLSYS APPS

12. Gather statistics

EXEC FND_STATS.GATHER_TABLE_STATS(OWNNAME => ‘APPLSYS’,TABNAME => ‘FND_LOBS’);

Parting Shot

It occurs to me during cleanup, that if you create your “temporary” FND_LOBS table (in my case, FND_LOBS_NEW) in a new tablespace instead of apps_ts_media, once you’re done you could probably reclaim all the disk allocated for this one-time project. The alternative is that your apps_ts_media is now 3x (or more) what you need, as you eliminated your large, basicfiles FND_LOBS table, and now have a compressed and deduplicated version on securefiles which will presumably take a long, long time to fill up your apps_ts_media tablespace now that you’ve expanded it for the project. Because of high water marks, I was unable to shrink my tablespace in any significant way after I was done.

That’s It Folks

I tested this a few ways from the functional standpoint.  I ran a report which printed a LOB (Invoice) which was part of a requisition.  I opened an attachment in Quality.  I saved an attachment in Quality.  Everything checked out.  On to a full regression cycle!  Thanks for tuning in, let me know if you find this useful.

April 24, 2014

Simplifying an R12 Install

Filed under: R12 — kkempf @ 7:18 am

Pre-validation RPMs

I’d discussed installing an RPM to check your kernel settings and do other trivial OS/Linux tasks before installing an Oracle database here.  Now Oracle has officially released the same mechanism to validate an R12 (12.1, 12.2) environment; I caught wind of it on Steven Chan’s blog here.  This is a nice touch, since the requirements (especially on the application server side) include many x86 packages which are a pain to get.  You can read about it here in Doc ID 1330701.1.  Essentially, you ensure your yum settings allow you to be subscribed to Oracle add-on’s (I’m assuming Oracle Linux here, if you’re not on OL I guess you could point to the public repositories?).  

yum install oracle-ebs-server-R12-preinstall 


 

 

April 16, 2014

Password Reset Error

Filed under: 11i, Cloning, R12 — kkempf @ 12:31 pm

Just minding my business

I received an email saying the following error occurs in an 11i cloned instance.  Mind you, this is because we added users to the environment, and by rule Oracle requires them to change their password the first time they login so the password is different than what the system administrator assigned.

Internet Explorer error message:

jsp

 

I’ll spell out a few keywords to the search engines can index this one: AppsChangePassword.jsp java.lang.NoClassDefFoundError JSP Error

If you’re running Chrome, here’s your error message

 

 

 

 

That Was Supposed to be a Joke

From Chrome, I just got a white page, no error, no nothing.  I had to debug this from IE, which just pains me.  Oh yeah, I’m not supposed to use Chrome for Ebusiness suite, even though it works fine and I’ve been using it from a Linux desktop for 8 years now.  Incidentally, there’s a plug in for Chrome to make R12 work, it turns out it does work fine though I have some privacy concerns… you can find it here.

The Fix

First I tried to bounce apache, but that did nothing, so I opened an SR.  I think I found this fix on MOS before the analyst gave it to me, but it didn’t seem like a great fit based on the description in the doc so I didn’t do it until he told me to.  Hat tip to MOS and the ATG team, they identified a fix for an issue quickly and without asking me 10 irrelevant questions first.  Anyways, run this script

$JTF_TOP/admin/scripts/ojspCompile.pl –compile -s ‘AppsChangePassword.jsp’ –flush

April 1, 2011

11.5.10.2 technical upgrade to r12 by the numbers

Filed under: 11i, Oracle, R12 — kkempf @ 9:26 am

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

March 4, 2011

That can’t be a solution!

Filed under: 11i, R12 — kkempf @ 11:22 am

Ghetto IT Volume I

Dabbling in R12

I began an R12 upgrade in a sandbox environment a few weeks back, to see what had changed since the last time I’d done the technical upgrade.  I know R12 is inevitable, and with the release of 12.1.3 I wanted to see how painful the path was.  Turns out, it’s about the same as it always has been applying a major patch in Oracle E-Business Suite:

  1. Wait for a worker to fail.
  2. Search on the worker name in Metalink.  Open SR if you can’t find it to get a fix
  3. adctrl to restart the worker or use the not-so-secret option #8 to skip the worker
  4. pray the fix works.

Does Oracle operate in the real world?

The problem I hit this morning is described in Note 1072538.1: Basically, 3 PER workers failed because of a code problem:

When trying to upgrade from 11.5.10.2 to 12.1.1, “Patch 6678700 – 12.1.1 : Upgrade Patch” has failed again with INVALID_SEE_COMMENT_IN_SOURCE against the following modules:

FAILED: file pereirsd.sql on worker 4.
FAILED: file peaeirsd.sql on worker 6.
FAILED: file peaeiasd.sql on worker 7.

The fix works, but the real-world application is beyond stupid.  They want a patch applied, to fix the error, so you can continue your R12 upgrade patch.  Right.  Because as soon as I start another patch, it asks to truncate all the existing patch data tables (in process) which exist because my current patch isn’t complete and is waiting for me to fix failed workers.  In other words, I’m 20 hours into an upgrade, and now I have to apply a fix-it patch and start over?  No thanks.

Ghetto IT

It may be un-PC, but we use this term in our department to refer to a cheap, easy method for getting things done which nobody wants to talk about or admit works.  In my case, I pulled the patch, dug into it to find only one piece of sql was in it.  I ran the sql separately in another sqlplus session as apps:

  • @/scratch/stage/r12/r12.1.1/8845395/per/patch/115/sql/peasgasddrop.sql

Then I restarted the 3 failed workers with adctrl and, magically, they completed normally and the patch went on (6678700, or the R12 upgrade patch).

Blog at WordPress.com.