Monday, February 4, 2008

UTL_FILE_DIR issue after applying patch 5985992 'TXK AUTOCONFIG RUP Q'

Do you have more than 240 characters in your utl_file_dir?

if so, you should read this before applying patch 5985992 'TXK AUTOCONFIG RUP Q (Jul/Aug 2007)'.

After applying this patch, AutoConfig on database tier failed with the following error:

[PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: [RDBMS_ORACLE_HOME]/appsutil/install/[context_name] 
      afdbprf.sh              INSTE8_PRF         1

AutoConfig is exiting with status 1

It wasn't clear why afdbprf.sh failed so I ran it manually from ssh terminal and I got this:

$ [RDBMS_ORACLE_HOME]/appsutil/install/[context_name]/afdbprf.sh

afdbprf.sh started at Tue Jan 29 17:43:21 IST 2008

The environment settings are as follows ...

       ORACLE_HOME : ....
       .........
       .........

Application Id : 0
Profile Name : BIS_DEBUG_LOG_DIRECTORY
Level Id : 10001
New Value : /usr/tmp
Old Value : /usr/tmp
declare
*
ERROR at line 1:
ORA-12899: value too large for column
"APPLSYS"."FND_PROFILE_OPTION_VALUES"."PROFILE_OPTION_VALUE" (actual: 486,
maximum: 240)
ORA-06512: at line 44
ORA-06512: at line 139

Looking into afdbprf.sql  (executed by afdbprf.sh) reveals the problem:

.......

--Setting  BIS_DEBUG_LOG_DIRECTORY
           set_profile(0, 'BIS_DEBUG_LOG_DIRECTORY',
                       10001, 0,
                       '&BIS_DEBUG_LOG_DIR',
                       NULL);

-- This profile option was earlier set in addbprf.sql via bug 2843457, Now moved here for bug 5722442
--
-- Set up UTL_FILE_LOG profile option
--
          set_profile(1, 'UTL_FILE_LOG',
                      10001, 0, '/usr/tmp,........[more than 240 characters..... :-) ]',
                      NULL);

In order to fix bug 5722442, the UTL_FILE_LOG updated with the value of s_db_util_filedir from the database context file and a new bug was created (not a bug according to Bug 6404909).

If the s_db_util_filedir contains more than 240 characters it can't be stored in a profile since the PROFILE_OPTION_VALUE column defined as varchar2(240).

The s_db_util_filedir initialized when creating the database context file by adbldxml.pl, and set up with the value of utl_file_dir database parameter.

I'm not sure why it should be updated with all directories within utl_file_dir and not with the relevant directories only...?! 
The UTL_FILE_LOG profile (or "Stored procedure log directory") wasn't updated with AutoConfig before applying this patch, so I'm not sure regarding the purpose of this profile.
Before applying this patch I have this profile set to a directory that doesn't exist...

The solution according to Note:458511.1 - "After patch 5985992 AutoConfig On Database Tier Fails with script afdbprf.sh" is to change the value of s_db_util_filedir in the database context file to a value less the 240 characters and run AutoConfig again.

And it works, of course...

What should I do if I my utl_file_dir contains more than 240 characters?!

Actually I don't have a good answer to this question but I will try to locate all the places on which this issue might affect when s_db_util_filedir will be updated with a value less than 240 chars.

  1. AutoConfig on the DB Tier creates the [SID]_APPS_BASE.ora file under $RDBMS_ORACLE_HOME/dbs (if it's not already exist), which contains the utl_file_dir database parameter generated respectively to the s_db_util_filedir from the database context file.
    So if you plan to rebuild your init.ora with AutoConfig you will need to update the utl_file_dir manually.
  2. When executing adbldxml.pl on DB Tier to rebuild the database context file, the s_db_util_filedir will be filled with the utl_file_dir database parameter - that might contain more than 240 chars.
    Therefore, before executing AutoConfig you should edit the new context file and shorten the value of s_db_util_filedir.
  3. The UTL_FILE_LOG profile ("Stored procedure log directory")  will be updated with s_db_util_filedir shortened value.

You are welcome to leave a comment or update with additional information regarding this issue.

Aviad