Having trouble with the infamous TNS-12518 error? You’re not alone. This article will provide you with a step-by-step guide for troubleshooting and resolving this common Oracle connection issue.
Symptoms
TNS-12518 error is a common Oracle connection issue wherein your Oracle Database Listener is not successfully handing off the client connection request. This issue may present itself in the following ways:
- Error message when connecting to the database: “TNS-12518: TNS Listener could not hand off client connection”
- Client connection requests “hang” and are not successfully fulfilled by the Oracle Database Listener
Causes
The TNS-12518 error is typically caused by one of two factors:
- Incorrect setting in the Oracle Listener Configuration
- Insufficient resources or OS synchronous IO limits
Step-by-Step Troubleshooting Guide
For this troubleshooting guide, we will assume that you are running an Enterprise Edition Oracle Database 11g Release 2, and that your listener is named ‘listener’ and runs on port 1521.
- Confirm that the Oracle Database Listener is running
- Check for the presence of the following in tnsnames.ora:
- (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=listener)))
- (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
- Check if the net service name given by the error is present and valid by running the following command:
tnsping listener
- Confirm that the Oracle Listener is running with the correct configuration
- Ensure the listener is running with the correct server configuration, with the following command:
lsnrctl status listener- Check the listener configuration with the following command:
lsnrctl show listener- Make sure that the number of active connections is not greater than the maximum connections value, otherwise suffer a crash.
- Make sure that the remote_listener parameter is set to the correct hostname, otherwise the listener will never be able to hand off the connection.
- Increase the Kernel synchronous IO limits
- Increase the kernel synchronous IO limit to accommodate larger volumes of data by running the following commands:
echo fs.aio-max-nr = 1048576 >> /etc/sysctl.conf; sysctl -pecho fs.file-max = 6815744 >> /etc/sysctl.conf; sysctl -pecho kernel.shmall = 2097152 >> /etc/sysctl.conf; sysctl -pecho kernel.shmmax = 2147483648 >> /etc/sysctl.conf; sysctl -p- Test the changes with the following command:
sysctl -a | grep fs.aio-max-nr, fs.file-max, kernel.shmall and kernel.shmmax
FAQ
Q: Do different versions of Oracle behave differently when it comes to TNS-12518 errors?
A: Yes. Depending on the version, the commands you need to run and the configurations you need to set may vary. Make sure you are familiar with the specific version you are running before attempting to troubleshoot this error.
Q: If the issue persists after I have completed the above steps, what do I do?
A: If the issue persists, it is likely that the problem lies elsewhere. Try debugging other possible causes such as incorrect Oracle Network Configuration or performance issues with the server.
Q: Is there a hotfix for TNS-12518 errors?
A: As of this writing, Oracle does not have a dedicated patch for this error. Your best bet is to troubleshoot the issue using the guide provided above.