search

kubo / ruby-oci8

Ruby-oci8 - Oracle interface for ruby
Other
169 stars 75 forks source link

oci8.c:513:in oci8lib.so: ORA-12537: TNS:connection closed (OCIError) #66

Closed ozborn closed 9 years ago

ozborn commented 9 years ago

I am maintaining some legacy ruby code and one of the databases this middleware accesses was changed to an 11.2 Oracle database (from 11.1) that uses"TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production" instead of Linux. It now generates an ORA-12537: TNS:connection closed (OCIError).

I isolated the relevant code in a small script which does nothing but opens databases and query the Oracle version information. It queries 2 different databases, the problem database (PI) and another database (FIG) which hash an identical oracle version string except for a different TNS line - it appears to be running AIX. The results are below:

/opt/oracle/instantclient_12_1 Warning: NLS_LANG is not set. fallback to US7ASCII. FIG Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production PI oci8.c:513:in oci8lib.so: ORA-12537: TNS:connection closed (OCIError) from /usr/lib64/ruby/gems/1.8/gems/ruby-oci8-2.1.7/lib/oci8/oci8.rb:148:in initialize' from app/controllers/testdb.rb:32:innew' from app/controllers/testdb.rb:32

The problem does not appear to lie with the Oracle Instant Client or the network, I can query the target database from the host machine using the sqlplus provided in the Instant Client (either 11_2 or 12_1) as shown below: [root@cashew-dev instantclient_12_1]# ./ozborn_test.bsh /opt/oracle/instantclient_12_1

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 9 17:10:12 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Automatic Storage Management option

SQL> SELECT * FROM V$VERSION;

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production

SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Automatic Storage Management option

So I am quite puzzled by all of this, right now I suspect it is some kind of specific OCI8 / AIX connection issue where perhaps OCI8 is freaking out and dropping the connection if it doesn't see Oracle on Linux or Windows but I thought it better to post here than spend more time googling all the reasons you can generate an ORA-12537...

Any help appreciated, more info is provided below (ruby version, etc...). I have re-installed ruby-oci8 (actually upgraded it to 2.1.7 from 2.1.5) and that went okay (reported success) but I did have trouble generating the documentation.

-John

Other relevant information: ruby 1.8.7 (2011-06-30 patchlevel 352) [x86_64-linux] [root@cashew-dev instantclient_12_1]# file libclntsh.so libclntsh.so: symbolic link to `libclntsh.so.12.1' [root@cashew-dev instantclient_12_1]# file libclntsh.so.12.1 libclntsh.so.12.1: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, not stripped [root@cashew-dev instantclient_12_1]# file libclntsh.so.12.1 libclntsh.so.12.1: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, not stripped [root@cashew-dev instantclient_12_1]# ruby -r rbconfig -e "p Config::CONFIG['host']" "x86_64-redhat-linux-gnu" [root@cashew-dev instantclient_12_1]# ruby -r rbconfig -e "p Config::CONFIG['CC']" "gcc" [root@cashew-dev instantclient_12_1]# ruby -r rbconfig -e "p Config::CONFIG['CFLAGS']" "-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fno-strict-aliasing -fPIC " [root@cashew-dev instantclient_12_1]# ruby -r rbconfig -e "p Config::CONFIG['LDSHARED']" "gcc -shared" [root@cashew-dev instantclient_12_1]# ruby -r rbconfig -e "p Config::CONFIG['LDFLAGS']" "-L. -rdynamic -Wl,-export-dynamic" [root@cashew-dev instantclient_12_1]# ruby -r rbconfig -e "p Config::CONFIG['DLDLAGS']" nil [root@cashew-dev instantclient_12_1]# ruby -r rbconfig -e "p Config::CONFIG['LIBS']" "-lpthread -lrt -ldl -lcrypt -lm " [root@cashew-dev instantclient_12_1]# ruby -r rbconfig -e "p Config::CONFIG['GNU_LD']" "yes"

