DBPedias

Your Database Knowledge Community

piowar-ebs

  1. Oracle Apps 11i: Back to the future

    A while ago, I posted a few references for installing an 11i Vision instance, noting that it was "something you'll hopefully only need to do once." The fun doesn't stop with installing the software, though. Once you're done with the easy part you're left with:

    1. A desupported version of the database
    2. A desupported version of the Developer 6i tools (yes, even more obsolete than 6i's inherent obsolescence)
    3. JInitiator, which is also, you guessed it, desupported in favor of the standard Sun JDK.

    What's the big deal? After all, 11i is in Extended Support mode, so why should it matter if an installed Vision instance has technical components that are a bit long in the tooth? Extended Support or not, there are probably one or two (hah) 11i instances still stubbornly kicking around out there. Presumably those instances have been patched up at least a little bit. If you're installing an 11i Vision instance for educational purposes, it might help to have something that (hopefully) more closely matches the modern state of affairs.

    Below are some of my notes from when I dragged my 11i Vision instance forward from 2005 to 2011. Well, sort of. It is still on 32-bit Linux (OEL 5.5 in this case) ;-) . But the latest Developer 6i patch set has been applied, the database is at 11.2.0.2, Jinitiator's been replaced by JDK 6, and the AD tools are a bit more up-to-date. If you decide to use these notes, please understand that I intend them to be a rough guide, to save you some research time. I can't commit to detailed support of your effort. This is a reference, not a cookbook.

    Reference Notes

    Not surprisingly, upgrading 11i components to this decade requires a lot of preparatory reading. Even if you're really familiar with the contents, it's worth revisiting these docs; some of them were changed as recently as July 2011. Here's a list that should cover everything for 32-bit OEL:

    Database

    Apps

    Forms, Java, and friends

    Patches

    While you're reading, if you want to get a jump on downloading the patches listed in these documents, I've listed them below. Please validate that the patch numbers still match the documents; the Developer 6i patch document in particular are subject to relatively frequent updates.

    Database

    Apps

    Order of operations

    Tying all those notes and patches together looks daunting, but it's not actually that bad. Here's a quick summary of what needs to happen. Make sure to reference the relevant notes for detail. Please recall that we're working on a Vision "playground" here; some of my suggestions for speeding things up may not be comfortable in production.

    1. Start downloading all those fun patches! Here's a script to get you started, though you might need to make adjustments if patch requirements have changed.
    2. Upgrade the database from version 9.2.0.6 to 9.2.0.8, per note 216550.1 to make the 11gR2 upgrade smoother. Doesn't take long, and while the scripts are running, you can work on the next five steps. Just do the database pieces (Section 2, steps 3-9 & 12 should be sufficient) for now.
    3. Set s_discostatus=disabled in the context file to be picked up by the next Autoconfig run; this is 2011 for heaven's sake.
    4. Install the JDK version 6 rpm as root.
    5. Install 11.2.0.2 software and examples, per Note 881505.1
    6. Create NLS data directory per note 881505.1
    7. Apply database patches (9776940,10149223, 10165223, and 10229719) to the 11gR2 ORACLE_HOME.
    8. Bring the newly-patched 9.2.0.8 database up and apply the AD.I.7 patch set. Use the nocompileb option; you have a utlrp step coming later.
    9. Adjust adrelinknew.sh script, per Note 316806.1.
    10. To avoid an error during the next round of patches, make an adjustment to the IGW.IGW_AWARD_BUDGET_S sequence. This can also be fixed with a patch, but why go through the trouble of getting a password-protected patch for this exercise? 
      SQL> alter sequence IGW.IGW_AWARD_BUDGET_S increment by 10000;
SQL> select  IGW.IGW_AWARD_BUDGET_S.nextval from dual;
SQL> alter sequence IGW.IGW_AWARD_BUDGET_S minvalue 10000;
    11. Apply the rest of the Apps patches (Merge them all. Yes, seriously). Again, use the nocompiledb option, and noautoconfig as well; the JDK step below will take care of that for you.
    12. Apply Forms 19 patchset and the small pile of related Forms and 8.0.6 ORACLE_HOME patches as documented in Doc ID 125767.1 -- don't forget the relink steps afterward
    13. Run txkrun.pl per section 5.1 of note 401561.1 (use jdk_top=/usr/java/latest)
    14. Regenerate jar files for jdk6 (use force option)
    15. Deploy the JRE to the apps tier per note 290807.1
    16. Perform the database upgrade per note 881505.1. There's a lot to do there; follow the note closely.
    17. Find something else to do with your day; recompiling 140K invalid objects might take a while.

    Next steps ("Exercise for the Reader")

    What, you thought you were done? Now that all of the technical bits are more-or-less up-to-date, you can turn your attention to security patches and applications updates. That may seem like a lot for a playground Vision instance, and maybe it is. It's also too much to cover in this blog post, but check the following notes if you're an overachiever:

  2. Fun with EBS APIs, part II: Users and responsbilities (FND_USER_PKG)

    In my previous post, I demo'ed some simple scripts to make changes to E-Business Suite profile options, using the API provided by the FND_PROFILE package. This post will look at the FND_USER_PKG package (no relation to the "ABS brake system"). While my examples only reference user creation and responsibility assignment, it's really worth reviewing the contents of the rest of the package; there's lots of useful stuff in there.

    First things first

    Disclaimer time! Are you surprised? I hope you're not surprised. Am I really copying this section from the previous post? Yup, I am!

    1. The example code presented in this series of blog posts uses APIs written by Oracle to manipulate data in the E-Business Suite database. This does NOT imply that the code in these posts is endorsed or supported by Oracle.
    2. The examples in this series of blog posts are presented as demonstration code only, and are neither guaranteed to work in your environment, nor in all cases. Since my environments are not the same as yours, I cannot offer support in the event that this code does not work for you. You are encouraged to make whatever modifications are necessary to make these examples work for you.
    3. The scripts used in these examples were developed and implemented in test and development environments, where change management rules are often more lax. Be sure to test thoroughly, and to consult your organization's change management procedures, before attempting to use this code in production.

    Short form: There's data-changing code in these posts. Oracle won't support you if you use the code; I can't support you either. Before running this code in production without testing and approval, you should probably prepare a resumé and, optionally, schedule an appointment with a psychiatrist.

    Example 1: Duplicating a user's responsibilities

    This is probably most relevant in test/dev/CRP environments, where it's not uncommon to be asked, "Can you please set up Fred to have the same responsibilities as Jane in the UAT environment?" or "Can you please set up 12 users in the Training environment, all with the same responsibilities?" I've written a script called ebs_dupresp that will duplicate all of a user's directly-assigned responsibilities to a target user, optionally creating the user record:

    /*
       ebs_dupresp.sql
       Contact: John Piwowar
       Purpose: Grant one user's responsbilities to another, optionally 
                creating the target user
       Notes: ...
    */
    

    SET serveroutput on size 1000000 format word_wrapped
    SET verify off feedback off 
    

    --Readability improvements.  First time for everything.
    DEFINE src=&1
    DEFINE targ=&2
    

    PROMPT Source user: &&src
    PROMPT Target user: &&targ
    PROMPT CTRL-C might be a good idea if these are incorrect/reversed.
    ACCEPT create_user_pref prompt 'Create &&targ if non-existent? (y/N) '
    

    DECLARE
       src_user APPLSYS.FND_USER.USER_NAME%TYPE := upper('&&src');
       targ_user APPLSYS.FND_USER.USER_NAME%TYPE := upper('&&targ');
       create_flag BOOLEAN := FALSE;
       targ_userid APPLSYS.FND_USER.USER_ID%TYPE := NULL;
    

       --Cursor to pull source user's responsbilities
       --Note: does not pull end-dated responsibilities, since they were 
       --      presumably end-dated for a reason.  Also retains forward-looking
       --      end dates
       CURSOR cGetResps (user_in APPLSYS.FND_USER.USER_NAME%TYPE)
       IS
          SELECT a.application_short_name app
               , r.responsibility_key key
               , t.responsibility_name respname
               , s.security_group_key secgrp
               , g.end_date
            FROM apps.fnd_user_resp_groups_direct g
               , applsys.fnd_responsibility r
               , applsys.fnd_user u
               , applsys.fnd_application a
               , applsys.fnd_security_groups s
               , applsys.fnd_responsibility_tl t
           WHERE g.responsibility_id = r.responsibility_id
             AND g.security_group_id = s.security_group_id
             AND g.responsibility_application_id = r.application_id
             AND g.responsibility_application_id=t.application_id
             AND g.responsibility_id=t.responsibility_id
             AND r.application_id = a.application_id
             AND u.user_name = user_in
             AND g.user_id = u.user_id
             AND (g.end_date > SYSDATE or g.end_date is null);
    BEGIN
       IF upper(substr('&&create_user_pref',1,1)) = 'Y' THEN
          create_flag := TRUE;
       END IF; --create_user_pref=Y
      
       --Create user if needed 
       IF create_flag AND NOT (apps.fnd_user_pkg.userExists(targ_user))
       THEN
    

          targ_userid := apps.fnd_user_pkg.createuserid (
                             x_user_name => targ_user
                           , x_owner => 'CUST'
                           , x_unencrypted_password => 'chang3m3'
                           , x_description => 'Autocreated by ebs_dupresps.sql'
                           );
          --Probably won't ever get to this point, but just to be safe...
          IF targ_userid IS NULL THEN
             dbms_output.put_line('Failed to create ' || targ_user || 
                                  ', cannot continue'
                                 );
             RETURN;
          ELSE 
             dbms_output.put_line('User ' || targ_user || ' created with id ' ||
                                  targ_userid);
          END IF; --targ_userid
       ELSIF create_flag AND (apps.fnd_user_pkg.userExists(targ_user))
       THEN
          dbms_output.put_line('User ' || targ_user || ' already exists.');
       ELSIF NOT create_flag AND NOT (apps.fnd_user_pkg.userExists(targ_user))
       THEN
          dbms_output.put_line('User ' || targ_user  || 
                               ' does not exist and create flag not specified.');
          RETURN;
       END IF; --create_flag
    

       --Add responsibilities
      
       FOR new_resp IN cGetResps(src_user)
       LOOP
          dbms_output.put_line('Adding responsibility ' || new_resp.respname);
          apps.fnd_user_pkg.AddResp( username => targ_user
                                   , resp_app => new_resp.app
                                   , resp_key => new_resp.key
                                   , security_group => new_resp.secgrp
                                   , description => 'Added by ebs_dupresps.sql'
                                   , start_date => SYSDATE
                                   , end_date => new_resp.end_date
                                   );  
       END LOOP; --new_resp
    END; --ebs_dupresps
    /
    view raw ebs_dupresp.sql This Gist brought to you by GitHub.

    Here's a two-part demo. For the first part, I've assumed that I just got the following email from my boss:

    John, Please create an EBS account in the test environment for the new DBA, Devnul Larson. Just give him the same responsibilities that you have, for now.

    APPS@R12VIS(11.1.0.7)>@ebs_dupresp johnp devnull
Source user: johnp
Target user: devnull
CTRL-C might be a good idea if these are incorrect/reversed.
Create devnull if non-existent? (y/N) y
User DEVNULL created with id 1013423
Adding responsibility System Administrator
Adding responsibility Oracle Diagnostics Tool
APPS@R12VIS(11.1.0.7)>@lresp johnp
Currently active responsibilities for user johnp

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Diagnostics		      NONE
FND		     Application Diagnostics		      NONE
JTF		     Oracle Diagnostics Tool		      NONE
SYSADMIN	     System Administrator		      NONE
APPS@R12VIS(11.1.0.7)>@lresp devnull
Currently active responsibilities for user devnull

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Diagnostics		      NONE
FND		     Application Diagnostics		      NONE
JTF		     Oracle Diagnostics Tool		      NONE
SYSADMIN	     System Administrator		      NONE

    Later that day, another email arrives:

    On second thought, just assign all of SYSADMIN's responsibilities to the new guy. BTW, why are you assigning all the new tickets to DEVNULL?

    APPS@R12VIS(11.1.0.7)>@ebs_dupresp sysadmin devnull
Source user: sysadmin
Target user: devnull
CTRL-C might be a good idea if these are incorrect/reversed.
Create devnull if non-existent? (y/N) y
User DEVNULL already exists.
Adding responsibility Application Developer
Adding responsibility System Administrator
Adding responsibility Application Developer Common Modules
Adding responsibility System Administration
Adding responsibility Preferences SSWA
Adding responsibility Workflow
(I'll spare you the full list)

    It's worth noting that this is a fairly primitive approach, particularly when it comes to the user creation piece (no LDAP integration, no association of user record with an HR person/party record, etc). The ebs_dupresp script also doesn't touch things like profile options, so it shouldn't be considered a "user clone" script. You could probably accomplish this work with FNDLOAD or a third-party tool like DataLoader, but I'd rather write SQL. ;-)

    Example 2: Adding a list of responsibilities to a user

    Another common need is adding responsibilities to multiple users. Maybe you want to add the Application Developer responsibility to a handful of new consultants, or grant the Oracle Diagnostics responsibility to some freshly trained business analysts and power users. With a little bit of setup, it's pretty easy to do bulk responsibility assignments in a way that's easily repeated across multiple instances. Here's an example script, ebs_bulkresp, that does the job:

    /*
       ebs_bulkresp.sql
       Contact: John Piwowar
       Purpose: Add 1+ responsibilities to 1+ users
       Requires: desired action (validate or apply) as script argument
                 Table xxrespload, of the format:
                 Name			   Type
                 ----------------------------- --------------------
                 USER_NAME			   VARCHAR2(100)
                 APP_SHORTNAME		   VARCHAR2(50)
                 RESP_NAME			   VARCHAR2(100)
                 END_DATE			   DATE
       Notes:  Actually, validation happens no matter what, but it never hurts to be explicit
    */
    

    SET serveroutput on size 1000000 format word_wrapped
    SET verify off feedback off
    col user_name for a20
    col app for a10
    col end_date for a15
    col resp_name for a35
    

    PROMPT Working with following records in table xxrespload:
    SELECT user_name
         , app_shortname app
         , resp_name
         , nvl(to_char(end_date,'DD-MON-YYYY'),'NONE') end_date
      FROM xxrespload
     ORDER BY user_name
         , resp_name
    /
    

    DECLARE
       action VARCHAR2(8) := upper('&1');
       TYPE RespPair IS RECORD ( app APPLSYS.FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE
                               , resp APPLSYS.FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME %TYPE
                               );
       TYPE UserList IS TABLE OF APPLSYS.FND_USER.USER_NAME%TYPE;
       TYPE RespList IS TABLE OF RespPair;
       BadUsers UserList;
       BadResps RespList;
       --N.B. Annoying security_group_id kludge. NULL=STANDARD, woohoo!
       CURSOR cGetBulkResps IS
          SELECT x.user_name
               , x.app_shortname
               , x.resp_name
               , r.responsibility_key resp_key
               , s.security_group_key secgrp 
               , x.end_date
            FROM xxrespload x
            JOIN applsys.fnd_application a on (a.application_short_name=x.app_shortname)
            JOIN applsys.fnd_responsibility_tl t on (    a.application_id=t.application_id
                                                     and x.resp_name=t.responsibility_name)
            JOIN applsys.fnd_responsibility r on  (    t.application_id=r.application_id
                                                   and t.responsibility_id=r.responsibility_id)
            JOIN applsys.fnd_security_groups s on (nvl(r.security_group_id,0)=s.security_group_id)
           WHERE x.user_name is not null;
    BEGIN
       
       dbms_output.put_line('Starting validation...');
    

       --Validate supplied users
    

       SELECT DISTINCT user_name
         BULK COLLECT INTO BadUsers
         FROM xxrespload x
        WHERE NOT EXISTS (SELECT user_name 
                            FROM fnd_user u
                           WHERE u.user_name = x.user_name
                             AND (u.end_date is null OR u.end_date > SYSDATE)
                         );
    

       --Validate app-resp pairs
    

       SELECT DISTINCT app_shortname
                     , resp_name
         BULK COLLECT INTO BadResps
         FROM xxrespload x
        WHERE NOT EXISTS ( SELECT a.application_short_name
                                , r.responsibility_name
                             FROM applsys.fnd_responsibility_tl r
                             JOIN applsys.fnd_application a using (application_id)
                            WHERE a.application_short_name = x.app_shortname
                              AND r.responsibility_name = x.resp_name
                         );
    

       IF BadUsers.COUNT > 0 THEN
          dbms_output.put_line('Found ' || BadUsers.COUNT || ' invalid user names.');
       
          FOR i IN BadUsers.FIRST .. BadUsers.LAST
          LOOP 
             dbms_output.put_line('User ' || nvl(BadUsers(i),'<NULL>') || 
                                  ' does not exist or is end-dated.'
                                 );
          END LOOP; --BadUsers;
       ELSE
          dbms_output.put_line('All users valid.');
       END IF; --BadUsers.COUNT
    

       IF BadResps.COUNT > 0 THEN
          dbms_output.put_line('Found ' || BadResps.COUNT || ' invalid app-responsibility pairs.');
    

          FOR i IN BadResps.FIRST .. BadResps.LAST
          LOOP
             dbms_output.put_line('Responsibility ' || nvl(BadResps(i).resp,'<NULL>') || 
                                  ' invalid for app ' ||
                                  nvl(BadResps(i).app,'<NULL>') ||
                                  ', or vice versa.'
                                 ); 
          END LOOP; --BadResps
       ELSE
          dbms_output.put_line('All app-responsibility pairs valid');
       END IF; --BadResps.COUNT
    

       --Okay, now let's actually do stuff
       IF action = 'APPLY' THEN
          dbms_output.put_line('Adding responsibilities...');
          FOR new_resp IN cGetBulkResps
          LOOP
             dbms_output.put_line('User: ' || new_resp.user_name ||
                                  ' App: ' || new_resp.app_shortname ||
                                  ' Resp: ' || new_resp.resp_name
                                 );
    

             apps.fnd_user_pkg.AddResp( username => new_resp.user_name
                                      , resp_app => new_resp.app_shortname
                                      , resp_key => new_resp.resp_key
                                      , security_group => new_resp.secgrp
                                      , description => 'Added by ebs_bulkresps.sql'
                                      , start_date => SYSDATE
                                      , end_date => new_resp.end_date
                                      );
          END LOOP; --cGetBulkResps
       ELSE 
          dbms_output.put_line('Invoke script with APPLY to add responsibilities');
       END IF; --action='APPLY';
    END; --ebs_bulkresp
    /
    view raw ebs_bulkresp.sql This Gist brought to you by GitHub.

    As a demo, here's another theoretical work request:

    Please assign Application Developer and Oracle Diagnostics responsibilities to users SBERG, YWATSON, and WPARKER. SBERG will also need Functional Administrator. WPARKER will be rolling off the project at the end of the year, so please end-date his responsibilities accordingly. These changes are approved for both development environments and all three CRP instances.

    After loading the necessary information into a "staging table" (actually a simply-formatted external table), away we go:

    APPS@R12VIS(11.1.0.7)>@ebs_bulkresp validate
Working with following records in table xxrespload:

USER_NAME	     APP	RESP_NAME			    END_DATE
-------------------- ---------- ----------------------------------- ---------------
SBERG		     FND	Application Developer		    NONE
SBERG		     FND	Functional Administrator	    NONE
SBERG		     JTF	Oracle Diagnostics Tool 	    NONE
WPARKER 	     FND	Application Developer		    31-DEC-2011
WPARKER 	     JTF	Oracle Diagnostics Tool 	    31-DEC-2011
YWATSON 	     FND	Application Developer		    NONE
YWATSON 	     JTF	Oracle Diagnostics Tool 	    NONE
Starting validation...
All users valid.
All app-responsibility pairs valid
Invoke script with APPLY to add responsibilities
APPS@R12VIS(11.1.0.7)>@ebs_bulkresp apply
Working with following records in table xxrespload:

USER_NAME	     APP	RESP_NAME			    END_DATE
-------------------- ---------- ----------------------------------- ---------------
SBERG		     FND	Application Developer		    NONE
SBERG		     FND	Functional Administrator	    NONE
SBERG		     JTF	Oracle Diagnostics Tool 	    NONE
WPARKER 	     FND	Application Developer		    31-DEC-2011
WPARKER 	     JTF	Oracle Diagnostics Tool 	    31-DEC-2011
YWATSON 	     FND	Application Developer		    NONE
YWATSON 	     JTF	Oracle Diagnostics Tool 	    NONE
Starting validation...
All users valid.
All app-responsibility pairs valid
Adding responsibilities...
User: YWATSON App: FND Resp: Application Developer
User: WPARKER App: FND Resp: Application Developer
User: SBERG App: FND Resp: Application Developer
User: SBERG App: FND Resp: Functional Administrator
User: YWATSON App: JTF Resp: Oracle Diagnostics Tool
User: WPARKER App: JTF Resp: Oracle Diagnostics Tool
User: SBERG App: JTF Resp: Oracle Diagnostics Tool
APPS@R12VIS(11.1.0.7)>@lresp ywatson
Currently active responsibilities for user ywatson

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      NONE
FND		     Application Diagnostics		      NONE
PSB		     Budget User - Accounting Dept, Progress  NONE
SQLGL		     General Ledger Super User, Progress S&L  NONE
ICX		     Internet Procurement, Progress S&L       NONE
JTF		     Oracle Diagnostics Tool		      NONE
SQLAP		     Payables Manager, Progress S&L	      NONE
ICX		     Preferences SSWA			      NONE
FND		     Workflow User, Progress S&L	      NONE
APPS@R12VIS(11.1.0.7)>@lresp sberg
Currently active responsibilities for user sberg

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      NONE
FND		     Application Diagnostics		      NONE
FND		     Functional Administrator		      NONE
IBE		     IBE_CUSTOMER			      NONE
JTF		     Oracle Diagnostics Tool		      NONE
ICX		     Preferences SSWA			      NONE
APPS@R12VIS(11.1.0.7)>@lresp wparker
Currently active responsibilities for user wparker

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      31-DEC-2011
FND		     Application Diagnostics		      31-DEC-2011
JTF		     Oracle Diagnostics Tool		      31-DEC-2011
ICX		     Preferences SSWA			      NONE
WMS		     Whse Mgmt Mobile User, Vision Operations NONE
		      (USA)

    It's rare that you'll get a request that also includes the name of the application to which a responsibility belongs. If you don't keep a mental inventory of valid pairs of application and responsibility names (or maybe that's just me?), here's a quick way to look up that sort of info:

    /*
      iresp.sql
      Contact: John Piwowar
      Purpose: List useful identifier for EBS responsibilities
      Notes: Provide fragment of responsbility name
    */
    

    col responsibility_name for a50
    col app_name for a10
    col app_id for 999999
    col resp_id for 999999
    

    set pagesize 9999 verify off
    

    select application_id app_id
         , a.application_short_name app_name
         , r.responsibility_id resp_id
         , r.responsibility_name
      from applsys.fnd_responsibility_tl r
      join applsys.fnd_application a using (application_id)
     where upper(r.responsibility_name) like upper('%&1%')
     order by responsibility_name
    /
    view raw iresp.sql This Gist brought to you by GitHub.

  3. Fun with EBS APIs, part I: Profile options (FND_PROFILE)

    The E-Business Suite APIs are not just the realm of developers and data migration specialists. There are a number of APIs, particularly in the FND module, that can can make life a lot easier for Apps DBAs. In my next two (maybe three, if I'm feeling ambitious) posts, I'll provide some examples of how to use PL/SQL to perform tasks that normally require slogging around in the EBS Forms interface.

    First things first

    Disclaimer time! Are you surprised? I hope you're not surprised.

    1. The example code presented in this series of blog posts uses APIs written by Oracle to manipulate data in the E-Business Suite database. This does NOT imply that the code in these posts is endorsed or supported by Oracle.
    2. The examples in this series of blog posts are presented as demonstration code only, and are neither guaranteed to work in your environment, nor in all cases. Since my environments are not the same as yours, I cannot offer support in the event that this code does not work for you. You are encouraged to make whatever modifications are necessary to make these examples work for you.
    3. The scripts used in these examples were developed and implemented in test and development environments, where change management rules are often more lax. Be sure to test thoroughly, and to consult your organization's change management procedures, before attempting to use this code in production.

    Short form: There's data-changing code in these posts. Oracle won't support you if you use the code; I can't support you either. Before running this code in production without testing and approval, you should probably prepare a resumé and, optionally, schedule an appointment with a psychiatrist.

    Okay, moving on…

    Now that I have all that out of the way, here are two basic examples demonstrating the use of FND_PROFILE. As with most PL/SQL code in the E-Business Suite database, FND_PROFILE is owned by the APPS schema. Browse through it, there's interesting stuff in there. Please note that the set_ebs_profile function referenced in these examples is my hackwork (defined in ebs_def_setprof.sql), not part of the Oracle-supplied API.

    Example 1: Setting profile options after cloning

    Autoconfig takes care of setting a lot of site-level profile options after cloning, but there may be a few instances when you need to change settings manually before releasing the instance to users. The following script is a template that I use, adding calls to set_ebs_profile as needed.

    /* 
       ebs_postclone_prof.sql
       Contact/blame: John Piwowar
       Purpose: Set Site-level profile options after an EBS clone
       Requires: ebs_def_setprof.sql
       Notes: * Uses internal name for profile option, rather than user-readable 
                value (e.g. "SITENAME", not "Site Name"). Consult 
                fnd_profile_options_tl for appropriate values.
              * Site name and color scheme are the obvious ones; add others as 
                needed
    */
    

    ACCEPT new_SID PROMPT "New SID (e.g. MYCLONE,NEWVIS, etc): "
    ACCEPT new_color PROMPT "New Color Scheme (TEAL, RED, KHAKI, OLIVE, PURPLE, TITANIUM): "
    ACCEPT env_type PROMPT "Instance type (e.g. Test, Dev, Sandbox): "
    ACCEPT data_src PROMPT "Data source note (e.g. 'Prod clone, data as of DD-MON-YYYY'): "
    

    set serveroutput on size 1000000 format word_wrapped;
    set verify off feedback off 
     
    spool ebs_postclone_prof_&&new_SID..log
    

    DECLARE
    @ebs_def_setprof
    BEGIN
    set_ebs_profile('FND_COLOR_SCHEME','&&new_color', TRUE);
    set_ebs_profile('SITENAME','&&env_type (&&new_SID.) - &&data_src', TRUE);
    END;
    /
    view raw ebs_postclone_prof.sql This Gist brought to you by GitHub.

    Example run: 
    APPS@R12VIS(11.1.0.7)>@ebs_postclone_prof
New SID (e.g. MYCLONE,NEWVIS, etc): JPR12VIS
New Color Scheme (TEAL, RED, KHAKI, OLIVE, PURPLE, TITANIUM): PURPLE
Instance type (e.g. Test, Dev, Sandbox): Visionary
Data source note (e.g. 'Prod clone, data as of DD-MON-YYYY'): Timeless data
Previous value of FND_COLOR_SCHEME: KHAKI
SUCCESS: Set FND_COLOR_SCHEME to PURPLE
Previous value of SITENAME: Vision Applications
SUCCESS: Set SITENAME to Visionary (JPR12VIS) - Timeless data

    Example 2: Setting multiple profile options for a user

    In a development environment, you may be asked to set debug profile options for particular modules. It can be handy to have a script that does this for a particular user, ensure that no one "accidentally" sets debug options at the site level (oh, I know, it's rare, but still…). It's ridiculously easy to put together a quick script to take care of this:

    /*
       om_debug.sql
       Contact/blame: John Piwowar
       Purpose: Set Order Management debug values for a given user
       Usage: @om_debug <username> <debug level>
       Requires: ebs_def_setprof.sql
       Notes: I'm sure I'll think of something...
    */
    

    SET serveroutput on size 1000000 format word_wrapped
    SET verify off feedback off 
    

    DECLARE
       outdir APPLSYS.FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
       uname APPLSYS.FND_USER.USER_NAME%TYPE := '&1';
       dlvl APPLSYS.FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE := '&2';
       @ebs_def_setprof
    BEGIN
       --find a writable directory the lazy way, same way FND_FILE does it. ;-)
       SELECT substr(value,1,instr(value,',')-1)
         INTO outdir
         FROM v$parameter
        WHERE name = 'utl_file_dir';
    

       dbms_output.put_line('Setting OM debug for ' || uname);
       set_ebs_profile('OE_DEBUG_LOG_DIRECTORY', outdir, TRUE);
       set_ebs_profile('ONT_DEBUG_LEVEL', dlvl, TRUE, uname);
    END;
    /
    view raw om_debug.sql This Gist brought to you by GitHub.

    Example run:

    APPS@R12VIS(11.1.0.7)>@om_debug jpiwowar 5
Setting OM debug for jpiwowar
Previous value of OE_DEBUG_LOG_DIRECTORY: /usr/tmp
Skipping OE_DEBUG_LOG_DIRECTORY, no change
Target user: jpiwowar
Previous value of ONT_DEBUG_LEVEL: 0
SUCCESS: Set ONT_DEBUG_LEVEL to 5

    The interesting bits

    The code that defines the procedure set_ebs_profile can be found below. I've purposely written the script to be plugged into anonymous PL/SQL blocks instead of creating a procedure, because deployment of custom code is not always permitted.

    /* 
       ebs_def_setprof.sql
       Contact/blame: John Piwowar
       Purpose: Braindead wrapper for setting site and user-level profile options 
                with fnd_profile.save. 
       Notes: * Call as part of the declare section of an anon PL/SQL block.
                That's my sad attempt at reusability without creating stored procs
              * Assumes a great many things, including that you're running as 
                a user with exec privileges on FND_PROFILE, and that you don't 
                need fancy error reporting if things go wrong. ;-)
              * Only handles site/user-level settings, per protocol El-Ay-Zed-Why
              * Still somewhat primitive, could probably use more of API for 
                success/failure messages, etc.
    */
    

    PROCEDURE set_ebs_profile (
              prof_option APPLSYS.FND_PROFILE_OPTIONS.PROFILE_OPTION_NAME%TYPE
            , prof_value  APPLSYS.FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE 
            , override BOOLEAN DEFAULT FALSE -- controls behavior when already set
            , uname FND_USER.USER_NAME%TYPE DEFAULT NULL -- if no username passed, set at site level
            )
    IS
       uid FND_USER.USER_ID%TYPE := NULL;
       prof_level VARCHAR2(8) := 'SITE'; --Current longest val is 'SERVRESP'
       set_result BOOLEAN := FALSE;
       prev_val_defined BOOLEAN := TRUE; --assume yes until we learn otherwise
       prev_val FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
    BEGIN
       IF uname IS NOT NULL THEN
          dbms_output.put_line('Target user: ' || uname);
          prof_level := 'USER';
          BEGIN --validate username
             SELECT user_id
               INTO uid
               FROM applsys.fnd_user
              WHERE user_name = upper(uname); --do as I think, not as I type
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
               dbms_output.put_line('ERROR: No userid found for ' || uname || '.');
               RETURN; --Whoops. Here's your bucket, bail away.
          END; --validate username
       END IF;
    

       --check for previously-set value
     
       fnd_profile.get_specific( name_z => prof_option
                               , user_id_z => uid
                               , val_z => prev_val
                               , defined_z => prev_val_defined
                               ); 
      IF NOT prev_val_defined  THEN 
          dbms_output.put_line(prof_option || ' not previously defined');
       END IF; --NOT prev_val
    

       IF prev_val_defined THEN
          dbms_output.put_line('Previous value of ' || prof_option || ': ' || 
                                prev_val
                              );
       END IF; --prev_val_defined
    

       IF (NOT override) AND prev_val_defined THEN --do nothing
          dbms_output.put_line('Skipping ' || prof_option || ', OVERRIDE=FALSE');
       ELSIF override AND (prev_val = prof_value) THEN --do nothing
          dbms_output.put_line ('Skipping ' || prof_option || ', no change');
       ELSE --try to set new profile
          set_result := fnd_profile.save( x_name => prof_option
                                        , x_value => prof_value
                                        , x_level_name => prof_level
                                        , x_level_value => uid
                                        );
          IF set_result THEN
             dbms_output.put_line( 'SUCCESS: Set ' || prof_option || ' to ' || 
                                   prof_value 
                                 );
             COMMIT; --may want to move outside function for large batch updates
          ELSE
             dbms_output.put_line( 'FAILED to set ' || prof_option || ' to ' || 
                                   prof_value 
                                 );
             dbms_output.put_line('Check option name and value and try again');
             ROLLBACK; --For safety only; current implementation of
                       --fnd_profile.save does not attempt a write before
                       --returning false
          END IF; --set_result
       END IF; --NOT override AND prev_val_defined 
    END set_ebs_profile;
    view raw ebs_def_setprof.sql This Gist brought to you by GitHub.

    Note that set_ebs_profile expects the internal name for a profile option, instead of the "user-readable" name you'd normally see in the Forms interface. It's useful to become familiar with a query similar to the following. Pay attention to the user_enabled_flag, or you might spend time wondering why your changes aren't sticking.

    /*
      iprof.sql
      Contact: John Piwowar
      Purpose: List useful identifiers for EBS profile options
      Notes:  Prompts for fragment of responsibility name
    */
    

    col prof_name for a40
    col prof_code for a25
    col user_enabled for a7
    col resp_id for 999999
    

    set pagesize 9999 verify off
    

    select profile_option_name prof_code
         , t.user_profile_option_name prof_name
         , o.user_enabled_flag user_enabled
      from fnd_profile_options o
      join fnd_profile_options_tl t using (profile_option_name)
     where upper(t.user_profile_option_name) like upper('%&1%')
     order by user_profile_option_name
    /
    view raw iprof.sql This Gist brought to you by GitHub. 
    APPS@R12VIS(11.1.0.7)>@iprof "OM: Debug"

PROF_CODE	              PROF_NAME                                USER_EN
------------------------- ---------------------------------------- -------
ONT_DEBUG_LEVEL           OM: Debug Level                          Y
OE_DEBUG_LOG_DIRECTORY    OM: Debug Log Directory                  N

  4. LISP

    After my recent public demo of Oracle E-Business Suite Diagnostics, people at my current employer are more interested in using the tool. In the course of applying the most recent patches to the IZU product, I came across a few quirks that I thought I'd share. Please note that these are 11i-related issues, so the relevance half-life is short.

    First, if you have the right combination of patches, you may find that wildcard searches for input fields no longer work. We first noticed this behavior last fall, after applying the July 2010 CPU patch. The workaround is to search without wildcards (just leave the field blank), though that can get unwieldy for large result sets. This issue was recently added to Note 235307.1, which states that there is no known solution at this time, but if you're running 11i, patch 1004979 resolves the problem. Note that this is an AD patch, not an IZU patch, but the payload is pretty light.

    I also discovered, after applying all of the latest patches for 11i Diagnostics, that some test submissions were failing with "Page not found" errors. The corresponding errors in the Apache logs looked like:

    [Fri Feb 31 16:12:74 1942] [error] [client xx.xx.xx.x] mod_security: Access denied with code 400.
Pattern match "!^([-_@|#!=A-Za-z0-9/ :.$]){0,255}([-_@|#!=A-Za-z0-9/ :.$]){0,255}$" at ARGS_NAMES.
[hostname "fakehost.goofynet.org"] [uri "/OA_HTML/jtfqaadv.jsp"] [unique_id TfKkxAoAZtUQAHE3Eeg]

    The ARGS_NAMES filter in IAS_ORACLE_HOME/Apache/Apache/conf/security.conf, intended to filter out potentially-nasty values in URLs, looks like this:

    SecFilterSelective ARGS_NAMES "!^([-_@|#!=A-Za-z0-9/ :.$]){0,255}([-_@|#!=A-Za-z0-9/ :.$]){0,255}$"

    That looks like a reasonably permissive list, but there are (two) ((very)) (((important))) ((((characters)))) (((((missing))))). Any test with parentheses in the input field names (yes, the names of the fields, not the values) will fail this filter, leading to the errors described about. In my case, I was looking at AOL setup tests, and a few of them have "Timeout (seconds)" fields.

    A My Oracle Support search turns up a patch that is supposed to fix this problem (10324904), but the tests I was running did not receive updates in that patch; the parentheses were still in the field names. During a short consultation with support, I was reminded of the chances (low) that a minor bug logged against 11i Diagnostics would get very much attention at this point in the product lifecycle. Instead, I deployed the workaround described in Note 1313128.1. While the note states that changes to the ARGS_NAMES filter will not survive AutoConfig runs, it's easy enough to make the changes stick by creating a custom template for security_ux_ias1022.conf in FND_TOP/admin/template/custom, and changing the ARGS_NAMES filter as follows:

    SecFilterSelective ARGS_NAMES "!^([-_@|#!=A-Za-z0-9/ ():.$]){0,255}([-_@|#!=A-Za-z0-9/ :.$]){0,255}$"

  5. Oracle Apps DBAs, have you empowered your users?

    Last week, I gave a talk to my local Oracle Applications User Group about the value of the Oracle E-Business Suite Diagnostics product (IZU). Feedback was positive, though I was still somewhat surprised that I'd been asked to discuss the topic in the first place. I've been a fan of the tool since 2006, so I've fallen into the trap of assuming that everyone knows about it already. My audience was primarily business analysts and privileged end-users, so I didn't get into technical nuts and bolts, not that there are very many. My general message was, "Here's a free tool from Oracle to help you track down EBS problems in your area of responsibility, all within the scope of the system privileges you already have."

    For Apps DBAs, the same message applies: It benefits you to provide this easy-to-configure, easy-to-use EBS product to your business analysts and "Power Users." They'll get access to diagnostic tests that will help them to identify problems and potential solutions (with period close, errant sales orders, wayward invoices, stuck workflows, etc) before they even have to involve Oracle Support. This, in turn, can engender a sense of ownership and understanding of the working of the EBS products.

    If the warm fuzzies derived from having empowered users aren't quite enough to motivate installing EBS Diagnostics, how about some enlightened misanthropy? With EBS Diagnostics, properly trained and encouraged users may be more likely to leave you alone. :-) Consider the usual opening salvo from Oracle Support when an EBS service request is raised:

    1. "Please run ACT/RDA/Diagnostic Apps Check for the product in question and upload the results"
    2. "What database version/applications version/product patchset are you running?"
    3. "What version of file X is installed on your system?"
    4. "Have you applied any patches recently?
    5. "Please check the value of profile option Y"

    Ordinarily, unless you have very lax security rules, answering these questions (except for #5, and maybe #2) requires input from a DBA, sysadmin, or someone else with privileged access to the system (via OAM, or ad-hoc database queries). Anyone with access to EBS Diagnostics, however, can run the RDA report (see #1 above) for an EBS product and answer all of those questions:


    EBS Diagnostics ACT Report Summary section

    (Yes, that's a screenshot from 11i, because that's the version I used for my talk. R12 looks much the same, just bluer).

    Maybe I'm preaching to the choir here, but I do still find people who either don't know about EBS Diagnostics, or don't see the value of the tool. If you've not done so already, you owe it to yourself to take a look at the Diagnostics product, starting with Note 167000.1: E-Business Suite Diagnostics Installation Guide . Put the tools in your users' hands, and see what happens. Sure, some of them might still just throw things over the wall to you, but others will undoubtedly surprise you.

  6. Presentation slides: Using Oracle EBS Diagnostics

    I somehow got roped into giving a presentation at the BC Oracle Applications User Group meeting on May 26, on the topic of Using Oracle E-Business Suite Diagnostic Tools. Moment of weakness, I guess, but with less than 12 hours to go, it'd be very bad form to bail now. ;-)

    The talk itself is mostly a demo, and the slides all by themselves don't convey the full impact of me talking too fast, talking too much, screwing up the timing on my jokes, involuntarily edging toward the door, and muttering prayers to whatever god is tuned in that my demo VM doesn't misbehave too badly. Nevertheless, there are a few references linked in the slides (whose brilliant idea was that?), and I'll be promising my audience that they can visit my blog to find the slides.

    Thanks to the members of the BCOAUG for the hospitality! Hope I didn't bore too many of you to death. :-) You can find the slides on Slideshare.

  7. Why merge EBS patches?

    We already know this stuff, right?

    “Everyone knows” that merging E-Business Suite patches is a good idea. It should be the number one, first-pass answer to the question of how to speed up patching, before staged APPL_TOP, distributed AD, database tuning, or even adding more workers. As part of my continuing series, “Captain Obvious over-explains the basics,” I've provided an anecdote to illustrate why merging patches is a good idea.

    First things first

    A few short notes about when you should and should not merge E-Business Suite patches, and a disclaimer:

    • Don't merge AD patches with non-AD patches. AD patches sometimes change the same utilities that you're using to apply patches, and should be addressed separately from other product patches.
    • Don't merge a patch if Oracle says that you shouldn't. It's rare that this will happen, but you still need to study your READMEs carefully.
    • Don't merge patches across codelines (11.5.x, 12.0, 12.1).
    • Other than that, go nuts. Seriously. There's no reason you can't merge, for example, HR, Inventory, and Order Management patches. You'd be surprised how many of the same files and patch actions are delivered with patches for separate, functionally distinct products.
    • Your results may not be as dramatic as what I'm describing in this post. Patch payloads differ, systems differ.

    Save time before the work even starts

    The larger the set of patches you have to apply, the more potential benefit you'll derive from merging the patches. A good example of a large patching exercise is the application of Extended Support baseline patches for EBS 11.5.10.2. I ended up with 123 patches to apply across multiple products, from rollups to family packs, with all of their small-to-mid-size pre-requisites. That’s a lot of work. So much work, in fact, that running the merged patch in noapply mode took 23 minutes:

    [applmgr@testsrv log]$ grep ‘AutoPatch started’ u_baseline_final.log
AutoPatch started at: Thu Mar 03 2011 15:27:45
[applmgr@testsrv log]$ ls -l u_baseline_final.log
-rw-r—r— 1 applmgr oinstall 10390236 Mar 3 15:50 u_baseline_final.log

    That's serious "think time" for what amounts to a long sequence of file version checks. But if you if you think that’s bad, watch what happened when I tried to run those patches in noapply mode individually. FWIW, the alias testpatch_this runs adpatch with a defaults file and apply=no, so I could get a timing run free of waits for user intervention.

    [applmgr@testsrv baseline_src]$ time for i in `ls`; do cd $i; testpatch_this; cd ..; done
<lots of patch files later…>
real 135m5.485s
user 19m31.403s
sys 5m41.994s

    Fewer decisions = less work to do

    So, where did the time savings come from? The admrgpch utility does a pretty simple job. It:

    1. Unzips all the patches to be merged
    2. Parses the individual patch driver files and to identify the actions required by the patch
    3. Merges actions from the current patch driver file with those of previously-parsed patches based on file version
    4. Copies the required files to the new merged patch directory
    5. Assembles the necessary patch actions into a new merged patch driver file, based on on the contents of the individual driver files

    Step 3 is where we saved nearly 2 hours. Here's a summary of the results of all of the merging and version-checking:

    SQL> select count(1) comparisions
  2  from patch_comps
  3  /

COMPARISIONS
------------
      190038

SQL> select merge_action
  2       , count(1) as file_count
  3    from patch_comps
  4   group by merge_action
  5   order by merge_action
  6  /

MERGE_ACTI FILE_COUNT
---------- ----------
addnew		96925
addnover	11243
newer		14481
older		15257
same		50056
skipnover	 2076

6 rows selected.

    There were just over 190000 potential files in our merged patch, accounting for at least one action each -- sometimes more, since there are forms to be generated, PL/SQL to be compiled, and executables to link after the files are copied into place. Along the way, admrgpch discarded 65000 files with the same or older version, and replaced 14000 with more recent versions. There's some repetition in the 'newer' and 'addnover' categories, but the rough result in this case is that close to 80000 of the possible files in this collection of patches have been discarded before adpatch is even run. No matter how fast your system is, if you hand it a pile of work that's 40% lighter than it could be, you can probably expect to finish a bit sooner.

    But wait, there's more! (sort of)

    In this case, merging patches has already saved us some significant time just in terms of cutting down the evaluation work that needs to be done at patch time. It's also interesting to look a bit more closely at the individual patch actions that are being culled. After all, if all we're doing is preventing several thousand files from being copied to the OA_HTML directory, well...it's nice, but not exactly compelling. If we can save time in the relinking and forms and reports generation phases, on the other hand, that's more real work that can be avoided. There's a small caveat here, of course: some of this "saved work" might be avoided by adpatch anyway, if the forms/reports to be generated are older than what's already on the system. Still, why relink a bunch of executables 20 times in one patch session, when once will do? 

    SQL> select patch_action
  2       , repeats
  3       ,  count(1) as file_count
  4       , repeats*count(1) as duplicates
  5        from
  6	   ( select filename
  7		      , patch_action
  8		      , count(1) - 1 as repeats
  9	       from patch_actions
 10	      where patch_action in ('genform','genrep', 'link')
 11	      group by filename, patch_action
 12	   )
 13   group by patch_action, repeats
 14   order by patch_action desc, repeats desc

    PATCH_ACTION				    REPEATS FILE_COUNT DUPLICATES
---------------------------------------- ---------- ---------- ----------
link						 25	     2	       50
						 24	     9	      216
						 23	    33	      759
						 22	   313	     6886
						 21	     6	      126
						 19	     1	       19
						 15	     1	       15
						 13	     3	       39
						 12	     1	       12
						 11	     2	       22
						  4	     2		8
						  3	     8	       24
						  2	     2		4
						  1	     2		2
						  0	     4		0
****************************************		       ----------
Total Dups							     8182

genrep						 18	     1	       18
						  8	     1		8
						  4	     1		4
						  3	     3		9
						  2	    19	       38
						  1	   421	      421
						  0	   379		0
****************************************		       ----------
Total Dups							      498

genform 					 21	    13	      273
						 20	    12	      240
						 19	    53	     1007
						 17	     4	       68
						 15	     6	       90
						 14	     1	       14
						 10	   119	     1190
						  9	     1		9
						  7	     1		7
						  6	     5	       30
						  5	    21	      105
						  4	   107	      428
						  3	    77	      231
						  2	   394	      788
						  1	   742	      742
						  0	  1245		0
****************************************		       ----------
Total Dups							     5222

  8. Installing EBS 11.5.10.2 Vision? Quick list of useful links.

    My EBS 11i Vision VM bit the dust recently, and I found myself needing to reinstall. I know, yes, backups. I know, okay? I KNOW. I know. *whimper*

    Anyhow, time to party like it's mid-2005! This isn't going to be as super-long as my R12 Vision install series (which is now an eBook that you should totally buy). Instead, it's a quick list of links to help you get started on something you'll hopefully only need to do once, if ever. At least I now have public notes in case, heaven forfend, I need to do this again myself. Please note that these links are biased toward an installation on Oracle Enterprise Linux 5, because that's the 32-bit Linux media I had closest to hand. There are a few quirks when installing on OEL5, but thankfully they're all well-documented.

    Start with the basics

    Installing Oracle Applications: A Guide to Using Rapid Install Release 11i (11.5.10.2)
    Just in case you need a refresher on system resource requirements and various other setup bits.
    Note 316803.1: Oracle Applications Release Notes, Release 11i (11.5.10.2)
    Not a lot of meat here, but it does provide a reference to the most recent Rapid Install/"startCD" patch, in the unlikely event that it changes. Don't hold your breath; the product's in Extended Support, and the release notes document hasn't been updated in 2 years.
    Note 316806.1: Oracle Applications Installation Update Notes, Release 11i (11.5.10.2)
    Here's the really useful stuff, listing required kernel versions, OS packages, and special instructions for various OS releases. The information in this note gets you 95% of what's needed to do the install. Make sure to apply patches 6365595 and 6078836, to avoid errors on "afmkinit.sh INSTE8_SETUP 127" and "libdb.so.3: cannot open shared object file," respectively.
    Most recent Rapid Install patch
    Why go digging through the release notes for the patch number?
    Note 316843.1: MD5 Checksums for 11i10.2 Rapid Install Media
    Just in case you think your old disk-based staging area may be suspect, and you're wondering if you need to go back to the install DVDs. You know, theoretically.

    OEL5-specific stuff

    Setup for Oracle's public YUM server
    You might need this setup to download additional packages, if you don't want to search your install media.
    Oracle Open-source "compatibility" project
    This is where to grab the additional required RPMs listed in note 316806.1. If you're wondering where to find binutils 2.15, it's in compat-binutils215-2.15.92.0.2-24.i386.rpm.
    Note 730444.1: Oracle Applications 11i Installation on OEL5 or RHEL5
    This note provides solutions to a pair of quirks related to installation on OEL5.
    Note 451994.1: Unable to Find 'kshell' in Path When Running adcfgclone.pl
    Rather than chasing down and installing pdksh, as suggested in Note 730444.1, I found that a simple export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2', as suggested in this note, to be sufficient to satisfy the installer's requirements.
    Note 747424.1: Installation of 11.5.10.2 On OEL Fails
    To avoid an interruption in the installation process, use the steps described in this note (replacing LD_ASSUME_KERNEL in adgetlnxver.sh) before running the zip commands described in the pre-installation tasks for patch 6365595 in Note 316806.1. Depending on your version of OEL, this may not be necessary, but why risk it?

    Thanks for joining me on this trip down memory lane. Please return your Wayback Machines, Deloreans, and telephone booths to their assigned places. Be excellent to each other.

  9. EBS R12 Vision Install Guide is now an eBook (for Kate)

    Chet and Jake have both already covered this topic, but please allow me the small conceit that someone out there might read about it here first. :) About a year ago, I wrote a series of guest posts for the ORACLENERD blog about installing a Vision instance of Oracle E-Business Suite Release 12. It proved to be inexplicably popular, and even spawned something Chet called the EBS Challenge, so Chet and I have bundled the whole series together into a PDF document that flows a bit better than a handful of posts spread over two blogs. Same quirky style, same nigh-endless stack of highly informative screenshots, but way less clicking, and you can take it with you!

    Donationware

    I was initially resistant to asking people to pay for content that I'd already made available for free, even considering the value-add of content restructuring and packaging. When we considered a charitable angle, though, I was all in. If you're a regular reader of Chet's blog, you'll know that one of the primary passions in his life is his daughter, Kate, who has been diagnosed with PDD-NOS. All proceeds from sales of the eBook, after Paypal takes their cut, will be directed to defraying the costs of Kate's care.

    If you're interested in building your very own R12 E-Business Suite playground, I hope you'll consider picking up a copy of the PDF version of the guide. If you're one of the thousands (that's still a bit mind-boggling) of people who have already read the guide on Chet's blog, and if you derived some value from the experience, I hope you'll consider buying a copy to show your appreciation. Heck, buy a copy for everyone in your office! Think of it as an opportunity to make a direct contribution to making life easier for Kate and her parents. You even get a chance to "try before you buy;" all the content in the eBook is already available for you to review on our blogs. I probably left in the same embarrassing typos and bad grammar. You can't lose, and if you buy, Kate wins.

  10. Finding debug and trace profiles in EBS

    It happens all too often in E-Business Suite systems, especially in test and development. Someone sets a profile option to gather trace information for an SR, or to debug a pesky series of forms. They follow the instructions in the relevant My Oracle Support note to the letter, but in the ensuing rush to ship the trace info to the support analyst or to dive into the debug files, they forget to disable the profile option that produces all of that lovely verbose output.

    Time passes. Then the email starts to come in.

    From your systems administrators:

    "Hey, any idea why /opt is at 95% on the dev database server?"

    "Dude, why are there several thousand .dbg files in /var/tmp?"

    From that one extra-special user who remembers that you like detail in your problem statements, and whose only noticeable flaw is a reluctance to be cloned:

    "I've noticed that when I try to do anything at all in Order Management on the test system, it's really dragging. Other parts of the system seem fine, though. Could you check if anything weird is happening with the three forms shown in the attached screenshots?"

    From the Service Desk:

    "We have lots of people complaining that E-Business Suite is slow. Have you guys changed anything?"

    After a long afternoon of looking around, you learn that:

    • /var/tmp is full of debug files from a concurrent request that runs every 10 minutes
    • /opt is filling up because someone (hey, don't snicker, maybe it was you. We've all been there) decided to set the "Initialization SQL Statement - Custom" profile for your most active user to perform a 10046 database event trace. At level 12. Without restricting by application or responsibility.
    • Thankfully, Order Management debug settings mostly affect only the Order Management module. Unfortunately, when they're not also restricted by user, everyone suffers.
    • Most of the other users complaining about slow E-Business Suite performance aren't even using the instance where the above problems arose. They're just struggling with old desktop machines, trying to keep 20 Forms windows open at once, or failing to resist adding one more really cute widget to their browser toolbar. Hey, not every E-Business Suite performance problem starts on the server side. ;)

    All is well again, but it took forever to track down all of those different settings. Wouldn't it be nice if there was a quick way to find debug-related system profile options that had been set recently (or not-so-recently)? You could use a script like this, but that might be a bit too noisy if there are frequent profile option changes.

    Here's a script that I use to try to capture as many of the debug and trace profile settings as possible. It's based on the query that I just linked, but filters on some common strings so you're more likely to get results that are relevant to identifying errant debug settings. It also attempts to ignore options that have been set as defaults at install or patch time. I'm not too proud of the multiple scalar subqueries inside the decode() statement. Clearly I stopped somewhere between "okay, it works" and "let me polish this 'til it gleams." :)

    Oh, and I'm toying with the idea of dropping my various scripts and snippets into Teh Cloud™ at GitHub. I don't claim to be organized enough to rate a repository, but I think the idea of gists is pretty nifty.

    -- check_ebs_trace_profs.sql
    --  Author: John Piwowar
    -- Purpose: Identify E-Business Suite system profile option settings that may
    --          be related to performance-degrading debug/trace activity
    --   Notes: Prompts for a cutoff date for when profile options were set
    --          May need additional tweaking for multi-language installations 
    set pagesize 9999
    set linesize 120
    set verify off
    col "Profile Option" for a25
    col "Option Level" for a13
    col "set for" for a20
    col "Value" for a20
    col "Set On" for a11
    col "Blame" for a20
    PROMPT Enter date value in form DD-MON-YYYY for check_since
       select tl.user_profile_option_name "Profile Option"
            , decode( val.level_id
                    , 10001, 'Site'
                    , 10002, 'Application'
                    , 10003, 'Responsibility'
                    , 10004, 'User'
                    , 10005, 'Server'
                    , 10006, 'Organization'
                    , 10007, 'Server+Resp'
                    , 'No idea, boss') "Option Level"
            , decode( val.level_id
                    , 10001
                    , 'EVERYWHERE!'
                    , 10002
                    , (select application_name
                         from fnd_application_tl
                        where application_id = val.level_value)
                    , 10003
                    , (select responsibility_name
                         from fnd_responsibility_tl
                        where responsibility_id = val.level_value
                          and application_id = val.level_value_application_id)
                    , 10004
                    , (select user_name
                         from fnd_user
                        where user_id = val.level_value)
                    , 10005
                    , (select host || '.' || domain
                         from fnd_nodes
                        where node_id = val.level_value)
                    , 10006
                    , (select name
                         from hr_all_organization_units
                        where organization_id = val.level_value)
                    , 10007
                    , 'Look it up' --per specification El-Ay-Zed-why
                    , '''Tis a mystery') "Set for"
            , val.profile_option_value "Value"
            , val.last_update_date "Set on"
            , usr.user_name "Set By"
         from fnd_profile_options opt,
              fnd_profile_option_values val,
              fnd_profile_options_tl tl,
              fnd_user usr
        where opt.profile_option_id = val.profile_option_id
          and opt.profile_option_name = tl.profile_option_name
          and regexp_like( tl.user_profile_option_name
                         , '(trace|log|debug|audit|diag|sql)'
                         , 'i'
                         )
          and not(regexp_like( tl.user_profile_option_name
                             , '(catalog|file|login|utilities)'
                             , 'i'
                             )
                 )
          and usr.user_id = val.last_updated_by
          and usr.user_name  not in ( 'AUTOINSTALL'
                                    , 'INITIAL SETUP'
                                    , 'ANONYMOUS')
          and val.last_update_date > '&check_since'
        order by val.last_update_date desc
    ;
    view raw check_ebs_trace_profs.sql This Gist brought to you by GitHub.
  1. 1
  2. Next ›
  3. Last »