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,

8 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
  2. I am curious to find out what blog system you have
    been using? I'm having some small security issues with my latest site
    and I'd like to find something more safe. Do you have any
    recommendations?

    ReplyDelete
    Replies

    1. Dear User,

      I have some basics ideas which I would like to share with you.
      Such as SQL Injection, Login redirection, Url settings and redirection.
      Try to debug yourself.

      Also, I don't have idea what type of website or blog you are using. But these are general guidance.
      Hope these may useful.

      Regards,
      ora-data Team

      Delete
  3. Greetings! Very helpful advice within this post! It's the little changes
    which will make the greatest changes. Many thanks for sharing!

    ReplyDelete
    Replies

    1. Dear User,

      Thank you very much...

      Regards,
      ora-data Team

      Delete
  4. Pretty nice post. I simply stumbled upon your blog and wished to mention that I've truly enjoyed
    browsing your weblog posts. In any case I'll be subscribing to your rss feed
    and I'm hoping you write again soon!

    ReplyDelete
    Replies

    1. Dear User,

      Thank you for your comment and interest , Please you can follow us on FB also.

      Regards,
      ora-data Team

      Delete

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