SYMPTOMS:
CAUSE:
The status of the service used to connect to the database shows online on instances 1 & 2:
ora.racdb1.serv1.svc
1 ONLINE ONLINE racdb11
2 ONLINE ONLINE racdb12
however the node listener on node 2 is having some problem, 'crsctl stat res -t' shows:
ora.LISTENER.lsnr
ONLINE ONLINE racnode1
ONLINE INTERMEDIATE racnode2 CHECK TIMED OUT
ONLINE ONLINE racnode3
Running 'lsnrctl status LISTENER' shows that the listener has no TCP endpoints open:
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
The listener supports no services
The command completed successfully
It is unclear how the listener got into this state., it prevents the instance(s) on node racnode2 to register their services with the node listener, since the instance connects to the listener via the TCP port 1525.
The instances can however register their services with the SCAN listeners, so the ORA-12516 error seems to be reported whenever a SCAN listener tries to hand off a connect request to the listener on node racnode2.
SOLUTION:
1. Find the PID of the node listener - e.g.:
$ ps -ef | grep tns
oracle 18135 1 0 Oct 3 ? 348:26 /u01/app/grid/11203/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle 21652 1 0 Feb 18 ? 96:44 /u01/app/grid/11203/bin/tnslsnr LISTENER -inherit
2. Kill the listener using:
kill -9 21652
3. Restart the listener using:
srvctl start listener -n racnode2
4. Make sure the listener is now listening on TCP endpoints
lsnrctl status LISTENER
5. One minute after the listener has been started check if it shows services:
lsnrctl services LISTENER
Reference: Connect Via SCAN Intermittently Fails With ORA-12516: TNS:listener Could Not Find Available Handler With Matching Protocol Stack (Doc ID 1638175.1)
- multinode (3+) RAC cluster
- application using SCAN and a dedicated service (here serv1) to connect to the database
- service is configured to run on instances 1 & 2 (preferred)
- intermittently connect attempts fail with error ORA-12516: TNS:listener could not find available handler with matching protocol Stack
CAUSE:
The status of the service used to connect to the database shows online on instances 1 & 2:
ora.racdb1.serv1.svc
1 ONLINE ONLINE racdb11
2 ONLINE ONLINE racdb12
however the node listener on node 2 is having some problem, 'crsctl stat res -t' shows:
ora.LISTENER.lsnr
ONLINE ONLINE racnode1
ONLINE INTERMEDIATE racnode2 CHECK TIMED OUT
ONLINE ONLINE racnode3
Running 'lsnrctl status LISTENER' shows that the listener has no TCP endpoints open:
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
The listener supports no services
The command completed successfully
It is unclear how the listener got into this state., it prevents the instance(s) on node racnode2 to register their services with the node listener, since the instance connects to the listener via the TCP port 1525.
The instances can however register their services with the SCAN listeners, so the ORA-12516 error seems to be reported whenever a SCAN listener tries to hand off a connect request to the listener on node racnode2.
SOLUTION:
1. Find the PID of the node listener - e.g.:
$ ps -ef | grep tns
oracle 18135 1 0 Oct 3 ? 348:26 /u01/app/grid/11203/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle 21652 1 0 Feb 18 ? 96:44 /u01/app/grid/11203/bin/tnslsnr LISTENER -inherit
2. Kill the listener using:
kill -9 21652
3. Restart the listener using:
srvctl start listener -n racnode2
4. Make sure the listener is now listening on TCP endpoints
lsnrctl status LISTENER
5. One minute after the listener has been started check if it shows services:
lsnrctl services LISTENER
Reference: Connect Via SCAN Intermittently Fails With ORA-12516: TNS:listener Could Not Find Available Handler With Matching Protocol Stack (Doc ID 1638175.1)
