Configure statspack in the OracleRAC Environment

Configure statspack in the OracleRAC Environment

Statspack is a product of the Oracle 9i era. It has a cross-milestone significance for monitoring and analyzing database performance and is the predecessor of AWR. After 10 Gb of Oracle, AWR replaces statspack. Even so, statpack is still a good choice if awr is abnormal or requires debugging of the package license. However, in the RAC environment, statspack is not supported and must be configured separately and managed using jobs. This article describes how to create a service and a job in the RAC environment to achieve the snapshot effect of each node at the same time.

I. Demo Environment

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]

2. Configure statspack

1) add service $ srvctl add service-d orcl10-s statspack_suse11a_srvc-r orcl101 $ srvctl add service-d orcl10-s restart-r orcl102 $ srvctl start service-d orcl10-s restart $ 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 running is running on instance (s) orcl102 $ srvctl config service-d orcl10 orcl10_srv PREF: orcl102 orcl101 AVAIL: Kernel PREF: orcl101 AVAIL: Kernel PREF: orcl102 AVAIL: $ lsnrctl status ......... service "statspack_suse11a_srvc" has 1 instance (s ). instance "orcl101", status READY, has 2 handler (s) for this service... ser Vice "statspack_suse11b_srvc" has 1 instance (s ). instance "orcl102", status READY, has 1 handler (s) for this service ................ 2) Configure statspackconn/as sysdbacreate tablespace perfstat datafile '+ ASM_DATA' size 500 m 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) create job classBEGIN DBMS_SCHEDULER.create_job_class (job_class_name => 'statspack _ suse11a_class ', service => 'statspack _ suse11a_srvc '); else (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 ---------------------------- declare statspack_suse11a_srvcSTATSPACK_SUSE11B_CLASS statspack_suse11b_srvcGRANT execute on sys. STATSPACK_SUSE11A_CLASS TO perfstat; grant execute on sys. resume TO perfstat; 4) create a conn perfstat/perfstatCREATE or replace procedure into 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 already 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) create a job scheduling process BEGIN DBMS_SCHEDULER.create_program (program_name => 'proc _ RAC_STATSPACK ', program_type => 'stored _ PROCEDURE ', program_action => 'db _ proc_rac_statspack', enabled => TRUE); END;/6) Clear jobs with the same name (if any) BEGIN commit ('orcl10 _ PERFSTAT_COLLECT_N1 ', force => true); Round ('orcl10 _ PERFSTAT_COLLECT_N2', force => true); Round ('orcl10 _ PERFSTAT_PURGE_N1 ', force => true); DBMS_SCHEDULER.drop_job ('orcl10 _ PERFSTAT_PURGE_N2 ', force => true); END;/7) create a job that generates snapshot and clears history snapshot -- Author: leshami -- Blog: http://blog.csdn.net/leshamiBEGIN Values (job_name => 'orcl _ PERFSTAT_COLLECT_N1 ', program_name => 'proc _ RAC_STATSPACK', start_date => define imestamp, 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 => 'in in STATSPACK. PURGE (31); end; ', start_date => paiimestamp, 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 => policimestamp, 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 => 'in in STATSPACK. PURGE (31); end; ', start_date => paiimestamp, repeat_interval => 'freq = DAILY; BYHOUR = 23; BYMINUTE = 30', job_class => 'statspack _ suse11b_class ', enabled => TRUE); END ;/

Iii. Verification results

1) Verify the created JobSQL> select OWNER, JOB_NAME, STATE, START_DATE, ENABLED from dba_scheduler_jobs 2 where owner = 'perfstat '; OWNER JOB_NAME STATE START_DATE ENABL valid values ----- PERFSTAT valid SCHEDULED valid values PM + 08:00 TRUEPERFSTAT valid SCHEDULED variable values PM + 08:00 invalid SCHEDULED variable values PM + 08:00 TRUEPERFSTAT valid SCHEDULED variable values PM + 08:00 TRUE2) run JobSQL manually> exec dbms_scheduler.run_job ('orcl _ PERFSTAT_COLLECT_N1 '); SQL> exec values ('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 Region 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 running RUN_DURATION ---------- running ----------------- running limit 156 running SUCCEEDED 22-AUG-14 running PM + 00:00:04 + 000 00:00:04 155 running SUCCEEDED running Limit PM + + 000