I was asked by one of my readers about “transparent transition” between the Primary and Standby databases when using a Data Guard environment. The question sounded relatively simple: how do you configure the clients so it will automatically work with the active site at any point in time?

The truth is that the solution is a lot easier than what you might think, and I even got to implement it in on an Oracle 10g database, more than five years.

To resolve this issue, all we can do is to create a service for Oracle and enable at any point in time only on one side (the primary side) to turn it on. Then, configure the clients to try to connect to both servers: the main site and the DR. Since the service is running on only one of the databases, the clients will always be able to connect to one site and not get an error message.

How to make it work

In the first step, we need to create a Service in the primary environment:

begin
   dbms_service.create_service('orclprod','orclprod');
end;
/

If we are using TAF (i.e. Transparent Application Failover) then we have to add a few more parameters to create the service:

begin
  dbms_service.create_service(service_name     => 'orclprod',
                              network_name     => 'orclprod',
                              failover_method  => 'basic',
                              failover_type    => 'select',
                              failover_retries => 5,
                              failover_delay   => 1);
end;
/

Then, turn on the Service:

begin
   dbms_service.start_service('orclprod');
end;
/

In principle, the order of starting the service is automatically ran in the DR as well, which is not what we wanted. To solve that, we need to specify in a startup trigger that we turn off the service only if it happens to be in the DR (or if came up with the role of DR in case they change roles):

CREATE TRIGGER Set_Primary_Service_Status AFTER STARTUP ON DATABASE
DECLARE
  v_role VARCHAR(30);
BEGIN
  SELECT DATABASE_ROLE INTO V_ROLE FROM V$DATABASE;
  
  IF v_role = 'PRIMARY' THEN
    DBMS_SERVICE.START_SERVICE('orclprod');
  ELSE
    DBMS_SERVICE.STOP_SERVICE('orclprod');
  END IF;
END;
/

At this point we should restart our standby database so it will also automatically turns off the service.

The script we have here doesn’t work properly in some cases – for example if we are working with Active Data Guard. In that case there is alter database open command (for read only and not staying mounted) which runs our startup trigger. When we switchover or failover, the trigger won’t run again and the service will not automatically start.

To resolve this issue, we can create a trigger that runs after changing the database role (work on 11g and after, haven’t tried on 10g):

CREATE TRIGGER Set_Primary_Service_Status AFTER db_role_change ON DATABASE
DECLARE
  v_role VARCHAR(30);
BEGIN
  SELECT DATABASE_ROLE INTO V_ROLE FROM V$DATABASE;
  
  IF v_role = 'PRIMARY' THEN
    DBMS_SERVICE.START_SERVICE('orclprod');
  ELSE
    DBMS_SERVICE.STOP_SERVICE('orclprod');
  END IF;
END;
/

Now, when it is set, we give our users the new TNS that includes both the shared servers and Service

orclprod =
 (DESCRIPTION =
    (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = lnx-server-1)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = lnx-server-2)(PORT = 1521))
       )
       (CONNECT_DATA =
       (SERVICE_NAME = orclprod)
    )
 )