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


1.listener problem
when I try to connect rac from the client using sqlplus / @tnsname as sysdba, I got this error
*ORA-12520: TNS:listener could not find available handler for requested type of server*
it was caused by configuration of listener and tnsnames.

*refer:Subject: Ora-12520 When listeners on VIP in 10g RAC Setup Doc ID: Note:342419.1*
//following the notes, it seems that you need to set local_listener in each node in spfiles(other dba agree with me)
//set the parameter on node rac1 in local_listener and you need to config them on each nodes
//remote_listener control the function of load balance,
//local_listener helps a lot in TAF
SQL> show parameter listener;

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string LISTENER_RAC2
remote_listener string LISTENERS_RAC

//section of tnsnames.ora on server side
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
)
)

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
)

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.128)(PORT = 1521)(IP = FIRST))
)
)

LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.129)(PORT = 1521)(IP = FIRST))
)
)

RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
(INSTANCE_NAME = rac2)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
(INSTANCE_NAME = rac1)
)
)

//listener.ora on server side
LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.129)(PORT = 1521)(IP = FIRST))
)
)

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.128)(PORT = 1521)(IP = FIRST))
)
)

rac failover test

use sqlplus to connect the rac
sqlplus sys/passwod@rac as sysdba

SQL> select instance_name from v$instance;

INSTANCE_NAME
——————————–
rac2

connect rac2 and shutdown the instance
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
//from the crs_stat -t, you can see the instance of rac2 was offline
oracle@rac2 admin$ crs_stat -t
Name Type Target State Host
————————————————————
ora.rac.db application ONLINE ONLINE rac1
ora….c1.inst application ONLINE ONLINE rac1
ora….c2.inst application OFFLINE OFFLINE
ora….C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2

//go to the session just created before,execute the select,you will get the instance rac1 which it fail over switch automatically from
//rac2 to rac1
SQL> select instance_name from v$instance;

INSTANCE_NAME
——————————–
rac1

//here are the configuration of tnsnames.ora,listener.ora on server side and the tnsnames.ora on client side
***************************************************************************
##listener.ora on shared NFS
LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.129)(PORT = 1521)(IP = FIRST))
)
)

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.128)(PORT = 1521)(IP = FIRST))
)
)

*********************************************************************************
#in tnsnames.ora,LISTENER_RAC1 and LISTENER_RAC2 were manul added, they are the entrance for local_listener=’LISTENER_RAC1′

RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
)
)

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
)

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.128)(PORT = 1521)(IP = FIRST))
)
)

LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.129)(PORT = 1521)(IP = FIRST))
)
)

RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
(INSTANCE_NAME = rac2)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
(INSTANCE_NAME = rac1)
)
)

*****************************************************
//following are the client side tnsnames.ora key section

*******************************************************
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
*******************************************************