Kevin Kempf's Blog

June 5, 2017

The trouble with editions

Filed under: Online Patching, R12.2 — kkempf @ 10:27 am
(fake quote to grab your attention)

EBS 12.2 and Edition Based Redefinition

Unless you’ve been living under a rock for the past 5 years, you’re probably aware that EBS 12.2 online patching leverages Edition Based Redefinition (EBR).  In a nutshell, when you begin an online patch cycle, the adop phase=prepare command creates a new edition of the database which “contains” all current objects, but will also absorb database changes as you apply patches.  Simplified, it becomes your “on deck” database, ready to take effect (or step up to the plate, if you prefer the analogy) after you complete the adop phase=cutover.

Recognizing you have a problem

Left unattended, this system will continue to work for a long time without issue.  In my case, over 2.5 years, before I realized I had become an edition hoarder and needed help.  I realized that I had old editions hanging around, back to the day of our 11i to 12.2 go live.  Oracle very conveniently names the editions after the date/time they were created.  So you can see our first 12.2 edition was V_20141127_2225 (11/27/2014 at 10:25pm).

select edition_name from dba_editions order by edition_name;

35 rows selected.

Get Help

The fix is pretty simple, but I’ll add a real-world “gotcha” to Oracle’s documentation.  You can look  at EBS 12.2 Maintenance guide( Pg 3-27 to see the official verbiage, but here’s the gist of how to drop the old editions:

  • adop phase=prepare
  • adop phase=apply patches=(whatever patches you want to do, or none)
  • adop phase=actualize_all
  • adop phase=finalize finalize_mode=full
  • adop phase=cutover
  • adop phase=cleanup cleanup_mode=full

I’ll point out that Oracle’s document does not include the apply phase, however you can indeed apply patches during an edition cleanup.  Not only have we done it, but Oracle said it was fine to do in an SR.

The other thing the document doesn’t tell you is that this is not necessarily an impact-free operation.  We had a long maintenance window on a Sunday afternoon and decided to defer the phase=cleanup cleanup_mode=full until Monday morning when users were in the system.  Within 10 minutes, we had calls to the help desk because of massive object contention and locks, to the point where nobody could log into EBS.  I look at Enterprise Manager and it’s bright red, with hundreds of blocking locks in the system.  I don’t know if this is a result of the fact that we were dropping 30+ editions, or if it will happen just doing 1 edition.  Regardless, we ended up stopping the front end during the next maintenance cycle, then running the cleanup, and it was done in 45 mins or less.

SQL> select edition_name from dba_editions order by edition_name;


November 18, 2016

EBS R12.2 Security

Filed under: Oracle, R12.2, Security — kkempf @ 10:27 am


That light at the end of the tunnel might be a freight train

If you’re running 12.2 and considering 12.2.6 any time in the near future, you need to be aware of significant changes coming to the security requirements of EBS.  At the East Coast Oracle conference in Raleigh a few weeks back, I sat in a session by Elke Phelps (Oracle) entitled “Ready or Not: Applying Secure  Configuration to Oracle  E – Business Suite“.   Oracle EBS is getting serious, if not belligerent, about security.

Bottom line: If you don’t comply with their security recommendations, your users will not be able to log into EBS.  That’s right.  This is probably the most concerning thing about the changes: they’re not suggested.  Don’t believe me?  Check out Doc 2174164.1 section 3.1:

The Secure Configuration Console automates the security configuration process by consolidating the security configuration process onto one user interface and creating a single checkpoint entry into the system. It checks your system against 16 high-priority security configuration guidelines and makes recommendations to the system administrator to either fix or suppress the failure. Until the system administrator acknowledges these checks, users will be denied entry into the system.

Do I have you attention now?

Here’s the thing: most of the things it appears to be concerned with are either simple fixes or common sense.  While I haven’t seen it myself, I’ve been assured there is a page somewhere (in OAM?) where the apps DBA can go in and check boxes saying “I understand I’ve broken security rules, now please let me use my ERP”. Think: DEV environments.

One problem with all of this is it’s implemented in Oracle’s typical unwieldy and disparate manner.  Start by grinding your way through the 468 page Oracle Ebusiness Suite R12.2 security guide.  Let me just say it’s not a real page turner and I’ve had less reading in graduate level courses.


So we move on to 2069190.1 where you can find a nifty set of scripts called  This is run against your database, and comes back with somewhat actionable results.  I will admit, it found some things I wasn’t aware of, but it’s not perfect.  By the numbers:

  • Check: Security Profiles: Configuration ERRORS
    • This checks POVs to see if you’re doing anything wrong.  In my case, it didn’t like a site level POV called Framework Validation Level (value was set to none).  The problem with this assessment, is that this isn’t a POV I can find in any way.  As in, I can’t query it up in forms, and when I try to use to change it, it errors.  First check and I have to open an SR.  Great start!
  • Check: Security Profiles: Configuration WARNINGS
    • This appears to check and see who has the ability to run diagnostics in EBS, as well as who can attach files and personalize self service bits.  The problem is, it doesn’t bother to check and see if the EBS user with these privs is end dated.  So I wound up with a list of end-dated consultants from our R12.2 upgrade, none of which can log in, and a list of IT super users.
  • Check: Security Profiles: Configuration MISSING
    • No idea, I “passed” this one
  • Check: Application Users with Default Passwords
    • I’ll give Oracle credit on this one, it found some interesting things here.  I had to change my guest password, and disable autoinstall.  Hint: try to find that user.. when you get tired of that game, run FND_USER_PKG.DISABLEUSER(‘AUTOINSTALL’);
    • It also found some schemas which had the default password.  As in, all the new schemas presumably introduced by the 12.2 upgrade, so kudos to Oracle there.  Mine were ddr, dna, dpp, gmo, ibw, inl, ipm, jmp, mth, qpr and rrs.  Obviously I hadn’t run FNDCPASS against “ALL” since the upgrade, and so these were just hanging around.
  • Check: DB Users With Default Passwords
    • Again, a handful out there, but if the script would exclude “EXPIRED & LOCKED” that’d be great.  Most significant was applsyspub.  I swear it used to be you were told not to change this, but it’s easy enough to fix with FNDCPASS
  • Check: For excessive privs in APPLSYSPUB
    • Passed this one
  • Check: Oracle Applications User Passwords Migrated to Non-Reversible Hash Password
    • Yeah, I hadn’t done this.  It’s an easy fix see 457166.1
  • Check: Server Security Status
    • Passed this one
  • Check: SSL Status
    • You need to be using TLS 1.2.  See 2143101.1.  If you’re not familiar with the process, welcome to a lot of work.  And now you get to keep up with an external signing authority!  Yay work!
  • Check: Credit Card Encryption Status
    • Beats me, I failed this, but we don’t store this in the database, so perhaps that would be helpful to note.
  • Check Status of 12.2 Security Features
    • I had a recommendations here, because I allowed unrestricted JSP access,  unrestricted redirects and a setting it didn’t like for Cookie Domain.  This is probably legitimate, but it’s going to cause havoc with our Oracle APEX implementation and will likely take time to fix.  There’s a script called in $FND_TOP/patch/115/bin which may help you (“Oracle E-Business Suite Release 12.2.6 delivers a configuration script which can assist in configuring the products in your Allowed JSP lists. Products will be turned off in the Allowed JSP family configuration files based on whether recent transactions are detected for the product. Customers are strongly recommended to configure the Allowed JSPs using this script.”).  Check out the Oracle EBS Security Guide
  • Check: Users with Access to Sensitive Pages
    • Disabled by default.  I guess I pass?

Where this falls apart for me

They’re going to hold your EBS hostage until you address security gaps?  Unacceptable.  While it’s true, you’re not going to wake up one morning to users unable to log into production, it’s still a rather arrogant shift.  I happen to know my company paid a fortune for their software, but now I’m not so confident we own it.  This feels like some back room legal department at Oracle decided to get a record that “so and so” at XYZ company deliberately agreed to ignore our security recommendations, and we have it on the record because they checked boxes acknowledging they assume the risk.  Except the reciprocal is not true.  If I get hacked despite having complied with Oracle recommendations, I’m pretty sure Oracle isn’t going to write me a check to help me fix it.

Oracle is making assumptions about my environment and punishing me if I don’t comply.  What if I have no internet exposure?  What if this is a DEV environment where I don’t care or can’t afford to keep up with TLS 1.2?

Instead, a warning message (old school nag screen) at login to any user stating something like “Your corporate EBS may be at risk to security vulnerabilities, please contact your system administrator” would have been equally effective.  I’m not going to let that screen be up there for the world to see, and I will get phone calls.  But if something happens beyond my control, my users can still get into EBS.

October 11, 2016

Determining your EBS Code Level and Family Pack

Filed under: Online Patching, Oracle, R12.2 — kkempf @ 10:29 am

Quick and Dirty

There’s lots of posts out there telling you how to determine your EBS patch levels, code levels, etc.  But whenever I google them, I can’t find the one that actually tells me what I want in a simplified way.

Here’s what matters most often to me as the Apps DBA:

  abbreviation in( 'ad','txk','fnd','fwk','atg_pf','icx' )
order by


ABBREVIATION                   CODELEVEL                                                                                                                                            
------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------
ad                             C.7                                                                                                                                                   
atg_pf                         C.4                                                                                                                                                   
fnd                            C.4                                                                                                                                                   
fwk                            C.4                                                                                                                                                   
icx                            D.3                                                                                                                                                   
txk                            C.7                                                                                                                                                   

 6 rows selected

May 25, 2016

When ADOP breaks because of the magic of checkfile

Filed under: Online Patching, Oracle, R12.2 — kkempf @ 3:57 pm

As usual, it’s been some time since I’ve thrown anything out here.  What’s been keeping me busy has been largely to do with ADOP.  I have a real love/hate relationship with this new addition to the EBS family, and lately it’s been more hate.

Oracle defines nocheckfile as an unsupported option which isn’t to be used unless explicitly stated in the readme for a patch.  Intriguing, wonder what that’s all about, why would it be there at all?  Let’s start from the beginning, defining default adop behavior and what checkfile does.  In theory, during the adop apply phase, the default option “checkfile” says “go see if I already know I have a more advanced or equal version of this code before I apply it”.  If it thinks it already has ingested this particular bit of code, don’t process it in an effort to save time during the patch.  Couple of key things here to note.  First, it’s trying to save me time.  Second, is it ever wrong about whether it’s already ingested an advanced version of the code?

First, let’s talk about saving time.  That’s a good thing.  If I only have to ingest 30% of a large patch, even using adop with the EBS available, that’s a good thing.  Now the second part.  It’s only good if it’s accurate.  100% accurate.  Because if it skips code I actually need, it turns out you wind up with a big, stinking mess to sort through.  There’s a little foreshadowing there.

Take this scenario, which you’ve probably guessed by now happened to me.  Some bug surfaced as a result of some aborted patch cycles which made my EBS adop cycle think it could safely skip pieces of a patch which it had thought were applied.  Only they weren’t, that patch cycle was aborted.

Here’s your first hint that you may be experiencing this: you get a warning during the apply phase that the patch already exists in your system, but you know that not to be the case.  At this point, Oracle thinks the patch is in, but you know it’s not.  So you innocently go on and use forceapply, per the adop “HINT”.  Now you also think the patch is in your system.  But it’s not.  Because of the magic of checkfile, and the fact that the patch was previously aborted and adop thinks it’s in there, you’re in a world of hurt now.

It’s a really insidious state to find yourself in, and the fix is to reapply the patches, using nocheckfile and forceapply.  This basically says “apply the patch despite the fact that you already think it’s installed, and force every bit of code into the system regardless of whether you think you already have it”.

I can tell you this: never run an adop abort without a full cleanup, as well as an fsclone to reset your patch environment.  That should keep you in good shape.  Officially Oracle now advertises this in Oracle E-Business Suite Maintenance Guide, Release 12.2 (Part Number E22954-20).  I strongly recommend reading that from cover to cover.  Because early on in 12.2, this wasn’t how adop abort was explained even by Oracle University.

March 10, 2016

My EBS R12.2 adop patch failed, now what?

Filed under: Oracle, R12.2 — kkempf @ 3:55 pm

adop fails a lot, especially when you’re building a regression test environment.  Remember the old DOS days when you yanked out the floppy disk but still had a: on the screen and DOS said (a)bort, (r)etry, (f)ail?  And abort and fail always seemed like the same thing, but somehow they weren’t?  Same thing with adop, only it calls them abandon and restart.

adop defaults to abandon=no restart=yes if not specified, but to me, that’s still a bit unclear.  For example, what the heck would abandon=yes restart=no do?  Here’s what I came up with

(1) abandon and cleanup (I strongly urge you to do a full cleanup, or you may be asking for trouble)

adop phase=abort,cleanup cleanup_mode=full
adop phase=fs_clone

(2) fix the problem via whatever means (adctrl in the patch environment, drop the index the patch is trying to re-create, etc) and retry from where it failed

adop phase=apply patches=17020683 restart=yes [abandon=no is implied and not required]

(3) fix what’s wrong but retry from the beginning of the patch, or try a new patch (why you would do the latter, I have no idea)

adop phase=apply patches=17020683 abandon=yes [restart=yes is implied and not required]   (same patch)
adop phase=apply patches=17893964,18497540 abandon=yes          (new patch)


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.


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.

  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
  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)

  l_col_mapping varchar2(1000);
  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)

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.

   l_error_count pls_integer := 0;
   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))

