oracle
ORA-12637 not shown in sqlcl client
When running Single Instance database XE 21c image, all the connections hang for a minute with sqlcl and sqlplus.
Error from sqlcl:
$ sql SYS/*****@localhost:1521/XE as sysdba
SQLcl: Release 21.3 Production on Mon Dec 13 11:21:31 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
USER = SYS
URL = jdbc:oracle:thin:@localhost:1521/XE
Error Message = IO Error: Got minus one from a read call (CONNECTION_ID=dFio4UhlSL+ekTbEyJEt4Q==)
Error from sqlplus:
$ sqlplus SYS/*******@localhost:1521/XE as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 13 11:23:35 2021
Version 21.4.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
ERROR:
ORA-12637: Packet receive failed
Alert log error (identical with both tools):
Fatal NI connect error 12537, connecting to:
(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.2.100)(PORT=44536))
VERSION INFORMATION:
TNS for Linux: Version 21.0.0.0.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 21.0.0.0.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 21.0.0.0.0 - Production
Version 21.3.0.0.0
Time: 13-DEC-2021 09:56:25
Tracing not turned on. Process Id = 2489
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0
2021-12-13T09:56:25.223958+00:00
opiodr aborting process unknown ospid (2489) as a result of ORA-609
2021-12-13T09:56:25.225671+00:00
Errors in file /opt/oracle/diag/rdbms/xe/XE/trace/XE_ora_2489.trc:
ORA-00609: could not attach to incoming connection
ORA-12537: TNS:connection closed
2021-12-13T09:57:44.066530+00:00
Environment:
- New compute instance running in OCI. Oracle Linux 8 (Image: Oracle-Linux-Cloud-Developer-8.4-2021.08.27-0)
- "dnf update" just executed
- podman version 3.3.1
- sqlcl version: SQLcl: Release 21.3.3.0 Production Build: 21.3.3.322.1724
- sqlplus version: Version 21.4.0.0.0
Tests done:
- Connecting to the DB from inside the container works as expected. Issue seems network related between host and container.
- Running XE 21c container from gvenzl/oracle-xe:latest (https://github.com/gvenzl/oci-oracle-xe) works fine.
- DISABLE_OOB=ON in $HOME/.sqlnet.ora works with sqlplus as documented in the FAQ but it does not work with sqlcl
My preferred sql client is sqlcl so it took me a while until I realized that the issue was the one described in the FAQ (ORA-12637: Packet receive failed). I had previously tried the fix but $HOME/.sqlnet.ora in not used by sqlcl.
Once I saw the ORA error reported by sqlplus, then I realized that .sqlnet.ora file was not being used by sqlcl. The fix I found for sqlcl is to use JAVA_TOOL_OPTIONS.
$ export JAVA_TOOL_OPTIONS="-Doracle.net.disableOob=true"
$ sql SYS/*******@localhost:1521/XE as sysdba
I wanted to share this so other folks can find the answer more easily. Anyway, I have two questions:
- I think the FAQ would be better if we add the sqlcl scenario. If you think the same, just let me know and I can do the PR.
- It puzzles me why the issue is not reproduced from gvenzl repo. Any ideas @gvenzl ?
I came here after figuring it out to state what @cmolinah has already stated. I reproduced the same on Oracle Linux 9 as the base operating system on hardware I have.
All I initially found was about the DISABLE_OOB setting solving it and that it has been implemented in the server setup sqlnet.ora.
That setting is just a client setting there and does not cause the client to use it unless the connecting process is using an Oracle client.
This impact SQLcl and I imagine - while not tested - SQL Developer if one has not configured for an Oracle Client to be used.
After I had figured out the solution I found that there is some text about it in the FAQ. I never thought of looking there as it wasn't listed under known issues in the readme. I also mistakenly assumed it more covered things like "how do I get a dedicated connection" rather than known issues on can run into and how to resolve them.
Still, it explains how to deal with it when an Oracle Client is involved. But not how to deal with it for thin clients. There one can manage it with specifying it in the connect string or by setting JAVA_TOOL_OPTIONS as @cmolinah shows. For sqlcl one can also configure an Oracle client and then issue "sql -oci" to make it use the client. For SQL Developer one could configure it with the client and request OCI for all connections or for individual ones.
As running client less is extremely common this is something that either stops people from using it or causes them to search and find for it themselves. Would it make sens to have instructions for this in readme or in faq?
