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!
