How To Configure Server Side Transparent Application Failover [ID 460982.1]

How To Configure Server Side Transparent Application Failover [ID 460982.1]
	Modified23-NOV-2010TypeHOWTOStatusPUBLISHED

In this Document
Goal
Solution

---

Goal

How to configure Server Side Transparent Application Failover (TAF)?

Server side TAF settings override client-side counterparts that might be configured in TNS connect descriptors. If TAF is not configured on the client side, then at a minimum, the failover type must be set to enable TAF. If the failover type is set on the server side, then the failover method will default to BASIC. Delay and retries are optional and may be specified independently.

Solution

1.Createa service on the RAC cluster to setup for TAF.Example creates aservice called server_taf to the database called rac, with instance names rac1 & rac2

srvctladdservice-drac-sserver_taf-r"rac1,rac2"-PBASIC

2.Start the service server_taf

srvctlstartservice-drac-sserver_taf

3.Check service is running

srvctlconfigservice-drac

ractestPREF:rac1rac2AVAIL:
server_tafPREF:rac1rac2AVAIL:

4.Find the service_id value for the service just created

sqlplus/nolog
Connect/assysdba
SQL>selectname,service_idfromdba_serviceswherename='server_taf';

NAMESERVICE_ID
--------------------------------------------------------------------------
server_taf6

5. Review the standard setup for the services

SQL>colnameformata15
colfailover_methodformata11heading'METHOD'
colfailover_typeformata10heading'TYPE'
colfailover_retriesformat9999999heading'RETRIES'
colgoalformata10
colclb_goalformata8
colAQ_HA_NOTIFICATIONSformata5heading'AQNOT'

SQL>selectname,failover_method,failover_type,failover_retries,goal,clb_goal,aq_ha_notifications
fromdba_serviceswhereservice_id=6

NAMEMETHODTYPERETRIESGOALCLB_GOALAQNOT
-------------------------------------------------------------------
server_tafLONGNO

Note there is no value method, type or retries. These are require todo server side TAF.
This is due toBug 6886239DBMS_SERVICE parameters are note added using "srvctl add service" fixed in release 11.2

6.Add the server side failover parameters to the service.

Server side TAF method is BASIC. BASIC is the only value currently supported. This means that a new connection is established at failure time. It is not possible to pre-establish a backup connection. (which is to say, PRECONNECT is not supported)

SQL>executedbms_service.modify_service(service_name=>'server_taf'-
,aq_ha_notifications=>true-
,failover_method=>dbms_service.failover_method_basic-
,failover_type=>dbms_service.failover_type_select-
,failover_retries=>180-
,failover_delay=>5-
,clb_goal=>dbms_service.clb_goal_long);

PL/SQLproceduresuccessfullycompleted.

Addtional failover parameters value can be found in theOracle�Database PL/SQL Packages and Types Reference 11g Release 1 (11.1), under section 116 DBMS_SERVICE

7.Check the service and we can now see values for Method, Type and Retries.

SQL>selectname,failover_method,failover_type,failover_retries,goal,clb_goal,aq_ha_notifications
fromdba_serviceswhereservice_id=6


NAMEMETHODTYPERETRIESGOALCLB_GOALAQNOT
-------------------------------------------------------------------
server_tafBASICSELECT180NONELONGYES

8.Check the listener has the service registered.

lsnrctlservices

Service"server_taf.za.oracle.com"has2instance(s).
Instance"rac1",statusREADY,has2handler(s)forthisservice...
Handler(s):
"DEDICATED"established:0refused:0state:ready
REMOTESERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=dell01)(PORT=1521))
"DEDICATED"established:0refused:0state:ready
LOCALSERVER
Instance"rac2",statusREADY,has1handler(s)forthisservice...
Handler(s):
"DEDICATED"established:0refused:0state:ready
REMOTESERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=dell02)(PORT=1521))

9.Create a net service name. Here we have client load balancing between the two nodes.

SERVERTAF=
(DESCRIPTION=
(LOAD_BALANCE=yes)
(ADDRESS=(PROTOCOL=TCP)(HOST=dell01)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=dell02)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=server_taf.za.oracle.com)
)
)

10.Testing...

SQL*Plus:Release10.2.0.1.0-ProductiononTueOct212:15:442007

Copyright(c)1982,2005,Oracle.Allrightsreserved.


Connectedto:
OracleDatabase10gEnterpriseEditionRelease10.2.0.3.0-Production
WiththePartitioning,RealApplicationClusters,OLAPandDataMiningoptions

SQL>selecthost_name,instance_namefromv$instance;

HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
dell02
rac2

11.Shutdown the database in the node the connection has routed to

SQL>selectinstance_namefromV$instance;

INSTANCE_NAME
----------------
rac2

SQL>shutdownabort;
ORACLEinstanceshutdown.

12.TAF will now kick in

SQL>/

HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
dell01
rac1

Oracle Net client trace of sqlplus connection during failover shows :

Shows the calling net service name

[02-OCT-2007 12:15:44:618] niotns: Calling address: (DESCRIPTION=(LOAD_BALANCE=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=dell01)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=dell02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=server_taf.za.oracle.com)
(CID=(PROGRAM=d:/oracle/102ee/bin/sqlplus.exe)(HOST=sflood-uk2)(USER=sflood))))

Selected node dell02

[02-OCT-2007 12:15:44:648] nttbnd2addr: looking up IP addr for host: dell02

Connection handshake

[02-OCT-2007 12:15:44:878] nscon: sending NSPTCN packet
[02-OCT-2007 12:15:45:229] nscon: got NSPTRS packet
[02-OCT-2007 12:15:45:229] nscon: sending NSPTCN packet
[02-OCT-2007 12:15:45:429] nscon: got NSPTAC packet

The select running

[02-OCT-2007 12:16:04:046] nspsend: 00 00 00 00 00 E8 64 0B |......d.|
[02-OCT-2007 12:16:04:046] nspsend: 01 2E 73 65 6C 65 63 74 |..select|
[02-OCT-2007 12:16:04:046] nspsend: 20 68 6F 73 74 5F 6E 61 |.host_na|
[02-OCT-2007 12:16:04:046] nspsend: 6D 65 2C 69 6E 73 74 61 |me,insta|
[02-OCT-2007 12:16:04:046] nspsend: 6E 63 65 5F 6E 61 6D 65 |nce_name|
[02-OCT-2007 12:16:04:046] nspsend: 20 66 72 6F 6D 20 76 24 |.from.v$|
[02-OCT-2007 12:16:04:046] nspsend: 69 6E 73 74 61 6E 63 65 |instance|

Here the the time theinstance was shutdown

[02-OCT-2007 12:16:05:077] nioqrc: exit
[02-OCT-2007 12:18:20:642] nioqsn: entry

Select attempts to run again

[02-OCT-2007 12:18:20:652] nspsend: 00 00 00 00 00 E8 64 0B |......d.|
[02-OCT-2007 12:18:20:652] nspsend: 01 2E 73 65 6C 65 63 74 |..select|
[02-OCT-2007 12:18:20:652] nspsend: 20 68 6F 73 74 5F 6E 61 |.host_na|
[02-OCT-2007 12:18:20:652] nspsend: 6D 65 2C 69 6E 73 74 61 |me,insta|
[02-OCT-2007 12:18:20:652] nspsend: 6E 63 65 5F 6E 61 6D 65 |nce_name|
[02-OCT-2007 12:18:20:652] nspsend: 20 66 72 6F 6D 20 76 24 |.from.v$|
[02-OCT-2007 12:18:20:652] nspsend: 69 6E 73 74 61 6E 63 65 |instance|

Fails, due to instance was shutdown

[02-OCT-2007 12:18:20:652] nserror: nsres: id=0, op=68, ns=12537, ns2=12560; nt[0]=507, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[02-OCT-2007 12:18:20:652] nsrdr: error exit
[02-OCT-2007 12:18:20:652] nsdo: nsctxrnk=0
[02-OCT-2007 12:18:20:652] nsdo: error exit
[02-OCT-2007 12:18:20:652] nioqer: entry
[02-OCT-2007 12:18:20:652] nioqer: incoming err = 12151
[02-OCT-2007 12:18:20:652] nioqce: entry
[02-OCT-2007 12:18:20:652] nioqce: exit
[02-OCT-2007 12:18:20:652] nioqer: returning err = 3113

TAF kicks in

[02-OCT-2007 12:18:20:652] nsc2addr: (DESCRIPTION=(LOAD_BALANCE=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=dell01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=server_taf.za.oracle.com)(CID=(PROGRAM=d:/oracle/102ee/bin/sqlplus.exe)(HOST=sflood-uk2)(USER=sflood))))

Connectionfails over to node dell01

[02-OCT-2007 12:18:20:652] nttbnd2addr: looking up IP addr for host: dell01

Connection handshake is completed

[02-OCT-2007 12:18:20:863] nscon: sending NSPTCN packet
[02-OCT-2007 12:18:23:547] nscon: got NSPTRS packet
[02-OCT-2007 12:18:23:547] nscon: sending NSPTCN packet
[02-OCT-2007 12:18:23:747] nscon: got NSPTAC packet

Select is run

[02-OCT-2007 12:18:47:861] nspsend: 00 00 00 00 00 E8 64 0B |......d.|
[02-OCT-2007 12:18:47:861] nspsend: 01 2E 73 65 6C 65 63 74 |..select|
[02-OCT-2007 12:18:47:861] nspsend: 20 68 6F 73 74 5F 6E 61 |.host_na|
[02-OCT-2007 12:18:47:861] nspsend: 6D 65 2C 69 6E 73 74 61 |me,insta|
[02-OCT-2007 12:18:47:861] nspsend: 6E 63 65 5F 6E 61 6D 65 |nce_name|
[02-OCT-2007 12:18:47:861] nspsend: 20 66 72 6F 6D 20 76 24 |.from.v$|
[02-OCT-2007 12:18:47:861] nspsend: 69 6E 73 74 61 6E 63 65 |instance|

---

Related

------------------------------------------------------------------------------