SQL*Net connectivity/config problem

  • From: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
  • To: oracle_l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 25 Aug 2009 09:58:13 +1000

(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@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@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 ...
<msg time='2009-08-24T18:43:59.204-05:00' org_id='oracle' comp_id='tnslsnr' 
 type='UNKNOWN' level='16' host_id='lwdbc21.multiservice.com'
 host_addr='172.25.170.14'>
<txt>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 
 </txt>
</msg>
<msg time='2009-08-24T18:43:59.205-05:00' org_id='oracle' comp_id='tnslsnr' 
 type='UNKNOWN' level='16' host_id='lwdbc21.multiservice.com'
 host_addr='172.25.170.14'>
<txt>TNS-12520: TNS:listener could not find available handler for requested type of server 
 </txt>
</msg>

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@lwdbc21 admin]$ sqlplus system/xxx@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@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.

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 08-2009