Test Transparent Failover (TAF)

Oracle TAF enables any failed database connections to reconnect to another node within the cluster. The failover is transparent to the user. Oracle re-executes the query on the failed over instance and continues to display the remaining results to the user.

Create a new database service.

Let’s begin by creating a new service called CRM. Database services can be created using either DBCA or the srvctl utility. Here you will use DBCA to create the CRM service on PROD1.

Service Name :CRM
Database Name:PROD
Preferred Instance : PROD1
Available Instance : PROD2
TAF Policy :BASIC

Log in as a oracle user in RAC1 node and execute:

rac1-> dbca

Welcome: Select Oracle Real Application Clusters database.
Operations: Select Services Management.
List of cluster databases: Click on Next.
Database Services: Click on Add.
Add a Service: Enter “CRM.”
Select prod1 as the Preferred instance.
Select prod2 as the Available instance.
TAF Policy: Select Basic.
Click on Finish.

Database Configuration Assistant: Click on No to exit.

Note: The Database Configuration Assistant creates the CRM service name entry in tnsnames.ora file.

Check Service name String

SQL> connect system/oracle@prod1
Connected.

SQL> show parameter service

NAME TYPE VALUE
------------------------------ ----------- ------------------------
service_names string prod, CRM

SQL> connect system/oracle@prod2
Connected.

SQL> show parameter service

NAME TYPE VALUE
------------------------------ ----------- ------------------------
service_names string prod

Connect the first session using the CRM service.

If the returned output of failover_type and failover_mode is 'NONE', verify that the CRM service is configured correctly in tnsnames.ora.

SQL> connect system/oracle@crm
Connected.

SQL> select instance_number instance#, instance_name, host_name, status from v$instance;

INSTANCE# INSTANCE_NAME HOST_NAME STATUS
---------- ---------------- --------------------- ------------
1 prod1 rac1.xyz.com OPEN

SQL> select failover_type, failover_method, failed_over from v$session where username='SYSTEM';

FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
------------- --------------- ----------------
SELECT BASIC NO

Shut down the instance from another session.
Connect as the sys user on CRM instance and shut down the instance.

rac1-> export ORACLE_SID=prod1

rac1-> sqlplus / as sysdba

SQL> select instance_number instance#, instance_name, host_name, status
from v$instance;

INSTANCE# INSTANCE_NAME HOST_NAME STATUS
---------- ---------------- --------------------- ------------
1 prod1 rac1.xyz.com OPEN

SQL> shutdown abort;
ORACLE instance shut down.

Verify that the session has failed over.
From the same CRM session you opened previously, execute the queries below to verify that the session has failed over to another instance.

SQL> select instance_number instance#, instance_name, host_name, status
from v$instance;

INSTANCE# INSTANCE_NAME HOST_NAME STATUS
---------- ---------------- --------------------- ------------
2 prod2 rac2.xyz.com OPEN

SQL> select failover_type, failover_method, failed_over from v$session
where username='SYSTEM';

FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
------------- --------------- ----------------
SELECT BASIC YES

Relocate the CRM service back to the preferred instance.

After PROD1 is brought back up, the CRM service does not automatically relocate back to the preferred instance. You have to manually relocate the service to prod1.

rac1-> export ORACLE_SID=prod1

rac1-> sqlplus / as sysdba

SQL> startup

ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1218556 bytes
Variable Size 104859652 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> show parameter service
NAME TYPE VALUE
------------------------------ ----------- ------------------------
service_names string prod

rac2-> export ORACLE_SID=prod2

rac2-> sqlplus / as sysdba

SQL> show parameter service

NAME TYPE VALUE
------------------------------ ----------- ------------------------
service_names string prod, CRM

rac1-> srvctl relocate service -d prod -s crm -i prod2 -t prod1

SQL> connect system/oracle@prod1
Connected.

SQL> show parameter service

NAME TYPE VALUE
------------------------------ ----------- ------------------------
service_names string prod, CRM

SQL> connect system/oracle@devdb2
Connected.

SQL> show parameter service

NAME TYPE VALUE
------------------------------ ----------- ------------------------
service_names string prod