DBA Hub

📋Steps in this guide1/6

Enable/Disable JOBS during Maintenance

Enable/Disable JOBS during Maintenance Contents

oracle clusteringintermediate
by OracleDba
12 views
1

Overview

Enable/Disable JOBS during Maintenance Contents Disable Before Maintenance Before Maintenance
2

Section 2

Enable After Maintenance Disable Before Maintenance 1. Capture values
3

Section 3

2. Disable ALL DBMS_JOBS 3. Disable ALL DBMS_SCHEDULER JOBS

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 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 >

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 >
4

Section 4

SCHEDULER_DISABLED TRUE <--------- Enable After Maintenance 4. Enable ALL DBMS_JOBS

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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 >
5

Section 5

5. Enable ALL DBMS_SCHEDULER JOBS 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. Caution

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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 >

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 >
6

Section 6

: 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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!