Friday, June 20, 2008

ORA-00600 solved....

The ORA-00600 from my previous post is solved. Also the notifications are being send again.
The 'ORA-00600 [kwqidrdq: loop], [0], [0], [0], [0], [], [], []' error was shown in the EMD_NOTIFICATION.QUEUE_READY.
Normally you receive this error wwhen records are deleted manually from queue tables, which is unsupported (also see note 1070715.6).
The problem was solved by executing the following statements in sql :

[oemprd01@amssys07 udump]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 19 08:43:53 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn / as sysdba

Connected.
SQL> show parameter aq

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1

SQL> alter system set aq_tm_processes=0;
System altered.

SQL> conn sysman
Enter password:
Connected.
SQL> delete from AQ$_mgmt_notify_qtable_I i 2
where not exists (select t.msgid from mgmt_notify_qtable t where i.msgid = t.msgid);
1 row deleted.
SQL> delete from AQ$_mgmt_notify_qtable_H h 2

where not exists (select t.msgid from mgmt_notify_qtable t where h.msgid = t.msgid);
1 row deleted.
SQL> delete from AQ$_mgmt_notify_qtable_T ti 2

where not exists (select t.msgid from mgmt_notify_qtable t where ti.msgid = t.msgid);
0 rows deleted.
SQL> commit;
Commit complete.

SQL> conn / as sysdba
Connected.
SQL> alter system set aq_tm_processes=1;
System altered.


After this the ORA-00600 error was gone, trace files were no longer generated in the udump directory and OEM notifications were being send again.
Only thing that isn't clear for me....Did the export/import of the sysman schema cause this problem ?
I think it did, but what could I have done to prevent it. Because I followed all the steps mentioned by Metalink.
Next time, check the queue status before exporting sysman.
select owner,name,queue_table,queue_type,enqueue_enabled,dequeue_enabled
from dba_queues;
Then queues can be controlled by
dbms_aqadm.stop_queue
dbms_aqadm.start_queue


3 comments:

Unknown said...

Thanks. You saved me time of creating an SR.

Bas Klaassen said...

No prolem,

that's what the blog is for... :)

aterrao said...

It has been very helpful.