This discussion is archived
12 Replies Latest reply: Apr 15, 2013 8:49 PM by sunny_123 RSS

Listener on RAC node 2 not accepting user connections

sunny_123 Newbie
Currently Being Moderated
Hey guys i am experiencing a disturbing scenario, i have a 2 RAC node cluster . The problem is that the second listener is not registering any connections . I have verified the services of listener using lsnrctl status (the default name is LISTENER), i also have verified the local and remote listener parameters they are fine but running the fol query shows count =0 against inst_id=2;

SQL > select count() from gv$session where username='XYZ' and inst_id=2;*


Count
--------
0


Any help is appreciated in this regard please.
  • 1. Re: Listener on RAC node 2 not accepting user connections
    FreddieEssex Pro
    Currently Being Moderated
    10g? 11g? 11gR2?

    What version you using?

    11gR2 uses SCAN Listener.

    In all cases you need to setup remote listener.

    More info required.
  • 2. Re: Listener on RAC node 2 not accepting user connections
    sunny_123 Newbie
    Currently Being Moderated
    Yes i am using Oracle 11gr2 (sorry for not mentioning it in my post), actually websphere is using vip for connect string so the question of using scan is void here. In the connect string websphere application server data source is inputting the vip listener address and the ports, in this scenario my RAC node 1 listener is registering the connections but not the second node. Thanks
  • 3. Re: Listener on RAC node 2 not accepting user connections
    FreddieEssex Pro
    Currently Being Moderated
    Okay...I won't go into the pros and cons of using scan etc and I'm assuming you have a good reason not to do so.

    Can you post the output of the following from both nodes of your RAC please:
    lsnrctl status listener 
  • 4. Re: Listener on RAC node 2 not accepting user connections
    FreddieEssex Pro
    Currently Being Moderated
    Can you also post the tnsnames you are using?
  • 5. Re: Listener on RAC node 2 not accepting user connections
    sunny_123 Newbie
    Currently Being Moderated
    Yeah i know very well the purpose of SCAN . But web admins are sticking to the vip . Well here is the output of lsnrctl status:-

    LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 11-APR-2013 16:41:55

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

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
    Start Date 10-MAR-2013 13:00:14
    Uptime 17 days 2 hr. 42 min. 18 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
    Listener Log File /u01/app/oracle/diag/tnslsnr/fedb6/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=5.2.21.1)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=5.2.21.7)(PORT=1521)))
    Services Summary...
    Service "+ASM" has 1 instance(s).
    Instance "+ASM2", status READY, has 1 handler(s) for this service...
    Service "grid1.xyz" has 1 instance(s).
    Instance "grid12", status READY, has 1 handler(s) for this service...
    Service "grid1XDB.xyz" has 1 instance(s).
    Instance "grid12", status READY, has 1 handler(s) for this service...
    Service "xyz" has 1 instance(s).
    Instance "grid12", status READY, has 1 handler(s) for this service...
    The command completed successfully

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

    and here is tnsnames.ora output :-


    GRID1 =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = grid1-scan)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = grid1.xyz)
    )
    )


    PRIMARY1_SERV =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 5.2.21.1)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = grid1.xyz)
    (INSTANCE_NAME= grid11)

    )
    )
    PRIMARY2_SERV =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 5.2.21.6)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = grid1.xyz)
    (INSTANCE_NAME = grid12)
    )
    )
  • 6. Re: Listener on RAC node 2 not accepting user connections
    FreddieEssex Pro
    Currently Being Moderated
    The lsnrctl status output shows that the grid1.xyz service only has the grid12 instance registered with it. Both instances should be registered.

    What service is your websphere app connecting to?

    You've setup the remote_listener parameter?

    Please post the output of below from both nodes:
    show parameter remote
  • 7. Re: Listener on RAC node 2 not accepting user connections
    sunny_123 Newbie
    Currently Being Moderated
    Yeah its showing only grid12 because its the local listener output which is being shown in lsnrctl status, and its the same instance which is not registering any connection with the database (*count(*) =0*). Also the question of remote_listener is void because as i notified earlier that web sphere is using vip for connection not scan. If i were using scan i could have looked into remote_listener parameter.
  • 8. Re: Listener on RAC node 2 not accepting user connections
    FreddieEssex Pro
    Currently Being Moderated
    I know it's the local listener.

    Perhaps in future you shouldn't be so patronising when members of this forum are giving up their time to assist others.

    So if you are directing queries to your vip on the local listener which only has one instance registered to it, why is it a surprise that you only connect to the one instance?
    the question of remote_listener is void
    Really? It really really isn't.

    Think about it.

    I'm sure you'll work it out.
  • 9. Re: Listener on RAC node 2 not accepting user connections
    sunny_123 Newbie
    Currently Being Moderated
    Yes i agree you are absolutely right !. But the connect string which web admin is giving in that he is mentioning both Vip's (RAC node 1 and RAC node 2). For some time in past both listeners were accepting the connections. I have tried to manually register the local listeners with corresponding Vips using (alter system command), PMON process has perfectly registered it and its running from the right home (grid home), all cluster resources are up but still no connections accepted by it. I will post the connect string too which is being given at web sphere end. Thanks
  • 10. Re: Listener on RAC node 2 not accepting user connections
    sunny_123 Newbie
    Currently Being Moderated
    connect string :- jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=5.2.21.2)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=5.2.21.7)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xyz)))

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

    local_listner
    SQL> show parameter local_listener;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
    DRESS=(PROTOCOL=TCP)(HOST=fedb
    6-vip)(PORT=1521))))

    --------------------------------------------------------------------------------------------------------------
    remote_listener
    SQL> show parameter remote_listener;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    remote_listener string grid1-scan.xyz:1521

    Edited by: sunny_123 on Apr 12, 2013 2:32 AM
  • 11. Re: Listener on RAC node 2 not accepting user connections
    FreddieEssex Pro
    Currently Being Moderated
    But web admins are sticking to the vip
    IMHO you would expect DBA's to provide the webadmins with what to use and not the other way around.

    In any case, can you connect to the vip on node2 if you specify that explicitly from a client?

    All your connections will go to node 1 and only go to node 2 if node 1 is down.

    Have you tried using LOAD_BALANCE = ON in your connect string??

    Or just use SCAN and forget about this headache.
  • 12. Re: Listener on RAC node 2 not accepting user connections
    sunny_123 Newbie
    Currently Being Moderated
    Yes you are right ! I meant that if scan is in place then there is no logic in using vip ! Actually i have to take a down time for testing this case scenario. But surely i will try that !

Legend

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