1 2 Previous Next 20 Replies Latest reply: Jul 30, 2013 12:22 AM by Billy~Verreynne RSS

    default service only connects to one node

    user13454469

      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

          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

            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

              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

                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

                  Check the following :

                   

                  tnsping TESTDB

                  nslookup scan-test

                  • 6. Re: default service only connects to one node
                    user13454469

                    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

                      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

                        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

                          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

                            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

                              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

                                Are iptables enabled on hosts ?

                                • 13. Re: default service only connects to one node
                                  user13454469

                                  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
                                    Billy~Verreynne

                                    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