Please make sure you have followed the steps from this article before configuring TAF.
The configuration of TAF in FlashGrid Cluster environment is essentially the same as in an on-prem Oracle RAC environment. The main and only difference is the connect string, which will be based on a FlashGrid Cluster connect string.
To configure TAF:
Create database service
- Create a service on the cluster to setup for TAF
$ srvctl add service -db TAF -service FG_TAF -preferred "TAF1,TAF2" -tafpolicy BASIC
- Start the created service
$ srvctl start service -db TAF -service FG_TAF
- Check that the service is running
$ srvctl config service -db TAF
Service name: FG_TAF
Server pool:
Cardinality: 2
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Pluggable database name:
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: TAF1,TAF2
Available instances:
CSS critical: no
Service uses Java: false
- Now you can modify the service according to your needs, for example:
$ srvctl modify service -db TAF -service FG_TAF -failovermethod BASIC -failovertype SELECT -notification TRUE -clbgoal LONG
- Let's check the configuration again, we can see the failover configured now
Service name: FG_TAF Server pool: Cardinality: 2 Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: true Global: false Commit Outcome: false Failover type: SELECT Failover method: BASIC Failover retries: Failover delay: Failover restore: NONE Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: BASIC Edition: Pluggable database name: Hub service: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Replay Initiation Time: 300 seconds Drain timeout: Stop option: Session State Consistency: DYNAMIC GSM Flags: 0 Service is enabled Preferred instances: TAF1,TAF2 Available instances: CSS critical: no Service uses Java: false
Test TAF via SQL*Plus (recommended)
It is recommended to test TAF via SQL*Plus since it is a native Oracle Call Interface (OCI) client that does not require additional configuration to support TAF.
-
Create the connection string for the service
FG_TAF= (DESCRIPTION= (TRANSPORT_CONNECT_TIMEOUT=3) (RETRY_COUNT=6) (ADDRESS= (PROTOCOL=tcp) (HOST=rac1.example.com) (PORT=1521)) (ADDRESS= (PROTOCOL=tcp) (HOST=rac2.example.com) (PORT=1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FG_TAF) ) ) - Test the failover by connecting through the service, then stopping the instance where we got connected first and verify the failover working
[oracle@rac1 ~]$ sqlplus system/password@FG_TAF SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 4 11:40:53 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 SQL> select host_name,instance_name from v$instance; HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- rac1.example.com TAF1
-- shutdown the instance to which the client is connected. Then, rerun the same query
SQL> select host_name,instance_name from v$instance; HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- rac2.example.com TAF2
Test TAF via SQLcl
Alternatively, SQLcl can be used to test TAF. The following prerequisites must be met for that:
- SQLcl must use OCI driver
- The driver can be set in the connection string, e.g.:
sql user/password@jdbc:oracle:oci:@fg_taf - It can also be specified as an option for
SQLcl, e.g.:sql -oci user/password@fg_taf
oracle@client:~/sqlcl/bin$ ./sql system/password@jdbc:oracle:oci:@fg_taf SQLcl: Release 20.4 Production on Mon Feb 22 10:46:40 2021 Copyright (c) 1982, 2021, Oracle. All rights reserved. Last Successful login time: Mon Feb 22 2021 10:46:41 +00:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 SQL> SQL> select host_name, instance_name from v$instance; HOST_NAME INSTANCE_NAME ________________ ___________________ rac1.example.com TAF1 SQL> SQL> select failover_type, failover_method, failed_over from v$session where sid=sys_context('userenv', 'sid'); FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER ________________ __________________ ______________ SELECT BASIC NO SQL> -- shutdown the instance to which the client is connected. Then, rerun the same queries SQL> SQL> select host_name, instance_name from v$instance; HOST_NAME INSTANCE_NAME ________________ ___________________ rac2.example.com TAF2 SQL> SQL> SQL> select failover_type, failover_method, failed_over from v$session where sid=sys_context('userenv', 'sid'); FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER ________________ __________________ ______________ SELECT BASIC YES
Test TAF via SQL Developer
Note: Some older versions of SQL Developer cannot be used to test TAF due to Bug 32445960 : SQL DEVELOPER SHOWS FAILED_OVER=NO WHEN A NODE OF 2 NODE RAC FAILOVER. This article has been validated against SQL Developer 21.4.1.349.
- Download Oracle Instant Client Basic
- Unzip the client
- Include the Instant Client location to the PATH environment variable as the first entry
- Start SQL*Developer
- Go to Tools→Preferences→Database→Advanced
- Tick the Use Oracle Client checkbox
-
Click Configure:
- Specify Client Type: Instant Client
- Specify Client Location: the location to which the Instant Client was unzipped
- Click the Test button
-
View the test results in the SQL Developer log window "Oracle Client Test Results - Log". Make sure that all checks are passed. Sample output:
Testing the Instant Client located at D:\oracle\product\client\instantclient_19_13 Testing client directory ... OK Testing loading Oracle JDBC driver ... OK Testing checking Oracle JDBC driver version ... OK Driver version: 19.13.0.0.0 Testing testing native OCI library load ... OK Success!
- Click the OK button in the Configure Oracle Client window and restart SQL Developer
- Create a new database connection
- Specify Name, Username, Password for the new connection
- Select Custom JDBC in Connection Type
-
Paste
jdbc:oracle:oci:@followed by the TNS description string in the Custom JDBC URL field:jdbc:oracle:oci:@(DESCRIPTION= (TRANSPORT_CONNECT_TIMEOUT=3) (RETRY_COUNT=6) (ADDRESS= (PROTOCOL=tcp) (HOST=rac1.example.com) (PORT=1521)) (ADDRESS= (PROTOCOL=tcp) (HOST=rac2.example.com) (PORT=1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FG_TAF)))
- Click Test. Make sure that the test is successful
- Click Save. Click Connect
-
The following script can be used to test TAF:
set echo on set lines 200 col host_name for a25 select host_name, instance_name from v$instance; col failover_method for a15 col failed_over for a12 select failover_type, failover_method, failed_over from v$session where sid=sys_context('userenv', 'sid'); -
Run it as a script to get the instance to which the client is connected. Shut down abort that instance. Then, rerun the same script. Here is a sample output:
SQL> set lines 200 SQL> SQL> col host_name for a25 SQL> SQL> select host_name, instance_name from v$instance; HOST_NAME INSTANCE_NAME ------------------------- ---------------- rac2.example.com TAF2 SQL> SQL> col failover_method for a15 SQL> col failed_over for a12 SQL> SQL> select failover_type, failover_method, failed_over from v$session where sid=sys_context('userenv', 'sid'); FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER ------------- --------------- ------------ SELECT BASIC NO -- shutdown the instance to which the client is connected. Then, rerun the same script SQL> set echo on SQL> SQL> set lines 200 SQL> SQL> col host_name for a25 SQL> SQL> select host_name, instance_name from v$instance; HOST_NAME INSTANCE_NAME ------------------------- ---------------- rac1.example.com TAF1 SQL> SQL> col failover_method for a15 SQL> col failed_over for a12 SQL> SQL> select failover_type, failover_method, failed_over from v$session where sid=sys_context('userenv', 'sid'); FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER ------------- --------------- ------------ SELECT BASIC YES