Skip to content

Trouble when connect user/pswd@TNSName #88

Closed
Closed
Trouble when connect user/pswd@TNSName#88
Assignees
pesse

Description

@SebGt
SebGt
opened on 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

Activity

  1. pesse commented on Jul 9, 2018

    @pesse
    pesse
    on Jul 9, 2018
    Member

    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

  2. SebGt commented on Jul 9, 2018

    @SebGt
    SebGt
    on Jul 9, 2018
    Author

    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

  3. pesse commented on Jul 9, 2018

    @pesse
    pesse
    on Jul 9, 2018
    Member

    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?

  4. SebGt commented on Jul 9, 2018

    @SebGt
    SebGt
    on Jul 9, 2018
    Author

    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

  5. SebGt commented on Jul 9, 2018

    @SebGt
    SebGt
    on Jul 9, 2018
    Author

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

  6. pesse commented on Jul 9, 2018

    @pesse
    pesse
    on Jul 9, 2018
    Member

    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.

  7. SebGt commented on Jul 9, 2018

    @SebGt
    SebGt
    on Jul 9, 2018
    Author

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

  8. pesse
    self-assigned this
    on Jul 9, 2018

  9. pesse commented on Jul 9, 2018

    @pesse
    pesse
    on Jul 9, 2018
    Member

    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?

  10. SebGt commented on Jul 10, 2018

    @SebGt
    SebGt
    on Jul 10, 2018
    Author

    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.

  11. pesse commented on Jul 10, 2018

    @pesse
    pesse
    on Jul 10, 2018
    Member

    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

  12. jgebal commented on Jul 10, 2018

    @jgebal
    jgebal
    on Jul 10, 2018
    Member

    @SebGt what version of cli are you using?

  13. SebGt commented on Jul 10, 2018

    @SebGt
    SebGt
    on Jul 10, 2018
    Author

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

  14. SebGt commented on Jul 10, 2018

    @SebGt
    SebGt
    on Jul 10, 2018
    Author

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

  15. SebGt
    closed this as completedon Jul 10, 2018
  16. SebGt
    reopened this on Jul 10, 2018

  17. SebGt commented on Jul 10, 2018

    @SebGt
    SebGt
    on Jul 10, 2018
    Author

    Sorry wrong action ...

  18. pesse commented on Jul 10, 2018

    @pesse
    pesse
    on Jul 10, 2018
    Member

    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

  19. SebGt commented on Jul 10, 2018

    @SebGt
    SebGt
    on Jul 10, 2018
    Author

    Even with Oracle 11g ?

  20. pesse commented on Jul 10, 2018

    @pesse
    pesse
    on Jul 10, 2018
    Member

    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

  21. SebGt commented on Jul 10, 2018

    @SebGt
    SebGt
    on Jul 10, 2018
    Author

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

  22. jgebal commented on Jul 10, 2018

    @jgebal
    jgebal
    on Jul 10, 2018
    Member

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

  23. SebGt commented on Jul 10, 2018

    @SebGt
    SebGt
    on Jul 10, 2018
    Author

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

  24. SebGt commented on Jul 10, 2018

    @SebGt
    SebGt
    on Jul 10, 2018
    Author

    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)

  25. SebGt commented on Jul 10, 2018

    @SebGt
    SebGt
    on Jul 10, 2018
    Author

    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.

  26. SebGt commented on Jul 10, 2018

    @SebGt
    SebGt
    on Jul 10, 2018
    Author

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

  27. jgebal commented on Jul 10, 2018

    @jgebal
    jgebal
    on Jul 10, 2018
    Member

    Good you have figured it out.

  28. jgebal
    closed this as completedon Jul 10, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions