How to Clean 11i Apps Concurrent Manager Tables

This can be used as a method to clear the errors upon bringing the internal manager back up.

Use this method for 11.5.7+ instances provided the managers are down and no FNDLIBR processes are
still running.
 
FIRST
Update process status codes to TERMINATED
Updating invalid process status codes in FND_CONCURRENT_PROCESSES

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;
UPDATE  fnd_concurrent_processes
SET     process_status_code = 'K'
WHERE   process_status_code not in ('K', 'S');
commit;
SECOND
Set all managers to 0 processes
Updating running processes in FND_CONCURRENT_QUEUES
Setting running_processes = 0 and max_processes = 0 for all managers

UPDATE  fnd_concurrent_queues
SET     running_processes = 0, max_processes = 0;
commit;
THIRD
Reset control codes
Updating invalid control_codes in FND_CONCURRENT_QUEUES
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;

UPDATE  fnd_concurrent_queues
SET     control_code = NULL
WHERE   control_code not in ('E', 'R', 'X')
AND     control_code IS NOT NULL;
commit;
FOURTH
Also null out target_node for all managers
UPDATE  fnd_concurrent_queues
SET     target_node = null;
commit;
FIFTH
Set all 'Terminating' requests to Completed/Error
Also set Running requests to completed, since the managers are down
Updating any Running or Terminating requests to Completed/Error
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;
    
UPDATE  fnd_concurrent_requests
SET     phase_code = 'C', status_code = 'E'
WHERE   status_code ='T' OR phase_code = 'R';
commit;
SIXTH
Set all Runalone flags to 'N'Updating any Runalone flags to 'N'
set serveroutput on
set feedback off
declare
      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;
/
commit;
Now you can start with your Concurrent Managers...!!!!!!!!
 
 
**************************************************************************************************** 

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...