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

转载

mb5ff4099f0a555 2010-12-20 13:44:00

文章标签 sql oracle ide sed .net 文章分类 代码人生

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

	Modified 23-NOV-2010 Type HOWTO Status PUBLISHED

In this Document

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. Create a service on the RAC cluster to setup for TAF. Example creates a service called server_taf to the database called rac, with instance names rac1 & rac2

srvctl add service -d rac -s server_taf -r "rac1,rac2" -P BASIC

2. Start the service server_taf

srvctl start service -d rac -s server_taf

3. Check service is running

srvctl config service -d rac

ractest PREF: rac1 rac2 AVAIL:

server_taf PREF: rac1 rac2 AVAIL:

4. Find the service_id value for the service just created

sqlplus /nolog

Connect / as sysdba

SQL> select name,service_id from dba_services where name = 'server_taf';

NAME SERVICE_ID

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

server_taf 6

5. Review the standard setup for the services

SQL>col name format a15

col failover_method format a11 heading 'METHOD'

col failover_type format a10 heading 'TYPE'

col failover_retries format 9999999 heading 'RETRIES'

col goal format a10

col clb_goal format a8

col AQ_HA_NOTIFICATIONS format a5 heading 'AQNOT'

SQL>select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications

from dba_services where service_id = 6

NAME METHOD TYPE RETRIES GOAL CLB_GOAL AQNOT

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

server_taf LONG NO

Note there is no value method, type or retries. These are require todo server side TAF.

This is due to Bug 6886239 DBMS_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> execute dbms_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/SQL procedure successfully completed.

Addtional failover parameters value can be found in the

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

SQL>select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications

from dba_services where service_id = 6

NAME METHOD TYPE RETRIES GOAL CLB_GOAL AQNOT

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

server_taf BASIC SELECT 180 NONE LONG YES

8. Check the listener has the service registered.

lsnrctl services

Service "server_taf.za.oracle.com" has 2 instance(s).

Instance "rac1", status READY, has 2 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

REMOTE SERVER

(ADDRESS=(PROTOCOL=TCP)(HOST=dell01)(PORT=1521))

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Instance "rac2", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

REMOTE SERVER

(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: Release 10.2.0.1.0 - Production on Tue Oct 2 12:15:44 2007

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select host_name,instance_name from v$instance;

HOST_NAME

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

INSTANCE_NAME

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

dell02

rac2

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

SQL> select instance_name from V$instance;

INSTANCE_NAME

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

rac2

SQL> shutdown abort;

ORACLE instance shut down.

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 the instance 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))))

Connection fails 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|