1 Test case :
1.1 Creation de 2 tables
1.1.1 Rac_sticky
create table rac_sticky (
sid number,
service_name varchar2(30),
instance_name varchar2(30),
time timestamp with time zone);
1.1.2 Rac_no_sticky
create table rac_no_sticky (
sid number,
service_name varchar2(30),
instance_name varchar2(30),
time timestamp with time zone);
1.2 service :
srvctl add service -d drpocrad –s departures -r DRPOCRAD1 -a DRPOCRAD2
srvctl start service -d drpocrad -s departures
1.3 job class
BEGIN
DBMS_SCHEDULER.create_job_class (
job_class_name => 'departures_class',
service => 'departures',
logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
comments => 'run on the departures service');
END;
2 Job sticky
begin
dbms_scheduler.create_job
(
job_name => 'RACsticky',
job_class => 'departures_class',
job_type => 'plsql_block',
job_action => 'begin insert into rac_sticky
(sid,instance_name,service_name,time)
select sid, instance_name, service_name,
systimestamp from v$session, v$instance
where sid = (select sid from v$mystat
where rownum = 1);
end;',
repeat_interval => 'freq=minutely;interval=2'
);
dbms_scheduler.set_attribute
(
name => ' RACsticky ',
attribute => 'instance_id',
value => 1
);
dbms_scheduler.enable(' RACsticky ');
end;
/
3 Job pas sticky :
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'RAC_NO_STCIKY',
job_class => 'departures_class',
job_type => 'plsql_block',
job_action => 'begin insert into rac_no_sticky
(sid,instance_name,service_name,time)
select sid, instance_name, service_name,
systimestamp from v$session, v$instance
where sid = (select sid from v$mystat
where rownum = 1);
end;',
repeat_interval => 'freq=minutely;interval=2');
BEGIN
DBMS_SCHEDULER.set_attribute (name => 'RAC_NO_STCIKY',
attribute => 'instance_stickiness',
VALUE => FALSE);
END;
DBMS_SCHEDULER.enable ('RAC_NO_STCIKY');
END;
/
4 Conclusion
Table RAC_NO_STICKY:
31 departures DRPOCRAD2 4/5/2013 11:20:38.986239 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:22:36.444641 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:24:36.045879 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:26:36.023985 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:28:36.029161 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:30:36.026770 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:32:36.030054 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:34:36.021619 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:36:36.023324 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:38:36.053010 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:40:36.039355 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:42:36.032869 AM -04:00
31 departures DRPOCRAD2 4/5/2013 11:44:36.044179 AM -04:00
164 departures DRPOCRAD1 4/5/2013 11:46:42.243874 AM -04:00
Table RAC_STICKY :
En rouge l arret de l instance 1
33 departures DRPOCRAD1 4/5/2013 11:13:55.758974 AM -04:00
139 departures DRPOCRAD1 4/5/2013 11:15:53.068770 AM -04:00
139 departures DRPOCRAD1 4/5/2013 11:17:53.051646 AM -04:00
139 departures DRPOCRAD1 4/5/2013 11:19:53.048000 AM -04:00
139 departures DRPOCRAD1 4/5/2013 11:21:53.063570 AM -04:00
139 departures DRPOCRAD1 4/5/2013 11:23:53.056897 AM -04:00
162 departures DRPOCRAD1 4/5/2013 11:38:03.028472 AM -04:00
162 departures DRPOCRAD1 4/5/2013 11:39:53.053529 AM -04:00
162 departures DRPOCRAD1 4/5/2013 11:41:53.044608 AM -04:00
156 departures DRPOCRAD1 4/5/2013 11:43:53.050679 AM -04:00
164 departures DRPOCRAD1 4/5/2013 11:46:38.831013 AM -04:00
37 departures DRPOCRAD1 4/5/2013 11:47:53.043582 AM -04:00
37 departures DRPOCRAD1 4/5/2013 11:49:53.033371 AM -04:00
Bascule sur service et/ou arrêt de l instance 1 ,
le job 'RAC_NO_STCIKY' continue alors que le RACsticky ne roule que sur l instance 1.
5 Cleanup
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'MG. RACsticky ');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'MG. RAC_no_sticky ');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB_CLASS
(
job_class_name => 'SYS.DEPARTURES_CLASS'
,force => TRUE
);
END;
/
Srvctl stop service -d drpocrad -s departures
Srvctl remove service -d drpocrad -s departures
Drop table rac_sticky;
Aucun commentaire:
Enregistrer un commentaire