Oracle RAC環境下配置statspack，racstatspack

Oracle RAC環境下配置statspack，racstatspack

    Statspack是Oracle 9i時代的產物，對於監控與分析資料庫效能有著跨裡程碑的意義，是AWR的前身。在Oracle 10g後AWR取代了statspack。儘管如此，awr異常或者需要調試包license的情況下statpack依舊是不錯的選擇。然而在RAC環境中，statspack並不支援，需要單獨的進行配置以及使用job來進行管理。本文描述的則是通過在RAC環境下建立service，以及job來達到各節點同時產生snapshot的效果。

 

一、示範環境

suse11a:oracle:orcl101 > cat /etc/issueWelcome to SUSE Linux Enterprise Server 11 SP3  (x86_64) - Kernel \r (\l).suse11a:oracle:orcl101 > sqlplus -vSQL*Plus: Release 10.2.0.5.0 - Productionsuse11a:oracle:orcl101 > $ORA_CRS_HOME/bin/crsctl query crs activeversionCRS 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 orcl10Service orcl10_srv is running on instance(s) orcl102, orcl101Service statspack_suse11a_srvc is running on instance(s) orcl101Service 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)配置statspackconn / as sysdbacreate 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 classBEGIN   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_CLASSAUTO_TASKS_JOB_CLASSSTATSPACK_SUSE11A_CLASS        statspack_suse11a_srvcSTATSPACK_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/perfstatCREATE OR REPLACE PROCEDURE db_proc_rac_statspackAS   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/leshamiBEGIN   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) 驗證建立的JobSQL> 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     TRUEPERFSTAT        ORCL_PERFSTAT_COLLECT_N1       SCHEDULED       22-AUG-14 02.42.37.269292 PM +08:00     TRUEPERFSTAT        ORCL_PERFSTAT_COLLECT_N2       SCHEDULED       22-AUG-14 02.43.17.414613 PM +08:00     TRUEPERFSTAT        ORCL_PERFSTAT_PURGE_N2         SCHEDULED       22-AUG-14 02.43.17.438804 PM +08:00     TRUE2) 手工執行JobSQL> 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/

oracle搭建RAC環境時,設定grid使用者環境變數需要指定一項THREADS_FLAG=native!這項環境變數是個什的？

線程標誌。以下是官網文檔上的截取：

Set the THREADS_FLAG variable (UNIX only)The TimesTen JDBC driver uses native threads. Green threads are not supported.On some UNIX platforms, to use the native threads package, you must set the THREADS_FLAG environment variable to native. How you set the flag depends on your shell.In csh, the syntax is:setenv THREADS_FLAG nativeIn sh, the syntax is:THREADS_FLAG=nativeexport THREADS_FLAG
 
oracle11gR2 rac環境的監聽怎配置？

把你的 tnsnams.ora 檔案貼出來