7. Finish the redefinition process.  Also very fast.

       ('ARUP', 'FND_LOBS', 'FND_LOBS_NEW');

8. Confirm it worked

select securefile
from dba_lobs
where table_name = 'FND_LOBS'

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


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.

December 4, 2015

FND_LOBS and Securefiles (Part I)

Filed under: 11g, R12.2 — kkempf @ 11:05 am

FND_LOBS, how I hate thee

In our EBS environment, users have filled up FND_LOBS with attachments for the past 10 years.  From a functional standpoint, this is mostly quotes (.pdf’s) attached to purchase orders, and M$ Office documents attached to customer orders.  It grows without bounds, and in most cases there’s no expiration date included in the table or means to purge it with any reassurance that I’m not breaking something.  During a database 12c class last summer in Chicago, I learned some of the benefits of securefiles and it seemed like something to look into.  Then I was at a conference in Raleigh in September and it came up again.  Seeing a trend, I decided to start playing around with it (finally, yes, I know this is not new technology!).


Securefiles are like lobs 2.0 in Oracle.  It’s their attempt to better manage unstructured data within Oracle, and it’s a core database feature, having nothing directly to do with EBS.  The best document I found on it was here.  Here’s what caught my attention: it can leverage deduplication and advanced compression.  I didn’t see any easy way to see what % of my FND_LOBS content was duplicate, but let’s be honest, users don’t care if they upload the same thing 10 times, it’s not their problem.


