ORA-20100: ORA-20100: tmp creation failed

 After we cloned our TEST instance, some of the reports(seeded and custom) completed with error. On checking the log file found the below error

After a short search found some metalink documents which shed light on this issue. The reason is UTL_FILE_DIR and the environment variable $APPLPTMP are pointing to the same directory but that mount is already used by the grid software in my environment.

Insight on the Issue

1. The EBS environment variable $APPLPTMP is set to temporary directory path on the 'Database' node.
2. The $APPLPTMP path is needed only on the Database node and not required on the Application node
3. The $APPLPTMP path needs to be the first path in the UTL_FILE_DIR database parameter.
4. If multiple databases runs on the same host, then use a unique value for this temporary directory(i.e. All DB should not use the same directory and also the directory should not be owned by a different user. Normally the owner for this directory will be root.)
5. If there are more than one database(i.e. RAC) then the path must be a shared point
6.  The environment variable $APPLPTMP / UTL_FILE_DIR database parameter is used to process concurrent requests that use
1. Database tools
2. Programs of the execution method: PL/SQL, Stored Procedure, SQL*Plus, SQL*Loader, etc.
7. Therefore this directory must have sufficient disk space and privileges to allow reports to be created and allow the applmgr(APPS OS User) to move the output to $APPLCSF/$APPLOUT
8. The sysadmin concurrent report "Generate concurrent processing environment information" can be used to check $APPLPTMP. 
1. No parameter is required to run this program. 
2. This program is safe to run as it does not change or create anything on the instance.
3. It only reports a listing of environment variables visible to the concurrent managers.

 Below are the steps, I followed to overcome this issue.

1. Take backup of pfile, spfile, context file on the DB Tier
2. Take backup of context file on the APPS Tier
3. Check the existing entries from pfile and context file on DB and APPS Tiers
4. Run the report "Generate concurrent processing environment information" to account the environment variables
5. Create your temporary folder at the desired location with appropriate ownership and permissions
6. Change the entries to your desired location on pfile and context file on DB and APPS Tiers
7. Run auto-config on both DB and APPS tiers
8. Check the entries from pfile and context file on DB and APPS Tiers
9. Re-test the issue.

Take backup of pfile, spfile, context file on the DB Tier

Take backup of context file on the APPS Tier

Check the existing entries from pfile and context file on DB and APPS Tiers

Run the report "Generate concurrent processing environment information" to account the environment variables

Create your temporary folder at the desired location with appropriate ownership and permissions

Change the entries to your desired location on pfile and context file on DB and APPS Tiers

Run auto-config on both DB and APPS tiers

Now shutdown the DB, startup with the modified pfile, check the entries for to be proper and run autoconfig on the DB Node.

Check the entries from pfile and context file on DB and APPS Tiers

Now Re-test the issue. This time the concurrent program(OAM Applications Dashboard Collection) has completed successfully without any issues.

Reference:

12.2 E-Business Suite Concurrent Processing Reports Fails With ORA-06512: at "APPS.FND_FILE" and "ORA-20100: ORA-20100: File O0047353.tmp Creation Failed" Errors (Doc ID 2329514.1)

How To Set APPLPTMP and UTL_FILE_DIR (Doc ID 2141438.1)

Concurrent Requests Fail With Error ORA-20100 (Doc ID 2723960.1)

How To Change Temporary Directories in Apps? (Doc ID 469377.1)

****************************நன்றி****************************