12c dataguard issue....

So this past week we've been doing some more 12.1.0.2 migrations - for the first time we are doing it in a system that has dataguard in place.

The upgrade itself was fine, no issues there but we discovered an issue when we tried to connect to the dataguard service name we were using.

It was throwing the error ORA-12514

Which is very familiar to a lot of you i'm sure - the full error message is

"TNS:listener does not currently know of service requested in connect descriptor"

It's basically saying you want to connect to database service "X" however the listener you are communicating with has no record of this service "X".

Now 99% of the time this is always user error - this is some misconfiguration in the listener, the database settings or the tnsnames file.

So we checked and double checked and even triple checked. Everything looked correct - the listener had a service "X" and the tnsnames had the service "X" - so what was going wrong....?

Well the service in question was the special dataguard service that is stopped/started based on a database trigger working out if the instance is primary of standby - it's a pretty standard trigger than I'm sure we got from somewhere on oracle.com some time ago 

It's of this form

create or replace trigger manage_service after startup on database 

declare 

role varchar2(40); 

begin 

select database_role into role from v$database; 

if role = 'PRIMARY' then 

dbms_service.start_service('X'); 

else 

dbms_service.stop_service('X'); 

end if; 

end; 

/ 

So if i'm the primary the service X should be registered with the listener when the database comes up - otherwise not.

The weird thing with this was that the entry was appearing in the listener correctly - so it looked like everything was OK - it's only when you try and connect you get the error.

Curiouser and curiouser.....

Now suspending my disbelief for a while i did some basic tests on a standalone instance on 11g vs 12c taking dataguard out of the picture - and i mocked up this test

First i create a database service called FRED

begin 

dbms_service.create_service( service_name => 'FRED', 

network_name => ' FRED', 

failover_method => 'BASIC', 

failover_type => 'SELECT', 

failover_retries => 180, 

failover_delay => 1); 

end; 

/ 

then activate it 

begin 

dbms_service.start_service('FRED') 

end; 

/ 

Now in 11.2 this all works fine and i can connect to it fine (using a 12c shared listener), if i do the exact same test in 12c (same server, same listener) it doesn't work.....

It seems that it registers OK with the listener but the service is not actually there - this can be confirmed if you look at the value of the SERVICE_NAMES database setting - in 11.2 the start_service call updates this parameter - in 12c it doesn't.

So believe it or not it is a bug (and Oracle support were a little surprised too.....)

So anyway bug has now gone to development to investigate...

My assumption (which is pure speculation) is that the changes to take work away from pmon for service registration (using this new lreg process) has missed out this particular use case for services?

Anyway we'll see what they come back with.

If there is no quick fix i think we'll just modify our startup trigger to change the service_names database parameter explicitly which should fix it - we don;t want this issue to prevent our 12c upgrades.