Unable to Unschedule Propagation. Streams Queue is Invalid ORA-24085


Symptoms

Exec dbms_aqadm.unschedule_propagation('STRMADMIN.Q_PLN40_DTD_TRADE','PSGDTD40.WORLD')

ERROR at line 1:
ORA-24080 : unschedule_propagation pending for QUEUE STRMADMIN.Q_PLN40_DTD_TRADE
and DESTINATION PSGDTD40.WORLD
ORA-06512 : at "SYS.DBMS_PRVTAQIP", line 1472
ORA-06512 : at "SYS.DBMS_PRVTAQIP", line 1517
ORA-06512 : at "SYS.DBMS_AQADM", line 806
ORA-06512 : at line 1

Cause

Streams Queue is invalid.
It seems to be invalidated by dropping a large number of table rules manually.

When we try to stop the queue using dbms_aqadm.stop_queue, we get the following error:
ORA-24085 : operation failed, queue Q_PLN40_DTD_TRADE is invalid

Alert log keeps showing the same error as well.

Solution

To implement the solution, please execute the following steps:

1. Shutdown the Database:
2. Set job_queue_processes=0, and aq_tm_processes=0
3. Start the database with restrict option.
4. Run the following command for each queue which is invalid:

In 9.2

execute dbms_aqadm_syscalls.kwqa_3gl_validatequeue('','','');

for eg:

execute dbms_aqadm_syscalls.kwqa_3gl_validatequeue('APPLSYS','WF_DEFERRED_QUEUE_M','WF_DEFERRED_TABLE_M');

In 10g

execute dbms_aqadm_syscalls.kwqa_3gl_validatequeue('',''); 
for eg:
execute dbms_aqadm_syscalls.kwqa_3gl_validatequeue('APPLSYS','WF_DEFERRED_QUEUE_M');

5. commit;
6. shutdown the database.
7. Now set job_queue_processes back to normal value and take aq_tm_processes out of
    the init.ora/spfile
8. Start the database normally


Validating the queue resolved the issue and we could unschedule propagation finally:

exec dbms_aqadm.unschedule_propagation('STRMADMIN.Q_PLN40_DTD_TRADE','PSGDTD40.WORLD')
pl/sql procedure completed successfully.

No comments:

Post a Comment