1 2 Previous Next 15 Replies Latest reply: Feb 2, 2013 9:00 AM by EdStevens RSS

    Client connections from linux to Solaris DB server failing

    user4566776
      Hello,

      I am getting the following error when I am connecting from my client box (Linux x86_64) with oracle client 11.1 installed onto my Solaris-SPARC DB server which has an oracle 11.2 installed

      ORA-12520: TNS:listener could not find available handler for requested type of
      server

      I checked a lot of stuff online. They all said.
      1. Check your Listner service
      The listener service seems to be fine because I ca connect to it using the listner from my local Solaris SPARC box and also from another Soalris SPARC box which has an 10 g oracle client

      2. The process limit must have got saturated.
      However that is not the case either I have checked the v$resource limit view and confirmed, I have also increased the process count

      However when I do an TNSPING from my linux box using the service name of the Solaris DB I am able to get a successful ping.

      I need your help to fix it.
      I presume Oracle client and DB server can be on a heterogeneous distributed system ?

      Regards,
      Valli
        • 1. Re: Client connections from linux to Solaris DB server failing
          EdStevens
          user4566776 wrote:
          Hello,

          I am getting the following error when I am connecting from my client box (Linux x86_64) with oracle client 11.1 installed onto my Solaris-SPARC DB server which has an oracle 11.2 installed

          ORA-12520: TNS:listener could not find available handler for requested type of
          server

          I checked a lot of stuff online. They all said.
          1. Check your Listner service
          The listener service seems to be fine because I ca connect to it using the listner from my local Solaris SPARC box and also from another Soalris SPARC box which has an 10 g oracle client

          2. The process limit must have got saturated.
          However that is not the case either I have checked the v$resource limit view and confirmed, I have also increased the process count

          However when I do an TNSPING from my linux box using the service name of the Solaris DB I am able to get a successful ping.
          tnsping doesn't do what you think it does. It only confirms name resolution and an open net link to the listener.

          see: [url http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/] tnsping-101

          >
          I need your help to fix it.
          I presume Oracle client and DB server can be on a heterogeneous distributed system ?
          Yes it absolutely can.
          >
          Regards,
          Valli
          • 2. Re: Client connections from linux to Solaris DB server failing
            user4566776
            Thanks for the reply, can yoy let me know how to troubleshoot / resolve it.
            • 3. Re: Client connections from linux to Solaris DB server failing
              EdStevens
              user4566776 wrote:
              Thanks for the reply, can yoy let me know how to troubleshoot / resolve it.
              I can't recall the last time I personally faced your specific error message. I'd have to research it, which is something you can do. Else wait for someone else to contribute to the thread.
              • 4. Re: Client connections from linux to Solaris DB server failing
                sb92075
                when was last time this client successfully connected?
                what changed since then?

                the listener records every connection request in listener.log file

                post the line that contains 12520 status code & adjacent lines here.
                • 5. Re: Client connections from linux to Solaris DB server failing
                  user4566776
                  Hello All,

                  Any suggestions on how can this be troubleshooted.

                  Regards,

                  Valli
                  • 6. Re: Client connections from linux to Solaris DB server failing
                    sb92075
                    user4566776 wrote:
                    Hello All,

                    Any suggestions on how can this be troubleshooted.

                    Regards,

                    Valli
                    when was last time this client successfully connected?
                    what changed since then?

                    the listener records every connection request in listener.log file

                    post the line that contains 12520 status code & adjacent lines here.
                    • 7. Re: Client connections from linux to Solaris DB server failing
                      user4566776
                      Hello,

                      The client connection was setup just yesterday, so it does not have a track record of working fine before but other client connections from here to linux DB's are working fine.

                      The error message is the same ora-12520 as pasted above.
                      • 8. Re: Client connections from linux to Solaris DB server failing
                        sb92075
                        user4566776 wrote:
                        Hello,

                        The client connection was setup just yesterday, so it does not have a track record of working fine before but other client connections from here to linux DB's are working fine.

                        The error message is the same ora-12520 as pasted above.
                        since you choose to ignore my request, I choose to not waste more time with you.
                        You're On Your Own (YOYO)!

                        Handle:     user4566776
                        Status Level:     Newbie
                        Registered:     Jul 10, 2009
                        Total Posts:     88
                        Total Questions:     34 (30 unresolved)

                        why so many unanswered questions?
                        • 9. Re: Client connections from linux to Solaris DB server failing
                          Richard Harrison .
                          Hi,
                          ARe you trying to connect to a shared server when none are available - post your tnsnames.ora and the output of lsnrctl status.

                          Regards,
                          Harry
                          • 10. Re: Client connections from linux to Solaris DB server failing
                            user4566776
                            Hello Richard,

                            tnsnames.ora as on my Linux M/c (11.1)
                            ---------------------------
                            RNO =
                            (DESCRIPTION =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = gux)(PORT = 1521))
                            (CONNECT_DATA =
                            (SERVER = SHARED)
                            (SERVICE_NAME = RNO)
                            )
                            )

                            ----------------------
                            Listener.ora as on Solaris SPARC (11.2)

                            SID_LIST_LISTENER =
                            (SID_LIST =
                            (SID_DESC =
                            (GLOBAL_DBNAME = RNO)
                            (ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
                            (SID_NAME = RNO)
                            )
                            (SID_DESC =
                            (GLOBAL_DBNAME = POW)
                            (ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
                            (SID_NAME = POW)
                            )
                            )
                            LISTENER =
                            (DESCRIPTION_LIST =
                            (DESCRIPTION =
                            (ADDRESS = (PROTOCOL = IPC)(KEY = RNO))
                            )
                            (DESCRIPTION =
                            (ADDRESS = (PROTOCOL = IPC)(KEY = POW))
                            )
                            (DESCRIPTION =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = gux)(PORT = 1521))
                            )
                            )

                            ADR_BASE_LISTENER = /oracle/app

                            Lsnrctl status
                            ----------------------

                            LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 01-FEB-2013 21:36:13

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

                            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=RNO)))
                            STATUS of the LISTENER
                            ------------------------
                            Alias LISTENER
                            Version TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
                            Start Date 01-FEB-2013 12:25:30
                            Uptime 0 days 9 hr. 10 min. 42 sec
                            Trace Level off
                            Security ON: Local OS Authentication
                            SNMP OFF
                            Listener Parameter File /oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
                            Listener Log File /oracle/app/diag/tnslsnr/gux/listener/alert/log.xml
                            Listening Endpoints Summary...
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=RNO)))
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=POW)))
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gux)(PORT=1521)))
                            Services Summary...
                            Service "RNO" has 2 instance(s).
                            Instance "RNO", status UNKNOWN, has 1 handler(s) for this service...
                            Instance "RNO", status READY, has 1 handler(s) for this service...
                            Service "RNOXDB" has 1 instance(s).
                            Instance "RNO", status READY, has 1 handler(s) for this service...
                            Service "POW" has 2 instance(s).
                            Instance "POW", status UNKNOWN, has 1 handler(s) for this service...
                            Instance "POW", status READY, has 1 handler(s) for this service...
                            Service "POWXDB" has 1 instance(s).
                            Instance "POW", status READY, has 1 handler(s) for this service...
                            The command completed successfully


                            Regards,

                            Valli

                            Edited by: user4566776 on Feb 1, 2013 8:08 AM
                            • 11. Re: Client connections from linux to Solaris DB server failing
                              user4566776
                              Hello Sb92075,

                              No, I did not mean to ignore your question, but I overlooked the listener log and I taught you were asking me the ora- error message,
                              regret for oversight.

                              Listner.log o/p for a TNSPING and failed sql connection.
                              -----------------------------------------------------------------------------

                              P.s.-> some adress and SID 's have been renamed

                              host_addr='*.*.*.253'>
                              <txt>01-FEB-2013 21:39:05 * service_update * POW * 0
                              </txt>
                              </msg>
                              <msg time='2013-02-01T21:39:08.401+05:30' org_id='oracle' comp_id='tnslsnr'
                              type='UNKNOWN' level='16' host_id='gux'
                              host_addr='*.*.*.253'>
                              <txt>01-FEB-2013 21:39:08 * service_update * POW * 0
                              </txt>
                              </msg>


                              <msg time='2013-02-01T21:39:22.262+05:30' org_id='oracle' comp_id='tnslsnr'
                              type='UNKNOWN' level='16' host_id='gux'
                              host_addr='*.*.*.253'>
                              <txt>01-FEB-2013 21:39:22 * ping * 0
                              </txt>
                              </msg>



                              <msg time='2013-02-01T21:39:35.415+05:30' org_id='oracle' comp_id='tnslsnr'
                              type='UNKNOWN' level='16' host_id='gux'
                              host_addr='*.*.*.253'>
                              <txt>01-FEB-2013 21:39:35 * service_update * POW * 0
                              </txt>
                              </msg>
                              <msg time='2013-02-01T21:39:55.451+05:30' org_id='oracle' comp_id='tnslsnr'
                              type='UNKNOWN' level='16' host_id='gux'
                              host_addr='*.*.*.253'>
                              <txt>01-FEB-2013 21:39:55 * (CONNECT_DATA=(SERVER=SHARED)(SERVICE_NAME=RNO)(CID=(PROGRAM=sqlplus@flr)(HOST=flr)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=*.*.*.105)(PORT=31462)) * establish * RNO * 12520
                              </txt>
                              </msg>
                              <msg time='2013-02-01T21:39:55.452+05:30' org_id='oracle' comp_id='tnslsnr'
                              type='UNKNOWN' level='16' host_id='gux'
                              host_addr='*.*.*.253'>
                              <txt>TNS-12520: TNS:listener could not find available handler for requested type of server
                              </txt>
                              </msg>
                              <msg time='2013-02-01T21:40:05.435+05:30' org_id='oracle' comp_id='tnslsnr'
                              type='UNKNOWN' level='16' host_id='gux'
                              host_addr='*.*.*.253'>
                              <txt>01-FEB-2013 21:40:05 * service_update * POW * 0
                              </txt>
                              </msg>


                              Let me know if I have missed anything else.

                              Regards,
                              Valli
                              • 12. Re: Client connections from linux to Solaris DB server failing
                                sb92075
                                post results from OS command below

                                lsnrctl service
                                • 13. Re: Client connections from linux to Solaris DB server failing
                                  Richard Harrison .
                                  Hi,
                                  Remove the server=shared line from your tnsnames.ora that's the problem.

                                  Cheers,
                                  Harry
                                  • 14. Re: Client connections from linux to Solaris DB server failing
                                    user4566776
                                    Thanks Harry, you are amazing !!! you saved my day.

                                    I would need one more help => for my knowledge, when do we use this "(SERVER = SHARED)" in tnsnames? is this for Shared server config instead of dedicate connection mode?

                                    I presumed my DB was in shared server config as the spfile parameter shared_servers=5

                                    Regards,
                                    Valli
                                    1 2 Previous Next