search

dbeaver / dbeaver

Free universal database tool and SQL client
https://dbeaver.io
Apache License 2.0
39.35k stars 3.4k forks source link

Oracle Custom JDBC URL is not changed after TNS alias change on tnsnames.ora #19061

Open Bart-De-Pauw opened 1 year ago

Bart-De-Pauw commented 1 year ago

Description

I'm connecting to an an Oracle database using the TNS Network Alias. This alias is defined in a tnsnames.ora file.

DBALIAS=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbservice)))

This file is present in the Oracle Client subfolder %ORACLE_HOME%/network/admin/tnsnames.ora It is working as expected.

Whenever you create a connection using TNS, it generates a Custom JDBC URL Template like jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbservice)))

The problem is when you change the alias in your tnsnames.ora, I notice that my connection is no longer working, because of the Custom JDBC URL.

Could you refresh the Custom JDBC URL automatically?

DBeaver Version

Community Edition Version 22.3.2.202301060755

Operating System

Windows Server 2019

Database and driver

Database: Oracle 19.16.0 Driver: Oracle Thin Client 12.2.0.1

Steps to reproduce

  1. Edit tnsnames.ora and create an alias (see issue description)

    DBALIAS=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbservice)))

  2. Create a Oracle connection using TNS network alias DBALIAS no problem to establish a connection here, and I see that the Custom JDBC URL is created

  3. For the purpose of this issue, I create a new database service and stop the old service

    begin dbms_service.create_service('newdbservice','newdbservice'); dbms_service.start_service('newdbservice'); dbms_service.stop_service('dbservice'); alter system register; end; /

  4. Change the tnsnames.ora alias and let it point to SERVICE_NAME=newdbservice DBALIAS=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=newdbservice)))

  5. Disconnect and reconnect the Oracle connection. You will get an connection error.

Only when you change the TNS entry, it is regenerating the Custom JDBC URL, but thats not the intention imho.

Can you check and provide a possible solution?

Much appreciated. Best regards, Bart

Additional context

No response

Bart-De-Pauw commented 1 year ago

Relates to https://github.com/dbeaver/dbeaver/issues/3396

LonwoLonwo commented 1 year ago

Hello @Bart-De-Pauw

Ok, I can reproduce this issue.

As a workaround - you can click on the "Test Connection" button in your Connection Settings after the TNS file changing. "Test Connection" reads the file again.

MrJack91 commented 1 year ago

Would be great if the TNS-file is always used. (That's the idea of the TNS). Why is there any cache between at all?

This will make problems if we change TNS that all DBeaver users have to refresh all their connections.

Thanks for any effort.

RamonCar66 commented 1 year ago

Hello, I experience a similar issue when copying an existing (working) TNS connection. The new connection returns the error message

Listener refused the connection with the following error: ORA-12504, TNS:listener was not given the SID in CONNECT_DATA

The intention of the operation is copying an existing connection, then changing the user / password to access a different schema in the new connection.

When comparing the connection properties of the existing and new connections, I see the copied one has lost a part ot the connection string:

ORIGINAL: "url": "jdbc:oracle:thin:@(DESCRIPTION\u003d(ADDRESS_LIST\u003d(FAILOVER\u003don)(LOAD_BALANCE\u003doff)(ADDRESS\u003d(PROTOCOL\u003dTCP)(HOST\u003dfrgnbrac02-scan.XXX.com)(PORT\u003d1521))(ADDRESS\u003d(PROTOCOL\u003dTCP)(HOST\u003dfrgnbora31.XXX.com)(PORT\u003d1521)))(CONNECT_DATA\u003d(FAILOVER_MODE\u003d(TYPE\u003dselect)(METHOD\u003dbasic)(RETRIES\u003d20)(DELAY\u003d15))(SERVER\u003ddedicated)(SERVICE_NAME\u003dNG_DG)))",

COPIED: "url": "jdbc:oracle:thin:@(DESCRIPTION\u003d(ADDRESS_LIST\u003d(FAILOVER\u003don)(LOAD_BALANCE\u003doff)(ADDRESS\u003d(PROTOCOL\u003dTCP)(HOST\u003dfrgnbrac02-scan.XXX.com)(PORT\u003d1521)))",

(a) I've not managed to update that string via the user interface (b) The "Test Connection" button workaround described above does not work for me.

Steps to reproduce: Pre: you need to have a working Oracle connection based on TNS

  1. On the connections panel, right click on the working connection, do Copy
  2. On the connections panel blank area, right click, do Paste
  3. Open the new connection: normally it works
  4. Edit the new connection, then change user and password, Save
  5. Try to open the new connection: You should receive the same error above.

Workaround:

  1. Close dbEaver
  2. Go to youe %appdata%\DBeaverData\workspace6\General.dbeaver folder
  3. edit data-sources.json
  4. copy the url entry from the working connection to the failing connection
  5. restart dbEaver
  6. Try to open the connection, now it works

Thanks very much for dbEaver, it is the best!