I posted a couple of months ago about an issue i was having with 12c dataguard not working as i expected - see here
I then found my own workaround for it - see here
However the original ticket i raised with support came back suggesting that this 'manual' method of controlling services should not be used and the changes that have happened in this area have been done deliberately because of CDB/PDB architecture changes.
I wasn't happy about this so i contacted someone at Oracle who would have the inside line about what was happening here.
He replied back and actually was not able to reproduce the issue, he was running with Oracle restart though (and even though the service was not created through restart i thought perhaps this was having some effect).
Anyway i tried the test case again on an installation i had of restart and it did seem to work fine but i was still a little confused - why should this really make any difference?
So i tried the test case again on a 'traditional' database and guess what it worked! ??????
What happened?
Well at first i couldn't believe it but then i went back and compared the test cases - there was a difference....
The original non working test case used this block of code
begin
dbms_service.create_service( service_name => 'FRED',
network_name => ' FRED',
failover_method => 'BASIC',
failover_type => 'SELECT',
failover_retries => 180,
failover_delay => 1);
end;
/
The new one just this (with no TAF settings)
begin
dbms_service.create_service( service_name => 'FRED',
network_name => ' FRED');
end;
/
So now we are getting somewhere - and if we look at the doc page for this proc we get a hint maybe why https://docs.oracle.com/database/121/ARPLS/d_serv.htm#ARPLS68020
There are 2 versions of the code the original one and a new overloaded one that seems to be the one that should be being used.
So lets try out the new way of setting up a TAF service
So we create the basics of the service with minimal parameters
exec DBMS_SERVICE.CREATE_SERVICE('FRED2','FRED2');
Then we build an array of parameters to pass in to modify service
DECLARE
params dbms_service.svc_parameter_array;
BEGIN
params('FAILOVER_METHOD') :='BASIC';
params('FAILOVER_TYPE') :='SELECT';
params('FAILOVER_DELAY') :=1;
params('FAILOVER_RETRIES') :=180;
DBMS_SERVICE.MODIFY_SERVICE('ELHUBDG',params);
END;
/
Then we start it
exec dbms_service.start_service('FRED2');
And guess what it works!
So it seems the DBMS_SERVICE call in 12c has a bug unless you use the new one - so use the new one!
If you do use the new one then the startup trigger can just stay as it is. However i/we should be using oracle restart to do this and not triggers as i was told by my oracle contact - and before you say "but its deprecated" - take a look at this note on MOS Doc ID 1584742.1
Comments
Post a Comment