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.