国内最全IT社区平台 联系我们 | 收藏本站
华晨云阿里云优惠2
您当前位置:首页 > 互联网 > Oracle RAC环境下配置statspack

Oracle RAC环境下配置statspack

来源:程序员人生   发布时间:2014-10-04 08:00:00 阅读次数:2922次

    Statspack是Oracle 9i时代的产物,对于监控与分析数据库性能有着跨里程碑的意义,是AWR的前身。在Oracle 10g后AWR取代了statspack。尽管如此,awr异常或者需要调试包license的情况下statpack依旧是不错的选择。然而在RAC环境中,statspack并不支持,需要单独的进行配置以及使用job来进行管理。本文描述的则是通过在RAC环境下创建service,以及job来达到各节点同时产生snapshot的效果。

 

一、演示环境

suse11a:oracle:orcl101 > cat /etc/issue Welcome to SUSE Linux Enterprise Server 11 SP3 (x86_64) - Kernel (l). suse11a:oracle:orcl101 > sqlplus -v SQL*Plus: Release 10.2.0.5.0 - Production suse11a:oracle:orcl101 > $ORA_CRS_HOME/bin/crsctl query crs activeversion CRS active version on the cluster is [10.2.0.5.0]

二、配置statspack

1)首先添加service $ srvctl add service -d orcl10 -s statspack_suse11a_srvc -r orcl101 $ srvctl add service -d orcl10 -s statspack_suse11b_srvc -r orcl102 $ srvctl start service -d orcl10 -s statspack_suse11a_srvc $ srvctl start service -d orcl10 -s statspack_suse11b_srvc $ srvctl status service -d orcl10 Service orcl10_srv is running on instance(s) orcl102, orcl101 Service statspack_suse11a_srvc is running on instance(s) orcl101 Service statspack_suse11b_srvc is running on instance(s) orcl102 $ srvctl config service -d orcl10 orcl10_srv PREF: orcl102 orcl101 AVAIL: statspack_suse11a_srvc PREF: orcl101 AVAIL: statspack_suse11b_srvc PREF: orcl102 AVAIL: $ lsnrctl status ......... Service "statspack_suse11a_srvc" has 1 instance(s). Instance "orcl101", status READY, has 2 handler(s) for this service... Service "statspack_suse11b_srvc" has 1 instance(s). Instance "orcl102", status READY, has 1 handler(s) for this service... ............. 2)配置statspack conn / as sysdba create tablespace perfstat datafile '+ASM_DATA' size 500m autoextend on; @?/rdbms/admin/spcreate GRANT EXECUTE ON DBMS_LOCK TO perfstat; GRANT CREATE JOB TO perfstat; GRANT EXECUTE ON sys.DBMS_SCHEDULER TO perfstat; GRANT EXECUTE ON sys.DBMS_ISCHED TO perfstat; 3)创建job class BEGIN DBMS_SCHEDULER.create_job_class ( job_class_name => 'statspack_suse11a_class', service => 'statspack_suse11a_srvc'); DBMS_SCHEDULER.create_job_class ( job_class_name => 'statspack_suse11b_class', service => 'statspack_suse11b_srvc'); END; / SQL> select job_class_name, service from dba_scheduler_job_classes; JOB_CLASS_NAME SERVICE ------------------------------ ------------------------------------------------- DEFAULT_JOB_CLASS AUTO_TASKS_JOB_CLASS STATSPACK_SUSE11A_CLASS statspack_suse11a_srvc STATSPACK_SUSE11B_CLASS statspack_suse11b_srvc GRANT EXECUTE ON sys.STATSPACK_SUSE11A_CLASS TO perfstat; GRANT EXECUTE ON sys.STATSPACK_SUSE11B_CLASS TO perfstat; 4)创建用于同步节点的过程 conn perfstat/perfstat CREATE OR REPLACE PROCEDURE db_proc_rac_statspack AS w_status NUMBER (38); w_handle VARCHAR2 (60); w_snap_level NUMBER; BEGIN w_snap_level := 7; sys.DBMS_LOCK.allocate_unique (lockname => 'Synchronize Statspack', lockhandle => w_handle); w_status := sys.DBMS_LOCK.request (lockhandle => w_handle, lockmode => DBMS_LOCK.x_mode, timeout => 300, -- seconds, default is dbms_lock.maxwait release_on_commit => FALSE -- which is the default ); IF (w_status = 0) THEN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, 'dd hh24:mi:ss') || ': Acquired lock, running statspack'); statspack.snap (w_snap_level); DBMS_OUTPUT.put_line ( 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_job_run_details 9 WHERE job_name like '%ORCL_PERFSTAT%' 10 ORDER BY actual_start_date DESC) 11 WHERE ROWNUM < 15; LOG_ID JOB_NAME JOB_SUBNAME STATUS ACTUAL_START_DATE RUN_DURATION ---------- ------------------------- ------------ ----------- ------------------------------------- ---------------- 156 ORCL_PERFSTAT_COLLECT_N2 SUCCEEDED 22-AUG-14 02.43.32.470484 PM +08:00 +000 00:00:04 155 ORCL_PERFSTAT_COLLECT_N1 SUCCEEDED 22-AUG-14 02.43.00.288887 PM +08:00 +000 00:00:04


四、参考
http://www.oracle-class.com/?p=2384
http://jonathanlewis.wordpress.com/2011/01/14/statspack-on-rac/

生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------
分享到:
------分隔线----------------------------
关闭
程序员人生