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 \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_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/

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

关于exp/imp的总结学习(r4笔记第26天)

关于exp/imp,是很常用的数据导出导入工具,在10g开始推出的数据泵datapump相当于是exp/imp的补充和升级版本。在后续章节再做一个总结。 exp...

3087
来自专栏杨建荣的学习笔记

不经意发现的dba_objects和dba_tables中的细节(r7笔记第56天)

今天有一个同学问我一个问题,因为白天比较忙也没有在意,在下班后坐地铁的时候抽空看了这个问题,感觉还是蛮有意思的。但是当时也没有任何答案,就准备自己回去好好实验一...

3853
来自专栏乐沙弥的世界

PL/SQL 包编译时hang住的处理

       最近PL/SQL包在编译时被hang住,起初以为是所依赖的对象被锁住。结果出乎意料之外。下面直接看代码演示。

946
来自专栏杨建荣的学习笔记

merge语句导致的CPU使用率过高的优化(r7笔记第4天)

今天有一个数据库有点反常,早上的时候报出了CPU使用率的警告。 警告内容如下: ZABBIX-监控系统: -----------------------...

3355
来自专栏杨建荣的学习笔记

SQL*Loader-805的解决(r2笔记36天)

使用sql*loader是大型项目中数据迁移的利器。如果是外部系统,其他数据库到oracle的数据迁移,使用文本式文件是最兼容的方式。 sqlldr的加载效率是...

3178
来自专栏飞扬的花生

文件方法

C# 获取指定目录下所有文件信息、移动目录、拷贝目录 /// <summary> /// 获取目录下的所有文件夹和文件的path ...

1955
来自专栏乐沙弥的世界

RAC环境下的阻塞(blocking blocked)

      RAC环境下的阻塞不同于单实例情形,因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session,v$lock相应的应变化为...

1232
来自专栏乐沙弥的世界

使用 DBMS_REPAIR 修复坏块

       对于Oracle数据块物理损坏的情形,在我们有备份的情况下可以直接使用备份来恢复。对于通过备份恢复,Oracel为我们提供了很多种方式,冷备,基于...

932
来自专栏deed博客

Oracle在Linux下安装

2442
来自专栏杨建荣的学习笔记

执行计划变化导致CPU负载高的问题分析 (r8笔记第20天)

前几天碰到一个CPU负载较高的问题。从系统层面来看,情况不是很严重,但是从应用的角度来说,已经感觉到很慢了。因为前端的调用频率还是比较高。所以会把这个问题放大。...

2687

扫码关注云+社区

领取腾讯云代金券