Si vous n’avez pas accès à AWR à cause de l’absence de licences Diagnostic pack / tuning Pack, ou tout simplement vous souhaitez utiliser le bon vieux statspack sur une base Cluster, cet article vous permettera de le configurer de telle façon à ce que la collecte sur les différentes instances ne pose pas de problème.
En effet, surtout sur les RAC disposant de plusieurs noeuds (6 – 8), il peut être généré des locks dans les tables statspack quand une instance tente de collecter et de mettre à jour les tables, pendant qu’une autre est déja en train d’insérer des données…
Même si on revoit l’ordonnancement, cette problématique peut arriver.
-> L’idéal est donc d’avoir une procédure qui réalise la collecte tout en vérifiant qu’il n’y a pas de lock pour pourrait empecher la collecte de clichés.
Cette procédure devra être appelée par un job, qui lui même fera parti d’une CLASS, lié à un service RAC défini sur une instance nommée.
Dans cet article, il s’agit d’un RAC 2 node, mais bien sur cela peut être fait de façon identique (moyennant l’ajout d’actions relatif au nombre de noeuds) sur un Rac 4 – 6 – 8 …. noeuds.
Methode :
## Installation statspack :
L’installation est identique à une instance single :
conn / as sysdba
@?/rdbms/admin/spcreate.sql
## Attribuer les droits sur DBMS_LOCK pour Perfstat :
grant execute on dbms_lock to perfstat;
## creation d’une procédure qui gerera les clichés, appartenant au user « 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, -- en secondes, default est: dbms_lock.maxwait
release_on_commit => false -- Defaut
);
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;
/
## Ajout de services RAC qui seront utilisés par les jobs pour pouvoir prendre des clichés sur les 2 instances :
– Création :
srvctl add service -d ASPUTF8 -s statspack_node1_srvc -r myinstance1
srvctl add service -d ASPUTF8 -s statspack_node2_srvc -r myinstance2
– Démarrage des services :
srvctl start service -d MYBASE -s statspack_node1_srvc
srvctl start service -d MYBASE -s statspack_node2_srvc
– Status / config :
srvctl status service -d MYBASE
srvctl config service -d MYBASE
## Creation des jobs class en spécifiant les services crées, via le compte SYS
BEGIN
DBMS_SCHEDULER.create_job_class(
job_class_name => 'statspack_node1_class',
service => 'statspack_node1_srvc');
DBMS_SCHEDULER.create_job_class(
job_class_name => 'statspack_node2_class',
service => 'statspack_node2_srvc');
END;
/
## Une petite vérification :
select job_class_name, service from dba_scheduler_job_classes
JOB_CLASS_NAME SERVICE
------------------------------ ----------------------------------------------------------------
ORA$AT_JCMED_SQ
AQ$_PROPAGATION_JOB_CLASS
XMLDB_NFS_JOBCLASS
STATSPACK_NODE1_CLASS statspack_node1_srvc
STATSPACK_NODE2_CLASS statspack_node2_srvc
## Ajout des permissions sur les class précedemment crées pour perfstat :
GRANT EXECUTE ON sys.statspack_node1_class TO perfstat;
GRANT EXECUTE ON sys.statspack_node2_class TO perfstat;
## Creation d’un job en associant la class qui convient (user perfstat)
Prerequis : en sys
grant create job to perfstat;
grant execute on dbms_scheduler to perfstat;
Depuis le user Perfstat :
BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'PROC_RAC_STATSPACK',
program_type => 'STORED_PROCEDURE',
program_action => 'db_proc_rac_statspack',
enabled => TRUE
);
END;
/
##note : si des jobs existent :
BEGIN
dbms_scheduler.drop_job('MYJOBDECOLLECTE');
END;
/
## A ce moment, il reste à creer les jobs (collecte / purge), et ce pour toutes les class précédemment crées (pour tous les noeuds du cluster) :
Toujours depuis le compte perfstat, pour une planification toutes les 30 min :
--- creation du job pour le noeud 1 :
BEGIN
dbms_scheduler.create_job(
job_name => 'CMS_PERFSTAT_COLLECT_N1',
program_name => 'PROC_RAC_STATSPACK',
start_date => SYSTIMESTAMP,
repeat_interval=>'FREQ=hourly; INTERVAL=1; BYMINUTE=30',
job_class => 'statspack_node1_class',
comments => 'This job will run on MYINSTANCE1',
enabled => TRUE);
dbms_scheduler.create_job
(job_name=>'CMS_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_node1_class',
enabled=>TRUE);
END;
/
--- creation du job pour le noeud 2 :
BEGIN
dbms_scheduler.create_job(
job_name => 'CMS_PERFSTAT_COLLECT_N2',
program_name => 'PROC_RAC_STATSPACK',
start_date => SYSTIMESTAMP,
repeat_interval=>'FREQ=hourly; INTERVAL=1; BYMINUTE=30',
job_class => 'statspack_node2_class',
comments => 'This job will run on MYINSTANCE2',
enabled => TRUE);
dbms_scheduler.create_job
(job_name=>'CMS_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_node2_class',
enabled=>TRUE);
END;
/
## Verifier les jobs
select OWNER, JOB_NAME, STATE, START_DATE, ENABLED from dba_scheduler_jobs where owner= 'PERFSTAT';
OWNER JOB_NAME STATE START_DATE ENABL
---------- ------------------------------ --------------- --------------------------------------------------------------------------- -----
PERFSTAT CMS_PERFSTAT_PURGE_N1 SCHEDULED 20/03/14 16:46:07,623233 +01:00 TRUE
PERFSTAT CMS_PERFSTAT_COLLECT_N2 SCHEDULED 20/03/14 16:47:59,093053 +01:00 TRUE
PERFSTAT CMS_PERFSTAT_COLLECT_N1 SCHEDULED 20/03/14 16:46:07,549197 +01:00 TRUE
PERFSTAT CMS_PERFSTAT_PURGE_N2 SCHEDULED 20/03/14 16:47:59,151279 +01:00 TRUE
## On vérifie les snapshots , via le user perfstat
Sur le noeud 1 :
EXEC dbms_scheduler.run_job('CMS_PERFSTAT_COLLECT_N1');
Sur le noeud 2 :
EXEC dbms_scheduler.run_job('CMS_PERFSTAT_COLLECT_N2');
Vérification :
alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
select INSTANCE_NUMBER, SNAP_TIME from stats$snapshot order by 1, 2;
INSTANCE_NUMBER SNAP_TIME
--------------- -----------------
1 20-03-14 17:01:09
2 20-03-14 17:06:19
Enjoy !!
dbafaq.wordpress.com 