CMCLEAN.SQL

rongren

CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables [ID 134007.1]
		Modified 28-DEC-2009     Type SCRIPT     Status PUBLISHED

Checked for relevance on 07-May-2009Applications Install 10.7 to 12.0PURPOSE-------This document provides a reference to self service Cleaning theConcurrent Manager tables. SCOPE & APPLICATION-------------------This reference is made available as a tool to assist DBA's andSystem AdministratorsNon Destructive Script to Clean Concurrent Manager Tables ---------------------------------------------------------*** ATTENTION 11.5.7+ OAM Users ***Please run the Concurrent Manager Recovery feature to address any ConcurrentManager / Concurrent Processing issues within the Oracle Application Manager.Using the Concurrent Manager Recovery wizard is the method to clear the errors upon bringing the internal manager back up.The cmclean script can still be used for 11.5.7+ instances provided the managers are down andno FNDLIBR processes are still running.For Concurrent Internal Manager failures, it is recommended to run the Concurrent Manager Recoveryfeature 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 applications logged in as the sysadmin userid. Navigate:Oracle Applications Manager > Concurrent Managers OR Concurrent Requests > Site Map > Diagnosticsand Repair > Concurrent Manager RecoveryFor information on the Concurrent Manager Recovery feature, please reference the Oracle ApplicationsSystem Administrator's Guide - Maintenance provides information for frequent tasks such as monitoring your system with Oracle Applications Manager, administering Oracle E-Business Suite Secure EnterpriseSearch, managing concurrent managers and reports, using diagnostic utilities including logging, managing profile options, and using alerts. *** ATTENTION 11.5.7+ OAM Users ***NOTE:  This script works with 10.7, 11.0, 11.5 & 12 Applications.NOTE:  Ensure that No FNDLIBR processes are running as detailed within  the Troubleshooting Note:104541.1 and that the Concurrent Manager  is down.  You need to issue a commit once the script is run for the changes  to take effect.NOTE:  Copy from the first REM statement below to last REM statement of this   document and save as: cmclean.sql==================================================================== REMREM FILENAMEREM   cmclean.sqlREM DESCRIPTIONREM   Clean out the concurrent manager tablesREM NOTESREM   Usage: sqlplus  @cmclean REM   REMREM   $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $REM            REMREM +======================================================================+set verify off;set head off;set timing offset pagesize 1000column 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'WHENEVER SQLERROR EXIT ROLLBACK;DOCUMENT   WARNING : Do not run this script without explicit instructions             from Oracle Support    *** Make sure that the managers are shut down     ***    *** before running this script                    ***                  *** If the concurrent managers are NOT shut down, ***   *** exit this script now !!                       ***#accept answer prompt 'If you wish to continue type the word ''dual'': 'set feed offselect null from &answer;set feed onREM     Update process status codes to TERMINATEDpromptprompt  ------------------------------------------------------------------------prompt  -- Updating invalid process status codes in FND_CONCURRENT_PROCESSESset feedback offset head onbreak on managerSELECT  concurrent_queue_name manager,         concurrent_process_id pid,        process_status_code pscodeFROM    fnd_concurrent_queues fcq, fnd_concurrent_processes fcpWHERE   process_status_code not in ('K', 'S')AND     fcq.concurrent_queue_id = fcp.concurrent_queue_idAND     fcq.application_id = fcp.queue_application_id;set head offset feedback onUPDATE  fnd_concurrent_processesSET     process_status_code = 'K'WHERE   process_status_code not in ('K', 'S');REM     Set all managers to 0 processes promptprompt  ------------------------------------------------------------------------prompt  -- Updating running processes in FND_CONCURRENT_QUEUESprompt  -- Setting running_processes = 0 and max_processes = 0 for all managersUPDATE  fnd_concurrent_queuesSET     running_processes = 0, max_processes = 0;REM     Reset control codespromptprompt  ------------------------------------------------------------------------prompt  -- Updating invalid control_codes in FND_CONCURRENT_QUEUESset feedback offset head onSELECT  concurrent_queue_name manager,        control_code ccodeFROM    fnd_concurrent_queuesWHERE   control_code not in ('E', 'R', 'X')AND     control_code IS NOT NULL;set feedback onset head offUPDATE  fnd_concurrent_queuesSET     control_code = NULLWHERE   control_code not in ('E', 'R', 'X')AND     control_code IS NOT NULL;REM     Also null out target_node for all managersUPDATE  fnd_concurrent_queuesSET     target_node = null;REM     Set all 'Terminating' requests to Completed/ErrorREM     Also set Running requests to completed, since the managers are downpromptprompt  ------------------------------------------------------------------------prompt  -- Updating any Running or Terminating requests to Completed/Errorset feedback offset head onSELECT  request_id request,        phase_code pcode,        status_code scodeFROM    fnd_concurrent_requestsWHERE   status_code = 'T' OR phase_code = 'R'ORDER BY request_id;        set feedback onset head offUPDATE  fnd_concurrent_requestsSET     phase_code = 'C', status_code = 'E'WHERE   status_code ='T' OR phase_code = 'R';REM     Set all Runalone flags to 'N'REM     This has to be done differently for Release 10promptprompt  ------------------------------------------------------------------------prompt  -- Updating any Runalone flags to 'N'promptset serveroutput onset feedback offdeclare        c         pls_integer := dbms_sql.open_cursor;        upd_rows  pls_integer;        vers      varchar2(50);        tbl       varchar2(50);        col       varchar2(50);        statement varchar2(255);begin        select substr(release_name, 1, 2)        into   vers        from fnd_product_groups;        if vers >= 11 then           tbl := 'fnd_conflicts_domain';           col := 'runalone_flag';        else           tbl := 'fnd_concurrent_conflict_sets';           col := 'run_alone_flag';        end if;        statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';        dbms_sql.parse(c, statement, dbms_sql.native);        upd_rows := dbms_sql.execute(c);        dbms_sql.close_cursor(c);        dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');end;/prompt prompt  ------------------------------------------------------------------------prompt  Updates complete.prompt  Type commit now to commit these updates, or rollback to cancel.prompt  ------------------------------------------------------------------------promptset feedback onREM  <= Last REM statment -----------------------------------------------------Disclaimer EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMSALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOTLIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULARPURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTSTHAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE ORRELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUREXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OROTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVENO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THATRESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANYTIME WITHOUT NOTICE.Limitation of Liability IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION INCONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.