OracleRAC环境下配置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 \r (\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_li

OracleRAC环境下配置statspack(一)