[root@cashew-dev instantclient_12_1]# tail -n 100 /usr/lib64/ruby/gems/1.8/gems/ruby-oci8-2.1.7/ext/oci8/mkmf.log

have_func: checking for rb_class_superclass() in ruby.h... -------------------- no

"gcc -o conftest -I. -I/usr/lib64/ruby/1.8/x86_64-linux -I. -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fno-strict-aliasing -fPIC -I/opt/oracle/instantclient_12_1/sdk/include -Wall conftest.c -L. -L/usr/lib64 -L. -rdynamic -Wl,-export-dynamic -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lruby -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lpthread -lrt -ldl -lcrypt -lm -lc" conftest.c: In function âtâ: conftest.c:5: error: ârb_classsuperclassâ undeclared (first use in this function) conftest.c:5: error: (Each undeclared identifier is reported only once conftest.c:5: error: for each function it appears in.) checked program was: /* begin / 1: #include 2: 3: /top/ 4: int main() { return 0; } 5: int t() { void ((volatile p)()); p = (void (()()))rb_classsuperclass; return 0; } / end */

"gcc -o conftest -I. -I/usr/lib64/ruby/1.8/x86_64-linux -I. -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fno-strict-aliasing -fPIC -I/opt/oracle/instantclient_12_1/sdk/include -Wall conftest.c -L. -L/usr/lib64 -L. -rdynamic -Wl,-export-dynamic -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lruby -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lpthread -lrt -ldl -lcrypt -lm -lc" conftest.c: In function âtâ: conftest.c:5: warning: implicit declaration of function ârb_class_superclassâ /tmp/ccOBlncL.o: In function t': /usr/lib64/ruby/gems/1.8/gems/ruby-oci8-2.1.7/ext/oci8/conftest.c:5: undefined reference torb_classsuperclass' collect2: ld returned 1 exit status checked program was: /* begin / 1: #include 2: 3: /top/ 4: int main() { return 0; } 5: int t() { rb_classsuperclass(); return 0; } / end */

have_func: checking for rb_thread_blocking_region() in ruby.h... -------------------- no

"gcc -o conftest -I. -I/usr/lib64/ruby/1.8/x86_64-linux -I. -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fno-strict-aliasing -fPIC -I/opt/oracle/instantclient_12_1/sdk/include -Wall conftest.c -L. -L/usr/lib64 -L. -rdynamic -Wl,-export-dynamic -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lruby -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lpthread -lrt -ldl -lcrypt -lm -lc" conftest.c: In function âtâ: conftest.c:5: error: ârb_thread_blockingregionâ undeclared (first use in this function) conftest.c:5: error: (Each undeclared identifier is reported only once conftest.c:5: error: for each function it appears in.) checked program was: /* begin / 1: #include 2: 3: /top/ 4: int main() { return 0; } 5: int t() { void ((volatile p)()); p = (void (()()))rb_thread_blockingregion; return 0; } / end */

"gcc -o conftest -I. -I/usr/lib64/ruby/1.8/x86_64-linux -I. -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fno-strict-aliasing -fPIC -I/opt/oracle/instantclient_12_1/sdk/include -Wall conftest.c -L. -L/usr/lib64 -L. -rdynamic -Wl,-export-dynamic -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lruby -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lpthread -lrt -ldl -lcrypt -lm -lc" conftest.c: In function âtâ: conftest.c:5: warning: implicit declaration of function ârb_thread_blocking_regionâ /tmp/ccPPsMEU.o: In function t': /usr/lib64/ruby/gems/1.8/gems/ruby-oci8-2.1.7/ext/oci8/conftest.c:5: undefined reference torb_thread_blockingregion' collect2: ld returned 1 exit status checked program was: /* begin / 1: #include 2: 3: /top/ 4: int main() { return 0; } 5: int t() { rb_thread_blockingregion(); return 0; } / end */

have_func: checking for rb_thread_call_without_gvl() in ruby/thread.h... -------------------- no

"gcc -o conftest -I. -I/usr/lib64/ruby/1.8/x86_64-linux -I. -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fno-strict-aliasing -fPIC -I/opt/oracle/instantclient_12_1/sdk/include -Wall conftest.c -L. -L/usr/lib64 -L. -rdynamic -Wl,-export-dynamic -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lruby -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lpthread -lrt -ldl -lcrypt -lm -lc" conftest.c:1:25: error: ruby/thread.h: No such file or directory conftest.c: In function âtâ: conftest.c:5: error: ârb_thread_call_withoutgvlâ undeclared (first use in this function) conftest.c:5: error: (Each undeclared identifier is reported only once conftest.c:5: error: for each function it appears in.) checked program was: /* begin / 1: #include <ruby/thread.h> 2: 3: /top/ 4: int main() { return 0; } 5: int t() { void ((volatile p)()); p = (void (()()))rb_thread_call_withoutgvl; return 0; } / end */

"gcc -o conftest -I. -I/usr/lib64/ruby/1.8/x86_64-linux -I. -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fno-strict-aliasing -fPIC -I/opt/oracle/instantclient_12_1/sdk/include -Wall conftest.c -L. -L/usr/lib64 -L. -rdynamic -Wl,-export-dynamic -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lruby -L/opt/oracle/instantclient_12_1 -Wl,-rpath,/opt/oracle/instantclient_12_1 -lclntsh -lpthread -lrt -ldl -lcrypt -lm -lc" conftest.c:1:25: error: ruby/thread.h: No such file or directory conftest.c: In function âtâ: conftest.c:5: warning: implicit declaration of function ârb_thread_call_withoutgvlâ checked program was: /* begin / 1: #include <ruby/thread.h> 2: 3: /top/ 4: int main() { return 0; } 5: int t() { rb_thread_call_withoutgvl(); return 0; } / end */

kubo commented 9 years ago

I guess that your situation is as follows:

Client Server Status
ruby 1.8.7-p352, ruby-oci8 2.1.5, Oracle ?.? on Linux Oracle 11.1 on Linux okay
ruby 1.8.7-p352, ruby-oci8 2.1.5, Oracle ?.? on Linux Oracle 11.2.0.3.0 on Linux okay
ruby 1.8.7-p352, ruby-oci8 2.1.5, Oracle ?.? on Linux Oracle 11.2.0.3.0 on AIX ORA-12537
sqlplus, Oracle 11.2 instant client on AIX Oracle 11.2.0.3.0 on AIX okay
sqlplus, Oracle 12.1 instant client on AIX Oracle 11.2.0.3.0 on AIX okay
ruby 1.8.7-p352, ruby-oci8 2.1.7, Oracle 12.1 instant client on Linux Oracle 11.2.0.3.0 on AIX okay

Is it correct?

I have looked differences between 2.1.5 and 2.1.7 but I could not find the reason that 2.1.7 went okay though 2.1.5 caused ORA-12537. If you want to know the reason, checkout the ruby-oci8 git repository and use git bisect.

ozborn commented 9 years ago
Mostly correct, except for that I have the problem in ruby-oci8 2.1.7 as well so git bisect will not help me unfortunately. To fill in some details where you are asking about the ?.? version of Oracle on the client side you can fill in Oracle 12.1 instant client. However the problem is also present in 11.2 Oracle instant client. Client Server Status
ruby 1.8.7-p352, ruby-oci8 2.1.5, Oracle 11.2 instant client on Linux Oracle 11.2 on Linux okay
ruby 1.8.7-p352, ruby-oci8 2.1.7, Oracle 11.2 instant client on Linux Oracle 11.2 on Linux okay
ruby 1.8.7-p352, ruby-oci8 2.1.5, Oracle 12.1 instant client on Linux Oracle 11.2 on Linux okay
ruby 1.8.7-p352, ruby-oci8 2.1.7, Oracle 12.1 instant client on Linux Oracle 11.2 on Linux okay
ruby 1.8.7-p352, ruby-oci8 2.1.5, Oracle 12.1 instant client on Linux Oracle 11.1 on Windows okay
ruby 1.8.7-p352, ruby-oci8 2.1.7, Oracle 12.1 instant client on Linux Oracle 11.1 on Windows okay
ruby 1.8.7-p352, ruby-oci8 2.1.5, Oracle 12.1 instant client on Linux Oracle 11.2 on AIX ORA-12537
ruby 1.8.7-p352, ruby-oci8 2.1.7, Oracle 12.1 instant client on Linux Oracle 11.2 on AIX ORA-12537

I am thinking there is some peculiarity (flag?, attach_mode?) that AIX needs when it is called?

kubo commented 9 years ago

I am thinking there is some peculiarity (flag?, attach_mode?) that AIX needs when it is called?

No. Oracle client library bridges the difference of bitness and endianness.

Could you run the following combination?

Client Server
sqlplus of Oracle 12.1 instant client on Linux Oracle 11.2 on AIX

If it works, could you install ocidump, run the following commands on Linux, mask username, password and tnsname in the command output, and post it to gist?

# Change /full/path/of/libocidump.so and username/password@tnsname
echo 'SELECT * FROM V$VERSION;' | LD_PRELOAD=/full/path/of/libocidump.so sqlplus username/password@tnsname_to_aix
LD_PRELOAD=/full/path/of/libocidump.so ruby -roci8 -e 'OCI8.new("username/password@tnsname_to_aix").exec("SELECT * FROM V$VERSION") do |row| puts row.join(","); end'
ozborn commented 9 years ago

Thank you for all your help!

I did as you requested and created a gist: https://gist.github.com/ozborn/d239923369f6c72f163b

I forgot to include the command output, so it is here (minor cleanup editing):

[root@cashew-dev current]# ./ocidump.bsh SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 13 10:28:29 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Automatic Storage Management option SQL> Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Automatic Storage Management option Warning: NLS_LANG is not set. fallback to US7ASCII. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production

So this workis! To replicate the TNS:connection closed error when I use just a simple connection string: "user/password@//host.uab.edu:1521/somesid" The dump is here: https://gist.github.com/ozborn/07afd37587daa4b6e177 Output is below:

[root@cashew-dev current]# ./ocidump.bsh

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 13 10:41:17 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Automatic Storage Management option SQL> Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Automatic Storage Management option Warning: NLS_LANG is not set. fallback to US7ASCII. oci8.c:513:in oci8lib.so: ORA-12537: TNS:connection closed (OCIError) from /usr/lib64/ruby/gems/1.8/gems/ruby-oci8-2.1.7/lib/oci8/oci8.rb:148:in initialize' from -e:1:innew' from -e:1

ozborn commented 9 years ago

So just to summarize for anyone reading this thread, the ORA-12537 (error message "TNS: connection closed") resulted when I opened the connection like this:

OCI8.new("user/password@//host.uab.edu:1521/somesid")

This can be worked around by either opening a connection like this:

OCI8.new("user/password@(description=(address=(protocol=tcp)(host=host.uab.edu)(port=1521))(connect_data=(server=dedicated)(sid=somesid)))")

Or I believe it is possible (?) to get the original syntax to work if ruby-oci8 is somehow aware of the "somesid" identifier via tnsname.ora but that is not normally present with the instance client. Setting up TNS_ADMIN environment variable pointing to a directory with tnsnames.ora in still resulted in my connection closed error, however based on the non-AIX databases connecting in this fashion it must be possible to use something like tnsnames.ora with the instant client. If I figure out how to get the short somesid form working I will post here.

kubo commented 9 years ago

Thank you for your responses!

Have you tried sqlplus with easy connect naming as follows?

echo 'SELECT * FROM DUAL;' | /opt/oracle/instantclient_12_1/sqlplus "user/password@//host.uab.edu:1521/somesid"

You posted two ocidump log files but the output of sqlplus used same connect strings. (The lengthes are same at least.)

Could you try the following two connect strings?

ozborn commented 9 years ago

Yes, I tried sqlplus with easy connect naming ( "user/password@//host.uab.edu:1521/somesid) and it gives me: ORA-12154: TNS:could not resolve the connect identifier specified

However if I try easy connect naming using a different (Linux 11.2) target database using the same calling convention ( "user2/password2@//host2.uab.edu:1521/sid2) I get no error.

Perhaps somehow sqlplus knows about sid2, but not somesid? Creating a tnsnames.ora isn't helping me (and I can't find one on the client), the environment I run it in doesn't look suspicious. Defining or not defining ORACLE_HOME, TNS_ADMIN doesn't matter. I have no idea how it knows about sid2 but not somesid.

On the ruby side, using:

with the strings you suggest (and 1 extra) I get these results: https://gist.github.com/ozborn/a602b963f47b51ea0c66

They are summarized here:

Server Oracle Instance | Code Snippet | Error

  1. AIX | ruby -rubygems -e 'require "oci8"; OCI8.new("user/password@(description=(address=(protocol=tcp)(host=host1.hs.uab.edu)(port=1521))(connect_data=(server=dedicated)(sid=somesid)))").exec("SELECT * FROM V$VERSION") do |row| puts row.join(","); end' | SUCCESS
  2. AIX | ruby -rubygems -e 'require "oci8"; OCI8.new("user/password@//host1.hs.uab.edu:1521/somesid").exec("SELECT * FROM V$VERSION") do |row| puts row.join(","); end' | ORA-12537: TNS:connection closed (OCIError)
  3. AIX | ruby -rubygems -e 'require "oci8"; OCI8.new("user/password@//host1.hs.uab.edu:1521/somesid:dedicated").exec("SELECT * FROM V$VERSION") do |row| puts row.join(","); end' | ORA-12537: TNS:connection closed (OCIError)
  4. Linux | ruby -rubygems -e 'require "oci8"; OCI8.new("user2/password2@//host2.hs.uab.edu:1521/sid2").exec("SELECT * FROM V$VERSION") do |row| puts row.join(","); end' | SUCCESS
kubo commented 9 years ago

@ozborn, you have no need to get ocidump log any more. I have thought that you can connect to AIX from Linux with sqlplus but not with ruby-oci8. Then I requested it to check difference of OCI call procedures with sqlplus and ruby-oci8.

Yes, I tried sqlplus with easy connect naming ( "user/password@//host.uab.edu:1521/somesid) and it gives me: ORA-12154: TNS:could not resolve the connect identifier specified

NAMES.DIRECTORY_PATH may be set in sqlnet.ora and it doesn't include ezconnect. If so, easy connect naming is disabled.

with the strings you suggest (and 1 extra) I get these results:

Could you try OCI8.new("user/password@(description=(address=(protocol=tcp)(host=host.uab.edu)(port=1521))(connect_data=(service_name=somesid)))") as I requested in the previous post. It uses service_name=somesid, not sid=somesid.

Summary:

Client Server command Status
Linux AIX sqlplus with easy connect naming ORA-12154
Linux AIX ruby-oci8 with easy connect naming ORA-12537
Linux AIX sqlplus with full connect naming Success
Linux AIX ruby-oci8 with full connect naming Success
Linux Linux ruby-oci8 with easy connect naming Success

I'm not sure that the problem is a ruby-oci8 issue. If it works with sqlplus and not with ruby-oci8, I have will to fix it as I can. But if it doesn't work with sqlplus also, you should ask to the Oracle support.

kubo commented 9 years ago

What sqlplus do you use on Linux? Could you post the output of the following command?

which sqlplus
ldd `which sqlplus`

If it is not a sqlplus of Oracle instant client, install Instant Client Package - SQL*Plus and use it.

ozborn commented 9 years ago

I'm using the 12_1 client sqlplus, it is not actually in my path and I specify it fully when I call it: [root@cashew-dev instantclient_12_1]# ldd ./sqlplus linux-vdso.so.1 => (0x00007fff48dff000) libsqlplus.so => not found libclntsh.so.12.1 => not found libclntshcore.so.12.1 => not found libmql1.so => not found libipc1.so => not found libnnz12.so => not found libdl.so.2 => /lib64/libdl.so.2 (0x0000003426a00000) libm.so.6 => /lib64/libm.so.6 (0x0000003427600000) libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003427200000) libnsl.so.1 => /lib64/libnsl.so.1 (0x0000003435a00000) librt.so.1 => /lib64/librt.so.1 (0x0000003427e00000) libc.so.6 => /lib64/libc.so.6 (0x0000003426e00000) /lib64/ld-linux-x86-64.so.2 (0x0000003426600000)

ozborn commented 9 years ago

Your summary table is correct, I've added some more detail below. At this point I'm also not sure there is any bug with the ruby-oci8 code, although I am still confused by the "Connection closed" error message when it sounds like it is really that it can't find the service name? I also don't understand how/where it is picking up the easy connect string for the linux database service but not the AIX database service...

Regardless I think it would be good to update the API (http://www.rubydoc.info/gems/ruby-oci8/2.1.7/OCI8) to show that it is possible to connect using the format: oci=OCI8.new('user', 'password', '(description=(address=(protocol=tcp)(host=host.hs.uab.edu)(port=1521))(connect_data=(server=dedicated)(sid=somesid)))') Being able to use this full connect naming in the ruby-oci API is huge for me as it allows me to use the new AIX oracle service.

As you suggested I also tried connecting using: oci=OCI8.new('user', 'password', '(description=(address=(protocol=tcp)(host=host.hs.uab.edu)(port=1521))(connect_data=(server=dedicated)(service_name=somesid)))')

This gave some interesting results shown in a table below:

Client Server Command Status
Linux Linux Full Connect Naming (service_name = sid) Success
Linux Linux Full Connect Naming (sid = sid) Success
Linux AIX Full Connect Naming (service_name = sid) ORA-12537
Linux AIX Full Connect Naming (sid = sid) Success

All of the results in the table above use ruby-oci8. Just to be consistent, I ran the same format through sqlplus and got identical results. It seems using "service_name" with full connect naming is acceptable for one service but not another service (which happens to be AIX). I think is probably not an issue with AIX, but that somehow both sqlplus and ruby-oci are aware of the linux server database service but not the AIX one. You mentioned sqlnet.ora, but I can't find that file anywhere in the instant client directory.

At this point I'm happy I have an API I can use, but I am still mystified by the discrepancy in behavior between calling these two different services.

kubo commented 9 years ago

The connect descriptor //host.uab.edu:1521/somesid converts to (description=(address=(protocol=tcp)(host=host.uab.edu)(port=1521))(connect_data=(service_name=somesid))) according to oracle manual. The converted connect identifier uses (service_name=...), not (sid=xxx).

Client Server Connect Naming Status
Linux Linux Easy Success
Linux Linux Full (service_name = sid) Success
Linux Linux Full (sid = sid) Success
Linux AIX Easy ORA-12537
Linux AIX Full (service_name = sid) ORA-12537
Linux AIX Full (sid = sid) Success

I bet that sqlplus with easy connect naming fails with ORA-12537 when ORA-12154 error is solved.

This isn't a ruby-oci8 issue. You should ask to the DBA of the AIX server.

Is there any way for sqlplus or ruby-oci to list the services it is aware of?

No at client side. Service names are managed at server side. Oracle client library picks up hostname and port number from a connect descriptor, connects to the server and sends the connect descriptor itself. TNS listener at the server side accepts the connect descriptor, picks up service name from it and searches the service name from the list of known services. It returns success to the client if the service name is found. It returns ORA-12514: TNS:listener does not currently know of service requested in connect descriptor if the service name is not found. IMO, ORA-12537 is caused by accidental process termination of the TNS listener.