JCA Binding execute of Reference operation 'Calling_DB_run_settlement'(Stored Procedure) failed due to: Stored procedure invocation error(ORA-20100: File l32.tmp creation for FND_FILE failed)

Hi Folks ,

I have faced this scenario in 11.1.1.4 Env.

Error:-

Binding fault has occured in the Stored Prosedure Component please check with the Admin;.............................
FaultName: bindingFault
Fault Info: com.oracle.bpel.client.BPELFault: faultName: {{http://schemas.oracle.com/bpel/extension}bindingFault} messageType: {{http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage} parts: {{ summary=Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'Calling_DB_xxwfs_run_wex_settlement' failed due to: Stored procedure invocation error. Error while trying to prepare and execute the STORED_PROCEDURE. An error occurred while preparing and executing the .STORED_PROCEDURE Cause: java.sql.SQLException: ORA-20100: File l32.tmp creation for FND_FILE failed. You will find more information on the cause of the error in request log. ORA-06512: at "APPS.FND_FILE", line 667 ORA-06512: at "STORED_PROCEDURE", line 375 ORA-06512: at line 1 Check to ensure that the API is defined in the database and that the parameters match the signature of the API. This exception is considered not retriable, likely due to a modelling mistake. To classify it as retriable instead add property nonRetriableErrorCodes with value "-20100" to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties are integers. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution. ,detail=ORA-20100: File l3247078.tmp creation for FND_FILE failed. You will find more information on the cause of the error in request log. ORA-06512: at "APPS.FND_FILE", line 667 ORA-06512: at "STORED_PROCEDURE", line 375 ORA-06512: at line 1 ,code=20100}

Solution:-  Doc ID 392635.1

Metalink description below

Error ORA-20100 .tmp Creation For FND_FILE Failed In FA Concurrent Requests (Doc ID 392635.1)

APPLIES TO:

Oracle Assets - Version 11.5.10.2 and later
Oracle Application Object Library - Version 11.5.1 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 14-Jan-2013***

SYMPTOMS

When attempting to run a concurrent request such as the 'Generate Asset Trace' program (FATRACE), the following error occurs in the log file:

ERROR
ORA-20100: File o0013319.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
Error: function fa_asset_trace_pkg.save_output returned failure

CAUSE

This error is caused by a concurrent manager setup and privilege related issue in the $APPLPTMP environment variable and UTL_FILE_DIR database parameter.

For example:
There could be one user for the Application Tier (applmgr in group applmgr) that is used by the Concurrent Manager, and one user for the database (oracle in group dba) that is used by the database.

The .tmp file is released by user oracle out by utl_file_dir, but the Concurrent Manager will try to access it with user applmgr.  As they are in different groups, this could be an issue on Linux/Unix environments.

Another environment problem could be that the utl_file_dir first directory is not $APPLPTMP.  Then the Concurrent Manager will check in a directory for a tmp and will not find it.

This error can also be seen in cases were there is more then one instance on the same server, and they share the same directory for $APPLPTMP or $UTL_FILE_DIR.  One instance can try to write to the same temporary file, which is locked by the other one.

SOLUTION

Please do the following:

1.  Change the UTL_FILE_DIR to have the same first value as the $APPLPTMP, ex: /usr/tmp
Change it in the initSID.ora and reboot the database after.

2.  Grant all users read/write permissions to that directory:
chmod 777 /usr/tmp

3.  If the error persists, check the following notes:

Oracle Applications System Administrator's Guide - Configuration
Note:157756.1: APPLPTMP , utl_file_dir and FND_FILE package
Note:303913.1: Upgrading From 11.5.9 To 11.5.10: Ora-20100 , Ora-06512 Fnd_file Creation failed
Note:123977.1: ORA-20100 & ORA-6512 When Running a Concurrent Process

4.  If still having the issue, please log an SR with Oracle Support, RDBMS team.

REFERENCES

NOTE:123977.1 - ORA-20100 & ORA-6512 When Running a Concurrent Process
NOTE:148262.1 - Concurrent Processing - Concurrent Requests Fail with FDPSTP failed due to ORA-20100: File o0002712.tmp creation for FND_FILE
NOTE:157756.1 - Concurrent Requests Complete with ORACLE Error 20100 in FDPSTP
NOTE:303913.1 - Upgrade to 11.5.10fails with ORA-20100, ORA-06512 'creation for FND_FILE failed'

Happy learning......!!!!!!!!!   Fun Sharing....!!!!!!!!!!!