Enable/Disable JOBS during Maintenance

Enable/Disable JOBS during Maintenance

Contents
___________________________________________________________________________________________________

Disable Before Maintenance

1. Capture values
2. Disable ALL DBMS_JOBS
3. Disable ALL DBMS_SCHEDULER JOBS

Enable After Maintenance

4. Enable ALL DBMS_JOBS
5. Enable ALL DBMS_SCHEDULER JOBS
___________________________________________________________________________________________________

Disable Before Maintenance


1. Capture values

-- For DBMS_JOBS

SYS @ CDB2 > show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     4000  <-----

SYS @ CDB2 >

-- FOR DBMS_SCHEDULER JOBS

SYS @ CDB2 > COL ATTRIBUTE_NAME FOR A40
SYS @ CDB2 > COL VALUE FOR A60
SYS @ CDB2 > select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

ATTRIBUTE_NAME                           VALUE
---------------------------------------- ------------------------------------------------------------
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY                              30
DEFAULT_TIMEZONE                         Europe/Vienna
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION                  NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
LAST_OBSERVED_EVENT                      6F4E2CF66A235446E055000000000001::19440::SCHED::0001
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT                       0
CURRENT_OPEN_WINDOW

11 rows selected. <----

SYS @ CDB2 >


2. Disable ALL DBMS_JOBS

To disable jobs created by dbms_jobs set job_queue_processes to zero.

SYS @ CDB2 > alter system set job_queue_processes=0 scope=both sid='*';

System altered.

SYS @ CDB2 > show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0  <-------

SYS @ CDB2 >


3. Disable ALL DBMS_SCHEDULER JOBS

SYS @ CDB2 > exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

PL/SQL procedure successfully completed.

SYS @ CDB2 > 
SYS @ CDB2 > col ATTRIBUTE_NAME for a30
SYS @ CDB2 > col VALUE for a60
SYS @ CDB2 > set lines 180
SYS @ CDB2 > set pages 999
SYS @ CDB2 > select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

ATTRIBUTE_NAME                 VALUE
------------------------------ ------------------------------------------------------------
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY                    30
DEFAULT_TIMEZONE               Europe/Vienna
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION        NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
LAST_OBSERVED_EVENT            6F4E2CF66A235446E055000000000001::19440::SCHED::0001
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT             0
CURRENT_OPEN_WINDOW
SCHEDULER_DISABLED             TRUE  <---------

12 rows selected.  <-----

SYS @ CDB2 >

 

Enable After Maintenance


4. Enable ALL DBMS_JOBS

SYS @ CDB2 > alter system set job_queue_processes=4000 scope=both sid='*';

System altered.

SYS @ CDB2 > show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     4000 <-------

SYS @ CDB2 >


5. Enable ALL DBMS_SCHEDULER JOBS

SYS @ CDB2 > exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

PL/SQL procedure successfully completed.

SYS @ CDB2 > 
SYS @ CDB2 > col ATTRIBUTE_NAME for a30
SYS @ CDB2 > col VALUE for a60
SYS @ CDB2 > set lines 180
SYS @ CDB2 > set pages 999
SYS @ CDB2 > select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

ATTRIBUTE_NAME                 VALUE
------------------------------ ------------------------------------------------------------
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY                    30
DEFAULT_TIMEZONE               Europe/Vienna
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION        NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
LAST_OBSERVED_EVENT            6F4E2CF66A235446E055000000000001::19440::SCHED::0001
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT             0
CURRENT_OPEN_WINDOW

11 rows selected.  <------

SYS @ CDB2 >

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *