In this Document
Purpose
Scope and Application
11gR2 RAC TAF Configuration for Admin and Policy Managed Databases
Concepts & Overview
Administrator-managed Database
Policy-managed Database
References
Applies to:
Oracle Net Services - Version: 11.2.0.1 to 11.2.0.2 - Release: 11.2 to 11.2
Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.2 [Release: 11.2 to 11.2]
Information in this document applies to any platform.
Purpose
This article discussed about Transparent Application Failover (TAF) and its configuration details for 11gR2 cluster database.
Scope and Application
This article is intended for Oracle Cluster Administrators who would like more details about TAF. Basic understanding of Oracle Clusterware Database is assumed.
11gR2 RAC TAF Configuration for Admin and Policy Managed Databases
Concepts & Overview
TAF allows Oracle clients to reconnect to surviving instance in the event of a failure of the current instance to which it connected. There are two types of TAF available, SESSION and SELECT.
SESSION: Session Failover re-creates the connections and sessions to the surviving instance.
SELECT: In addition to recreating the session, Select Failover also replays the queries that were in progress.
There are two method in which TAF estabilishes the faiover connection, BASIC and PRECONNECT.
BASIC: The second connection is re-established only after the first connection to the instance failed.
PRECONNECT: Two connections are established when first login to database. A login to database will create two connection at the same time. For this to work clusterware actually starts two service. One main service and another shadow service.
TAF can be configured at the Oracle Client side in tnsnames.ora or at the Database Server side using
srvctl. Configuring it at the server is preferred as it is convenient to put the configuration in one place i.e. on the server. This article focuses on configuration at the server using
srvctl. TAF configuration is associated with the database service. Creating the service with TAF related options is all about configuring TAF.
1. Create service with srvctl
srvctl defines services in OCR which is accessible across nodes. The service configuration details can be viewed with
srvctl config service command.
2. Start the Service with srvctl
When the service is started with srvctl, it actually creates a service in the database i.e. RDBMS. The service name and the configuration details of the service created at the database can be viwed with DBA_SERVICES view. Then the SERVICE_NAMES database parameter is set, which allows PMON to register this service with the listener. Hence connections can be established through listener to the database.
NOTE:
The clusterware automatically starts/stops and manages the services in the database according to the configuration of the service.
manually setting the SERVICE_NAMES parameter in RAC is not allowed as clusterware manages setting this parameter.
Let's have a look at the
srvctl syntax for creating service. Databases in 11gR2 can be Administrator-managed or Policy-managed. Certain options can be used only with certain type of database.
srvctl add service -d <db_unique_name> -s <service_name>
-r "<preferred_list>" [-a "<available_list>"] [-P {BASIC | NONE | PRECONNECT}]
-g <server_pool> [-c {UNIFORM | SINGLETON}]
[-k <net_num>]
[-l [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]]
[-y {AUTOMATIC | MANUAL}]
[-q {TRUE|FALSE}]
[-x {TRUE|FALSE}]
[-j {SHORT|LONG}]
[-B {NONE|SERVICE_TIME|THROUGHPUT}]
[-e {NONE|SESSION|SELECT}]
[-m {NONE|BASIC}]
[-z <failover_retries>]
[-w <failover_delay>]
Here is the description of the options that we are going to use in configuring TAF.
-r "<preferred_list>" [-a "<available_list>"] [-P {BASIC | NONE | PRECONNECT}]
This clause is valid
only for Administrator-managed database which support PRECONNECT method.
-g <server_pool> [-c {UNIFORM | SINGLETON}]
This clause is valid
only for Policy-managed database where in PRECONNECT method is not available.
The above two options instruct clusterware on how to handle this service.
[-e {NONE|SESSION|SELECT}]
This defines the type of TAF whether SESSION or SELECT.
[-m {NONE|BASIC}]
This defines the method of TAF.
[-z <failover_retries>]
This defines the the number of times to attempt to connect after a failover.
[-w <failover_delay>]
This defines the amount of time in seconds to wait between connect attempts.
Above four options are passed to database while starting up the service and is viewable in DBA_SERVICES view.
Administrator-managed Database
Basic Method
1. Create Service
Syntax:
srvctl add service -d <db_unique_name> -s <service_name>
-r "<preferred_list>" [-a "<available_list>"] [-P {BASIC | NONE | PRECONNECT}]
[-e {NONE|SESSION|SELECT}]
[-m {NONE|BASIC}]
[-z <failover_retries>]
[-w <failover_delay>]
Example:
$
srvctl add service -d db112
-s mysrvb
-r db1121,db1122
-P basic
-e select
-m basic
-z 10
-w 2
Though this example only uses -r option, you can use both -r and -a option.
2. Start the service
$
srvctl start service -d db112
-s mysrvb
Check if the service is rightly registered with the listener.
$lsnrctl status listener_scan1
. . .
. . .
Service "mysrvb" has 2 instance(s).
Instance "db1121", status READY, has 1 handler(s) for this service...
Instance "db1122", status READY, has 1 handler(s) for this service...
. . .
. . .
3. Make a connection.
The client connect string do not require any special TAF parameters. A simple tnsnames.ora entry as shown below will do.
mysrvb =
(description =
(address = (protocol=tcp)(host=secrac-scan)(port=1521))
(connect_data=
(service_name=mysrvb)
)
)
$
sqlplus scott/tiger
@mysrvb
Once connectd GV$SESSION view will reflect the connection made is TAF enabled. The FAILOVER_METHOD and FAILOVER_TYPE column reflects it and this confirms the TAF configuration is correct.
SQL> select inst_id,username,service_name,failover_type,failover_method,failed_over from gv$session where username='SCOTT';
INST_ID USERNAME SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI
------- -------- ------------ ------------- ---------- ---
1 SCOTT mysrvb SELECT BASIC NO
Note that this has connected to the instance 1 with BASIC method and SELECT type.
Preconnect Method
1. Create service
Syntax:
srvctl add service -d <db_unique_name> -s <service_name>
-r "<preferred_list>" [-a "<available_list>"] [-P {BASIC | NONE | PRECONNECT}]
[-e {NONE|SESSION|SELECT}]
[-m {NONE|BASIC}]
Example:
$
srvctl add service -d db112
-s mysrv
-r db1121
-a db1122
-P preconnect
NOTE:
* The -e, -m option should be set to none. Omiting this option actually sets it to none.
* Use both -r and -a option for preconnect, hence a shadow service can be started.
2. Start the service.
$
srvctl start service -d db112
-s mysrv
Preconnect creates an additional shadow service.
$ crsctl stat res -t
. . .
. . .
ora.db112.mysrv.svc
1 ONLINE ONLINE secrac1
ora.db112.mysrv_preconnect.svc
1 ONLINE ONLINE secrac3
. . .
. . .
Note that the service
'mysrv' is started on the preferred instance. And the shadow service
'mysrv_preconnect' is started on the available instance. The shadow service is always defined as
<servicename>_preconnect.
Check if the service is correctly registered with the listener.
$ lsnrctl status listener_scan1
. . .
. . .
Service "mysrv" has 1 instance(s).
Instance "db1121", status READY, has 1 handler(s) for this service...
Service "mysrv_preconnect" has 1 instance(s).
Instance "db1122", status READY, has 1 handler(s) for this service...
. . .
. . .
3. Define TNS entry.
Preconnect requires special parameter at the client side tnsnames.ora file. The TAF Type and the TAF method is actually specified at the client end.
mysrv =
(description =
(address = (protocol=tcp)(host=secrac-scan)(port=1521))
(connect_data=
(service_name=mysrv)
(failover_mode=
(backup=mysrv_preconnect)
(type=select)
(method=preconnect)
)
)
)
mysrv_preconnect =
(description =
(address = (protocol=tcp)(host=secrac-scan)(port=1521))
(connect_data=(service_name=mysrv_preconnect))
)
Connect to the database with this tns entry
$
sqlplus scott/tiger
@mysrv
SQL> select inst_id,username,service_name,failover_type,failover_method,failed_over from gv$session where username='SCOTT';
INST_ID USERNAME SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI
------- -------- ---------------- ------------- ---------- ---
1 SCOTT mysrv SELECT PRECONNECT NO
2 SCOTT mysrv_preconnect NONE NONE NO
Note that it has made two connections. The first one is the active main connection with PRECONNECT method and SELECT type. The second one is the shadow connection that will be used when the connection to the first instance fails.
Policy-managed Database
Basic Method
1. Create the service
Syntax:
srvctl add service -d <db_unique_name> -s <service_name>
-g <server_pool> [-c {UNIFORM | SINGLETON}]
[-e {NONE|SESSION|SELECT}]
[-m {NONE|BASIC}]
[-z <failover_retries>]
[-w <failover_delay>]
Example:
$
srvctl add service -d db1
-s mysrvp
-g mysrvpool
-c uniform
-e select
-m basic
-z 10
-w 2
2. Start the service
$
srvctl start service -d db1
-s mysrvp
Check if the service is correctly registered with the listener
$ lsnrctl status listener_scan1
. . .
. . .
Service "mysrvp" has 2 instance(s).
Instance "db1_1", status READY, has 1 handler(s) for this service...
Instance "db1_2", status READY, has 1 handler(s) for this service...
. . .
. . .
3. Make a connection.
The client connect string do not require any special TAF parameters. A simple tnsnames.ora entry as shown below will do.
mysrvp =
(description =
(address = (protocol=tcp)(host=secrac-scan)(port=1521))
(connect_data=
(service_name=mysrvp)
)
)
$
sqlplus scott/tiger
@mysrvp
SQL> select inst_id,username,service_name,failover_type,failover_method,failed_over from gv$session where username='SCOTT';
INST_ID USERNAME SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI
------- -------- ------------ ------------- ---------- ---
1 SCOTT mysrvp SELECT BASIC NO