As usual, check with your sales rep before you do this stuff in production.  Some of what I’m doing requires a license for Advanced Compression on your core database, and that’s not cheap.

Sizing up the Problem

A quick query shows that the fnd_lobs table (really, it’s associated lob segment) is consuming 150 GB.  That may not seem staggering, except multiply it by all EBS instances, add dataguard, and account for increased backup times and sizes, and it gets my attention.  Also take into account that this one object accounts for ~15% of my database size.  It’s been on my radar for a long time.

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

APPLSYS    FND_LOBS    FILE_DATA    SYS_LOB0000034032C00004$$    149115.25
APPLSYS    FND_LOBS_DOCUMENT    CONTENT    SYS_LOB0000034093C00008$$    0.125

A Quick and Dirty Test Case

I wanted to see what my space savings was going to be if I converted this table to securefiles.  Thus I added a little space to apps_ts_media (well, a lot!) and created a table called fnd_lobs_new to look just like fnd_lobs, except it used securefiles as it’s lob storage choice, and high compression.

  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 10 initrans 10 maxtrans 255 storage
 initial 128k buffer_pool default
 logging nocompress lob
 store as securefile
 deduplicate compress high tablespace apps_ts_media chunk 8192 storage( initial 128k buffer_pool default) pctversion 0 nocache logging
