This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jul 29, 2013 10:22 PM by BillyVerreynne RSS

default service only connects to one node

user13454469 Newbie
Currently Being Moderated

Hello all,

 

 

We just did a fresh install of oracle 11.2.0.3 on RHEL6.  This is a 2 node RAC.

 

 

Below is my tns entry on my local machine/laptop...

 

 

TESTDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = scan-test)(PORT = 1527))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TESTDB)

    )

  )

 

Now when i try to connect using above tns, it always takes me to node2.  I have tried like quite a few times.

The above is my db name and service name is the default.

Wierd part is when i create a NEW service and use that as my service name, it dose the load balancing part.

connects me to node1 first then node2, then node1, then node1, then node2 ......

 

 

I understand we should create a seperate service to use with RAC and should not use the DEFAULT service name.

But i want to figure out why this is happening with my default service...

 

 

Here is my info about DB and listener... scan listener2 and 3 are running on node1 and scan listener1 is running on node2

both nodes are pretty idle as we only have a dummy DB on it and nothing else running.

 

 

The local listener(LISTENER) have the local instance (testdb1 and asm1) registred with status of ready,

same with node2

 

 

the scan listner have TESTDB service with 2 instance registred(testedb1, testdb2)...

 

 

--node1

SQL> show parameter listener%;

 

 

NAME                                 TYPE        VALUE

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

listener_networks                    string

local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD

                                                 DRESS=(PROTOCOL=TCP)(HOST=110.20.10.77)(PORT=1521))))

remote_listener                      string      scan-test:1527

SQL>

 

--node2

SQL> show parameter listener%;

 

 

NAME                                 TYPE        VALUE

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

listener_networks                    string

local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD

                                                 DRESS=(PROTOCOL=TCP)(HOST=110.20.10.78)(PORT=1521))))

remote_listener                      string      scan-test:1527

SQL>

 

 

 

 

here are all my resources

 

 

NAME                                          TARGET     STATE           SERVER       STATE_DETAILS

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

ora.DATA.dg                                   ONLINE     ONLINE          node1

ora.DATA.dg                                   ONLINE     ONLINE          node2

ora.FLASH.dg                                  ONLINE     ONLINE          node1

ora.FLASH.dg                                  ONLINE     ONLINE          node2

ora.LISTENER.lsnr                             ONLINE     ONLINE          node1

ora.LISTENER.lsnr                             ONLINE     ONLINE          node2

ora.OCR_VOTE.dg                               ONLINE     ONLINE          node1

ora.OCR_VOTE.dg                               ONLINE     ONLINE          node2

ora.asm                                       ONLINE     ONLINE          node1     Started

ora.asm                                       ONLINE     ONLINE          node2     Started

ora.gsd                                       OFFLINE    OFFLINE         node1

ora.gsd                                       OFFLINE    OFFLINE         node2

ora.net1.network                              ONLINE     ONLINE          node1

ora.net1.network                              ONLINE     ONLINE          node2

ora.ons                                       ONLINE     ONLINE          node1

ora.ons                                       ONLINE     ONLINE          node2

ora.LISTENER_SCAN1.lsnr                       ONLINE     ONLINE          node2

ora.LISTENER_SCAN2.lsnr                       ONLINE     ONLINE          node1

ora.LISTENER_SCAN3.lsnr                       ONLINE     ONLINE          node1

ora.testdb.db                                 ONLINE     ONLINE          node1     Open

ora.testdb.db                                 ONLINE     ONLINE          node2     Open

ora.cvu                                       ONLINE     ONLINE          node1

ora.oc4j                                      ONLINE     ONLINE          node1

ora.scan1.vip                                 ONLINE     ONLINE          node2

ora.scan2.vip                                 ONLINE     ONLINE          node1

ora.scan3.vip                                 ONLINE     ONLINE          node1

ora.node1.vip                              ONLINE     ONLINE          node1

ora.node2.vip                              ONLINE     ONLINE          node2

 

 

 

 

