CMCLEAN.SQL is Dangerous. Be Warned!

Description

I’m sure one of the most popular scripts for Apps DBAs on My Oracle Support is cmclean.sql from MOS Article ID 134007.1 “Concurrent Processing - CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables”. DBAs usually use the script to clean up stale data from concurrent processing tables (FND_CONCURRENT_%) after incidents like a crash of the database or concurrent processing node. This script sets correct completion phase and status codes for terminated concurrent requests and sets correct control codes for terminated concurrent manager processes. Despite the assuring “Non Destructive” claim in the title of the MOS Article there is a possibility to lose concurrent request schedules when cmclean.sql is executed. First of all it’s important to understand how scheduled concurrent requests are executed and resubmitted.

Execution

A simplified process is:

1. Concurrent manager process (e.g. FNDLIBR in case of Standard Manager) queries the FND_CONCURRENT_REQUESTS table for pending requests.
   
   
2. When a pending request is found, the manager process updates the PHASE_CODE=R (Running) and STATUS_CODE=R (Running).
   
   
3. The next step is to start the executable of the concurrent program. If it’s a PL/SQL procedure – FNDLIBR connects to the DB and executes the PL/SQL code, if it’s a java program – FNDLIBR starts up a java process to execute the java class, etc.
   
   
4. FNDLIBR catches the exit codes from the executable of the concurrent program and updates the statuses in FND_CONCURRENT_REQUESTS accordingly - PHASE_CODE=C (Completed) and STATUS_CODE = C (Normal), G (Warning) or E (Error).
   
   
5. FNDLIBR checks if the concurrent request has a schedule and needs to be resubmitted. If yes – it resubmits a new concurrent request with the same parameters.

But what happens if the FNDLIBR process crashes, terminates or gets killed while it’s running a concurrent request? Who takes care of the statuses in FND_CONCURRENT_REQUESTS table and how the request is resubmitted if the concurrent manager process is not there anymore? It appears the Internal Concurrent Manager (ICM) takes care of these tasks. It checks the running requests periodically (every two minutes by default) and if it finds any that are missing the concurrent manager process and the DB session, it updates the statuses for the concurrent request and also resubmits it if it has a schedule. This action is followed by a log entry in the ICM log file: [sourcecode highlight="4"] Process monitor session started : 17-JUL-2013 04:24:24 Found running request 5829148 attached to dead manager process. Setting request status to completed. Found dead process: spid=(15160), cpid=(2032540), ORA pid=(35), manager=(0/0) Starting STANDARD Concurrent Manager : 17-JUL-2013 04:24:25 Process monitor session ended : 17-JUL-2013 04:24:25 [/sourcecode] Interesting to note, if the Internal Concurrent Manager is terminated at the same time with the manager process and is restarted later by the reviver process or by running “adcmctl.sh start” manually, the ICM performs the same check of running requests as part of the startup sequence, but this time it restarts the request instead of terminating and resubmitting it.

The log of the ICM contains the following lines: [sourcecode highlight="2"] Found running request 5829146 attached to dead manager process. Attempting to restart request. [/sourcecode] The concurrent request is started again with exactly the same request_id as the previous time it was terminated, and the log file of the request will contain information from 2 executions – the 1 ^st which didn’t complete and then the 2 ^nd which probably completed. I think this scenario is very confusing and instead of restarting the request it should better be terminated and a new one should be submitted. Let’s get back to the problem with cmclean.sql! The worst thing that can be done is running cmclean.sql after the crash of the concurrent processing node before starting up the concurrent managers. Why? Because cmclean.sql cleans up data in FND_CONCURRENT_REQUESTS by executing one simple update statement to change the phase and status of any “Running” or “Terminating” request to “Completed/Error”: [sourcecode language="sql"] UPDATE fnd_concurrent_requests SET phase_code = 'C', status_code = 'E' WHERE status_code ='T' OR phase_code = 'R'; [/sourcecode] Cmclean.sql does not resubmit the request if it has a schedule. Execute it and you risk to lose some scheduled programs without any warning.

Similarly – never run cmclean.sql if you stopped the concurrent managers using “adcmctl.sh abort” or “kill -9” on concurrent manager processes to speed up the shutdown procedure. There’s the same risk to lose some scheduled requests. Despite the risks, cmclean.sql is still a useful tool in case concurrent managers don’t come up after a failure or there are some stale data that is otherwise not cleaned up. But please, be careful when you run it! Check closely the list of requests reported in the following section of the outputs from cmclean.sql, because these requests have to be resubmitted manually if they had schedules. [sourcecode] -- Updating any Running or Terminating requests to Completed/Error Request ID Phase Status ---------- ------ ------ 6607 R W 6700 R W 893534056 R R 3 rows updated. [/sourcecode]

"Concurrent Manager Recovery" wizard is even worse! (Added on Jul 21, 2013)

1. I scheduled 2 concurrent programs ("CP Java Regression Test" and "CP PLSQL Regression Test") to restart in 1 minute after the previous execution completes. These are simple test concurrent programs which sleep for some time and then complete.
2. I made sure both programs were running and terminated all concurrent manager process and DB sessions for these concurrent programs.
3. The termination of the processes and sessions left the rows in FND_CONCURRENT_REQUESTS with PHASE_CODE=R and STATUS_CODE=R
4. I executed the "Concurrent Manager Recovery" wizard which fixed the status codes of the concurrent manager processes, but didn't touch the statuses of the concurrent requests - I thought this was a good thing (I expected the ICM to clean up the statuses and resubmit the requests at its startup phase)
5. I started up the concurrent managers, but ICM didn't clean up the 2 stale records in FND_CONCURRENT_REQUESTS table. The 2 requests appeared as they would be running, while in fact they didn't have any OS processes or DB sessions.

So what should we do to clean up the concurrent processing tables after crashes or cloning? (Added on Jul 21, 2013)

P.S. The description of the behavior of ICM in this blog post is a result of investigation performed on R12.1.3. I believe it behaves the same way in R12.1 and probably even in R12.0 and 11i, but I didn’t check. MOS Article ID 134007.1 which contains the cmclean.sql script is valid for Applications versions 10.7 to 12.1.3 – be careful when using it independently from the version of your e-Business Suite installation.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?