alter table applsys.fnd_lobs_new nologging;
insert into applsys.fnd_lobs_new select * from applsys.fnd_lobs;  (this takes a LONG time.  As in 12+ hours for me)
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
APPLSYS                FND_LOBS_NEW              FILE_DATA                      SYS_LOB0008283485C00004$$        46989.875
APPLSYS                FND_LOBS_DOCUMENT          BLOB_CONTENT                     SYS_LOB0000034093C00007$$               25
APPLSYS                FND_LOBS_DOCUMENT          CONTENT                         SYS_LOB0000034093C00008$$             .125

Bottom Line

I reduced fnd_lobs from 150GB to 47GB.  That’s a win!

Next Steps

My test isn’t practical.  I simply wanted to see if there was enough gain to continue with an online conversion.  There is.  Next post I hope to show you exactly how to convert FND_LOBS to securefiles without any downtime.

October 29, 2015

Fun with adop

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

Sphincter said what?

I was doing some core ETCC database patching against a 12.2 DEV instance today.  I was hoping to get to AD/TXK Delta 7 and see how that worked.  But after a big opatch session, when I restarted services, adop phase=prepare threw this error:

Validating system setup…
[ERROR]     Failed to execute SQL statement :
select AD_ZD_ADOP.GET_INVALID_NODES() from dual
[ERROR]     Error Message :
[ERROR]     Failed to execute SQL statement :
select AD_ZD_ADOP.GET_INVALID_NODES() from dual
[ERROR]     Error Message :
[UNEXPECTED]Nodes “-1” are listed in ADOP_VALID_NODES table but not in FND_NODES table.
[UNEXPECTED]To correct this, run AutoConfig on nodes “-1”
[UNEXPECTED]Error while checking if this is a multi node instance

