Oracle RAC环境下配置Statspack

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 - Productionsuse11a:oracle:orcl101 > $ORA_CRS_HOME/bin/crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.5.0]二、配置Statspack1）首先添加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/spcreateGRANT 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_srvcGRANT 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 ：www.linuxidc.com
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;
/更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-10/107568p2.htm

MySQL 系统变量（system variables）在 Oracle 数据库中实现 MapReduce相关资讯 Oracle RAC Statspack

Oracle RAC 更换存储实验（今 06:38）
Oracle RAC客户端tnsnames.ora相关（09/02/2015 19:31:57）
如何删除statspack生成的历史数据（05/05/2015 14:14:06）

11.2.04 Oracle RAC 目录中（12/21/2015 15:10:42）
关于心跳网络引起的Oracle RAC的节（06/04/2015 17:53:20）
Oracle 11gR2 RAC集群服务启动与关（04/29/2015 10:27:54）

本文评论查看全部评论（0）

评论声明

尊重网上道德，遵守中华人民共和国的各项有关法律法规