While upgrading Oracle E-Business Suite (EBS) to R12.2.11, we faced an issue that highlights the aftermath of the “event” initialization parameter being absent at the (Container Database) CDB Level.
sqlplus -s APPS/***** @/d01/apps/PDBSID/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql ‘/d01/apps/PDBSID/fs1/EBSapps/appl/xdp/12.0.0/patch/115/sql/XDPMIGFX.sql ‘
Connected.
PL/SQL procedure successfully completed.
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 68
ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.
Directory &FILE_DIR is invalid, contact your system administrator.
Action: Check that the directory is a valid directory with write permissions on the database server node and is listed under
utl_file_dir para
ORA-06512: at “APPS.FND_FILE”, line 319
ORA-06512: at “APPS.FND_FILE”, line 364
ORA-06512: at “APPS.FND_FILE”, line 421
ORA-06512: at line 21
The immediate thought that comes to mind is whether there is a possible discrepancy between $APPLPTMP and the “value” column of APPS.V$PARAMETER.
[applmgr@hostname ~]$ echo $APPLPTMP
/ora1/temp/PDBSID
select NAME,VALUE from apps.v$parameter where name=’utl_file_dir’;
NAME VALUE
— — — — — — — — — — — — — — — — — — — — — — — — — -
utl_file_dir /ora1/temp/PDBSID
Therefore, this is ruled out. It is also recommended to set full permissions to the $APPLPTMP location in the database server.
[oracle@hostname ~]$ cd /ora1/temp
[oracle@hostname temp]$ ls -lrt
total 20
drwxrwxrwx. 2 oracle oinstall 20480 Jul 8 17:50 PDBSID
However, a manual file creation was still failing –
SQL> exec FND_FILE.PUT_LINE(fnd_file.log, ‘Hello World!’);
BEGIN FND_FILE.PUT_LINE(fnd_file.log, ‘Hello World!’); END;
*
ERROR at line 1:
ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.
Directory &FILE_DIR is invalid, contact your system administrator.
Action: Check that the directory is a valid directory with write permissions on
the database server node and is listed under utl_file_dir para
ORA-06512: at “APPS.FND_FILE”, line 319
ORA-06512: at “APPS.FND_FILE”, line 364
ORA-06512: at “APPS.FND_FILE”, line 421
ORA-06512: at line 1
The last action plan is to look at this particular section of note “Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (Doc ID 2525754.1)”.
Note: To enable the UTL_FILE functionality on which the supplemental UTL_FILE_DIR parameter depends, ensure that you have set the following event at the CDB level in your database initialization parameters: event=’10946 trace name context forever, level 8454144'
Apart from striking out init parameter UTL_FILE_DIR (inherent part of the previous database releases 11g,12c), Oracle introduced the “event” parameter to ensure compatibility between EBS 12.2 and database 19c in the sense that UTL_FILE functions will still be able to write temporary files on the directories specified by UTL_FILE_DIR parameter defined in APPS.V$PARAMETER and APPS.V$PARAMETER2 views; unlike that of previous aforementioned versions, where just UTL_FILE_DIR parameter met the same expectations.
Somehow for our client, the event was reset. Setting it back at the CDB Level resolved the issue. And, ALL went WELL!
