DPY-4000 Error: unable to find <TNS> in <path>/tnsnames.ora DUPLICATE

[IndexProselyte]

I am having a problem accesing my Oracle DB that's running on the Oracle cloud with this python library.

As allready pointed out by the opened issue with the same error message it is most likely due to a corrupted tnsnames.ora file.
I have tried to regenerate the tns file many times thru the Oracle webpage (and even rewriting it from scratch once) but nothing worked.

I have also put it thru the brantwills tns file checker to see where i may have a syntax error. But that site always just threw a:

Entry ["dbsosit_high"], Line 1 Column 46: no viable alternative at input '(retry_delay'

At first i thought it may be from a badly generated tns file, but after remaking it at least 7 times the same error occurs both on the checker and the python library.
I don't know what i am doing wrong at this point, as i can connect to the database just fine thru sqldeveloper.

CODE:
`import oracledb

connection=oracledb.connect(
user="admin",
password="pass",
dsn='tns from the tnsnames.ora file',
config_dir="Desktop//Oracle cloud//Wallet_DBSOSIT",
wallet_location="Desktop//Oracle cloud",
wallet_password="admin123")`

INFO:
I am running this on a Windows 11 pc
Python version: 3.10
Oracledb library version: latest
Database is hosted on the Oracle cloud Free tier, and it's a ADW
Tutorial i am following: https://docs.oracle.com/en-us/iaas/autonomous-database-shared/doc/connecting-python-mtls.html

[cjbj]

Have you tried an absolute (not relative) path for wallet_location and config_dir? Why the double forward slashes?

[anthony-tuininga]

Can you connect through SQL*Plus? Are you able to share the contents of the tnsnames.ora file? If you don't want to share the details of your environment, simply modify the contents to include host names like "localhost" and aliases like "my_alias" (and remove any additional aliases) and put the tnsnames.ora file in some common path (c:\temp\tnsnames.ora) and set the value of config_dir to c:\temp and confirm that the problem still occurs. This code should also result in the error:

import oracledb

params = oracledb.ConnectParams(config_dir=r'c:\temp')
params.parse_connect_string('my_alias')

[IndexProselyte]

It seems i cannot connect to the DB with SQL*PLUS as it throws this error: ORA-12560: TNS:protocol adapter error
Which (after a quick google search) means that the DB isn't running, even tho it is?
The web gui says it's AVAILABLE and i can still connect to it with sqldeveloper.
(As it said in the installation tutorial i used this connection command: sqlplus "service name from tnsnames.ora" so that could be a point of failure)

And here is the tns file, maybe it will help.

dbsosit_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=g745d01961eb0b7_dbsosit_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

dbsosit_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=g745d01961eb0b7_dbsosit_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

dbsosit_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=g745d01961eb0b7_dbsosit_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

[anthony-tuininga]

I put a copy of the entries you supplied in a tnsnames.ora file in the current directory, then ran this code:

import os

import oracledb

params = oracledb.ConnectParams(config_dir=os.getcwd())
params.parse_connect_string("dbsosit_high")

No errors were raised. Please use this code and let me know what error you get!

[IndexProselyte]

It works fine!

[anthony-tuininga]

Great! Now you have to find out how to reproduce the error by tweaking the configuration until you are able to find it -- or you are able to connect as desired and can no longer reproduce your original error at all. ;-)

[IndexProselyte]

After some tinkering i have solved the issue with this code:

import os
import oracledb

params = oracledb.ConnectParams(config_dir=os.getcwd())
params.parse_connect_string("dbsosit_high")
oracledb.init_oracle_client(lib_dir=r'Oracle cloud/instantclient_21_10')
conn = oracledb.connect(user="username", password="admin123", params=params)

The parsing of the connect string solved the DPX-4000 Error.
To then connect and write to my DB i also needed to switch to Thick mode connection.
If you are having a problem with connecting with Thick mode check if you extracted the wallet into the network/admin folder.

[anthony-tuininga]

What error do you get when using thin mode?

[IndexProselyte]

The first Error is:
oracledb.exceptions.OperationalError: DPY-6000: Listener refused connection. (Similar to ORA-12506)

Which then caused the following error: (thrown when creating a connect object)
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database.
DPY-6000: Listener refused connection. (Similar to ORA-12506)

[anthony-tuininga]

That error (ORA-12506) indicates that you haven't set up ACLs (access control lists) to allow your client to connect without a wallet. You can do that or you would need to specify the wallet location and wallet password when connecting, like this:

import oracledb

conn = oracledb.connect(user="username", password="password", dsn="dbsosit_high", 
                        wallet_location="the_location_of_the_wallet",
                        wallet_password="the_wallet_password")

[IndexProselyte]

I see, i thought that an admin account wouldn't need such a thing.
I am still kinda new to the whole Oracle cloud system.

Thanks for the tip!

[cjbj]

For reference, see the python-oracledb doc on connection to Oracle Cloud Autonomous databases: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#connecting-to-oracle-cloud-autonomous-databases

[siva42641]

Hello Team,

Unable to find dsn in tnsnames.ora file. Please suggest on this? Am i missing any configuration?

tnsnames.ora file:
ORCLPDB=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxxxxx)(PORT=xxxxx)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxxxxxx)))

[anthony-tuininga]

If you have a new issue, please open a new discussion or issue. The error you supplied occurs because you supplied an empty string for the dsn. You need to supply the string "orclpdb" based on the contents of your tnsnames.ora file!