Disable DBMS Scheduler jobs on startup

My intention is to disable all dbms jobs prior opening the database.

Set job_queue_processes = 0 in pfile. Create the spfile on ASM or NFS.

Duplicate Database With RMAN Without Connecting To Target Database [ID 732624.1].

Manually, do

  1. Rman Controlfile restore
  2. Perform Manual restore and recovery

Startup the database in restricted mode (resetlogs upgrade)

alter database open resetlogs upgrade;

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

Also, run  the output of the following SQL to disable jobs explicitly:

SELECT 'EXEC dbms_scheduler.disable('||chr(39)|| owner ||'."'|| job_name || '"'||chr(39) ||',TRUE);'
     from dba_scheduler_jobs
     where owner not in ('SYS','SYSTEM','EXFSYS','ORACLE_OCM') order by owner;

Note that only connect AS SYSDBA is allowed when OPEN in UPGRADE mode. hence do the following:

@$ORACLE_HOME/rdbms/admin/utlirp.sql

@$ORACLE_HOME/rdbms/admin/utlrp.sql

shutdown immediate;

startup;
If startup do not happen due the error in identifying the controlfile, then

  1. check the new controfile names that has been created,
  2. update them in the PFILE,
  3. startup database using PFILE
  4. Recreate SPFILE
  5. shutdown immediate
  6. startup

Optional Steps:

  1. Rename the database using NID in the end, if required.
  2. Disable the archive log mode

Cheers. Have fun.

Advertisements

About mpoojari
Oracle Database/Applications Administrator

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: