Introduction: – Ora-20100 temporary file creation failed for fnd_file| ORA-20100: ORA-20100: File o00031194568.tmp creation failed for fnd_file. File could not be opened or operated on as requested. Action: Make sure the directory – /usr/tmp/RMDBUAT – is a valid directory with write permissions and is accessible from the database server node for write operation.
Concurrent Request logs for ora-20100 error fnd_file failure : –
Enter Password:
REP-1401: 'c_do_insertformula': Fatal PL/SQL error occurred.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.FA_RSVLDG_REP_INS_PKG", line 1526
ORA-20100: ORA-20100: File o0003119930.tmp creation failed.
File could not be opened or operated on as requested.
Action: Make sure the directory - /usr/tmp/RMDBUAT - is a valid directory with write permissions and is accessible from the database server node for write operati
ORA-06512: at "APPS.FND_FILE", line 319
O
REP-0069: Internal error
REP-57054: In-process job terminated: Terminated with error:
REP-1401: 'c_do_insertformula': Fatal PL/SQL error occurred.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.FA_RSVLDG_REP_INS_PKG", line 1526
ORA-20100: ORA-20100: File o0003119930.tmp creation failed.
File could not be opened or operated on as requested.
Action: Make sure the directory - /usr/tmp/RMDBUAT - is a valid directory with write permissions and is accessible fro
Report Builder: Release 10.1.2.3.0 - Production on Thu Oct 6 10:25:1 2022
To fix above issue we need to set proper value for utl_file_dir in dbTier and s_applptmp in appsTier.
Verify :-
Currently in our database utl_file_dir value showing as below. Now we will update it. In 19c database, some different method to update utl_file_dir.
SQL> select value from v$parameter where name=’utl_file_dir’;
VALUE
——————————————————————————–
/usr/tmp/RMDBUAT,/rmouat/oracle/temp/ RMDBUAT
How to fix Ora-20100.
Before going to start the steps to resolve the issue. Let’s understand the details of environment.
In my case, we have multi-node environment as below.
| Primary node | Secondary node | DMZ node |
| rmdbebsuat01 | rmdbebsuat02 | rmdbebsuatdmz01 |
Let’s start the following steps to resolve the issue in 19c.
Step 1:- Update the s_applptmp on each application.
Make sure in context file s_applptmp value updated as /tmp/RMDBUAT on each node as below. After update s_applptmp, need to be run autoconfig (run and patch file system) on all application to populate the changes.
Primary node
[rmouat@rmdbebsuat01 ~]$ cat $CONTEXT_FILE|grep s_applptmp
/tmp/RMDBUAT
Secondary node
[rmouat@rmdbebsuat02 ~]$ cat $CONTEXT_FILE|grep s_applptmp
/tmp/RMDBUAT
DMZ node
[rmouat@rmdbebsuatdmz01 ~]$ cat $CONTEXT_FILE|grep s_applptmp
/tmp/RMDBUAT
Steps 2: – Run txkCfgUtlfileDir.pl with mode as getUtlFileDir on dbTier to get utl_file_dir.
After that update the $ORACLE_HOME/dbs/RMDBUAT_utlfiledir.txt.
Note: – Source the PDB environment before executing below.
$perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE -oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=getUtlFileDir
Note:- may use -skipdirvalidation=Yes -skipautoconfig=yes
Update/replace RMDBUAT_utlfiledir.txt and put as /tmp/USRLUAT in last line.
Steps 3. Run txkCfgUtlfileDir.pl with mode as setUtlFileDir.
Make sure below step completed successfully.
[rmouat@rmodbuatsrv 19.3.0]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE -oraclehome=$ORACLE_HOME -o utdir=$ORACLE_HOME/appsutil/log -mode=setUtlFileDir
Enter the APPS Password:
Enter the SYSTEM Password:
Script Name : txkCfgUtlfileDir.pl
Script Version : 120.0.12020000.15
Started : Thu Oct 6 17:58:22 EDT 2022
Log File : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/txkCfgUtlfileDir.log
Context file: /rmouat/oracle/19.3.0/appsutil/RMDBUAT_rmodbuatsrv.xml exists.
Value for s_applptmp on Apps Tier nodes is valid
Completed : Thu Oct 6 17:58:31 EDT 2022
Successfully Completed the script
ERRORCODE = 0 ERRORCODE_END
Steps 4. Check & verify the txkCfgUtlfileDir.log.
Verify the logs after executing perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl using mode as setUtlFileDir. All should be complete successfully without any error.
[rmouat@rmodbuatsrv 19.3.0]$ cat /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/txkCfgUtlfileDir.log
Script Name : txkCfgUtlfileDir.pl
Script Version : 120.0.12020000.15
Started : Thu Oct 6 17:58:22 EDT 2022
Log File : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/txkCfgUtlfileDir.log
-----------
Values used
-----------
Context File : /rmouat/oracle/19.3.0/appsutil/RMDBUAT_rmodbuatsrv.xml
Oracle Home : /rmouat/oracle/19.3.0
Upgraded Database Oracle Home :
OUT Directory : /rmouat/oracle/19.3.0/appsutil/log
Script execution mode : setUtlFileDir
Script execution tier : db
Service Type : onpremise
Skip directory validation : No
Skip AutoConfig : No
==============================
Validating the context file...
==============================
=============================
Inside sourceEnvironment()...
=============================
Sourcing the environment file: /rmouat/oracle/19.3.0/RMDBUAT_rmodbuatsrv.env
Required ENVIRONMENT has been set....
=========================
Validating oracle home...
=========================
Oracle Home: /rmouat/oracle/19.3.0 exists.
===========================
Validating out directory...
===========================
Oracle Home: /rmouat/oracle/19.3.0/appsutil/log exists.
===================================
Validating script execution mode...
===================================
Script execution mode: setUtlFileDir
Script execution mode is valid.
=============================
Validating DB service type...
=============================
Service Type: onpremise
Service type is valid.
=======================================
Inside checkContextFileAndExecTier()...
=======================================
Context file and Exec Tier arguments are in SYNC.
======================================
Inside checkScriptModeAndExecTier()...
======================================
Script Mode and Exec Tier arguments are in SYNC.
=====================================
Validating APPS schema credentials...
=====================================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/validate_apps_password.sql
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/validate_apps_password.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/validate_apps_password.log
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Validated APPS credentials.
========================
Inside getDBVersion()...
========================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_db_version.sql
SQL output file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_db_version.out
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_db_version.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_db_version.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Getting the DB version.
DB_VERSION = db190
====================
Inside isCDBPDB()...
====================
db_tenancy: multi-tenant
DB type is multi-tenant. Re-setting ORACLE_SID and unsetting LOCAL.
======================================
Inside checkSecCaseSensitiveLogon()...
======================================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_sec_case_sensitive_logon.sql
SQL output file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_sec_case_sensitive_logon.out
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_sec_case_sensitive_logon.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_sec_case_sensitive_logon.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Getting the parameter SEC_CASE_SENSITIVE_LOGON.
Parameter SEC_CASE_SENSITIVE_LOGON is set to FALSE.
SEC_CASE_SENSITIVE_LOGON_FLAG: FALSE
=======================================
Validating SYSTEM schema credentials...
=======================================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/validate_system_password.sql
SQL output file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/validate_system_password.log
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/validate_system_password.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/validate_system_password.log
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Validated SYSTEM credentials.
=====================
Inside getDBName()...
=====================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_db_name.sql
SQL output file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_db_name.out
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_db_name.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_db_name.sql
EXIT STATUS: 0
Getting the value of DB Name...
db_name: RMDBUAT
============================
Inside setFileLocations()...
============================
UTL_FILE_DIR_FILE: /rmouat/oracle/19.3.0/dbs/RMDBUAT_utlfiledir.txt
========================
Inside getEBSTmpDir()...
========================
EBS_TMP_DIR: /rmouat/oracle/temp/RMDBUAT
===========================
Inside loadUtlFileDirs()...
===========================
Reading the UTL_FILE_DIRs from file /rmouat/oracle/19.3.0/dbs/RMDBUAT_utlfiledir.txt
EBS temp directory not defined in the text file, hence appending it explicitly.
===================================
Validating Util File directories...
===================================
Validating /tmp/RMDBUAT...
Directory is valid.
Validating /rmouat/oracle/temp/RMDBUAT...
Directory is valid.
=================================
Inside createEBSCustomTables()...
=================================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/create_utl_file_tables.sql
==============================
Inside checkDBTableExists()...
==============================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/check_EBS_UTLFILE_PARAM.sql
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/check_EBS_UTLFILE_PARAM.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/check_EBS_UTLFILE_PARAM.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Checking EBS_UTLFILE_PARAM table exists.
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/check_EBS_UTLFILE_PARAM.out
pattern: TABLE-EXISTS
================
Pattern found...
================
EBS_UTLFILE_PARAM exists.
==============================
Inside checkDBTableExists()...
==============================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/check_EBS_UTLFILE_PARAM2.sql
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/check_EBS_UTLFILE_PARAM2.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/check_EBS_UTLFILE_PARAM2.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Checking EBS_UTLFILE_PARAM2 table exists.
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/check_EBS_UTLFILE_PARAM2.out
pattern: TABLE-EXISTS
================
Pattern found...
================
EBS_UTLFILE_PARAM2 exists.
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/create_utl_file_tables.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/create_utl_file_tables.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
EBS custom tables created succussfully.
================================
Inside insertEBSCustomParam()...
================================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/insert_ebs_custom_param.sql
====================================
Inside validateUtlFileDirLength()...
====================================
Value of UTL_FILE_DIR is /tmp/RMDBUAT,/rmouat/oracle/temp/RMDBUAT
Length of UTL_FILE_DIR is 41
Maximum allowed length is 4000 characters.
Length of UTL_FILE_DIR is within the limits.
=================================
Inside concatUtlFileDirValue()...
=================================
util_file_dir_array_length: 2
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/insert_ebs_custom_param.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/insert_ebs_custom_param.out
pattern: ERROR|SP2-0027
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Inserted row into EBS tables successfully.
===================================
Inside createUtlFileDirObjects()...
===================================
================================
Inside getUtlFileDirObjects()...
================================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_utl_file_dir_objects.sql
SQL output file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_utl_file_dir_objects.out
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_utl_file_dir_objects.sql
error_lines_array_length: 0
search_text_lines_array_length: 0
EXIT STATUS: 0
Getting the directory objects.
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/create_utl_file_dir_object.sql
===========================================
Inside checkUtlFileDirObjectPathExists()...
===========================================
EBS custom directory object EBS_UTL_FILE_DIR_5296984704482 exists for /rmouat/oracle/temp/RMDBUAT
EBS custom directory object already exists for path : /rmouat/oracle/temp/RMDBUAT
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/create_utl_file_dir_object.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/create_utl_file_dir_object.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Directory objects created successfully.
===================================
Inside deleteUtlFileDirObjects()...
===================================
=================================
Inside getUnwantedDirObjects()...
=================================
==============================
Inside getOutboundDirList()...
==============================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_outbound_dir.sql
SQL output file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_outbound_dir.out
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_outbound_dir.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_outbound_dir.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Getting the outbound dir for DB Tier nodes.
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/unwanted_dir_objects.sql
SQL output file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/unwanted_dir_objects.out
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/unwanted_dir_objects.sql
error_lines_array_length: 0
search_text_lines_array_length: 0
EXIT STATUS: 0
Getting the unwanted directory objects.
============================
Inside checkOutboundDir()...
============================
UNWANTED_DIR_OBJECTS_STRING:
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/drop_utl_file_dir_object.sql
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/drop_utl_file_dir_object.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/drop_utl_file_dir_object.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Directory objects deleted successfully.
=============================
Inside checkApplpTmpInDB()...
=============================
=============================
Inside getApplpTmpCtxVar()...
=============================
Generating SQL file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_applptmp.sql
SQL output file : /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_applptmp.out
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Removing the file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_applptmp.sql
==============================
Inside searchFileContents()...
==============================
log_file: /rmouat/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Thu_Oct_6_17_58_07_2022/get_applptmp.out
pattern: ERROR
=============================
Could not find the pattern...
=============================
EXIT STATUS: 0
Getting the applptmp for Apps Tier nodes.
============================================================================
* *
* Summary report of s_applptmp validation on Apps Tier nodes *
* *
* Follow below steps on all nodes whose s_applptmp is INVALID *
* 1. Correct the value of s_applptmp *
* 2. Execute AutoConfig *
* *
* NOTE: *
* Value for s_applptmp should be one of the values present in UTL_FILE_DIR *
* *
============================================================================
rmdbebsuat01 : /tmp/RMDBUAT --> VALID
rmdbebsuat02: /tmp/RMDBUAT --> VALID
rmdbebsuatdmz01: /tmp/RMDBUAT --> VALID
Value for s_applptmp on Apps Tier nodes is valid
Completed : Thu Oct 6 17:58:31 EDT 2022
Successfully Completed the script
ERRORCODE = 0 ERRORCODE_END
Steps 5:- Re-run the concurrent request to check ora-20100 19c issue is resolved.
After successfully update utl_file_dire in database and s_appltmp in appsTer, we should re-run the concurrent request to check whether issue ‘ora-20100 temporary file creation failed‘ has been resolved or not. In my case issue was resolve.