Oracle RAC环境下配置statspack

﹏ヽ暗。殇╰゛Y 2022-08-12 14:29 204阅读 0赞

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_www.oracle-class.com_p_2384]  
[http://jonathanlewis.wordpress.com/2011/01/14/statspack-on-rac/][http_jonathanlewis.wordpress.com_2011_01_14_statspack-on-rac]

[http_www.oracle-class.com_p_2384]: http://www.oracle-class.com/?p=2384
[http_jonathanlewis.wordpress.com_2011_01_14_statspack-on-rac]: http://jonathanlewis.wordpress.com/2011/01/14/statspack-on-rac/