Is cmclean.sql safe to run in EBS R12.x?

There are bit and pieces for information on "cmclean.sql". Here, this is an attempt to place all information related to cmclean.sql in one place. 

There is a script, cmclean.sql, most Apps DBA has run atleast few dazon times in their life time to fix the concurrent manager start up issues either during maintenance activities or Migration/Upgrade activities. This script was initally develped to clean source data during EBS installation and not for general maintenance or any other activities.

Running CMCLEAN.SQL

1. Will not update/clean any pending requests.
2. It updates only the requests which are in either RUNNING OR TERMINATING status to COMPLETED with ERROR. But, here is the catch. It doesn't reschedule requests which have a periodic schedule attached to them.

So apps DBAs, who are aware of the issue, spend atleast 1 to 2 hours after every major maintenance activity on EBS, following up with Business people, to get these cmclean terminated request rescheduled.

Some DBAs, who are not aware of this limitation, end up with system which has some jobs missing and might cause the business team upset or spend good amount of time investigating whats wrong with the system. 

As per the MOS Doc ID 2183718.1; The script CMCLEAN.SQL is NOT recommended or supported with E-Business Suite (EBS) Release 12.

If you encounter such situations, either use cpadmin.sh (adadmin.sh style utility) or use the Concurrent Manager Recovery Wizard. 

Concurrent Processing Command-Line Utility

The command-line utility cpadmin consolidates various existing utilities for concurrent processing into a single menu-based utility. This adadmin-style utility can be used for multiple tasks, including:

• Manager Status: Use this option to display the statuses of all managers.

• Clean CP Tables: Use this option to clean up the concurrent processing tables. This utility replaces cmclean.sql.

• Set Manager Diagnostics: Turn diagnostics on or off for individual managers with this option.

• Manager Control: Use this option to send a request such as start, stop, or verify to an individual manager.

• Rebuild Manager Views: Use this option to rebuild the FND_CONCURRENT_WORKER_REQUEST and FND_CONCURRENT_CRM_REQUESTS views.

• Move Request Files: Change request log and output file locations with this option.

• Analyze Request: Use this option to analyze a concurrent request.

Complete the steps below to run the command-line utility and its maintenance tasks.

1. Set the environment.

You must set the environment in order to apply the environment variables that define your system. This task is common to many utilities. See Setting the Environment, Oracle E-Business Suite Maintenance Guide for the preparatory steps.

2. From any directory, start cpadmin with this command:

$ cpadmin.sh

The utility starts and prompts you for the APPS password (required).

3. Respond to prompts.

Supply the information requested by cpadmin prompts. Prompts unique to an option are described with the option.

When you complete the prompts, the Main Menu appears.

4. Choose one of the tasks listed above.

Enter 2 if you are running this utility in place of cmclean.sql

Read the above instructions and proceed accordingly. 

Now this the table has been updated for deactivated and terminated manager process. 

It also checks the runaway process for the Managers. 

Now, You have successfully fixed the concurrent manager issues. 

5. Exit the cpadmin utility.

For more information, please visit the Oracle Documentation here: http://docs.oracle.com/cd/E26401_01/doc.122/e22953/T174296T575591.htm#8159578

Concurrent Manager Recovery Wizard

NOTE: The Concurrent Managers have to be stopped before the Wizard is run. Also insure that listeners are not stopped before the managers are stopped.

Please run the Concurrent Manager Recovery wizard to address Concurrent Manager failures within the Oracle Applications Manager.

Using the Concurrent Manager Recovery wizard is the method to clear the errors upon bringing the Internal Manager back up.

For Concurrent Internal Manager failures, it is recommended to run the Concurrent Manager Recovery feature using the Oracle Applications Manager. This feature should be used for recovering from when the Internal Manager won't start. This is accessed from the Troubleshooting wizards available within Oracle Applications Manager logged in as the sysadmin userid. 

Navigate:

Oracle Applications Manager > Concurrent Managers OR Concurrent Requests > Site Map > Diagnostics and Repair > Concurrent Manager Recovery.

Reference:

Is the Script CMCLEAN.SQL Supported in EBS R12? (Doc ID 2183718.1)

Concurrent Processing - Command-Line Utility OR Recovery Wizard (Doc ID 134007.1)