FAQ
(11.1)
I'm attempting to make a local SQL*Net connection on a DB server and
I'm seeing an ORA-12520 error.

[oracle_at_lwdbc21 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 24-AUG-2009
18:24:58

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER

Alias LISTENER_LWDBC21
Version TNSLSNR for Linux: Version 11.1.0.7.0 -
Production
Start Date 24-AUG-2009 18:02:40
Uptime 0 days 0 hr. 22 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF

Listener Parameter File /var/oracle/product/11g/db/network/admin/
listener.ora
Listener Log File /var/oracle/diag/tnslsnr/lwdbc21/
listener_lwdbc21/alert/log.xml
Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.170.14)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.170.16)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "rac21d.prod.multiservice.com" has 1 instance(s).

Instance "rac21d1", status READY, has 1 handler(s) for this
service...
Service "rac21d_XPT.prod.multiservice.com" has 1 instance(s).

Instance "rac21d1", status READY, has 1 handler(s) for this
service...
The command completed successfully
[oracle_at_lwdbc21 admin]$ lsnrctl services

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 24-AUG-2009
18:25:10

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER

Service "+ASM_XPT" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER

Service "rac21d.prod.multiservice.com" has 1 instance(s).

Instance "rac21d1", status READY, has 1 handler(s) for this
service...

Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER

Service "rac21d_XPT.prod.multiservice.com" has 1 instance(s).

Instance "rac21d1", status READY, has 1 handler(s) for this
service...

Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER

The command completed successfully

Here's the error from the log ...

24-AUG-2009 18:43:59 * (connect_data=(server=shared)
(service_name=rac21d.prod.multiservice.com)(instance_name=rac21d1)
(CID=(PROGRAM=sqlplus)(HOST=lwdbc21.multiservice.com)(USER=oracle))) *
(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.170.16)(PORT=27901)) * establish *
rac21d.prod.multiservice.com * 12520


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


Here's the entry from the tnsnames.ora file ...

rac21d1 =

(description =
(address =
(protocol = tcp)
(host = lwdbc21-vip.multiservice.com)
(port = 1521)
)
(connect_data =
(server = shared)
(service_name = rac21d.prod.multiservice.com)
(instance_name = rac21d1)
)
)

In action ...

[oracle_at_lwdbc21 admin]$ sqlplus system/xxx_at_rac21d1

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Aug 24 18:51:51 2009

Copyright (c) 1982, 2008, Oracle. All rights reserved.

ERROR:

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

I can 'see' myself with tnsping ...

[oracle_at_lwdbc21 admin]$ $ORACLE_HOME/bin/tnsping rac21d1 5

TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 24-
AUG-2009 18:53:06

Copyright (c) 1997, 2008, Oracle. All rights reserved.

Used parameter files:
/var/oracle/product/11g/asm/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address = (protocol = tcp) (host

= lwdbc21-vip.multiservice.com) (port = 1521)) (connect_data = (server
= shared) (service_name = rac21d.prod.multiservice.com) (instance_name
= rac21d1)))
OK (0 msec)
OK (10 msec)
OK (0 msec)

OK (0 msec)
OK (10 msec)

The database instance has been 'cloned' from another machine. It is
to be our standby DR database cluster. I'm doing a roll-your-own
dataguard to keep it in sync with production.

Any clues as to the problem?

Thanks,

Steve

This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.

Search Discussions

  • Stefan Knecht at Aug 25, 2009 at 6:34 am
    In your tnsnames.ora you're asking for a shared server. Your database
    however, only offers dedicated servers.

    Either, change server to dedicated in your tnsnames.ora, or set up shared
    servers.

    Cheers

    Stefan P Knecht
    CEO & Founder
    s_at_10046.ch

    10046 Consulting GmbH
    Schwarzackerstrasse 29
    CH-8304 Wallisellen
    Switzerland

    Phone +41-(0)8400-10046
    Cell +41 (0) 79 571 36 27
    info_at_10046.ch
    http://www.10046.ch

    On Tue, Aug 25, 2009 at 1:58 AM, Steve Baldwin
    wrote:
    (11.1)
    I'm attempting to make a local SQL*Net connection on a DB server and I'm
    seeing an ORA-12520 error.

    [oracle_at_lwdbc21 admin]$ lsnrctl status

    LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 24-AUG-2009 18:24:58

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

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER_LWDBC21
    Version TNSLSNR for Linux: Version 11.1.0.7.0 -
    Production
    Start Date 24-AUG-2009 18:02:40
    Uptime 0 days 0 hr. 22 min. 17 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File
    /var/oracle/product/11g/db/network/admin/listener.ora
    Listener Log File
    /var/oracle/diag/tnslsnr/lwdbc21/listener_lwdbc21/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.170.14)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.170.16)(PORT=1521)))
    Services Summary...
    Service "+ASM" has 1 instance(s).
    Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Service "+ASM_XPT" has 1 instance(s).
    Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Service "rac21d.prod.multiservice.com" has 1 instance(s).
    Instance "rac21d1", status READY, has 1 handler(s) for this service...
    Service "rac21d_XPT.prod.multiservice.com" has 1 instance(s).
    Instance "rac21d1", status READY, has 1 handler(s) for this service...
    The command completed successfully
    [oracle_at_lwdbc21 admin]$ lsnrctl services

    LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 24-AUG-2009 18:25:10

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

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    Services Summary...
    Service "+ASM" has 1 instance(s).
    Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "+ASM_XPT" has 1 instance(s).
    Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "rac21d.prod.multiservice.com" has 1 instance(s).
    Instance "rac21d1", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "rac21d_XPT.prod.multiservice.com" has 1 instance(s).
    Instance "rac21d1", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    The command completed successfully

    Here's the error from the log ...

    type='UNKNOWN' level='16' host_id='lwdbc21.multiservice.com'
    host_addr='172.25.170.14'>
    24-AUG-2009 18:43:59 * (connect_data=(server=shared)(service_name=
    rac21d.prod.multiservice.com
    )(instance_name=rac21d1)(CID=(PROGRAM=sqlplus)(HOST=
    lwdbc21.multiservice.com)(USER=oracle))) *
    (ADDRESS=(PROTOCOL=tcp)(HOST=172.25.170.16)(PORT=27901)) * establish *
    rac21d.prod.multiservice.com * 12520


    type='UNKNOWN' level='16' host_id='lwdbc21.multiservice.com'
    host_addr='172.25.170.14'>
    TNS-12520: TNS:listener could not find available handler for
    requested type of server



    Here's the entry from the tnsnames.ora file ...

    rac21d1 =
    (description =
    (address =
    (protocol = tcp)
    (host = lwdbc21-vip.multiservice.com)
    (port = 1521)
    )
    (connect_data =
    (server = shared)
    (service_name = rac21d.prod.multiservice.com)
    (instance_name = rac21d1)
    )
    )

    In action ...

    [oracle_at_lwdbc21 admin]$ sqlplus system/xxx_at_rac21d1

    SQL*Plus: Release 11.1.0.7.0 - Production on Mon Aug 24 18:51:51 2009

    Copyright (c) 1982, 2008, Oracle. All rights reserved.

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

    I can 'see' myself with tnsping ...

    [oracle_at_lwdbc21 admin]$ $ORACLE_HOME/bin/tnsping rac21d1 5

    TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 24-AUG-2009
    18:53:06

    Copyright (c) 1997, 2008, Oracle. All rights reserved.

    Used parameter files:
    /var/oracle/product/11g/asm/network/admin/sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (description = (address = (protocol = tcp) (host =
    lwdbc21-vip.multiservice.com) (port = 1521)) (connect_data = (server =
    shared) (service_name = rac21d.prod.multiservice.com) (instance_name =
    rac21d1)))
    OK (0 msec)
    OK (10 msec)
    OK (0 msec)
    OK (0 msec)
    OK (10 msec)

    The database instance has been 'cloned' from another machine. It is to be
    our standby DR database cluster. I'm doing a roll-your-own dataguard to
    keep it in sync with production.

    Any clues as to the problem?

    Thanks,

    Steve

    ------------------------
    This email is intended solely for the use of the addressee and may
    contain information that is confidential, proprietary, or both.
    If you receive this email in error please immediately notify the
    sender and delete the email.

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 24, '09 at 11:58p
activeAug 25, '09 at 6:34a
posts2
users2
websiteoracle.com

2 users in discussion

Stefan Knecht: 1 post Steve Baldwin: 1 post

People

Translate

site design / logo © 2022 Grokbase