MOS had a direct hit on this (1988581.1):  “Nodes “-1″ are listed in ADOP_VALID_NODES table but not in FND_NODES table.”, but it didn’t fix the issue.  In fact in accordance with this note I was fine.

The Fix

On the database tier, go to $ORACLE_HOME/appsutil/admin and re-run adgrants, then recompile the package:

sqlplus / as sysdba

@adgrants apps

alter package apps.AD_ZD_ADOP compile body;

After this, adop phase=prepare ran happily enough, and included an explicit autoconfig.  I can’t promise this will work for you, but since the error message is rather vague and unhelpful I thought I’d post this.

April 28, 2015

Security Patch Releases with EBS: Mission Impossible

Filed under: R12.2, Security — kkempf @ 12:33 pm

Regression Test Time

We’re about to enter our first R12.2 regression test.  High on my list is to get to Delta 6 on the AD/TXK side, and since the security patches just came out I figured I’d get up to date there.  For pretty good reason, we’re still running base, and it took me some time to get the ETCC (patch 17537119) happy that I’ve got all the required patches.

Per 1967243.1 I figured I’d start with the core database.  Now we have combo PSU’s for database, OJVM, and GI, in addition to SPU’s and stand-alone PSU’s for each component.  It’s gotten complicated since I looked last!  The patches are all opatch installed; ideally I’d get the OJVM, SPU and PSU applied to the database home.

OJVM (20406239)

This is a documented problem on MOS.  Apparently, it’s not really cumulative, because I have to be on Oct 2014 or better security set to apply this.  Next.

The following make actions have failed :

Re-link fails on target “jox_refresh_knlopt ioracle”.

Do you want to proceed? [y|n]
User Responded with: N


PSU (20299013)

This one was even worse!

There are no patches that can be applied now.

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
20488666, 20299013, 19791273, 19730032, 18260550, 17420796

Following patches are not required, as they are subset of the patches in Oracle Home or subset of the patches in the given list :
17811789, 19393542, 18828868, 18614015, 17892268, 17600719, 17468141, 16992075, 16929165

Following patches will be rolled back from Oracle Home on application of the patches in the given list :
20488666, 17811789, 19791273, 19730032, 19393542, 18260550, 18828868, 17420796, 18614015, 17892268, 17600719, 17468141, 16992075, 16929165

Conflicts/Supersets for each patch are:

Patch : 20299013

Bug Conflict with 20488666
Conflicting bugs are:
17912217 ETCC R12.2 requirement per 1594274.1

Bug Superset of 17811789
Super set bugs are:

Conflict with 19791273 ETCC R12.2 requirement per 1594274.1
Conflict details:

Bug Conflict with 19730032 ETCC R12.2 requirement per 1594274.1
Conflicting bugs are:
17174582,  18282562,  18244962,  17614134,  18674024,  17050888,  17478145,  18331850,  18964939,  17883081,  18436307

Bug Superset of 19393542
Super set bugs are:

Conflict with 18260550 ETCC R12.2 requirement per 1594274.1
Conflict details:

Bug Superset of 18828868
Super set bugs are:

Conflict with 17420796 ETCC R12.2 requirement per 1594274.1
Conflict details:

Bug Superset of 18614015
Super set bugs are:

Bug Superset of 17892268
Super set bugs are:

Bug Superset of 17600719
Super set bugs are:

