Concurrent Request failing due to XML publisher directory not available

Issue:

Concurrent Request Failing during OutputPost Processing

Below is the error message from OutputPost Processor Logs:

 [UNEXPECTED]  java.io.FileNotFoundException: /usr/tmp/ERPTEST1/xdokjshyw821_1905677210.fo (No such file or directory)
        at java.io.FileOutputStream.open(Native Method)
        at java.io.FileOutputStream.<init>(FileOutputStream.java:221)
        at java.io.FileOutputStream.<init>(FileOutputStream.java:171)
        at oracle.apps.xdo.common.tmp.TmpFile.createTmpFileJDK118(Unknown Source)
        at oracle.apps.xdo.common.tmp.TmpFile.createTmpFile(Unknown Source)
        at oracle.apps.xdo.template.FOProcessor.getTmpFOFile(Unknown Source)
        at oracle.apps.xdo.template.FOProcessor.createFO(Unknown Source)
        at oracle.apps.xdo.template.FOProcessor.generate(Unknown Source)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5984)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3475)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3564)
        at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:311)
        at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:185)
Cause 

The Temporary directory for XML Publisher has not been modified after making the clone so it was pointing to non existing location and getting error out.

Solution

1. Create a new Temporary Directory on OS level for the cloned environment.
2. Make sure that the application owner (APPLMGR) has Read and Write permission this directory.

Concurrent Manager Troubleshooting : Multiple Concurrent Requests Struck without Processing

Scenario: Concurrent Requests are in Running Normal state for longer time than normal duration of the Program.

Analysis: 

Step 1: Navigate to System Administrator Responsibility : Concurrent Manager --> Administer 

Identify the Concurrent Requests which are in Running Status.

Get the Database Session Details of the currently running concurrent requests using the below Query:

SELECT DISTINCT  a.request_id,C.INST_ID, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R' and a.status_coDe='R';



Step 2: Verify if the database sessions are active/Inactive at the database level using the below query.


select inst_id,sid,serial#,program,module,status,last_call_et,sql_id from gv$session where sid=&sid;

-- sid value to be taken from output of Sql Query in Step 1


If Database session is INACTIVE And Running no sql for more than an Hour, we can Terminate the Concurrent Requests.
If the Database session is ACTIVE and has an SQL_ID attached with it, Need to check on tuning the sql being run by the database session.

I will cover more details about SQL Tuning in another post.