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

Error:

From Alert Log:

FAL[client, MRP0]: Error 12520 connecting to MISC for fetching gap sequence
Mon May 27 12:35:03 2013
Errors in file /opt/oracle/admin/ISC/bdump/isc_mrp0_1513.trc:
ORA-12520: TNS:listener could not find available handler for requested type of server
Mon May 27 12:35:33 2013
FAL[client, MRP0]: Error 12520 connecting to MISC for fetching gap sequence
Mon May 27 12:35:33 2013
Errors in file /opt/oracle/admin/ISC/bdump/isc_mrp0_1513.trc:
ORA-12520: TNS:listener could not find available handler for requested type of server
Mon May 27 12:36:37 2013
FAL[client, MRP0]: Error 12520 connecting to MISC for fetching gap sequence
Mon May 27 12:36:37 2013
Errors in file /opt/oracle/admin/ISC/bdump/isc_mrp0_1513.trc:
ORA-12520: TNS:listener could not find available handler for requested type of server
Mon May 27 12:37:37 2013
FAL[client]: Failed to request gap sequence



From Trace:

*** 2013-05-27 12:35:33.878 61287 kcrr.c
FAL[client, MRP0]: Error 12520 connecting to MISC for fetching gap sequence
ORA-12520: TNS:listener could not find available handler for requested type of server
*** 2013-05-27 12:36:37.243
OCIServerAttach failed -1
.. Detailed OCI error val is 12520 and errmsg is 'ORA-12520: TNS:listener could not find available handler for requested type of server
'



Where:

Observed on Standby database , although primary were able to transfer archives to standby but standby was unable to connect to primary to Fetch the Gap sequence

Analysis:

1. Verified tnsping , found to be working





$tnsping MISC
TNS Ping Utility for Solaris: Version 10.2.0.3.0 - Production on 27-MAY-2013 12:33:10
Copyright (c) 1997, 2006, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mu-me01-003)(PORT = 1529))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MISC)))
OK (60 msec)

2. Checked sqlplus <usr>@<TNS_Alias>/<password> , found to be giving error ORA-12520
Initially Suspected password file issue & tried sqlplus sys@MISC as sysdba or sqlplus system@MISC , but came across below error every time

$sqlplus ajay@MISC
SQL*Plus: Release 10.2.0.3.0 - Production on Mon May 27 12:34:24 2013
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Enter password:
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server



Resolving ORA-12520 for sqlplus could have resolved issue for MRP0 also




Solution:


Tnsnames.ora changes done for SERVICE_NAME as below

MISC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mu-me01-003)(PORT = 1529))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MISC)
    )
  )

changed to

MISC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mu-me01-003)(PORT = 1529))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ISC)
    )
  )



where ISC is the db_unique_name or instance_name of the primary database

Verification:


$tnsping MISC

TNS Ping Utility for Solaris: Version 10.2.0.3.0 - Production on 27-MAY-2013 12:37:24
Copyright (c) 1997, 2006, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mu-me01-003)(PORT = 1529))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ISC)))
OK (60 msec)
 




$sqlplus ajay@MISC
SQL*Plus: Release 10.2.0.3.0 - Production on Mon May 27 12:37:28 2013
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL> exit