Test Transparent Failover (TAF)
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
Responses