11.2.0.3 upgrade ORA-27248: sys.dra_reevaluate_open_failures is running

I was doing a simple upgrade of Oracle RDBMS EE 11.2.0.2 to 11.2.0.3 using dbua.  It should have been a no brainer.  Except part way through I got the error ORA-27248: sys.dra_reevaluate_open_failures* is running.

I had to abort the upgrade which then meant I had to do a full rman recovery and start again.

The problem:  neither the pre-upgrade notes (or dbua) cover a check to ensure there are no scheduler jobs running.  Although even if you did check manually prior to hitting the 'yup upgrade' button, you could still be unlucky and the maintenance window start and the oracle internal jobs get going. 

Open migrate doesn't care, it allows these jobs to carry on running and the dbua doesn't check for them.

Why?  This was Oracle's answer:

If the database is running in restricted mode then no jobs will run (unless
you are using 11g and use the ALLOW_RUNS_IN_RESTRICTED_MODE attribute).
To check this use
SQL> select logins from v$instance ;
If logins is restricted you can disable the restricted mode using
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
ref: http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_sched.htm

I'm not sure why they think this would even begin to resolve this issue, infact if the upgrade was done in restructed mode then surely I wouldn't have had the issue.

The job sys.DRA_REEVALUATE_OPEN_FAILURES is created by default so that allow_runs_in_restricted_mode is false, the default is false.  It shouldn't run if the database is opened restricted mode.

So I must conclude that STARTUP UPGRADE is not putting the database into restricted mode and scheduled jobs will continue to run.

The pre-upgrade check notes make no mention of needing to do anything with scheduled jobs, but I have added a check into my process:

Select * from v_$scheduler_running_jobs;
Select * from dba_scheduler_running_jobs;
If there are jobs running then chances are dbua is going to abort *shrug*  probably *shrug*

After pressing Oracle for a while longer, they suggested this:

MY CAVEAT: DO NOT RUN SQL BELOW BEFORE READING AHEAD, DO NOT RUN THIS IF YOU HAVE THIS PROBLEM.

startup in restricted mode
startup restrict;
2. disable all windows
select 'exec DBMS_SCHEDULER.DISABLE('''||WINDOW_NAME||''');' from ALL_SCHEDULER_WINDOWS where ENABLED='TRUE';
3. close all windows
select 'exec DBMS_SCHEDULER.CLOSE_WINDOW('''||WINDOW_NAME||''');' from ALL_SCHEDULER_WINDOWS where ACTIVE='TRUE';
4. stop running & disable jobs
CASE WHEN state !='RUNNING' THEN 'begin' || CHR(10) || 'DBMS_SCHEDULER.STOP_JOB ('''||OWNER||'.'||JOB_NAME||''', TRUE); ' || CHR(10) || 'EXCEPTION WHEN OTHERS THEN NULL;' || CHR(10) || 'END; ' || CHR(10) || '/' || CHR(10) || 'exec DBMS_SCHEDULER.DISABLE ('''||OWNER||'.'||JOB_NAME||''', TRUE );'
ELSE
'exec DBMS_SCHEDULER.DISABLE ('''||OWNER||'.'||JOB_NAME||''', true);'
end

I'm  not convinced this is a good approach.  If you disable jobs you need to reenable them later. No?  So which jobs did you just disable?  No, I've no idea either?!  Which Windows did you just disable? Again, I've no idea! Which of your windows were already disabled?

Teflon Oracle Support.

I pressed Oracle again and they agreed that there needs to be a preupgrade process to check for running jobs before upgrading.

Until they update their documentation, I recommend you run :

Select * from v_$scheduler_running_jobs;
Select * from dba_scheduler_running_jobs;

And don't run your upgrade if you have running jobs.  If you do have running jobs and want to get rid of them then yes, I would do:

shutdown,
startup restrict,
select WINDOW_NAME, ENABLED  from ALL_SCHEDULER_WINDOWS where ENABLED='TRUE';

Make a note of those windows and disable them.

exec DBMS_SCHEDULER.DISABLE('WINDOW_NAME'); DBMS_SCHEDULER.DISABLE('WINDOW_NAME');
              at the end of the upgrade you need to remember to re-enable them.
              exec DBMS_SCHEDULER.ENABLE('WINDOW_NAME');


 If there are any Active windows you need to close them:

select WINDOW_NAME, ACTIVE from ALL_SCHEDULER_WINDOWS where ACTIVE='TRUE';

Make a note of those windows and disable them.

exec DBMS_SCHEDULER.CLOSE_WINDOW('WINDOW_NAME');WINDOW_NAME');
                     I expect these would become active again when get to their next active time and the window is
                     enabled.

check whether any jobs are running:

Select * from v_$scheduler_running_jobs;
Select * from dba_scheduler_running_jobs;

You could at this point stop the jobs but I would expect a restart to stop the jobs and because the window is disbled they ought to not restart. (I need to find time to test this.)

Shutdown immediate;
startup normal;

Select * from v_$scheduler_running_jobs;
Select * from dba_scheduler_running_jobs;

this must return no rows

and now you can follow the upgrade process.

I'm totally bemused, confused why no one else appears to have run into this problem or if they have, why have they not put something 'out there' in discussion.  I am still wondering whether I'm barking up the wrong tree SO IF YOU DO HIT THIS ISSUE PLEASE RAISE YOUR OWN SR AND GET SUPPORT FROM ORACLE but you may want to use these notes as a useful pointer.


*If you care the description for dra_reevaluate_open_failures is 're-evaluates open failures for the Database Recovery Advisor (used for assistance in case of loss or corruption of datafiles, redo log files or controlfiles).  Executes dbms_ir.reevaluateopenfailures.'  This isn't really relevant to this post as it could potentially occur with any job.

Read more...

ORA-04063: SYS.SCHEDULER$_EVENT_QUEUE has errors

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.

Read more...

Oracle 8.1.7.4 to 9.2.0.8

Today I moved an old production database from 8.1.7.4 to 9.2.0.8, and I hope this is the end of v8 for me.

It has become increasingly difficult to remember the quirks of 8.1.7, what was introduced at 9i and 10g and that ever nagging worry that if something went wrong with the database I was up the creek (and never mind the paddle, I hadn't even got a canoe).

I took the sensible approach and created a shiney new 9.2.0.8 instance and did an export, import.  Simples.

With a few additional grants and synonyms to be rebuilt followed by statistic setting up, it was pretty much all done within an hour.

Interestingly, the only reason to upgrade it was because the windows 2000 server it was sat on was dying a death and it had to move to another server.  Too good an opportunity to miss really.

But why only 9i?  That's what the application that is using the database is certified to!  Sigh

I think it says something fairly good for Oracle that 8.1.7.4 was good enough to just keeping on chugging along, doing what it was supposed to be doing without any problems.

So now it has moved from a very out of support database version to an out of support version.  Sigh again.

It is sat behind a firewall, it only has background services attaching to it, no 'real' users.  So the security I have put in place will be adequate for what it is but it falls a long way short of the tidy 11.2 databases I am now running in other areas.

Read more...