Sometimes , Eventhough the Database sessions related to the concurrent Programs are in INACTIVE State , we could not terminate the Concurrent Request with error "Could not Lock Request" , Issue could be Requests are struck at Post Processing Phase and OutPut Post Processor is locking the Concurrent Requests.
Need to follow the below steps to perform clean shutdown of Oracle Concurrent Manager.
a) Put Pending Concurrent Requests on hold using the below sql queries.
+Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
+select count(*) from apps.conc_req_on_hold
+ update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);
NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same
+ Commit;
You can find more details about putting Pending Concurrent Jobs on Hold here.
http://www.appsdbadiaries.com/2016/01/concurrent-requests-on-hold.html
b)Bring Down Concurrent Manager using adcmctl.sh stop
c) Update the status of Struck Concurrent Requests to "Terminated"
SQL> update fnd_concurrent_requests set status_code='X',phase_code='C' where status_code='R' and phase_code='R';
Commit;
Use Concurrent Manager Recovery Wizard from OAM to clear the database sessions associated with cancelled Requests.
d) Start Concurrent Manager using adcmctl.sh
e) Remove Hold on Concurrent Requests
SQL>update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);
Commit the changes
SQL>commit;
Need to follow the below steps to perform clean shutdown of Oracle Concurrent Manager.
a) Put Pending Concurrent Requests on hold using the below sql queries.
+Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
+select count(*) from apps.conc_req_on_hold
+ update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);
NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same
+ Commit;
You can find more details about putting Pending Concurrent Jobs on Hold here.
http://www.appsdbadiaries.com/2016/01/concurrent-requests-on-hold.html
b)Bring Down Concurrent Manager using adcmctl.sh stop
c) Update the status of Struck Concurrent Requests to "Terminated"
SQL> update fnd_concurrent_requests set status_code='X',phase_code='C' where status_code='R' and phase_code='R';
Commit;
Use Concurrent Manager Recovery Wizard from OAM to clear the database sessions associated with cancelled Requests.
d) Start Concurrent Manager using adcmctl.sh
e) Remove Hold on Concurrent Requests
SQL>update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);
Commit the changes
SQL>commit;