Configuring TNS entries for failover – Standby databases

1. Configure a service name on the primary database

SQL > exec DBMS_SERVICE.CREATE_SERVICE (-
service_name => ' bharat-ha',-
network_name => 'bharat-ha',-
failover_method => 'BASIC',-
failover_type => 'SELECT',-
failover_retries => 180,-
failover_delay => 1);
PL/SQL procedure successfully completed.

or

sql> alter system set service_names='bharat-ha' scope=both;

2. Start the service . Here we are creating a trigger which will start the service once the instance is detected as primary

SQL > CREATE OR REPLACE TRIGGER manage_dgservice
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('fsfo');
END IF;
END;

3. Add the following line to sqlnet.ora at the client side

SQLNET.OUTBOUND_CONNECT_TIMEOUT = 3

Above statement will assure that the failed connections will not wait for TCP timeout but will immediately proceed to the next host if the primary is unavailable

4. Add the below tns entry to tnsnames.ora

bharat-ha =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.31)(PORT = 1533))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.32)(PORT = 1533))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bharat-ha)
)
)

or add below if service_names is set in spfile

bharat-ha =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.31)(PORT = 1533))
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.32)(PORT = 1533))
 (LOAD_BALANCE = yes)
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = bharat-ha)
 (FAILOVER_MODE =
 (TYPE = SELECT) # (TYPE = SESSION) 
 (METHOD = BASIC)
 (RETRIES = 180)
 (DELAY = 5)
 )
 )
 )

Testing Failover Connection

1. Connect from a client server using the ‘bharat-ha’ service, check the instance names

SQL> sho parameter unique
NAME            TYPE      VALUE
-------------- --------  -----------
db_unique_name string     bharat_pri

2. From the client server, connected through the ‘bharat-ha’ service start a long running query and check how long it takes to complete

SQL> select 1,2,3 from dba_source
1 2 3
1 2 3
...
...
1 2 3
1 2 3
140829 rows selected.
Elapsed: 00:0:30.12

3. From the client server, connected through the ‘bharat-ha’ service issue again the same long running query, and in another session kill the primary database

SQL> select 1,2,3 from dba_source 
1 2 3
1 2 3
... 
... 

<< db process was killed at this point>>

[oracle@rac1 ~]$ ps -ef | grep pmon
oracle 3120 1 0 16:28 ? 00:00:00 ora_pmon_bharat
oracle 3255 3082 0 16:32 pts/1 00:00:00 grep pmon
[oracle@rac1 ~]$ kill -9 3120

This will trigger a fast start failover. Check the observer log

[W000 03/24 12:53:44.12] Observer started.
13:14:59.76 Sunday, March 24, 2013
Initiating Fast-Start Failover to database "bharat_stby"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "bharat_stby"
13:15:10.77 Sunday, March 24, 2013

At the client screen you will notice that the output from ‘select 1,2,3 from dba_source’ stops and then continues until finished

...
...
 <<<<<< Database process killed here
...
1 2 3
1 2 3
140829 rows selected
Elapsed: 00:01:23.23 <<<<<< the select took longer but finished

4. Check the instance name now

SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
bharat_stby
Elapsed: 00:00:00.07