Sunday, 11 December 2016

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.
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:







 

Thanks,

2 comments:

  1. Hi,

    Could you please confirm before running cmclean.sql we need to stop the services or not? if yes why and if not why?

    Thanks
    RAj

    ReplyDelete
    Replies

    1. Hi Raj,

      1. First,I would like to confirm one thing, this also depend on situation when you are running.
      2. In almost cases, we need to shut down the APPS Tier.

      3. Now a days cmclean is not anymore in r12.2.
      4. Running cmclean.sql may effect your custom code also for any managers.

      Above functions are also mentioned.
      Hope this may clear.

      Thanks for visiting, please let us know for any concerns.

      Regards,
      ora-data Team.

      Delete

Thank you for your comments and suggestions. It's good to talk you.