Gather Stats ORA-0000: normal, successful completion

ERROR:
Gather Stats ORA-0000: normal, successful completion
Gather statistics completed with error.
SOLUTION:
a. Check to see what records will be
backed up on the wf_queue_temp_jms_table backup table:
set linesize
155;
set pagesize
200;
set verify
off;
column
corrid format a50;column state format a12;
select
wfjd.corr_id corrid, msg_state state, count(*) COUNT
from
applsys.aq$wf_java_deferred wfjd
where
msg_state IN(‘READY’, ‘WAIT’)
group by
corr_id, wfjd.msg_state;
b. Shutdown the Workflow Agent Listener Service
and backup the records on the WF_JAVA_DEFERRED queue to the
apps.wf_queue_temp_jms_table backup table.
System Administrator
> Oracle Applications Manager > Workflow > Service Components >
Workflow Agent Listener Service

sqlplus apps/<apps pwd> @wfaqback.sql <Agent Name>

For Example:

sqlplus apps/apps @$FND_TOP/sql/wfaqback.sql WF_JAVA_DEFERRED

c. Make sure all the records are in the
wf_queue_temp_jms_table table.
set linesize 155;
set pagesize 200;
set verify off;

column corrid format a50;
column queue format a16;

select CORR_ID corrid, QUEUE queue, count (*)
from apps.wf_queue_temp_jms_table
group by CORR_ID, QUEUE;

d. Set aq_tm_processes =0:
alter system set
aq_tm_processes=0;

e. Note the name of the tablspace
containing index on CORRID that will need to be recreated later:
SELECT index_name,
tablespace_name
FROM all_indexes
WHERE index_name = ‘WF_JAVA_DEFERRED_N1’;
f. Drop the WF_JAVA_DEFERRED queue and
queue_table: 
declare

begin
dbms_aqadm.stop_queue(queue_name => ‘APPLSYS.WF_JAVA_DEFERRED’, wait =>
FALSE);
end;
/

declare

begin
dbms_aqadm.drop_queue_table(queue_table => ‘APPLSYS.WF_JAVA_DEFERRED’, force

=> TRUE);
end;
/

g. Recreate the WF_JAVA_DEFERRED queue.
sqlplus
<APPSusr>/<APPSpwd> @wfbesqc.sql <FNDusr> <FNDpwd>

For Example:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfbesqc.sql APPLSYS APPS

h. Add the subscribers.

sqlplus
APPSusr/<APPSpwd> @wfbesqsubc.sql <FNDusr> <FNDpwd>

Example Syntax:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfbesqsubc.sql APPLSYS APPS

i. Recreate the index (Please
ignore any ORA-00955 errors about object already exist as this adds index for
other objects.)
:

sqlplus
APPSusr/<APPSpwd> @FND_TOP/patch/115/sql/wfbesqidxc.sql APPLSYS APPS
tablespace_name

Example Syntax:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfbesqidxc.sql APPLSYS APPS
APPS_TS_QUEUES


j. Put the data for the WF_JAVA_DEFERRED
back into the queue.
sqlplus apps/<apps
pwd> @wfaqrenq.sql <Agent Name>

For Example:

sqlplus apps/apps @$FND_TOP/sql/wfaqrenq.sql WF_JAVA_DEFERRED


k. Confirm that all records are back on
the queue.
set linesize 155;
set pagesize 200;
set verify off;

column corrid format a50;
column state format a12;

select wfjd.corr_id corrid, msg_state state, count(*) COUNT
from applsys.aq$wf_java_deferred wfjd
where msg_state IN(‘READY’, ‘WAIT’)
group by corr_id, wfjd.msg_state;


l. Set aq_tm_processes =>1 and
restart the Workflow Agent Listener Service.
alter system set
aq_tm_processes=1;

m. Start the Workflow Agent Listener
Service and confirm it is now processing the events on the queue.
System Administrator
> Oracle Applications Manager > Workflow > Service Components >
Workflow Agent Listener Service
set linesize 155;
set pagesize 200;
set verify off;

column corrid format a50;
column state format a12;

select wfjd.corr_id corrid, msg_state state, count(*) COUNT
from applsys.aq$wf_java_deferred wfjd
where msg_state IN(‘READY’, ‘WAIT’)
group by corr_id, wfjd.msg_state;

  • July 11, 2017 | 26 views
  • Comments