Skip to Main Content

FAILOVER=ON, but NOFAILOVER on RAC - WHY ?

JhilJan 19 2018 — edited Jan 21 2018

Dear RAC Experts,

Failover is NOT happening.

Without configuring TAF, any possibility to make failover for self learning purpose ?

>> On machine 3

TEST_RAC =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

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

    (LOAD_BALANCE = yes)

      (FAILOVER = ON)

    )

    (CONNECT_DATA =

      (SERVICE_NAME = samdb.com)

    )

  )

>> IP configuration on both nodes  for RAC

# Public

192.168.56.10    rac1.oracle.com      rac1

192.168.56.20    rac2.oracle.com      rac2

# Virtual

192.168.56.70    rac1-vip

192.168.56.71    rac2-vip

# Scan

192.168.56.90    rac-scan

>> On Node1

Service "samdb.com" has 1 instance(s).

  Instance "samdb1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "samdbXDB.com" has 1 instance(s).

  Instance "samdb1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: RAC1.ORACLE.COM, pid: 7061>

         (ADDRESS=(PROTOCOL=tcp)(HOST=rac1.oracle.com)(PORT=17314))

The command completed successfully

>> On node2

Service "samdb.com" has 1 instance(s).

  Instance "samdb2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "samdbXDB.com" has 1 instance(s).

  Instance "samdb2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: RAC2.ORACLE.COM, pid: 7501>

         (ADDRESS=(PROTOCOL=tcp)(HOST=rac2.oracle.com)(PORT=28216))

The command completed successfully

>> On machine 3 --> it  acts as client machine

$ rlsqlplus scott/tiger@TEST_RAC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 20 05:01:41 2018

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

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

Data Mining and Real Application Testing options

>> On node1

Service "samdb.com" has 1 instance(s).

  Instance "samdb1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "samdbXDB.com" has 1 instance(s).

  Instance "samdb1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: RAC1.ORACLE.COM, pid: 7061>

         (ADDRESS=(PROTOCOL=tcp)(HOST=rac1.oracle.com)(PORT=17314))

The command completed successfully

>> On node2

Service "samdb.com" has 1 instance(s).

  Instance "samdb2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:1 refused:0 state:ready

         LOCAL SERVER

Service "samdbXDB.com" has 1 instance(s).

  Instance "samdb2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: RAC2.ORACLE.COM, pid: 7501>

         (ADDRESS=(PROTOCOL=tcp)(HOST=rac2.oracle.com)(PORT=28216))

The command completed successfully

>> On machine 3

$ rlsqlplus system/manager@TEST_RAC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 20 05:04:34 2018

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

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

Data Mining and Real Application Testing options

>> On node1

Service "samdb.com" has 1 instance(s).

  Instance "samdb1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "samdbXDB.com" has 1 instance(s).

  Instance "samdb1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: RAC1.ORACLE.COM, pid: 7061>

         (ADDRESS=(PROTOCOL=tcp)(HOST=rac1.oracle.com)(PORT=17314))

The command completed successfully

>> On node2

Service "samdb.com" has 1 instance(s).

  Instance "samdb2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:2 refused:0 state:ready         <====================================  2 times established on node 2 only

         LOCAL SERVER

Service "samdbXDB.com" has 1 instance(s).

  Instance "samdb2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: RAC2.ORACLE.COM, pid: 7501>

         (ADDRESS=(PROTOCOL=tcp)(HOST=rac2.oracle.com)(PORT=28216))

The command completed successfully

>> Please note

All 2 connections are connected to NODE2 from machine 3

I am restarting node 2 , but user connections are NOT moving to node1 - WHY ??

# init 0  <------------  On node2

>> On machine 3

SQL> select * from scott.emp;

select * from scott.emp

*

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 8416

Session ID: 37 Serial number: 3

Question :  Even i have mentioned failover=ON in tnsnames.ora file, why failover not occurred ?

Thanks in advance.

Comments
Post Details
Added on Jan 19 2018
3 comments
208 views