This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 2, 2013 7:00 AM by EdStevens RSS

Client connections from linux to Solaris DB server failing

711242 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    711242 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    711242 Newbie
    Currently Being Moderated
    Hello All,

    Any suggestions on how can this be troubleshooted.

    Regards,

    Valli
  • 6. Re: Client connections from linux to Solaris DB server failing
    sb92075 Guru
    Currently Being Moderated
    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
    711242 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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
    711242 Newbie
    Currently Being Moderated
    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
    711242 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    post results from OS command below

    lsnrctl service
  • 13. Re: Client connections from linux to Solaris DB server failing
    Richard Harrison . Expert
    Currently Being Moderated
    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
    711242 Newbie
    Currently Being Moderated
    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

Legend

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