|
scheduler рестарт убитого джоба
#39852711
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Новосибирск
Сообщения: 1 757
|
|
microbash,
SYS.SCHEDULER$_EVENT_QUEUE + event-based job
setup:
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.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
-- watchdog job
SQL> create table job_watchdog_log(
2 audsid int,
3 log_date date default sysdate);
Table created.
SQL>
SQL> create or replace procedure job_restart(
2 msg sys.scheduler$_event_info)
3 is
4 begin
5 insert into job_watchdog_log(audsid) values (sys_context('userenv', 'sessionid'));
6 commit;
7
8 dbms_scheduler.run_job(
9 job_name=> dbms_assert.enquote_name(dbms_assert.schema_name(msg.object_owner), false) ||
10 '.' ||
11 dbms_assert.enquote_name(msg.object_name),
12 use_current_session=> false);
13 end;
14 /
Procedure created.
SQL>
SQL> exec dbms_scheduler.add_event_queue_subscriber('job_watchdog')
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_scheduler.create_program( -
> program_name=> 'SCP_WATCHDOG', -
> program_action=> 'JOB_RESTART', -
> program_type=> 'STORED_PROCEDURE', -
> number_of_arguments=> 1, -
> enabled=> false)
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_scheduler.define_metadata_argument( -
> 'SCP_WATCHDOG', 'EVENT_MESSAGE', 1)
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_scheduler.enable('SCP_WATCHDOG')
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_scheduler.create_job( -
> job_name=> 'SCJ_WATCHDOG', -
> program_name => 'SCP_WATCHDOG', -
> queue_spec=> 'sys.scheduler$_event_queue,job_watchdog', -
> event_condition=> q'{tab.user_data.object_name='SCJ_TEST' and tab.user_data.event_type = 'JOB_STOPPED' and tab.user_data.event_status = 32}', -
> enabled=> true)
PL/SQL procedure successfully completed.
-- main job
SQL>
SQL> create table job_log(
2 audsid int,
3 log_date date default sysdate);
Table created.
SQL>
SQL> create or replace procedure job_proc
2 is
3 begin
4 insert into job_log(audsid) values (sys_context('userenv', 'sessionid'));
5 commit;
6 dbms_lock.sleep(300);
7 end;
8 /
Procedure created.
SQL>
SQL> exec dbms_scheduler.create_job( -
> job_name=> 'SCJ_TEST', -
> job_type=> 'STORED_PROCEDURE', -
> job_action=> 'job_proc', -
> enabled=> false, -
> auto_drop=> false)
PL/SQL procedure successfully completed.
SQL>
-- publish all events for debugging
SQL> exec dbms_scheduler.set_attribute( -
> 'SCJ_TEST', -
> 'RAISE_EVENTS', -
> dbms_scheduler.job_all_events)
PL/SQL procedure successfully completed.
run job:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SQL> exec dbms_scheduler.enable('SCJ_TEST')
PL/SQL procedure successfully completed.
SQL>
SQL> select jl.audsid, jl.log_date, s.logon_time, p.spid
2 from job_log jl,
3 v$session s,
4 v$process p
5 where jl.audsid = s.audsid(+)
6 and s.paddr = p.addr(+);
AUDSID LOG_DATE LOGON_TIME SPID
---------- ------------------- ------------------- ------------------------
13029636 22.08.2019 06:39:43 22.08.2019 06:39:43 25117
kill server process:
wait:
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.
SQL> select msg_id, enq_time, qt.user_data.event_type event_type, qt.user_data.object_name object_name, qt.user_data.event_status event_status
2 from sys.aq$scheduler$_event_qtab qt
3 where consumer_name = 'JOB_WATCHDOG'
4 order by enq_time desc fetch first 10 rows only;
MSG_ID ENQ_TIME EVENT_TYPE OBJECT_NAME EVENT_STATUS
-------------------------------- ------------------- ------------ ------------ ------------
90B34EC684C9621DE053DB1F13AC2859 22.08.2019 11:39:43 JOB_STARTED SCJ_TEST 1
-- job stopped event
SQL> select msg_id, enq_time, qt.user_data.event_type event_type, qt.user_data.object_name object_name, qt.user_data.event_status event_status
2 from sys.aq$scheduler$_event_qtab qt
3 where consumer_name = 'JOB_WATCHDOG'
4 order by enq_time desc fetch first 10 rows only;
MSG_ID ENQ_TIME EVENT_TYPE OBJECT_NAME EVENT_STATUS
-------------------------------- ------------------- ------------ ------------ ------------
90B183BC7EF735DEE053DB1F13AC6AEF 22.08.2019 11:40:24 JOB_STOPPED SCJ_TEST 32
90B34EC684C9621DE053DB1F13AC2859 22.08.2019 11:39:43 JOB_STARTED SCJ_TEST 1
-- job started event
SQL> select msg_id, enq_time, qt.user_data.event_type event_type, qt.user_data.object_name object_name, qt.user_data.event_status event_status
2 from sys.aq$scheduler$_event_qtab qt
3 where consumer_name = 'JOB_WATCHDOG'
4 order by enq_time desc fetch first 10 rows only;
MSG_ID ENQ_TIME EVENT_TYPE OBJECT_NAME EVENT_STATUS
-------------------------------- ------------------- ------------ ------------ ------------
90B3521ECC136221E053DB1F13ACF163 22.08.2019 11:40:39 JOB_STARTED SCJ_TEST 1
90B183BC7EF735DEE053DB1F13AC6AEF 22.08.2019 11:40:24 JOB_STOPPED SCJ_TEST 32
90B34EC684C9621DE053DB1F13AC2859 22.08.2019 11:39:43 JOB_STARTED SCJ_TEST 1
log tables:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SQL> select jl.audsid, jl.log_date, s.logon_time, p.spid
2 from job_log jl,
3 v$session s,
4 v$process p
5 where jl.audsid = s.audsid(+)
6 and s.paddr = p.addr(+)
7 order by jl.log_date;
AUDSID LOG_DATE LOGON_TIME SPID
---------- ------------------- ------------------- ------------------------
13029636 22.08.2019 06:39:43
13029642 22.08.2019 06:40:39 22.08.2019 06:40:39 25121
SQL> select * from job_watchdog_log;
AUDSID LOG_DATE
---------- -------------------
13029639 22.08.2019 06:40:39
event_status
Adds further qualification to the event type. If event_type is "JOB_STARTED," status 1 indicates that it is a normal start, and status 2 indicates that it is a retry.
If event_type is "JOB_FAILED," status 4 indicates that it was a failure due to an error that was thrown during job execution, and status 8 indicates that it was an abnormal termination of some kind.
If event_type is "JOB_STOPPED," status 16 indicates that it was a normal stop, and status 32 indicates that it was a stop with the FORCE option set to TRUE. |
|
|
