6 Replies Latest reply: Mar 7, 2011 8:26 AM by MR139 RSS

    Oracle 11gR2 RAC - SCAN issue

    MR139
      We have Oracle 11gR2(11.2.0.1) RAC (2-node) on HP-UX. And we have some issue with SCAN Listener configuration.

      Issue-1*
      LISTENER_SCAN1 is running on node-1 and LISTENER_SCAN2 & LISTENER_SCAN3 is running on node-2. This is how it's running always. And we have 3 RAC database (cadprod, omsprod & eaiprod) running on this server. Both the instances of cadprod & eaiprod is registered on all three SCAN listeners.

      But omsprod is having issues, omsprod1 & omsprod2 are registered on LISTENER_SCAN1 on node-1, but omsprod2 is not register on LISTENER_SCAN2 & LISTENER_SCAN3 on node-2. and some times we are not able to connect the second instance (omsprod2).

      Issue-2*
      And if node-1 goes down, all three databases are failover to node-2, after that we are not able to connect omsprod & eaiprid database its giving the following error on client side,

      ORA-12516, TNS:listener could not find available handler with matching protocol stack

      once the node-1 came back its allowing the connection. But cadprod doesn't have this issue, we are able to connect after the failover.

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

      Listener Status:*

      oracle@hublhp1:/home/oracle$ lsnrctl status LISTENER_SCAN1

      LSNRCTL for HPUX: Version 11.2.0.1.0 - Production on 04-MAR-2011 09:53:29

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
      STATUS of the LISTENER
      Alias LISTENER_SCAN1
      Version TNSLSNR for HPUX: Version 11.2.0.1.0 - Production
      Start Date 28-FEB-2011 13:40:09
      Uptime 3 days 20 hr. 13 min. 20 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File /app/oracle/grid/product/11.2.0/network/admin/listener.ora
      Listener Log File /app/oracle/grid/product/11.2.0/log/diag/tnslsnr/hublhp1/listener_scan1/alert/log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.35.110)(PORT=1521)))
      Services Summary...
      Service "cadprod" has 2 instance(s).
      Instance "cadprod1", status READY, has 1 handler(s) for this service...
      Instance "cadprod2", status READY, has 1 handler(s) for this service...
      Service "eaiprod" has 2 instance(s).
      Instance "eaiprod1", status READY, has 1 handler(s) for this service...
      Instance "eaiprod2", status READY, has 1 handler(s) for this service...
      Service "eaiprodXDB" has 2 instance(s).
      Instance "eaiprod1", status READY, has 1 handler(s) for this service...
      Instance "eaiprod2", status READY, has 1 handler(s) for this service...
      Service "omsprod" has 2 instance(s).
      Instance "omsprod1", status READY, has 1 handler(s) for this service...
      Instance "omsprod2", status READY, has 1 handler(s) for this service...
      The command completed successfully


      oracle@hublhp3:/home/oracle$ lsnrctl status LISTENER_SCAN2

      LSNRCTL for HPUX: Version 11.2.0.1.0 - Production on 04-MAR-2011 09:52:40

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
      STATUS of the LISTENER
      Alias LISTENER_SCAN2
      Version TNSLSNR for HPUX: Version 11.2.0.1.0 - Production
      Start Date 28-FEB-2011 12:23:33
      Uptime 3 days 21 hr. 29 min. 6 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File /app/oracle/grid/product/11.2.0/network/admin/listener.ora
      Listener Log File /app/oracle/grid/product/11.2.0/log/diag/tnslsnr/hublhp3/listener_scan2/alert/log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.35.109)(PORT=1521)))
      Services Summary...
      Service "cadprod" has 2 instance(s).
      Instance "cadprod1", status READY, has 1 handler(s) for this service...
      Instance "cadprod2", status READY, has 1 handler(s) for this service...
      Service "eaiprod" has 2 instance(s).
      Instance "eaiprod1", status READY, has 1 handler(s) for this service...
      Instance "eaiprod2", status READY, has 1 handler(s) for this service...
      Service "eaiprodXDB" has 2 instance(s).
      Instance "eaiprod1", status READY, has 1 handler(s) for this service...
      Instance "eaiprod2", status READY, has 1 handler(s) for this service...
      Service "omsprod" has 1 instance(s).
      Instance "omsprod1", status READY, has 1 handler(s) for this service...
      The command completed successfully


      oracle@hublhp3:/home/oracle$ lsnrctl status LISTENER_SCAN3

      LSNRCTL for HPUX: Version 11.2.0.1.0 - Production on 04-MAR-2011 09:52:27

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
      STATUS of the LISTENER
      Alias LISTENER_SCAN3
      Version TNSLSNR for HPUX: Version 11.2.0.1.0 - Production
      Start Date 28-FEB-2011 12:23:34
      Uptime 3 days 21 hr. 28 min. 53 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File /app/oracle/grid/product/11.2.0/network/admin/listener.ora
      Listener Log File /app/oracle/grid/product/11.2.0/log/diag/tnslsnr/hublhp3/listener_scan3/alert/log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.35.111)(PORT=1521)))
      Services Summary...
      Service "cadprod" has 2 instance(s).
      Instance "cadprod1", status READY, has 1 handler(s) for this service...
      Instance "cadprod2", status READY, has 1 handler(s) for this service...
      Service "eaiprod" has 2 instance(s).
      Instance "eaiprod1", status READY, has 1 handler(s) for this service...
      Instance "eaiprod2", status READY, has 1 handler(s) for this service...
      Service "eaiprodXDB" has 2 instance(s).
      Instance "eaiprod1", status READY, has 1 handler(s) for this service...
      Instance "eaiprod2", status READY, has 1 handler(s) for this service...
      Service "omsprod" has 1 instance(s).
      Instance "omsprod1", status READY, has 1 handler(s) for this service...
      The command completed successfully
      oracle@hublhp3:/home/oracle$
      oracle@hublhp3:/home/oracle$
        • 1. Re: Oracle 11gR2 RAC - SCAN issue
          12cdb
          YOu do not need more than 2 SCAN listeners in your case, 3 scan listeners are required for 3 or more nodes cluster.

          Node1 - Scan1, cad1, oms1, eai1
          Node2 - scan2, scan3, cad2, oms2, eai2

          understand that oms2 is not registered on scan2 & scan 3

          Please provide the contents of listener.ora file for both nodes.
          • 2. Re: Oracle 11gR2 RAC - SCAN issue
            MR139
            Node-1*
            LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
            LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
            LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
            LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

            Node-2*
            LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
            LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
            LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
            LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
            ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
            • 3. Re: Oracle 11gR2 RAC - SCAN issue
              12cdb
              can you restart (stop &start) the scan2 and scan3 listener..

              keep in mind using the srvctl command..

              when you stop just wait for a minute before you start again.
              • 4. Re: Oracle 11gR2 RAC - SCAN issue
                12cdb
                can you restart (stop &start) the scan2 and scan3 listener..

                keep in mind using the srvctl command..

                when you stop just wait for a minute before you start again.
                • 5. Re: Oracle 11gR2 RAC - SCAN issue
                  12cdb
                  Any luck!!
                  • 6. Re: Oracle 11gR2 RAC - SCAN issue
                    MR139
                    sorry for the late reply,

                    I have re-started SCAN2 & SCAN3 Listeners, but no changes. so we decided to restart the server, after we restart the server, SCAN1 startd on node-2 and SCAN2 & SCAN3 startd on node-1, and this time all the instances are registerd to all SCAN Listeners.

                    But still I am not sure about ORA-12516 error, if for some reason any one node fails we will get the following error and while application users try to connect the database. Can you please help me to solve this issue.

                    ORA-12516, TNS:listener could not find available handler with matching protocol stack