Sunday, October 21, 2007

How to Clean 11i Apps Concurrent Manager Tables

Print this post

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

2 comments:

Murali said...

Thanks. This article helped me with my TEST environment clean up.

Anonymous said...

Hi,

I think this is good script.
But I have one question for the oracle 11.5.9 concurrent manager tables cleanup.
So Can we use the cmclean.sql for the concurrent manager tables cleanup?

Thanks
OKY