Using Transparent Application Failover in Oracle Real Applications Clusters

You need to make sure that both instances RACDB1 and RACDB2 are running. Open a terminal window and execute the following command

If either of the databases are offline, execute any one of the following commands depending on your situation:

srvctl start instance -d RACDB -i RACDB1
srvctl start instance -d RACDB -i RACDB2

From your terminal window and execute the following commands:

cd /home/oracle/wkdir
sqlplus /nolog
@rac_setup

This script connects to the database, grant the DBA role to HR, and create the HR.EMP table as a copy of the HR.EMPLOYEES table.

connect / as sysdba
grant connect, resource, dba to hr;
connect hr/hr
drop table emp purge;
create table emp as select * from employees;

Open a browser window, and enter the following URL:

http://raclinux1.us.oracle.com:1158/em

On the Enterprise Manager Login page, enter SYS in the User Name field, oracle in the Password field, and select SYSDBA from the Connect As drop down list. Once done, click the Login button.

On the Cluster Database home page, click the Maintenance link.

On the Maintenance page, click the Cluster Managed Database Services link under the Services section.

On the Cluster and Database Login page, enter oracle/oracle for the Cluster Credentials, and sys/oracle for the Database Credentials. Once done, click Continue.

On the Cluster Managed Database Services page, click the Create Service button.

You are now going to use PL/SQL to modify SERV1 so that it can be used by TAF. In this tutorial, you are using the BASIC method. From your terminal session, execute the following command:

@rac_modserv

connect / as sysdba
execute dbms_service.modify_service(          -
service_name => 'SERV1' -
, 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);
exit;

Before you can start using your service, you need to add a corresponding entry in your tnsnames.ora file. To do so, execute the following commands:

cd $ORACLE_HOME/network/admin
gedit tnsnames.ora

Add the following entry to the bottom of the file:

SERV1 =
 (DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=ON)
  (ADDRESS=(PROTOCOL=TCP)(HOST=raclinux1-vip.us.oracle.com)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=raclinux1-vip.us.oracle.com)(PORT=1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = SERV1)
     ))

As you can see, you no longer have to specify the TAF options in your tnsnames.ora file. This is now done directly from the server (see previous step).

First you want to connect as HR using the SERV1 service. It will determine which instance you are connected to, and determine some important TAF characteristics. From your existing terminal window, execute the following commands:

cd /home/oracle/wkdir
sqlplus hr/hr@SERV1
@rac_query

The rac_query.sql script contains the following:

select instance_name from v$instance;
col service_name format a10
select failover_type,failover_method,failed_over
from v$session where username='HR';

Notice the connection is using TAF with the BASIC method. So far, this connection was not failed over.

You will now insert one row into the EMP table. Notice that you are still using the same session that was created in the previous step. Execute the following script:

@rac_insert1

The rac_insert1.sql script contains the following:

insert into emp select * from employees where rownum<2;
commit;

Open a second terminal window. From now on, this session will be called the second session, and the other one, the first.

You will remove your foreground process established during the first step to simulate a database crash. From the second session, execute the following shell script:

./rac_kill_sessions.sh

The rac_kill_sessions.sh shell script contains the following: 

ps -ef | grep "(LOCAL=NO)" | awk '{print "kill -9 " $2 }' > y.sh
. ./y.sh

Now you can try to insert a new row into the EMP table again. Switch to your first session and execute the following script:

@rac_insert2 

The rac_insert2.sql script contains the following: 

insert into emp select * from employees where rownum<2;

.

Because you just killed the server process of your session, it is no longer possible to insert the row.

Wait a few seconds and execute the following script from the first session:

@rac_insert1

The rac_insert1.sql script contains the following:

insert into emp select * from employees where rownum<2;
commit;

Again, you try to insert a new row in the EMP table. This time it succeeds because TAF automatically reestablished the connection

Now re-execute the following script:

@rac_query

The rac_query.sql script contains the following:

select instance_name from v$instance;
col service_name format a10 
select failover_type,failover_method,failed_over 
  from v$session where username='HR';

Your session was failed over automatically by TAF.

To cleanup your environment, execute the following script:

./rac_cleanup.sh

srvctl stop service -d RACDB -s SERV1 -f
srvctl disable service -d RACDB -s SERV1
srvctl remove service -d RACDB -s SERV1
sqlplus /nolog @rac_cleanup


connect / as sysdba
revoke dba from hr;
connect hr/hr
drop table emp purge;
exit;
                         

Note: you will need to enter Y to remove the service.