下包括tu等課件automating tasks with the scheduler

1、11.創(chuàng)建程序周炯 jon.zhoujiong6BEGIN DBMS_SCHEDULER.CREATE_PROGRAM(program_name => 'CALC_STATS2', program_action =>'HR.UPDATE_HR_SCHEMA_STATS',program_type => 'STORED_PROCEDURE' enabled => TRUE);END;/,關(guān)鍵成分和步驟 按以下步驟使用計(jì)劃任務(wù)簡(jiǎn)化任務(wù)管理:1. 創(chuàng)建程序.2. 創(chuàng)建計(jì)劃任務(wù).3. 創(chuàng)建并提交一份作業(yè)4. 作業(yè)Program S

2、cheduleJobattributesJob Arguments周炯 jon.zhoujiong5一個(gè)簡(jiǎn)單的 Job周炯 jon houjionggmail com4WHATWHEN簡(jiǎn)化任務(wù)管理在每月月底執(zhí)當(dāng)消息等待時(shí)當(dāng)視圖更新行一系列月底任務(wù)運(yùn)行一個(gè)隊(duì)列程序時(shí)表中數(shù)據(jù)每天備份數(shù)據(jù)庫(kù)一天2次計(jì) 算表和索引統(tǒng)計(jì)當(dāng)文件到達(dá)時(shí)開(kāi)啟加載每小時(shí)生成一當(dāng)當(dāng)前的索引結(jié)個(gè)報(bào)告束重建時(shí)開(kāi)啟重建周炯 jon zhoujionggmail com3目標(biāo) 課程目標(biāo): 用計(jì)劃任務(wù)來(lái)簡(jiǎn)化任務(wù)管理 創(chuàng)建作業(yè)，程序，計(jì)劃任務(wù) 監(jiān)測(cè)事務(wù)執(zhí)行 用基于時(shí)間或者基于的進(jìn)度表執(zhí)行一個(gè)計(jì)劃事務(wù) 用作業(yè)鏈來(lái)執(zhí)行一系列相關(guān)任務(wù) 用高級(jí)計(jì)劃

3、任務(wù)概念來(lái)作業(yè)執(zhí)行順序周炯 jon.zhoujiong2Key Comp. & Steps Schedules Job ChainsAdv.Concepts用計(jì)劃任務(wù)自動(dòng)化任務(wù)周炯 jon.zhoujoing12Creating a Time-Based Job Full Notes Page周炯 jon.zhoujiong12創(chuàng)建基于時(shí)間的作業(yè) 案例:創(chuàng)建一個(gè)每天晚上11點(diǎn)喚醒備份的作業(yè)周炯 jon.zhoujiong11BEGIN DBMS_SCHEDULER.CREATE_JOB(job_name=>'HR.DO_BACKUP', job_type =>

4、; 'EXECUTABLE',job_action => '/home/usr/dba/rman/nightly_incr.sh', start_date=> SYSDATE, repeat_interval=>'FREQ=DAILY;BYHOUR=23',/* next night at 11:00 PM */ comments => 'Nightly incremental backups');END;/使用基于時(shí)間或者基于的計(jì)劃任務(wù)周炯 jon houjionggmail com10ScheduleT

5、imeEventKey Comp. & Steps> Schedules Job Chains Adv.Concepts4.作業(yè)SELECT job_name, status, error#, run_duration FROM USER_SCHEDULER_JOB_RUN_DETAILS;JOB_NAMESTATUS ERROR# RUN_DURATION GATHER_STATS_JOB SUCCESS0 +000 00:08:20PART_EXCHANGE_JOB FAILURE 6576 +000 00:00:00周炯 jon zhoujionggmail com93.

6、創(chuàng)建并運(yùn)行一個(gè)作業(yè)周炯 jon.zhoujiong82.創(chuàng)建并使用計(jì)劃任務(wù)周炯 jon.zhoujiong7BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'stats_schedule', start_date => SYSTIMESTAMP,end date => SYSTIMESTAMP + 30,comments => 'Every hour'); END;/repeat_interval => 'FREQ=HOURLY;INTERVAL=1',3創(chuàng)

7、建復(fù)合計(jì)劃任務(wù)INCLUDEEXCLUDEINTERSECT周炯 jon.zhoujiong18Events Raised by the Scheduler Full Notes Page周炯 jon.zhoujiong17基于的計(jì)劃任務(wù) 類型: 用戶生成或應(yīng)用程序生成 計(jì)劃任務(wù)生成 計(jì)劃任務(wù)啟動(dòng)的 JOB_START JOB_SCH_LIM_REACHED JOB_SUCCEEDED JOB_DISABLED JOB_FAILED JOB_CHAIN_STALLED JOB_BROKEN JOB_ALL_EVENTSJOB COMPLETED提高的例子:周炯 jon houjionggmai

