Oracle RAC環境下配置Statspack

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 ：www.bkjia.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;
/

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