Blogs

Significance of Event Initialization Parameter in Oracle 19c Database

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!

More from this Author