Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Python-OracleDB connectivity issues from python/jupyter notebook on Win10 WSL2/Ubuntu.

User_4NH2KJun 7 2022

Hi - hope this is the correct discussion group for this topic.
Isssue:
I am not able to connect to a RAC Oracle database. I am connecting as myself being a proxy into another schema. Connection is all fine with usual SQLPlus or SQL Developer on windows or within the WSL2/Ubuntu linux. BUT when I do use Python-OracleDB module from within python3 or a jupyter-notebook on a WSL2/Ubuntu linux within Windows 10 client, I encountered some errors that I can fully shake off.
First I have been using thin mode connection. I have TNS_ADMIN and LD_LIBRARY_PATH set to some directory where the tnsnames.ora and where the Oracle Instant Client Lib directory are. Note I can only use what is avail in this company ie some Oracle Instant Client v12.2 which has this structure oracle/12.2/client64/lib (for the library) and oracle/12.2/client64/bin (with some of the binaries like sqlplus etc. This work fine when I point my sqlplus to them and connect ok.
Note as mentioned i am a proxying as another schema, so I use this connection string:
my_username[schema_user_I_am_connecting_as]/my_password@db_alias (from tnsnames.ora)
This works fine in sqlplus but not in python as:
import oracle_db
import db_config
conn = oracledb.connect(user=db_config.user, password, dsn=db_config.db_alias)
(I have verified the variables by print to check they ok - all fine as it is)
In this instance I get this error exceptions.OperationalError: DPY-6001: cannot connect to database. Service " ....." not registered with listener t host "....scan....:: port 99999 (Similar to ORA12514)
so my tnsnames.ora looks fine as I can connect also with these same db_alias, my username and my password. Note as mention i am proxying into another user schema to do some work of the schema's behalf. like this ( my_username[schema_user_I_am_connecting_as]/my_password@db_alias )
so the tnsnames.ora has an address_list with 2 separate host scan addresses - one of them is a standby in another site - so only one is up . Could that pose a problem in python connection?
Because i also sometimes see the warning message ORA-12514 for that standby address on my SQL Developer connection in the navigation connection tree when I hover my cursor over that connection. But it is fine in SQLDeveloper when I connect as it knows to pick the other address when using the db_alias in the tnsnames.
So I have tried some other combinations in python with not much success :
When i do :
conn = oracledb.connect("my_username[schema_user_I_am_connecting_as]/my_password@db_alias")
Get same error as above DPY-6001:
When I do:
oracledb.connect("my_username[schema_user_I_am_connecting_as]/my_password@//host-scan-name:port/servicename")
I get ORA-01017: invalid username/password: logon denied.
hum - close . I am still using the same user and password that work fine in sqlplus. now i have use an easyconnect method with the full address pass in the connection string. That works fine from sqlplus or sqlcli but not here. Has to do with python! I even put a raw string like r"connection str") - no still same login denied
I have also tried without a proxy user login and still gettin logon denied.
What am I doing wrong? or is the way using proxy to connect is nor correct or has a bug? password also obviously has non alpha char like # etc and the [ ] for proxy connection not causing some python grief here. or may be I just not reading the correct doc or missing some important stuff. It is definitely using Thin mode as i can see the error trace showing :
File .... "src/oracldb/impl/thin/connection.pyx or protocol .....
I have also tried without the LD_LIBRARY_PATH as I was using only thin mode.
Thanks in advance,

Comments
Post Details
Added on Jun 7 2022
7 comments
1,224 views