Bug Superset of 17468141
Super set bugs are:

Bug Superset of 16992075
Super set bugs are:

Bug Superset of 16929165
Super set bugs are:

Security Patch Update (20299015)


Conflicts/Supersets for each patch are:

Patch : 18203837

Conflict with 18260550 ETCC R12.2 requirement per 1594274.1
Conflict details:

Patch : 19972566

Conflict with 18614015 ETCC R12.2 requirement per 1594274.1
Conflict details:

Patch : 20506715

Conflict with 19730032 ETCC R12.2 requirement per 1594274.1
Conflict details:

Patch : 20631274

Bug Superset of 17600719 ETCC R12.2 requirement per 1594274.1
Super set bugs are:

Following patches have conflicts: [   18260550   18203837   18614015   19972566   19730032   20506715 ]
Refer to My Oracle Support Note 1299688.1 for instructions on resolving patch conflicts.

Security or Compatibility: Why are these two at odds?

I’m trying to do the right thing, but Oracle is making it so hard to do that I’ve lost interest.  I don’t have a month to figure out what merged patches I need, what patch can go and what can stay. Why can’t there be a single note for EBS by version, with all patches for all components?  A database patch set which already deconflicted all the oddball ETCC requirements and delivered something which you could actually install out of the gate would make life much easier.


April 7, 2015

ADOP intelligent warnings, the ever-fluid recommended tech patches, and multiple versions of the truth

Filed under: R12.2 — kkempf @ 1:34 pm

So there I was..

Running adop phase=prepare in my production R12.2 environment, and I notice a warning on the screen and in the logs:

[WARNING]   The following required database bug fixes <19393542> are missing from this node, <database hostname>. Refer to My Oracle Support Knowledge Document 1594274.1 to identify the patch that delivers this bug fix.

This raises some questions:  How did adop know I needed a patch?  How serious is this?

Consolidated ≠ Updated

I went and pulled Consolidated List of Patches and Technology Bug Fixes (aka Doc ID 1594274.1) and it’s changed a bit since we went live.  Mind you, I expected to find patches in there we hadn’t applied as part of our go live; we locked our code last October before the upgrade.  What I didn’t expect was to find 14 patches on the database alone.  After some tedious cross checking of my inventory versus the document, I remembered (patch 17537119).

Patch 17537119 is really cool. This little gem compares an .xml file in the patch with your inventory, and tells you what you’re missing.  Sweet!

$ unzip
inflating: README.txt
inflating: checkDBpatch.cmd
inflating: txk_R1220_DB_base_bugs.xml
$ ./
|    Copyright (c) 2005, 2014 Oracle and/or its affiliates.     |
|                     All rights reserved.                      |
|               EBS Technology Codelevel Checker                |
Executing Technology Codelevel Checker version: 120.23
Enter ORACLE_HOME value : /u01/appprod/oracle/proddb/
Is this a RAC environment [y/n] : n
Enter ORACLE_SID value : PROD
Bugfix XML file version: 120.0.12020000.20
Proceeding with the checks…
Getting the database release …
Setting database release to
DB connectivity successful.
Table to store Technology Codelevel Checker results exists in the database.
STARTED Pre-req Patch Testing : Tue Apr  7 14:29:20 EDT 2015
Log file for this session : ./checkDBpatch_6400.log
Got the list of bug fixes to be applied and the ones to be rolled back.
Checking against the given ORACLE_HOME
Opatch is at the required version.
Found patch records in the inventory.
All the required one-offs are not present in the given ORACLE_HOME
List of missing bug fixes:
** Please refer to MOS Doc ID “1594274.1:Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes” to find the corresponding patch that delivers the bug fix. If an overlay patch is needed for any particular patch listed, the footnote for that patch also applies to the overlay patch.**
Stored Technology Codelevel Checker results in the database successfully.
Apply the missing bug fixes and run the script again to confirm.
FINISHED Pre-req Patch Testing : Tue Apr  7 14:29:24 EDT 2015

One lies, one tells the truth

I realized that the output of did not agree with 1594274.1.  In fact, it contained more patches than 1594274.1.  I would have assumed they were aligned, and this is really confusing as a customer: Which one is the definitive source?

In the end, I went with the, since it was a superset of the information contained in the Consolidated List of Patches and Technology Bug Fixes.  Perhaps a name change is in order?

Older Posts »

Blog at