ORA-12537 Overview
There’re several possible causes to raise ORA-12537:
- Incorrect File Permission
- White or Black List
ORA-12537 Caused by Incorrect File Permission
Incorrect file permission will prevent connections from being established. That is to say, potentially successful connections will be interrupted by ORA-12537 due to wrong file permissions, specifically, the execution permission. As a result, we saw the error on the client side.
For Single-instance
One simple change can reproduce ORA-12537:
[oracle@primary ~]$ ll $ORACLE_HOME/bin/oracle
-rwsr-s--x. 1 oracle oinstall 239952653 Sep 11 20:36 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
[oracle@primary ~]$ chmod u-x $ORACLE_HOME/bin/oracle
[oracle@primary ~]$ ll $ORACLE_HOME/bin/oracle
-rwSr-s--x. 1 oracle oinstall 239952653 Sep 11 20:36 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
In the above, I removed the user execution permission on $ORACLE/bin/oracle. Now let’s try to make a connection from outside.
C:Usersedchen>sqlplus hr/hr@db11g
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 4 19:25:51 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12537: TNS:connection closed
Enter user-name:
What we saw in listener.log can prove that the connection was established, but somehow the listener cannot hand out the connection due to permission problem.
[oracle@primary ~]$ tail -f $ORACLE_BASE/diag/tnslsnr/$(hostname -s)/listener/trace/listener.log
...
Tue Mar 04 19:25:51 2019
04-MAR-2019 19:25:51 * (CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=D:instantclientsqlplus.exe)(HOST=MACHINE_NAME)(USER=edchen))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.12.123)(PORT=51385)) * establish * ORCL * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
Linux Error: 13: Permission denied
Further reading: Where is Oracle Listener Log Location
Errors in stack are all around permission problems and pointed to Linux error at the bottom.
Solution
That is, the solution is to add group permission to the file, which can rescue incoming connections from ORA-12537. Therefore, our action should be inspecting all the file permissions in $ORACLE, especially in $ORACLE_HOME/bin.
For RAC Database
After installing 11g RAC, we can test the database access, it would be successful via SCAN. But when we directly access on specific node, we found the connection failed on the second node with an error:
ORA-12537:TNS:connection closed
Generally speaking, the listener belongs to user grid, and the database belongs to user oracle in a RAC environment. Two users belong to a very special group oinstall. When we connected to the listener of the second node, the listener was acknowledged that it had no permission to access database. This could be the root cause of ORA-12537.
Let’s check some files’ permission from user grid‘s point of view.
- On the first node.
- On the second node.
$ ls -al $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 534683872 Mar 12 16:08
$ ls -al $ORACLE_HOME/bin/oracle
/oracle/database/product/11.2.0/dbhome_1/bin/oracle not found
That is to say, user grid can access oracle‘s files on the first node, but not on the second node. So we checked the following directories by user oracle on the second node:
$ cd $ORACLE_BASE
$ ls -l
total 2
drwx------ 3 oracle oinstall 96 Mar 12 16:02 admin
drwx------ 3 oracle oinstall 96 Mar 12 16:02 cfgtoollogs
drwxrwxr-x 11 oracle oinstall 1024 Mar 12 16:00 diag
drwxr-xr-x 3 oracle oinstall 96 Mar 12 16:09 product
$ cd product
$ ls -l
total 0
drwx------ 3 oracle oinstall 96 Mar 12 16:09 11.2.0
As you can see, it show that $ORACLE_BASE/product do have group permission, but $ORACLE_BASE/product/11.2.0 do not. As a result, grid cannot access the database files. That’s why we received ORA-12537 while connecting to the database.
Solution
Therefore, we should add group permission for grid to access on this directory in a cascading fashion:
$ chmod 755 11.2.0
$ ls -l
total 0
drwxr-xr-x 3 oracle oinstall 96 Mar 12 16:09 11.2.0
Now, connections are back to work. No more ORA-12537 are thrown.
Theoretically, two nodes should have symmetric structures with same permissions at the beginning. So I think it might be a bug, because I didn’t create the directory «11.2.0», OUI did.
ORA-12537 Caused by White or Black List
As I said in the above, ORA-12537 is meant for interrupting potentially successful connections. Here is another error pattern of ORA-12537.
In some cases, DBA explicitly blocks or allows some nodes listed in sqlnet.ora to limit the access to the database, which is essentially a black or white list in terms of network security. For example, we can implement a white list in sqlnet.ora like this:
TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(weblogic1.example.com, weblogic2.example.com, 10.10.0.0/16)
Solution
Make sure you are in the white list. Otherwise you might get ORA-12537 when you connect to the database.
For those who want to implement black lists, TCP.EXCLUDED_NODES parameter should be in sqlnet.ora.
Further reading: TNSPING Errors Collections
I’m facing the following error when trying to make a connection to oracle always free database.
Failed to connect: { [Error: ORA-12537: TNS:connection closed] errorNum: 12537, offset: 0 }
Here is my script:
const oracledb = require('oracledb');
console.log('Getting connection');
let connection = oracledb.getConnection({
user: 'ADMIN',
password: '*****',
connectString: '*****'
}).then(() => {
console.log('Connection established');
}).catch((err) => {
console.log('Failed to connect: ', err);
});
Here are my version strings:
> process.platform
win32
> process.version
v10.16.3
> process.arch
x64
> require('oracledb').versionString
'4.0.1'
I’m trying to connect to Autonomous Transaction Processing Instance from Oracle Always Free cloud with database version 18c.
Thanks for taking the time to read this. Do let me know if more info is required from me.
From the Cloud GUI page for your database, click ‘DB Connection’ to download the Client Credentials wallet.
Enter a new wallet password when prompted — this will not actually be needed for use with node-oracledb.
Unzip wallet.zip and edit sqlnet.ora. Set DIRECTORY to the directory with the extracted files. Keep the files safe. For node-oracledb you only need cwallet.sso, sqlnet.ora, and tnsnames.ora
Or don’t edit sqlnet. if you put the extracted files in a default location like instantclient_19_3/network/admin. In this case there is also no need to set TNS_ADMIN
$ export TNS_ADMIN=/Users/cjones/q/Cloud/alwaysfree
$ sqlplus -l admin@cjdb1_high
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 27 11:11:52 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Oct 27 2019 11:07:38 +11:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL ADMIN@cjdb1_high>
The next stage would be to create a normal (non admin) user to mess around with. Something like this:
define USERNAME = cj
begin execute immediate 'drop user &USERNAME cascade'; exception when others then if sqlcode <> -1918 then raise; end if; end;
/
create user &USERNAME;
alter user &USERNAME
default tablespace data
temporary tablespace temp
account unlock
quota unlimited on data;
grant connect
, resource
to &USERNAME;
grant create session
, create table
, create procedure
, create sequence
, create trigger
, create view
, create synonym
, alter session
, create type
, soda_app
to &USERNAME;
password &USERNAME
I’m working on a blog post for this.
I did as you mentioned and then I used the newly created username and password in oracledb.getConnection(). The error remains the same. Am I missing something?
FIrewalls, typos, proxies?
I’m not using any proxies and turning off all firewalls didn’t change a thing. Rechecked my username and password multiple times. I’m able to connect through SQL developer but not through oracledb in nodejs. Is it possible to turn on additional logging to know what is causing this problem, because my instance is certainly on as I’m able to connect to it through other means?
Start by giving us actual screen logs (not screen shots — please) of a terminal window showing the listing of the directory with the wallet, the contents in sqlnet.ora, you setting TNS_ADMIN (if you don’t use a default location for the wallet files), showing PATH containing the Instant Client libraries, and then running Nodejs
Sure. Here’s the directory listing of the wallet placed inside network/admin of instant client.
Volume in drive C is Windows-SSD
Volume Serial Number is 94D1-6E5D
Directory of C:oracleinstantclient-basic-windows.x64-19.3.0.0.0dbruinstantclient_19_3networkadmin
10/27/2019 08:54 AM <DIR> .
10/27/2019 08:54 AM <DIR> ..
10/27/2019 08:53 AM 6,661 cwallet.sso
10/27/2019 08:53 AM 6,616 ewallet.p12
10/27/2019 08:53 AM 3,241 keystore.jks
10/27/2019 08:53 AM 87 ojdbc.properties
10/27/2019 08:53 AM 114 sqlnet.ora
10/27/2019 08:53 AM 1,786 tnsnames.ora
10/27/2019 08:53 AM 3,335 truststore.jks
7 File(s) 21,840 bytes
2 Dir(s) 358,877,769,728 bytes free
Contents of sqlnet.ora file:
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes
My path:
echo %PATH%
-----;C:oracleinstantclient-basic-windows.x64-19.3.0.0.0dbruinstantclient_19_3;-----
Contents of temp.js
const oracledb = require('oracledb');
console.log('Getting connection');
let connection = oracledb.getConnection({
user: 'DUMMY',
password: 'Dummy1@Password',
connectString: 'adb.uk-london-1.oraclecloud.com:1522/**************_tp.atp.oraclecloud.com'
}).then((res) => {
console.log('Connection established:', res);
}).catch((err) => {
console.log('Failed to connect: ', err);
});
node temp.js
Getting connection
Failed to connect: { [Error: ORA-12537: TNS:connection closed] errorNum: 12537, offset: 0 }
Really stuck on this for a couple of days now. I’ve recently started with the oracle database and the worst part is that I remember it connecting previously. Thanks again for taking your time going through this issue. Highly appreciated.
@dmcghan Thanks a ton mate! Your video was really clear and helpful in explaining what to do and why we’re doing it. Great content!
@a1diablo Was updating sqlnet.ora really necessary as part of your solution? The string «?/network/admin» should be being expanded by the Oracle net code to (in your case) C:oracleinstantclient-basic-windows.x64-19.3.0.0.0dbruinstantclient_19_3networkadmin
@cjbj Not really. What I understood from the behavior was, that I cannot use connect strings directly in nodejs code. I had to use the name from tnsnames.ora and set TNS_ADMIN as the path of the wallet contents; only then it would work.
Since your wallet was in the default directory C:oracleinstantclient-basic-windows.x64-19.3.0.0.0dbruinstantclient_19_3networkadmin you don’t need to set TNS_ADMIN or edit sqlnet.ora
The ORA-12537 error is a common Oracle error that occurs when there is a problem with the network connection between the client and the server. This can result in a «Network Session: End of File» error message. In this guide, we will discuss the common causes of this error and provide step-by-step instructions to help you troubleshoot and resolve the issue.
Table of Contents
- Common Causes of ORA-12537
- Step-by-Step Guide to Troubleshoot ORA-12537
- Frequently Asked Questions (FAQ)
Common Causes of ORA-12537
There are several factors that can contribute to the ORA-12537 error:
Firewall restrictions: The firewall settings on the client or server machine may be blocking the required communication ports.
Incorrect listener configuration: The Oracle listener configuration file (listener.ora) may have incorrect settings or may be missing required entries.
Network issues: Problems with the network, such as high latency, packet loss, or misconfigured network devices, can cause this error.
- Oracle software bugs: In some cases, the error may be caused by a bug in the Oracle software. This is more likely if you are using an older version of Oracle.
Step-by-Step Guide to Troubleshoot ORA-12537
Follow these steps to troubleshoot and resolve the ORA-12537 error:
Step 1: Verify Firewall Settings
Check the firewall settings on both the client and server machines. Ensure that the required communication ports are open and that the Oracle listener is allowed to accept incoming connections.
Refer to your firewall documentation for instructions on how to configure these settings.
Step 2: Check the Listener Configuration
Review the Oracle listener configuration file (listener.ora) on the server machine. Ensure that the following entries are correct:
- The hostname or IP address of the server machine
- The port number on which the listener is listening (default is 1521)
- The service name(s) for the Oracle database instance(s)
If you make any changes to the listener.ora file, restart the Oracle listener service for the changes to take effect.
Step 3: Test Network Connectivity
Test the network connectivity between the client and server machines using tools such as ping, traceroute, and telnet.
- Use
pingto check whether the server machine is reachable from the client machine. - Use
tracerouteto identify any network issues or bottlenecks between the client and server machines. - Use
telnetto test connectivity to the Oracle listener port on the server machine.
If you encounter any network issues, consult your network administrator to help resolve them.
Step 4: Update Oracle Software
If the issue persists after completing the previous steps, consider updating your Oracle software to the latest version. This can help resolve any bugs or compatibility issues that may be causing the ORA-12537 error.
Refer to the Oracle documentation for instructions on how to update your Oracle software.
Frequently Asked Questions (FAQ)
What is the ORA-12537 error?
The ORA-12537 error is an Oracle error related to network connectivity issues between the client and server machines. It can result in a «Network Session: End of File» error message.
How can I resolve the ORA-12537 error?
To resolve the ORA-12537 error, you need to:
- Verify firewall settings on both the client and server machines.
- Check the Oracle listener configuration (listener.ora) on the server machine.
- Test network connectivity between the client and server machines.
- Update your Oracle software to the latest version, if necessary.
What is the default port for the Oracle listener?
The default port for the Oracle listener is 1521. However, the port number can be changed in the listener.ora configuration file if necessary.
How do I restart the Oracle listener service?
To restart the Oracle listener service, use the following command:
lsnrctl stop
lsnrctl start
How can I check the status of the Oracle listener?
To check the status of the Oracle listener, use the following command:
lsnrctl status
This will display information about the listener, including its current status, the listening endpoints, and the services it is managing.
When you’re sure all the TNS listener (SID_LIST_LISTENER in this case) services and db_domain are fine, but you still face the ORA-12537: TNS:connection closed error when trying to remotely access an open database, what can you do next?
[[email protected] admin]$ sqlplus sys/******@MYDB as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 3 17:45:29 2021 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-12537: TNS:connection closed
Here’s a quick tip — if you’ve been an Oracle DBA for long enough, you’ll know this workaround solves a variety of issues:
[[email protected] admin]$ cd $ORACLE_HOME/bin [[email protected] bin]$ ls -lrt oracle -rwxr-x--x. 1 oracle oinstall 242993632 Dec 18 19:20 oracle [[email protected] bin]$ chmod 6751 oracle [[email protected] bin]$ ls -lrt oracle -rwsr-s--x. 1 oracle oinstall 242993632 Dec 18 19:20 oracle
As you can see, the CHMOD 6751 did it again!
The reason this happened to me is, somehow, these permissions were wrong in my LOCAL host (the one I was trying to use the SQLPlus* from).
I hope my experience helps you. If you have any questions or anything to add, please leave me a comment.
Want to talk with an expert? Schedule a call with our team to get the conversation started.
ORA-12537: TNS: Connection Closed Error
Oracle’s ability to be customized with deep mechanics in regards to the construction of company databases is one of many reasons that it has become so prevalent in corporations today. Due in part to the complex nature of Oracle database structures, occasionally errors can arise, some of which carry a string of causes and additional problems. The ORA-12537 connection closed error is an example of one of these types of issues that is associated with an assortment of causes and potential outcomes. Let us look at some of these initial triggers for the error and see what can be done to counter them so databases can be returned to full functionality.
The Problem
The ORA-12537 is defined as an informational error, a message error triggered alongside a secondary error that typically offers the true programming issue. Oracle docs describes the cause as “a normal end-of-file condition being reached; partner has been disconnected”. It can occasionally relate to configuration issues in the sqlnet.ora, listerner.ora files or the protocol.ora. Furthermore, the error can derive from the firewall in a network being disconnected due to idling connections.
Another potential cause is that a path name is too long for the Oracle TNS client. Just as well, there is a common bug in Oracle known as 1566794: CONNECTIONS FAIL WITH ORA-12537 WHEN USE_SHARED_SOCKET IS SET IN 8.1.7. The resolution to this condition is to set the USED_SHARED_SOCKET parameter to FALSE.
The error can also occur if the user has listed a 10g database SID with a 9i listener, which can create the problem because of invalid inter-version connections. Additionally, the user can generate an ORA-12537 error in Oracle Apps 11i due to an eBusiness Suite security feature.
One final cause of the error is when the Tnsping Net Service name fails with the TNS-12537. This occurs because the Sqlnet.ora file parameter TCP.VALIDNODE_CHECKING is enabled and TCP.INVITEDNODES is set to a specific IP’s of the client machine. These parameters are enable by default in the Oracle Apps Installation.
The Solution
An initial step for taking on this error is to of course check your records and see what events transpired just prior to the occurrence of the event. From there, the user should attempt to verify that all service names match between the listener and the remote client connect strings. In the event that the firewall was disconnected to due idleness, check the idling parameters across your system and adjust them to reflect the networks usage.
For a path name that is too large, install the exe in a shorter named directory. The user can also modify the TNS Names entry so that it is a bit shorter, they can remove parameters from the entry that are largely unnecessary and they could replace domain names with IP addresses in the event that they shorten things up. When the error is prompted by the aforementioned eBusiness Suite security feature, the solution is to edit your sqlnet.ora file to include the client IP address in the tcp.invited_nodes = (hostname, etc.).
Lastly, to correct the TNSping Net Service name fails error, the user can disable these parameters in the Sqlnet.ora. The user can also add the client machine’s IP address in the TCP.INVITEDNODES list.
Looking forward
As can be seen from above, there are numerous causes of the ORA-12537. With that said, the user can make a couple of adjustments to offset this error. Maintaining an awareness of parameters that are enabled and noting idle times across the network can go a long way in preventing not just this error, but a number of problems within Oracle. Another slight modification to be made is keeping the programming as neat and concise as possible. Working to eliminate long strings of domain names and code is a strong discipline to instill in the database programming process. For more information about these methods, it would is advised to speak with a licensed Oracle consultant.