CMCLEAN.sql

Hi Friends,

When we do cloning, almost time we use cmclean.sql at the end. And for this we need a query which today I would like to share with you all.

We use cmclean.sql to cleanup the running and pending requests , If we stop concurrent managers using abort options then concurrent requests will be in running state Then when next time we will start the concurrent manager the processes will not start properly, which may cause some errors.

Specially, CMCLEAN.sql will update the following below tables:

1) FND_CONCURRENT_QUEUES

2) FND_CONCURRENT_PROCESSES

3) FND_CONCURRENT_REQUESTS

4) FND_CONFLICTS_DOMAIN

5) FND_CONCURRENT_CONFLICT_SETS

Please find the below steps to run the cmclean.sql.

Steps:
1. Copy this below script in the Wordpad and save as cmclean.sql. There should now be any space any junk char.

2. Then ftp through winSCP to the server.

3. Goto that location, where you have kept the cmclean.sql file.

4. connect to sqlplus

5. Follow below as:

SQL>@cmclean.sql;

SQL SCRIPTS:

============

REM
REM FILENAME
REM cmclean_query.sql
REM DESCRIPTION
REM Selects data on the concurrent manager tables the cmclean script updated.
REM
REM NOTES
REM Usage: sqlplus @cmclean_query.sql
REM
REM
REM $Id: cmclean_query.sql,v 1.0 2014/04/01 15:00:00 bkerr Exp $
REM
REM
REM +======================================================================+

spool cmclean_query.txt
set verify off;
set head off;
set timing off
set pagesize 1000

column manager format a20 heading 'Manager short name'
column pid heading 'Process id'
column pscode format a12 heading 'Status code'
column ccode format a12 heading 'Control code'
column request heading 'Request ID'
column pcode format a6 heading 'Phase'
column scode format a6 heading 'Status'


set feed on


REM Select process status codes that are TERMINATED

prompt
prompt ------------------------------------------------------------------------

prompt -- Select invalid process status codes in FND_CONCURRENT_PROCESSES
set feedback off
set head on
break on manager

SELECT concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE process_status_code not in ('K', 'S')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id;


REM List invalid control codes

prompt
prompt ------------------------------------------------------------------------

prompt -- Listing invalid control_codes in FND_CONCURRENT_QUEUES
set feedback off
set head on
SELECT concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;


REM List Target Node for All Managers

prompt
prompt ------------------------------------------------------------------------


REM Identify the target_node for all managers
prompt -- Identify the target_node for all managers
set feedback off
set head on
select target_node from fnd_concurrent_queues;


REM List Running or Terminating requests

prompt
prompt ------------------------------------------------------------------------

prompt -- Select Running or Terminating requests
set feedback off
set head on
SELECT request_id request,
phase_code pcode,
status_code scode
FROM fnd_concurrent_requests
WHERE status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;


prompt
prompt ------------------------------------------------------------------------

prompt Review complete.
prompt ------------------------------------------------------------------------

prompt
spool off
set feedback on

If you are facing any type of problem, kindly contact us @https://ora-data.blogspot.in/ or write to us, we will send you file through email.

Some more useful links:

Important SQLScripts for DBA

Blocking Session

R12.1 Vs R12.2

R12.2 Concepts &Architecture

Jobs Tips

Career Tips

 

Thanks,