我有一个oracle DBMS计划作业，在早上5点运行。服务器在凌晨4点宕机，6点又恢复了。一旦服务器和数据库上线，上午5点的作业就会自动启动。我该如何预防呢?我要写上结束日期吗?所
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYSTEM.DAILY_JOB'
,start_date => TO_TIMESTAMP_TZ('2021/04/09 03:00:00.000000 +00:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=DAILY;INTERVAL=1'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'SOME JOB'
,comments => 'NA'
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'MAX_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'AUTO_DROP'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'RESTART_ON_RECOVERY'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'RESTART_ON_FAILURE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYSTEM.DAILY_JOB'
,attribute => 'STORE_OUTPUT'
,value => TRUE);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'SYSTEM.DAILY_JOB');
END;
/
您必须更改属性RESTART_ON_RECOVERY，默认设置为true
restart_on_recovery
如果作业被设置为TRUE，并且该作业被数据库停止关闭，然后在数据库恢复时重新启动作业。
如果设置为FALSE，并且作业因数据库关闭而停止，则当数据库恢复时，作业被标记为已停止。
BEGIN
dbms_scheduler.create_job( job_name => 'MY_TEST',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN sleee; END;',
number_of_arguments => 0,
start_date=> systimestamp + 1 ,
repeat_interval=>'freq=hourly; byminute=0; bysecond=0;',
enabled =>FALSE,
auto_drop=>TRUE,
comments=> 'Test'
);
dbms_scheduler.set_attribute('MY_TEST','RESTART_ON_RECOVERY',false);
dbms_scheduler.enable('MY_TEST');
END;
/
SQL> BEGIN
dbms_scheduler.drop_job ( job_name => 'MY_TEST');
dbms_scheduler.create_job( job_name => 'MY_TEST',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN sleee; END;',
number_of_arguments => 0,
start_date=> systimestamp + 1 ,
repeat_interval=>'freq=hourly; byminute=0; bysecond=0;',
enabled =>FALSE,
auto_drop=>TRUE,
comments=> 'Test'
);
dbms_scheduler.set_attribute('MY_TEST','NLS_ENV','NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' ');
dbms_scheduler.set_attribute('MY_TEST','RESTART_ON_RECOVERY',false);
dbms_scheduler.enable('MY_TEST');
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
PL/SQL procedure successfully completed.
SQL> select job_name , restart_on_recovery from dba_scheduler_jobs where job_name = 'MY_TEST' ;
JOB_NAME
--------------------------------------------------------------------------------
RESTA
-----
MY_TEST
FALSE
测试场景
SQL> begin
sys.db 2 ms_scheduler.create_job
(
job_name => 'TEST_JOB'
3 4 5 ,start_date => systimestamp
,repeat_interval => 'freq=minutely;interval=1'
,job_class => 'default_job_class'
6 7 8 ,job_type => 'plsql_block'
,job_action => 'begin dbms_lock.sleep(2); end;'
,comments => 'Test restart_on_recovery'
9 10 11 );
sys.dbms_scheduler.set_attribute
( name => 'TEST_JOB'
12 13 14 ,attribute => 'auto_drop'
,value => false);
sys.dbms_scheduler.set_attribute
15 16 17 ( name => 'TEST_JOB'
,attribute => 'restart_on_recovery'
18 19 ,value => false);
sys.dbms_scheduler.set_attribute
( name => 'TEST_JOB'
20 21 22 ,attribute => 'restart_on_failure'
,value => false);
23 24 sys.dbms_scheduler.enable ('TEST_JOB');
25 end;
/ 26
PL/SQL procedure successfully completed.
SQL> select job_name,state,LAST_START_DATE,NEXT_RUN_DATE,RESTART_ON_RECOVERY,RESTART_ON_FAILURE from dba_scheduler_jobs where job_name='TEST_JOB'
JOB_NAME STATE LAST_START_DATE NEXT_RUN_DATE RESTA RESTA
------------------------------ --------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----- -----
TEST_JOB RUNNING 18-OCT-21 02.33.05.519165 PM +02:00 18-OCT-21 02.33.05.348417 PM +02:00 FALSE FALSE
让作业运行多次
SQL> select job_name,state,LAST_START_DATE,NEXT_RUN_DATE,RESTART_ON_RECOVERY,RESTART_ON_FAILURE,run_count,FAILURE_COUNT from dba_scheduler_jobs where job_name='TEST_JOB'
JOB_NAME STATE LAST_START_DATE NEXT_RUN_DATE RESTA RESTA RUN_COUNT FAILURE_COUNT
------------------------------ --------------- ---------------------------------------- ---------------------------------------- ----- ----- ---------- -------------
TEST_JOB SCHEDULED 18-OCT-21 02.39.05.537625 PM +02:00 18-OCT-21 02.40.05.540345 PM +02:00 FALSE FALSE 9 0
关机启动(中间有间隙)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 1694495520 bytes
Fixed Size 8897312 bytes
Variable Size 1442840576 bytes
Database Buffers 234881024 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL>
SQL> alter database open ;
Database altered.
SQL> select job_name,state,LAST_START_DATE,NEXT_RUN_DATE,RESTART_ON_RECOVERY,RESTART_ON_FAILURE,run_count,FAILURE_COUNT from dba_scheduler_jobs where job_name='TEST_JOB'
2 ;
JOB_NAME STATE LAST_START_DATE NEXT_RUN_DATE RESTA RESTA RUN_COUNT FAILURE_COUNT
------------------------------ --------------- ---------------------------------------- ---------------------------------------- ----- ----- ---------- -------------
TEST_JOB SCHEDULED 18-OCT-21 02.39.05.537625 PM +02:00 18-OCT-21 02.58.05.317688 PM +02:00 FALSE FALSE 9 0
在我的情况下，频率为1分钟，作业没有失败，并且在恢复后也没有重新启动。下一次执行将是我的调度窗口的下一次迭代。