8、l com16DBMS_SCHEDULER.SET_ATTRIBUTE('hr.do_backup','raise_events', DBMS_SCHEDULER.JOB_FAILED);創(chuàng)建基于的作業(yè) 案例:創(chuàng)建一個(gè)作業(yè)，如果在9點(diǎn)之前文件系統(tǒng)，則啟動(dòng)加載器周炯 jon zhoujionggmail com15BEGIN DBMS_SCHEDULER.CREATE_JOB(job_name=>'ADMIN.PERFORM_DATA_LOAD',job_type => 'EXECUTABLE',job_action =

9、> '/home/usr/dba/rman/report_failure.sh', start date => SYSTIMESTAMP,END;event_condition => 'tab.user_data.object_owner = ''HR'' and tab.user_data.object_name = ''DATA.TXT'' and tab.user_data.event_type = ''FILE_ARRIVAL''and tab.us

10、er_data.event_timestamp < 9 ', queue_spec => 'HR.LOAD_JOB_EVENT_Q');用企業(yè)級(jí)管理器創(chuàng)建基于的計(jì)劃任務(wù)周炯 jon.zhoujiong14創(chuàng)建基于計(jì)劃任務(wù) 要?jiǎng)?chuàng)建基于計(jì)劃任務(wù), 你應(yīng)該設(shè)定: 隊(duì)列說(shuō)明書(shū) (在何處把消息排隊(duì)來(lái)啟動(dòng)你的作業(yè)) (和Oracle Stream AQ規(guī)則相同語(yǔ)法) 啟動(dòng)作業(yè)為TRUE的條件Oracle Database 10gEvent ADTApplication(抽象數(shù)據(jù)類型)Queue周炯 jon.zhoujiong13Scheduler43.定義鏈步驟周炯 j

11、on.zhoujiong242.定義鏈步驟周炯 jon.zhoujiong23DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (1chain_name => 'bulk_load_chain',step_name => 'load_data_evt',event_condition => 'tab.user_data.object_owner = ''HR'' and tab.user_data.object_name = ''DATA.TXT'

12、9; and tab.user_data.event_type = ''FILE_ARRIVAL'' ',queue_spec => 'HR.LOAD_JOB_EVENT_Q');DBMS_SCHEDULER.DEFINE_CHAIN_STEP (2chain_name => 'bulk_load_chain',step_name => 'do_bulk_load', program_name => 'hr.load_data_prog);DBMS_SCHEDULER.DEF

13、INE_CHAIN_STEP (3chain_name => 'bulk_load_chain',step_name => 'rebuild_indx', program_name => 'hr.rebuild_indexes');1.創(chuàng)建作業(yè)鏈對(duì)象.周炯 jon houjionggmail com2212345鏈案例Dependency SchedulingSTARTSchedule周炯 jon zhoujionggmail com21JobLoad_data_evt 12Stop_when_53disk_full_evtEN

14、D4BULK_LOAD_CHAINRun_reports(HR.GEN_REPORTS)Do_bulk_loadRebuild_indxCreating Chains Full Notes Page周炯 jon.zhoujiong20創(chuàng)建作業(yè)鏈1. 創(chuàng)建作業(yè)鏈對(duì)象.2. 定義鏈步驟.3. 定義鏈的規(guī)則.4. 開(kāi)啟鏈: 鏈. 創(chuàng)建一個(gè)指向鏈的作業(yè).Job chain周炯 jon.zhoujiong19JobKey Comp. & Steps Schedules> Job Chains Adv.Concepts5用一個(gè)窗口來(lái)決定作業(yè)優(yōu)先級(jí)周炯 jon.zhoujiong30 Job

15、Priorit Job1y1 Job22 Job33 Job45 Job52APPL_JOBSOTHERADMIN_JOBSDaytime windowJob5Job4Job3Job2Job1創(chuàng)建窗口 為12月創(chuàng)建一個(gè)窗口，方案用END_OF_YEAR，并且在東部時(shí)間晚上6點(diǎn)到早上6 點(diǎn)保持活躍周炯 jon.zhoujiong29BEGIN DBMS_SCHEDULER.CREATE_WINDOW(window_name => 'DEC_NIGHTS', resource_plan => 'END_OF_YEAR',start_date =>

16、'01-DEC-03 06.00.00 PM EST',repeat_interval => 'FREQ=DAILY; BYHOUR=18', duration => '0 12:00:00',end_date => '31-DEC-03 06.00.00 AM EST',comments => 'Every day at 6:00 PM'); END;/創(chuàng)建作業(yè)類周炯 jon houjionggmail com28EXECUTE DBMS_SCHEDULER.CREATE_JOB_CLAS

17、Sjob_class_name => 'ADMIN_JOBS', -( -resource_consumer_group => 'DAYTIME_JOBS', - logging_level => DBMS_SCHEDULER.LOGGING_OFF);復(fù)合計(jì)劃任務(wù)概念ResourceResourceWindow consumer groupplangroupJob chainJob classWindowProgramJobScheduleArgumentsArgumentsTimeEvent周炯 jon zhoujionggmail com

18、27Key Comp. & Steps Schedules Job Chains> Adv.Concepts監(jiān)視作業(yè)鏈DBA | ALL | USER_SCHEDULER_CHAINSDBA | ALL | USER_SCHEDULER_CHAIN_RULES DBA | ALL | USER_SCHEDULER_CHAIN_STEPS DBA | ALL | USER_SCHEDULER_RUNNING_CHAINS周炯 jon.zhoujiong264.開(kāi)啟鏈周炯 jon.zhoujiong25BEGINDBMS_SCHEDULER.CREATE_JOB (job_name=> 'bulk_load_chain_job'