ORA-04063: SYS.SCHEDULER$_EVENT_QUEUE has errors

>> Thursday, 24 November 2011

I've been having a bit of a nightmare with what should have been a straight forward 11.2.0.2 upgrade from 10.2.0.4.

The dbua was falling over at about 64% with:

Problem Description:
ERROR at line 1:
ORA-04063: SYS.SCHEDULER$_EVENT_QUEUE has errors
ORA-06512: at "SYS.DBMS_AQADM", line 270
ORA-06512: at line 2

This seems to be linked to me using in 10.2 the mailjobevent and job_notification packages supplied by Oracle.

My final solution was (**PLEASE SEE UPDATE AT BOTTOM OF POST **):

as sys
drop procedure mailjobevent;
@?/rdbms/admin/catnosch.sql
@?/rdbms/admin/catsch.sql
@?/rdbms/admin/utlrp.sql
select * from dba_objects where status <> 'VALID';

You need zero rows returned before starting an upgrade.

Remember to always ensure you have a good backup before starting,  test on a non critical system before moving to one where it matters if you screw up and follow the upgrade prerequistes in the Oracle documentation.

utl_mail needs work at 11g, as it is different to 10g to I guess that's why my scheduled jobs that use the old packages were unhappy but if I come up with a comprehensive answer to what is the absolute root cause is I will update here, in the meantime I am posting this because I was getting nothing useful in any searches for the error.

*******25/11/2011 UPDATE ******

Please see this comment from Rahul:

I had run in to the same issue but oracle gave me resolution after work around and rerunning the catupgrd.sql went fine.
This "ORA-04063: SYS.SCHEDULER$_EVENT_QUEUE has errors" is Intermittent issue/defect during the upgrade.
if you encounter ,you can follow the workaround suggested.
This is fixed in 11.2.0.2

Work around:

exec dbms_aqadm_sys.validate_queue('SYS', 'SCHEDULER$_EVENT_QUEUE');
.
after this, exec dbms_aqadm.start_queue(queue_name =>'sys.scheduler$_event_queue');
and rerunning the catupgr.sql


I have not had the opportunity to try this but it would be worth giving it a go in your test system before resorting to dropping your mailjobevents like I did.

0 comments:

  © Blogger template Simple n' Sweet by Ourblogtemplates.com 2009

Back to TOP