Home » Server Options » RAC & Failsafe » Cannot connect through single client access name scan after 1st node pubic cable is plugged off (11.2.0.3 SE, SUSE Linux Enterprise Server 11 SP3 )
Cannot connect through single client access name scan after 1st node pubic cable is plugged off [message #643038] Sat, 26 September 2015 09:21 Go to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear all,

I pull out a public cable from imdb01, expecting that imdb01-vip will failover and all db connections to switch to imdb02

Here's my observation:


oracle@imdb02:~> srvctl status vip -n imdb01
VIP imdb01-vip is enabled
VIP imdb01-vip is not running
Detailed state on node imdb02:FAILED OVER
oracle@imdb02:~> srvctl status vip -n imdb02
VIP imdb02-vip is enabled
VIP imdb02-vip is running on node: imdb02


I did a double confirmation through ifconfig:


oracle@imdb02:~> /sbin/ifconfig
eth0      Link encap:Ethernet  HWaddr 40:F2:E9:2A:D3:02
          inet addr:192.168.56.62  Bcast:192.168.56.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:7123371 errors:6 dropped:7365 overruns:0 frame:4
          TX packets:4027265 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:1315536340 (1254.5 Mb)  TX bytes:779661543 (743.5 Mb)
          Memory:90580000-905a0000

eth0:1    Link encap:Ethernet  HWaddr 40:F2:E9:2A:D3:02
          inet addr:192.168.56.65  Bcast:192.168.56.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          Memory:90580000-905a0000

eth0:2    Link encap:Ethernet  HWaddr 40:F2:E9:2A:D3:02
          inet addr:192.168.56.67  Bcast:192.168.56.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          Memory:90580000-905a0000

eth0:3    Link encap:Ethernet  HWaddr 40:F2:E9:2A:D3:02
          inet addr:192.168.56.66  Bcast:192.168.56.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          Memory:90580000-905a0000

eth0:4    Link encap:Ethernet  HWaddr 40:F2:E9:2A:D3:02
          inet addr:192.168.56.64  Bcast:192.168.56.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          Memory:90580000-905a0000

eth0:5    Link encap:Ethernet  HWaddr 40:F2:E9:2A:D3:02
          inet addr:192.168.56.63  Bcast:192.168.56.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          Memory:90580000-905a0000


192.168.56.63 being imdb01-vip is indeed failover
The above is expected:

however I can't connect to db through single client access name:


sys@imdb-scan/ORCLSE> conn sys/*********@imdb-scan/ORCLSE as sysdba
ERROR:
ORA-12502: TNS:listener received no CONNECT_DATA from client
warning: You are no longer connected to ORACLE


Although I can connect through imdb02-vip. I also don't understand why there's still db connections through first instance

conn sys/*********@imdb02-vip/ORCLSE as sysdba
connected
SYS@imdb02-vip/ORCLSE> SELECT sys_context('userenv','sid') FROM dual;

SYS_CONTEXT('USERENV','INSTANCE')
--------------------------------------------------------------------------------

2

SYS@imdb-scan/ORCLSE>select inst_id,count(0) from gv$session where username is not
null AND type != 'BACKGROUND' GROUP BY inst_id ORDER BY inst_id;

   INST_ID   COUNT(0)
---------- ----------
         1         20
         2         17



I get the following from the listener log:


grep 'TNS-12502' /u01/app/grid/diag/tnslsnr/imdb02/listener/trace/listener.log
TNS-12502: TNS:listener received no CONNECT_DATA from client


I also cannot grep anything useful in listener_scan logs

I've check the listener status


grid@imdb02:~> lsnrctl status listener_scan1

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 25-SEP-2015 12:03:53

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                25-SEP-2015 11:33:06
Uptime                    0 days 0 hr. 30 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0.3/grid/network/admin/listener.ora
Listener Log File         /u01/app/11.2.0.3/grid/log/diag/tnslsnr/imdb02/listene                                                                                                             r_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.67)(PORT=1521)))
Services Summary...
Service "ORCLSE" has 1 instance(s).
  Instance "ORCLSE2", status READY, has 1 handler(s) for this service...



I've followed MOS 975457.1 How to Troubleshoot Connectivity Issue with 11gR2 SCAN Name, but still could not understand what went wrong. nearest forum posting is https://community.oracle.com/thread/1009237 but then again that post is concerned about vip not able to failover, whereas my case is vip failover but connection through SCAN fails and there's still db connections to the disconnected instance.

So would appreciate if someone could enlighten me on these two issues:
why cannot connect through scan. how to resolve this?
why there still db connections through the disconnected node

What makes the issue with SCAN challenging is that if I repeat the test again, sometimes it can connect, sometimes it cannot connect.


Many many thanks in advance!
Re: Cannot connect through single client access name scan after 1st node pubic cable is plugged off [message #643039 is a reply to message #643038] Sat, 26 September 2015 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
12502, 00000, "TNS:listener received no CONNECT_DATA from client"
// *Cause: No CONNECT_DATA was passed to the listener.
// *Action: Check that the service name resolved from TNSNAMES.ORA has
// the CONNECT_DATA component of the connect descriptor.
Re: Cannot connect through single client access name scan after 1st node pubic cable is plugged off [message #643040 is a reply to message #643038] Sat, 26 September 2015 09:52 Go to previous messageGo to next message
John Watson
Messages: 7149
Registered: January 2010
Location: Global Village
Senior Member
Your questions Quote:
why cannot connect through scan. how to resolve this?
why there still db connections through the disconnected node
First, you may be having issues with DNS and ARP and routing tables and caching. I am not a networking specialist, but I do know that if you have address caches enabled you may be trying to connect to what is now a useless MAC address.
Second, I would think that those sessions will (eventually) die. It will take a while for the server processes to realize that their user process has disconnected.
Re: Cannot connect through single client access name scan after 1st node pubic cable is plugged off [message #643041 is a reply to message #643040] Sat, 26 September 2015 10:03 Go to previous messageGo to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Hi John really appreciate your fast reply, yes the answer on dns caches makes sense

as regarding the database sessions, I've did another test:

I shutdown one of the node completely:

and the instance breakdown is as follow:


SYS@imdb-scan/ORCLSE>select inst_id,count(0) from gv$session where username is not
null AND type != 'BACKGROUND' GROUP BY inst_id ORDER BY inst_id;

         2         17


but how do u explain the fact that if I shutdown one of the node, all db connections will failover to another node?

and many many thanks
Re: Cannot connect through single client access name scan after 1st node pubic cable is plugged off [message #643049 is a reply to message #643041] Sun, 27 September 2015 02:08 Go to previous messageGo to next message
John Watson
Messages: 7149
Registered: January 2010
Location: Global Village
Senior Member
Quote:
but how do u explain the fact that if I shutdown one of the node, all db connections will failover to another node?
This is expected behavior, if you have enabled TAF. It is the reason for using RAC.
Re: Cannot connect through single client access name scan after 1st node pubic cable is plugged off [message #643072 is a reply to message #643049] Mon, 28 September 2015 06:28 Go to previous message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Hi John thanks for your reply. Really appreciate it
Previous Topic: Single query to access multiple RAC nodes
Next Topic: Scan VIP Failover But Node VIP Did Not Failover
Goto Forum:
  


Current Time: Tue Nov 21 04:22:50 CST 2017

Total time taken to generate the page: 0.11979 seconds