OracleRAC环境下配置statspack - 数据库编程

ne ( TO_CHAR (SYSDATE, 'dd hh24:mi:ss') || ': Snapshot completed'); w_status := sys.DBMS_LOCK.release (lockhandle => w_handle); ELSE DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, 'dd hh24:mi:ss') || CASE w_status WHEN 1 THEN ': Lock wait timed out' WHEN 2 THEN ': deadlock detected' WHEN 3 THEN ': parameter error' WHEN 4 THEN ': already holding lock' WHEN 5 THEN ': illegal lock handle' ELSE ': unknown error' END); END IF; END; / 5) 创建用于job调度的过程 BEGIN DBMS_SCHEDULER.create_program (program_name => 'PROC_RAC_STATSPACK', program_type => 'STORED_PROCEDURE', program_action => 'db_proc_rac_statspack', enabled => TRUE); END; / 6) 清除同名job(如果存在) BEGIN DBMS_SCHEDULER.drop_job ('ORCL10_PERFSTAT_COLLECT_N1',force=>true); DBMS_SCHEDULER.drop_job ('ORCL10_PERFSTAT_COLLECT_N2',force=>true); DBMS_SCHEDULER.drop_job ('ORCL10_PERFSTAT_PURGE_N1',force=>true); DBMS_SCHEDULER.drop_job ('ORCL10_PERFSTAT_PURGE_N2',force=>true); END; / 7) 创建产生snapshot以及清除历史snapshot的job --Author ：Leshami --Blog ：http://blog.csdn.net/leshami BEGIN DBMS_SCHEDULER.create_job ( job_name => 'ORCL_PERFSTAT_COLLECT_N1', program_name => 'PROC_RAC_STATSPACK', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=hourly; INTERVAL=1; BYMINUTE=30', job_class => 'statspack_suse11a_class', comments => 'This job will run on suse11a', ENABLED => TRUE); DBMS_SCHEDULER.create_job ( job_name => 'ORCL_PERFSTAT_PURGE_N1', job_type => 'PLSQL_BLOCK', job_action => 'begin STATSPACK.PURGE(31); end;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY; BYHOUR=23; BYMINUTE=30', job_class => 'statspack_suse11a_class', enabled => TRUE); END; / --- create the job for Node 2: BEGIN DBMS_SCHEDULER.create_job ( job_name =>

'ORCL_PERFSTAT_COLLECT_N2', program_name => 'PROC_RAC_STATSPACK', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=hourly; INTERVAL=1; BYMINUTE=30', job_class => 'statspack_suse11b_class', comments => 'This job will run on suse11b', enabled => TRUE); DBMS_SCHEDULER.create_job ( job_name => 'ORCL_PERFSTAT_PURGE_N2', job_type => 'PLSQL_BLOCK', job_action => 'begin STATSPACK.PURGE(31); end;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY; BYHOUR=23; BYMINUTE=30', job_class => 'statspack_suse11b_class', enabled => TRUE); END; /

三、校验结果

1) 验证创建的Job
SQL> select OWNER, JOB_NAME, STATE, START_DATE, ENABLED from dba_scheduler_jobs   
  2  where owner= 'PERFSTAT';  

OWNER           JOB_NAME                       STATE           START_DATE                              ENABL
--------------- ------------------------------ --------------- --------------------------------------- -----
PERFSTAT        ORCL_PERFSTAT_PURGE_N1         SCHEDULED       22-AUG-14 02.42.37.295650 PM +08:00     TRUE
PERFSTAT        ORCL_PERFSTAT_COLLECT_N1       SCHEDULED       22-AUG-14 02.42.37.269292 PM +08:00     TRUE
PERFSTAT        ORCL_PERFSTAT_COLLECT_N2       SCHEDULED       22-AUG-14 02.43.17.414613 PM +08:00     TRUE
PERFSTAT        ORCL_PERFSTAT_PURGE_N2         SCHEDULED       22-AUG-14 02.43.17.438804 PM +08:00     TRUE

2) 手工执行Job
SQL> exec dbms_scheduler.run_job('ORCL_PERFSTAT_COLLECT_N1');
                                                        
SQL> exec dbms_scheduler.run_job('ORCL_PERFSTAT_COLLECT_N2');

SQL> SELECT *                                                                                                       
  2    FROM (  SELECT log_id,                                                                                       
  3                   job_name,                                                                                     
  4              job_subname,                                                                                        
  5                   status,                                                                                        
  6                   actual_start_date,                                                                             
  7                   run_duration                                                                                   
  8              FROM dba_scheduler_j

OracleRAC环境下配置statspack(二)