i would like to understand why i always connect to node2 when using the default service name?

  • 1. Re: default service only connects to one node
    Mihael Pro
    Currently Being Moderated

    Default database service (TESTDB in your example) should support load balancing. Check the following:

     

     

    on node2: lsnrctl services LISTENER_SCAN1

    on node1: lsnrctl services LISTENER_SCAN2

    on node1: lsnrctl services LISTENER_SCAN3

  • 2. Re: default service only connects to one node
    user13454469 Newbie
    Currently Being Moderated

    all 3 of the scan listeners are up and running with the following status....

     

    Service "TESTDB" has 2 instance(s).

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

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

  • 3. Re: default service only connects to one node
    Mihael Pro
    Currently Being Moderated

    Then you should have load balancing. Recheck connections. Also you can shutdown instance on node2 and check again.

  • 4. Re: default service only connects to one node
    user13454469 Newbie
    Currently Being Moderated

    When i shutdown node1 instance, then connects are routed to node2...but when i have both of them up it only likes to go to node1...

     

    node1:/home/oracle> srvctl stop instance -d TESTDB -i TESTDB1

    node1:/home/oracle>

    node1:/home/oracle> srvctl status database -d TESTDB

    Instance TESTDB1 is not running on node node1

    Instance TESTDB2 is running on node node2

    node1:/home/oracle>

    node1:/home/oracle> sqlplus system@TESTDB

     

     

    SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 25 08:27:47 2013

     

     

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

     

     

    Enter password:

     

     

    Connected to:

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

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

    Data Mining and Real Application Testing options

     

     

    SQL> select instance_name from v$instance;

     

     

    INSTANCE_NAME

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

    TESTDB2

     

     

    SQL> exit

    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

    Data Mining and Real Application Testing options

    node1:/home/oracle> sqlplus system@TESTDB

     

     

    SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 25 08:27:47 2013

     

     

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

     

     

    Enter password:

     

     

    Connected to:

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

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

    Data Mining and Real Application Testing options

     

     

    SQL> select instance_name from v$instance;

     

     

    INSTANCE_NAME

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

    TESTDB2

     

     

    SQL> exit

    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

    Data Mining and Real Application Testing options

    node1:/home/oracle>

     

    ## NOW I START THE INSTNACE 1 BACKUP AGAIN...and same thing happens, connections goes to node1

     

    node1:/home/oracle> srvctl start instance -d TESTDB -i TESTDB1

    node1:/home/oracle> srvctl status database -d TESTDB

    Instance TESTDB1 is running on node node1

    Instance TESTDB2 is running on node usphi840

    node1:/home/oracle>

    node1:/home/oracle>

    node1:/home/oracle> sqlplus system@TESTDB

     

     

    SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 25 08:28:43 2013

     

     

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

     

     

    Enter password:

     

     

    Connected to:

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

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

    Data Mining and Real Application Testing options

     

     

    SQL> select instance_name from v$instance;

     

     

    INSTANCE_NAME

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

    TESTDB1

     

     

    SQL> exit

    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

    Data Mining and Real Application Testing options

    node1:/home/oracle> sqlplus system@TESTDB

     

     

    SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 25 08:28:57 2013

     

     

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

     

     

    Enter password:

     

     

    Connected to:

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

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

    Data Mining and Real Application Testing options

     

     

    SQL> select instance_name from v$instance;

     

     

    INSTANCE_NAME

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

    TESTDB1

     

     

    SQL> exit

    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

    Data Mining and Real Application Testing options

    node1:/home/oracle> sqlplus system@TESTDB

     

     

    SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 25 08:29:06 2013

     

     

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

     

     

    Enter password:

     

     

    Connected to:

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

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

    Data Mining and Real Application Testing options

     

     

    SQL> select instance_name from v$instance;

     

     

    INSTANCE_NAME

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

    TESTDB1

     

     

    SQL>

  • 5. Re: default service only connects to one node
    Mihael Pro
    Currently Being Moderated

    Check the following :

     

    tnsping TESTDB

    nslookup scan-test

  • 6. Re: default service only connects to one node
    user13454469 Newbie
    Currently Being Moderated

    C:\oracle\sql> tnsping testdb

     

     

    TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 25-JUL-2013 07:52:36

     

     

    Copyright (c) 1997, 2010, Oracle.  All rights reserved.

     

     

    Used parameter files:

    C:\oracle\product\11.2.0\client\network\admin\sqlnet.ora

     

     

     

     

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = scan-test)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB)))

    OK (80 msec)

    C:\oracle\sql> nslookup scan-test

    Server:  DNS SERVER

    Address:  XX.XX.XXX.XXX

     

     

    Name:    scan-test.domain.com

    Addresses:  XX.XX.XX.XX

       XX.XX.XX.XX

       XX.XX.XX.XX

      

     

     

    i tried that 3 times and it gives me IP's in round robin fasion, just the way it suppose to work... tried that both from my workstation

    and the server...same result...except i just noticed when i do nslookup on the server i get something wierd at server(;; Truncated, retrying in TCP mode)...altough i do get the round robin fashion of the ip but with ";; Truncated, retrying in TCP mode" message...

     

     

    node1:/home/oracle> scan-test

    ;; Truncated, retrying in TCP mode.

    Server:  DNS SERVER

    Address:  XX.XX.XXX.XXX

     

     

    Name: scan-test.domain.com

    Address: XX.XX.XX.XX

    Name: scan-test.domain.com

    Address: XX.XX.XX.XX

    Name: scan-test.domain.com

    Address: XX.XX.XX.XX

  • 7. Re: default service only connects to one node
    Mihael Pro
    Currently Being Moderated

    Okay. Then try to connect to TESTDB from the same machine several times, but without closing connections.

     

    sqlplus system@TESTDB

    select instance_name from v$instance;

     

    from second terminal :

    sqlplus system@TESTDB

    select instance_name from v$instance;

     

    ...

  • 8. Re: default service only connects to one node
    user13454469 Newbie
    Currently Being Moderated

    i had tried that yeterday and i just tried it again...about 10 different terminal and all session still open...it always takes me to instance1. 

  • 9. Re: default service only connects to one node
    Mihael Pro
    Currently Being Moderated

    Try to force connection to instance 2 :

     

    TESTDB2 =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 110.20.10.78)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = TESTDB)

        )

      )

  • 10. Re: default service only connects to one node
    user13454469 Newbie
    Currently Being Moderated

    i followed this doc and everything seems to work...i mean by passing scan...i can connect using the VIP just fine...its only when i use SCAN it takes me to node1

     

    How to Troubleshoot Connectivity Issue with 11gR2 SCAN Name (Doc ID 975457.1)


    When client program connects to RAC database through SCAN name, SCAN listener will accept t he request and redirect the connection to local listener. To identify connection issue, first try to connect to each local listener through node VIP, then try each SCAN listener through each SCAN VIP.

     

    To test through node VIP:

    sqlplus <username>/<password>@<nodename-vip.domain>:<local-listener-port>/<service-name>

     

    Example:

     

    sqlplus scott/tiger@racnode1-vip.us.eot.com:1521/testsvc


    Repeat the same test for all local listener/node VIP in the cluster.

     

    If GNS is used, node VIP name will be in the format of nodename-vip.gnssubdomain (example racnode1-vip.us.eot.com)

     

    If connection through local listener fails, check whether service/instance is registered properly to that local listener with "lsnrctl service <local-listener-name>".


    To test through SCAN VIP address:

    sqlplus <username>/<password>@<scan-ipn>:<scan-listener-port>/<service-name>

     

    Example:

     

    sqlplus scott/tiger@120.0.0.205:1521/testsvc

     

    Note it's IP address instead of SCAN name


    Repeat the same command for all SCAN IP
    If connection through SCAN listener fails, check whether service/instance is registered properly to that SCAN listener with "lsnrctl service <SCAN-listener-name>".
  • 11. Re: default service only connects to one node
    user13454469 Newbie
    Currently Being Moderated

    C:\oracle\sql>sqlplus system/XXXXX@110.20.10.78:1521/TESTDB

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 25 09:16:15 2013

     

     

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

     

     

     

     

    Connected to:

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

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

    Data Mining and Real Application Testing options

     

     

    Setting NLS_DATE_FORMAT to DD-MON-YY HH24:MI:SS

     

     

    INSTANCE_NAME   |HOST_NAME                                                       |STATUS      |STARTUP_TIME

    ----------------|----------------------------------------------------------------|------------|--------------------

    TESTDB2         |node2                                                        |OPEN        |24-JUL-2013 16:43:32

  • 12. Re: default service only connects to one node
    Mihael Pro
    Currently Being Moderated

    Are iptables enabled on hosts ?

  • 13. Re: default service only connects to one node
    user13454469 Newbie
    Currently Being Moderated

    i think i already checked that yesterday...but here is it again...this is true for both nodes

     

    node1:~# /etc/rc.d/init.d/iptables status

    iptables: Firewall is not running.

    node1:~#

  • 14. Re: default service only connects to one node
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    The client connects to the SCAN listener, requesting a service. The SCAN checks its registry to determine which local listeners support that service - and which local listener to redirect the client connection to.

     

    Typically, that will be to the local listener running on a virtual IP. So if 5 virtual IP listeners (5 node RAC) have registered the same service, the client will received a virtual hostname and port to connect to.

     

    The client needs to resolve that hostname to the appropriate virtual IP. However, it can resolve all 5 virtual hostnames to the same IP address (e.g. due to finger/copy-and-paste trouble when setting up the clients hosts file). Which means irrespective of load balancing by SCAN, and irrespective of the redirect the client receives, it will always connect to the same IP,

     

    Not saying that this is what is happening in your case - but I am saying do not fixate on your issue being solely a server-side issue. As client-side errors can also explain the error.

     

    My suggestion is to enable SQL*Net tracing (using a client side sqlnet.ora file) - and then make the connection to the service via the SCAN from the client's sqlplus executable. And see what the SQL*Net trace file says ito what it received from the Listener(s) it connected to, and was redirected to.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points