Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Oracle RAC and Oracle ONE NODE RAC 11.2.0 and Transparent Application Failover (TAF)

Oracle RAC and Oracle ONE NODE RAC 11.2.0 and Transparent Application Failover (TAF)

In this article we will look at Transparent Application Failover (TAF) setup with RAC. The TAF concept is tested with Oracle RAC and will use it to test with Oracle ONE NODE RAC. We will setup a tnsnames.ora entry for TAF and verify the TAF failover while migrating with Omotion the instance to another node. Managing Oracle ONE NODE RAC was a subject of an earlier article. The tnanames.ora entry RUPTAF is in the ANNEX. We will run a query as shown in the ANNEX in testtaf.sql and monitor the fail over while the ONE NODE RAC instance is moved to another node of the cluster. Before and after the query execution we will check the failover_method , failover_type and failed_over from v$session. We will connect to RUP database (instance RUP_1 on raclinux2) and will use Omotion to move it to raclinux1 node.

  1. How is defined RUPTAF

RUPTAF=

  (DESCRIPTION =

  (ENABLE=BROKEN)

  (LOAD_BALANCE = OFF)

  (FAILOVER = ON)

    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2-vip)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = RUP)

      (FAILOVER_MODE =

         (TYPE=SELECT)

         (METHOD=BASIC)

         (BACKUP=RUP)

      )

    )

  )

2.   Setup RAC ONE NODE
 

We looked at managing Oracle ONE NODE RAC here. We will setup Oracle RAC ONE NODE database and will verify it.


[oracle@raclinux2 ~]$ raconeinit

Candidate Databases on this cluster:

 #      Database        RAC One Node    Fix Required

===     ========        ============    ============

[1]         RAC0             NO              N/A

[2]         RONE             NO              N/A

[3]          RUP             NO              N/A

Enter the database to initialize [1]: 3

Database RUP is now running on server raclinux2

Candidate servers that may be used for this DB: raclinux1 

Enter the names of additional candidate servers where this DB may run (space delimited): raclinux1

Please wait, this may take a few minutes to finish.......

Database configuration modified.

[oracle@raclinux2 ~]$

[oracle@raclinux2 ~]$ raconestatus

RAC One Node databases on this cluster:

  Database UP Fix Required        Current Server                    Candidate Server Names

  ======== == ============ ==============================  ========================================

       RUP  Y       N                           raclinux2                       raclinux2 raclinux1  

Available Free Servers: 

[oracle@raclinux2 ~]$

3.    Start a SQL statement and migrate the database with Omotion as in step 4


[oracle@raclinux2 admin]$ sqlplus system/sys1@ruptaf

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 30 13:47:32 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> @/u03/testtaf.sql

 SID   SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER

---- --------- ------------- --------------- -----------

  49       685 SELECT        BASIC           NO

INSTANCE_NAME

----------------

RUP_1

  COUNT(*)

----------

    623183

 SID   SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER

---- --------- ------------- --------------- -----------

  49         2 SELECT        BASIC           YES

INSTANCE_NAME

----------------

RUP_2

  COUNT(*)

----------

    623183

SQL>

4.     Migrate with Omotion the RUP database while the SQL query in step 3 is running


[oracle@raclinux2 ~]$ Omotion -v

RAC One Node databases on this cluster:

 #      Database                    Server                      Fix Required

===     ========        ==============================          ============

[1]          RUP                             raclinux2                N

Enter number of the database to migrate [1]: 1

Specify maximum time in minutes for migration to complete (max 30) [30]:

RUP Database is administrator managed .

RUP database is running in RUP server pool.

Current Running instance: RUP_1

Current Active Server       : raclinux2

Available Target Server(s) :

 #            Server            Available

===     ==================      =========

[1]              raclinux1          Y

Enter number of the target node [1]: 1

Omotion Started...

Starting target instance on raclinux1...

Migrating sessions...

Stopping source instance on raclinux2...

Omotion Completed...

=== Current Status ===

Database RUP is running on node raclinux1

[oracle@raclinux2 ~]$

[oracle@raclinux2 ~]$ Omotion

RAC One Node databases on this cluster:

 #      Database                    Server                      Fix Required

===     ========        ==============================          ============

[1]          RUP                             raclinux1                N

Enter number of the database to migrate [1]: 1

Specify maximum time in minutes for migration to complete (max 30) [30]: 30

Available Target Server(s) :

 #            Server            Available

===     ==================      =========

[1]              raclinux2          Y

Enter number of the target node [1]: 1

Omotion Started...

Starting target instance on raclinux2...

Migrating sessions...

Stopping source instance on raclinux1...

Omotion Completed...

=== Current Status ===

Database RUP is running on node raclinux2

[oracle@raclinux2 ~]$

Summary

We configured a TAF tnsnames.ora entry and used Omotion to move the RUP database to another node of the cluster. Using the defined RUPTAF connect string we confirmed that the session fails over to the second instance started by Omotion using the defined failover attributes.

Annex

The RUPTAF tnsnames.ora entry and the test script follows.


# in tnsnames.ora

RUPTAF=

  (DESCRIPTION =

  (ENABLE=BROKEN)

  (LOAD_BALANCE = OFF)

  (FAILOVER = ON)

    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2-vip)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = RUP)

      (FAILOVER_MODE =

         (TYPE=SELECT)

         (METHOD=BASIC)

         (BACKUP=RUP)

      )

    )

  )

[oracle@raclinux2 admin]$ cat /u03/testtaf.sql

        col sid format 999

        col serial# format 99999999

        col failover_type format a13

        col failover_method format a15

        col failed_over format a11

        select sid, serial#, failover_type, failover_method, failed_over from v$session where username ='SYSTEM';

        select instance_name from v$instance;

        select count(*) from

        (

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source

        );

        col sid format 999

        col serial# format 99999999

        col failover_type format a13

        col failover_method format a15

        col failed_over format a11

        select sid, serial#, failover_type, failover_method, failed_over from v$session where username ='SYSTEM';

        select instance_name from v$instance;

        select count(*) from

        (

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source union

         select * from dba_source

        );

[oracle@raclinux2 admin]$

October 3, 2010 - Posted by | oracle

1 Comment »

  1. Gday Francie Rocamora here. Have you ever thought about creating an ebook or guest authoring on other websites? I have a blog based upon on the same subjects you discuss at http://gjilevski.wordpress.com/2010/10/03/oracle-rac-and-oracle-one-node-rac-11-2-0-and-transparent-application-failover-taf and would love to have you share some stories/information. I know my subscribers would enjoy your work. If you’re even remotely interested, feel free to send me an email.

    Comment by Francie Rocamora | September 15, 2011 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 773 other followers

%d bloggers like this: