Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Connection with TNS name doesn't work when DEFAULT_DOMAIN is defined in sqlnet.ora #90

Closed
SebGt opened this Issue Jul 11, 2018 · 17 comments

Comments

Projects
None yet
3 participants
@SebGt
Copy link

SebGt commented Jul 11, 2018

Hi,
Following the investigation done for #88 , I still have an issue with connection using TNSNAME.

When TNS alias is defined without DEFAULT_DOMAIN it is working fine.
Command used is like : utplsql run user/pswd@DB_TEST_DEV ...
Config details:

# sqlnet.ora Network Configuration File

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
# tnsnames.ora File

DB_TEST_DEV=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = 10.1.1.1)(Port = 1550))
    )
    (CONNECT_DATA =
      (SID = DB_T_DEV)
    )
  )

But when DEFAULT_DOMAIN is defined, the alias is not found by java API used to connect:
Command used is like : utplsql run user/pswd@DB_TEST_DEV ...

...
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier  "db_test_dev"
...

Config details:

# sqlnet.ora Network Configuration File
SQLNET.AUTHENTICATION_SERVICES= (NTS)
TRACE_LEVEL_CLIENT = OFF
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)
LOG_DIRECTORY_CLIENT = c:
NAMES.DEFAULT_DOMAIN = world
SQLNET.EXPIRE_TIME = 0
NAME.DEFAULT_ZONE = world
# tnsnames.ora File

DB_TEST_DEV.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = 10.1.1.1)(Port = 1550))
    )
    (CONNECT_DATA =
      (SID = DB_T_DEV)
    )
  )

In such config, connect string should be user/pswd@DB_TEST_DEV and when client opens the connection the TNS alias is determined using connect string + DEFAULT_DOMAIN in sqlnet.ora.

It seems that the java API doesn't use sqlnet.ora (googled but I found nothing about that).
So I tried to give the full alias name in connect string but API thought I pass an IP because there is a dot in connect string (it is what I guess)
Example:
utplsql run user/pswd@DB_TEST_DEV.WORLD ...

...
java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "host:port:sid"
...

(see #88 for full stack)

Any chance to have a look because config with DEFAULT_DOMAIN can't be changed ?
Thanks in advance.

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 11, 2018

utPLSQL-cli uses jdbc:oracle:thin-driver. The thin driver does not use the sqlnet.ora file.

See https://stackoverflow.com/questions/20685192/names-default-domain-world-not-being-set-when-looking-up-tnsnames-ora

At the moment I'm not sure about whether we should add support for oci-driver. It looks like much added complexity to me.
Your opinion, @jgebal? Others?

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Jul 11, 2018

Maybe we could check if sqlcl work with thus configuration. If it dies we can ask the sqlcl team how to do this.

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Jul 11, 2018

@pesse, are you able to reproduce that error locally (TNS & jdbc with DEFAULT_DOMAIN)?
Is there validation on api that could cause problems or is api just wrapping exception from the driver?
This looks like api exception:
IO Error: Invalid connection string format, a valid format is: "host:port:sid" ...

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 11, 2018

I'll try to reproduce it tomorrow evening.
The exception is from the driver, which is also connected to a known issue with 11.2.0.4
https://support.oracle.com/knowledge/Middleware/1915177_1.html

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 12, 2018

I can reproduce it. SQLcl seems to overcome the issues, though.

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 12, 2018

@SebGt you have a pretty old version of Java 8 installed:

java version "1.8.0_144"

Can you update it? I can reproduce DEFAULT_DOMAIN not getting populated, but I can't reproduce the IO Error: Invalid connection string format, a valid format is: "host:port:sid" ... error when calling
utplsql run app/app@db_test_dev.world ...

Docs for SQLcl recommend "Java Runtime Environment (JRE) version 8 update 161 or later."
https://docs.oracle.com/en/cloud/paas/exadata-express-cloud/csdbp/connecting-sqlcl.html#GUID-F78874A5-B04A-4129-A019-7752B31313D9

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 12, 2018

Got some info from the phenomenal Jeff Smith: in SQLcl they actually try a THICK connection first and just fall back to thin afterwards.
There's still something strange going on because I shouldn't be able to connect through thick because of having 32bit client installed, but this approach should solve the problems I think.
Will open a new issue so we include a similar fallback-scenario in a future cli-release

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 13, 2018

Thanks a lot for your investigations.
@pesse I will see if I can upgrade my java version (I have a doubt as it is packaged and automatically deployed).

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 20, 2018

Hey @SebGt
can you try if this latest development-version of utPLSQL-cli fixes your problem?
It first tries to connect via thick-driver, then fallback via thin.
https://bintray.com/utplsql/utPLSQL-cli/download_file?file_path=utPLSQL-cli-develop-201807200643.zip

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 20, 2018

Hi,
Thanks @pesse, I'm trying this morning and revert to you with result.

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 20, 2018

It is working with command line and I need to give the full name db_test_dev.world in command line.
Thanks a lot.
Now I have to make it working in Jenkins because the same command executed by Jenkins doesn't work.

jdbc:oracle:oci8:****/****@db_test_dev.world : no ocijdbc12 in java.library.path
jdbc:oracle:thin:****/****@db_test_dev.world : IO Error: could not resolve the connect identifier  "db_test_dev.world "
Could not establish connection to database. Reason: IO Error: could not resolve the connect identifier  "db_test_dev.world "
@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Jul 20, 2018

Do we change requirements and now additional ocijdbc library is needed from Oracle

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 20, 2018

No. We support thick driver, but we don't require it.
Seems like no oracle client is installed on your jenkins, @SebGt.
That case it falls back to thin-driver, which might not work properly if your java-version is outdated (as we discussed). Might also be ORACLE_HOME-setting sessions again.
Can you use full-qualified connect-string on jenkins?

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 20, 2018

I put ojdbc8.jar in lib folder of utPLSQL-CLI folder and in command line it is working fine.
It is only from Jenkins, so I'm checking the diff between the command line context and jenkins context.

Command line is executed from cmd window on server where jenkins is running.
ORACLE_HOME is the same for both (checked).

(sorry ;-) ) What means full-qualified connect-string ?

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 20, 2018

something like localhost:1521/service

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 20, 2018

I found.
My ORACLE_HOME was defined in windows like this:
ORACLE_HOME=Y:\dev\ORACLE (with Y a share on =\dev_server\Programs\apps)

For Jenkins, all network mapping are not available.
If I set ORACLE_HOME=\\dev_server\Programs\apps\dev\ORACLE it is working fine.

Thanks again for your help and all the job you do to provide this test API and all related components .

@SebGt SebGt closed this Jul 20, 2018

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 20, 2018

You're welcome - great it works now for you. And thanks for helping making the tooling even better ;)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.