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

Trouble when connect user/pswd@TNSName #88

Closed
SebGt opened this Issue Jul 9, 2018 · 24 comments

Comments

Projects
None yet
3 participants
@SebGt
Copy link

SebGt commented Jul 9, 2018

Hi,

When I use utPLSQL-cli (for the first time, before I used utplsql-sql-cli) I receive this error :

java.sql.SQLRecoverableException: IO Error: could not resolve the connect identifier  "db_test_dev"
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
	at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
	at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:117)
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:375)
	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:204)
	at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:445)
	at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:516)
	at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:116)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:97)
	at org.utplsql.cli.ConnectionInfo.getConnection(ConnectionInfo.java:34)
	at org.utplsql.cli.RunCommand.run(RunCommand.java:149)
	at org.utplsql.cli.Cli.main(Cli.java:33)
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier  "db_test_dev"
	at oracle.net.resolver.NameResolver.resolveName(NameResolver.java:181)
	at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:431)
	at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:693)
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:251)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340)
	... 14 more

Command line is :
V:\utPLSQL-cli\bin\utplsql run test_usr/test_pswd@db_test_dev -f=ut_xunit_reporter -o=tpkg_result.xml -p=test_usr.tpkg_one

When I do the same with test_usr/test_pswd@10.1.1.1:1550:db_t_dev it is working fine.

ORACLE_HOME variable is set as suggested in the doc.
Dis you have an idea ?
Thanks

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 9, 2018

Hi @SebGt ,

can you try the following?

V:\utPLSQL-cli\bin\utplsql run test_usr/test_pswd@10.1.1.1:1550/db_t_dev -f=ut_xunit_reporter ...
(/ instead of : to address a service)
Any chance you are using SQLDeveloper? How's your connect-string there?
How do you connect via sqlplus/sqlcl?

Cheers,
Pesse

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 9, 2018

Hi thanks for reply.
The run with connect string using IP:PORT:SID is working fine. It is only the run with TNSName that is not working.

My objective here is to migrate from utPLSQL 3.0.3 to 3.1.1. So install is OK on DB but unfortunately sql client is not working anymore and I must move on java client.

Otherwise connect with sqlplus test_usr/test_pswd@db_test_dev is working fine.

Thanks

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 9, 2018

Sorry for misread.
The error-message suggests that your tnsnames.ora points towards a "vista_dev" which can not be found. Can you tell me a bit more about your setup?
I don't use cli with TNSName on my machine, but will try. Didn't notice any problems so far, so I'm not 100% sure where to look for a problem.
Did you try with SQLDeveloper?

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 9, 2018

Sorry I did a wrong copy and past for the stack from another test I did with another DB.
Correct db is db_test_dev. (I changed my initial post)

From cmd window, I can connect sqlplus test_usr/test_pswd@db_test_dev.

I don't have SQLDeveloper. We are working with PL/SQL Developer.

Otherwise, my objective is to continue to run automatically my test using Jenkins.
Currently we are using utPLSQL 3.0.3 with SQL client and it is working fine.
With utPLSQL 3.1.1 I must move on java client.
It is what I understand but if you another solution to execute my tests from Jenkins ... ;-)

Thanks

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 9, 2018

I have these both env variables set :
ORACLE_HOME=\dev1\Programs\ORACLE
TNS_ADMIN=\dev1\Programs\ORACLE\network\admin

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 9, 2018

Yes, utplsql-cli (java) is the recommended command-line util and replaces the outdated utplsql-sql-cli completely.
I will look into this as soon as I have a time frame. Until then I suggest using the connectstring to run utplsql-cli.

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 9, 2018

Ok thanks for help.
I will wait and postpone my migration (I have around 30 jenkins jobs connected to almost 10 databases)

@pesse pesse self-assigned this Jul 9, 2018

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 9, 2018

Hey @SebGt, I tested it locally and have no issues to connect to my instance using TNSName:

C:\dev\utPLSQL\utPLSQL-cli>utplsql run app/app@ora_ut -c
Award bonus
  Sets new salary as pct commision * sales amount [,07 sec]
  Raises exception if null bonus is passed [,003 sec]

Here is my tnsnames.ora:

ora_ut =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)
    )
  )

and this is my ORACLE_HOME-env:

C:\dev\utPLSQL\utPLSQL-cli>echo %ORACLE_HOME%
C:\app\client\sani\product\12.2.0\client_1

What concerns me a bit is how your environment-variables look.
From your utplsql-call I assume you are on windows, but for windows you should start an absolute path with a device-char instead of just backslash. Maybe that's leading to problems?

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 10, 2018

Hi @pesse,
Thanks for your time.
I changed my ORACLE_HOME :

V:\Tools\UnitTests\Jenkins\utPLSQL-cli\bin>echo %ORACLE_HOME%
Y:\dev\ORACLE

