Configure Statspack in Oracle RAC Environment

Configure Statspack in Oracle RAC 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/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]

2. Configure Statspack

1) First add the 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) Configure statspack
Conn/as sysdba
Create tablespace perfstat datafile '+ ASM_DATA' size 500 m 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) create a 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) create a synchronization Node
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 = & gt; 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) 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
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) create a job -- Author: Leshami -- Blog: www.bkjia.com that generates and clears history snapshots.
DBMS_SCHEDULER.create_job (
Job_name => 'orcl _ PERFSTAT_COLLECT_N1 ',
Program_name => 'proc _ RAC_STATSPACK ',
Start_date => policimestamp,
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 => 'beginstatspack. PURGE (31); end ;',
Start_date => policimestamp,
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 => 'beginstatspack. PURGE (31); end ;',
Start_date => policimestamp,
Repeat_interval => 'freq = DAILY; BYHOUR = 23; BYMINUTE = 30 ',
Job_class => 'statspack _ suse11b_class ',
Enabled => TRUE );
END;
/

For more details, please continue to read the highlights on the next page:

1 2 Next Page