here is my tnsnames.ora :

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)
    )
  )

I still have the issue.

Do you think that the suffix ".WORLD" in tnsnames can cause the issue?

I tried to use DB_TEST_DEV.WORLD in connection string but I have this error:

V:\Tools\UnitTests\Jenkins\utPLSQL-cli\bin>utplsql run test_usr/test_pswd@db_test_dev.world

java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "host:port:sid"
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:117)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:375)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:204)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:445)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:516)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:116)
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:97)
        at org.utplsql.cli.ConnectionInfo.getConnection(ConnectionInfo.java:34)
        at org.utplsql.cli.RunCommand.run(RunCommand.java:116)
        at org.utplsql.cli.Cli.main(Cli.java:29)
Caused by: oracle.net.ns.NetException: Invalid connection string format, a valid format is: host:port:sid"
        at oracle.net.resolver.AddrResolution.resolveSimple(AddrResolution.java:498)
        at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:437)
        at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:693)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:251)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340)
        ... 14 more

Thanks again.

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 10, 2018

The error message suggests that your tnsnames.ora is not taken into consideration.
I just did a test with the same TNSName you used (db_test_dev.world) and had no problems connecting.
Did you restart your console after setting %ORACLE_HOME%? (Windows is stupid in that case)
Also there's a discrepancy between your first reported path to Oracle Home and the second:

ORACLE_HOME=\dev1\Programs\ORACLE
Y:\dev\ORACLE

Looks like we need a new debug-parameter in cli which provides some logging

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Jul 10, 2018

@SebGt what version of cli are you using?

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 10, 2018

@pesse: in first a"" was missing at beginning, it was \dev1\programs\Oracle and we have a share mapped as Y:\ and I changed ORACLE_HOME with Y:\dev\ORACLE and restarted a new window command. I also added it into env. variables (in system var.)

@jgebal: I tested the latest (3.1.0) and the previous one (3.0.4).

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 10, 2018

Just to be sure, to use ORACLE I had only to copy ojdbc6.jar into lib folder of utPLSQL-cli, correct ?

@SebGt SebGt closed this Jul 10, 2018

@SebGt SebGt reopened this Jul 10, 2018

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 10, 2018

Sorry wrong action ...

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 10, 2018

You should be using ojdbc8. That's the version suitable for Java 1.8 and Oracle 12.2
Though I don't think that's the problem. It looks like it can't find your tnsnames.ora

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 10, 2018

Even with Oracle 11g ?

@pesse

This comment has been minimized.

Copy link
Member

pesse commented Jul 10, 2018

Yes, interoperability-matrix at least says so. Though ojdbc6 should work if you're on 11.2.0.3 or 11.2.0.4
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#01_02

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 10, 2018

I will try with a specific tnsnames.ora file to see if something change.

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Jul 10, 2018

ojdbc is probably more bound to java version than to oracle db version.
Would be worth trying with ojdbc8

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 10, 2018

Same with ojdbc8, I will try to change tnsnames.ora location.

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 10, 2018

Same with a dedicated tnsnames.ora.

Just in case, this is my java version:

V:\Tools\UnitTests\Jenkins\utPLSQL-cli\bin>java -version
java version "1.8.0_144"
Java(TM) SE Runtime Environment (build 1.8.0_144-b01)
Java HotSpot(TM) 64-Bit Server VM (build 25.144-b01, mixed mode)
@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 10, 2018

I found the issue in my config.
I fact, I have an Oracle client installed locally on my server in C:\Oracle\product\11.2.0\client_64
And our tnsnames.ora is centralized on a server (to ease the publishing of changes) and accessible using Y:\dev\ORACLE\network\admin (tns_admin variable is set).

If ORACLE_HOME is set to Y:\dev\ORACLE it is not working
If ORACLE_HOME is set to C:\Oracle\product\11.2.0\client_64 it is not working
If I copy tnsnames.ora in C:\Oracle\product\11.2.0\client_64\Network\Admin and ORACLE_HOME is set to C:\Oracle\product\11.2.0\client_64 it is working.

Thanks for your help.

@SebGt

This comment has been minimized.

Copy link
Author

SebGt commented Jul 10, 2018

I forgot this last important point, in our tnsnames.ora we have 2 aliases defined for the same tns entry.

As per following Oracle doc, tns entry must be split in two entries to have 1 alias:
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=289351873470294&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=1253486.1&_afrWindowMode=0&_adf.ctrl-state=lzq8b0ri7_4

And alias should not have .WORLD extension.
DB_TEST_DEV.WORLD is wrong. DB_TEST_DEV is correct.

Then my tns entry was:

DB_TEST_DEV.WORLD,  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)
    )
  )

and now

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)
    )
  )

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

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Jul 10, 2018

Good you have figured it out.

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.