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.

Sign up for GitHub

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

Jump to bottom

ORA-12162 when connecting to database since upgrade to 1.3.0 #178

Closed
glM26 opened this issue Apr 27, 2023 · 10 comments
Closed

ORA-12162 when connecting to database since upgrade to 1.3.0 #178

glM26 opened this issue Apr 27, 2023 · 10 comments
Labels
bug Something isn't working patch available

Comments

@glM26
Copy link

glM26 commented Apr 27, 2023

  1. What versions are you using?

python-oracledb 1.3.1
Oracle DB 19c
RHEL 7.9
Python 3.8

  1. Is it an error or a hang or a crash?
    error
  2. What error(s) or behavior you are seeing?
    We are using python-oracledb in thick-mode to connect to an Oracle database specified in a tnsnames.ora file (i.e. TNS_ADMIN is set to point to the tnsnames.ora file).
    We are also using a sqlnet.ora file that contains a reference to a wallet that contains the DB user and password.

We connect to the database in the following way:

>>> import oracledb;oracledb.init_oracle_client();oracledb.connect(dsn="TEST.WORLD")
<oracledb.Connection to externally identified user>

This is working in python-oracledb up to version 1.2.2.
Since version 1.3.0 (as well as 1.3.1) the same code now results in an error:

>>> import oracledb;oracledb.init_oracle_client();oracledb.connect(dsn="TEST.WORLD")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/PYTHON_LIBS_38/lib64/python3.8/site-packages/oracledb/connection.py", line 1008, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File "/PYTHON_LIBS_38/lib64/python3.8/site-packages/oracledb/connection.py", line 133, in __init__
    impl.connect(params_impl, pool_impl)
  File "src/oracledb/impl/thick/connection.pyx", line 387, in oracledb.thick_impl.ThickConnImpl.connect
  File "src/oracledb/impl/thick/utils.pyx", line 413, in oracledb.thick_impl._raise_from_odpi
  File "src/oracledb/impl/thick/utils.pyx", line 403, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: ORA-12162: TNS:net service name is incorrectly specified

The tnsnames.ora file:

TEST.WORLD =
  (DESCRIPTION =
    (CONNECT_TIMEOUT=10)(RETRY_COUNT=3)
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=TCP)(HOST= primary_url)(PORT=1721))
      (ADDRESS=(PROTOCOL=TCP)(HOST= secondary_url)(PORT=1721))
    )
    (CONNECT_DATA=(SERVICE_NAME=TEST_PRIM.WORLD)(SERVER=DEDICATED))
 )

I can still connect to the database in 1.3.1 with all parameters in the connect-statement:

>>> import oracledb;oracledb.init_oracle_client();oracledb.connect(host="primary_url", port=1721, service_name="TEST.WORLD", user="TEST", password="")

<oracledb.Connection to TEST@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary_url)(PORT=1721))(CONNECT_DATA=(SERVICE_NAME=TEST.WORLD)))>

Have there been any changes in release 1.3.0 that I overlooked or is there a bug in release 1.3.0?
@glM26 glM26 added the bug Something isn't working label Apr 27, 2023
@anthony-tuininga
Copy link
Member

I'm not immediately aware of any changes that might be relevant. Looking at your sample tnsnames.ora I see the service_name is TEST_PRIM.WORLD, not TEST.WORLD as in your connection with all of the parameters. Is that just an oversight? I'll look into this and get back to you.

@glM26
Copy link
Author

glM26 commented May 3, 2023

I'm not immediately aware of any changes that might be relevant. Looking at your sample tnsnames.ora I see the service_name is TEST_PRIM.WORLD, not TEST.WORLD as in your connection with all of the parameters. Is that just an oversight? I'll look into this and get back to you.

We have been using the TEST_PRIM.WORLD in tnsnames.ora for quite some time now, while still connecting to TEST.WORLD.
I do not know why we use this, as we got the tnsnames.ora in this form from our DBA-team a couple of years ago.

But I just tried to change TEST_PRIM.WORLD to TEST.WORLD and it does not change the behavior, i.e. ORA-12162.

@cjbj
Copy link
Member

cjbj commented May 8, 2023 •
edited

@glM26 would you be able to create a sqlnet.ora file (on the machine where your tnsnames.ora file is) and add some tracing like:

trace_level_client=USER
adr_base=/tmp/mylogs
diag_adr_enabled=ON

Then execute mkdir /tmp/mylogs and run your app. Somewhere down the log dir will be a sqlnet.ora file: /tmp/mylogs/oradiag_*/diag/clients/user_*/host_*trace/sqlnet.log

Can you check that the connect descriptor is what you are expecting? There might be a few extra, expected attributes including CID and CONNECTION_ID.

@glM26
Copy link
Author

glM26 commented May 9, 2023

@cjbj
I added the trace-options to sqlnet.ora, it now looks like this:

WALLET_LOCATION =
    (SOURCE =
        (METHOD = FILE)
        (METHOD_DATA =
            (DIRECTORY = /opt/shared/shsdb/operation/etc/oracle_wallet)
        )
    )
SQLNET.WALLET_OVERRIDE = TRUE
TRACE_LEVEL_CLIENT=USER
ADR_BASE=/tmp/mylogs
DIAG_ADR_ENABLED=ON

With this I get the following trace-log (only first 64 lines) with oracledb 1.2.2:

Trace file /tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/trace/ora_529677_140072190089088.trc
2023-05-09 07:26:05.034 : nlstddt_do_alter_trace:Version 19.3.0.0.0
2023-05-09 07:26:05.042 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION FOLLOWS ---
2023-05-09 07:26:05.042 : nlstddt_do_alter_trace:New trace stream is /tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/trace/ora_529677_140072190089088.trc
2023-05-09 07:26:05.042 : nlstddt_do_alter_trace:New trace level is 4
2023-05-09 07:26:05.042 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION ENDS ---
2023-05-09 07:26:05.042 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION FOLLOWS ---
2023-05-09 07:26:05.042 : nlstdts_trace_source:Attempted load of system pfile source /opt/sas/share/ora/sqlnet.ora
2023-05-09 07:26:05.042 : nlstdts_trace_source:Parameter source loaded successfully
2023-05-09 07:26:05.042 : nlstdts_trace_source:
2023-05-09 07:26:05.042 : nlstdts_trace_source:Attempted load of local pfile source /home/in.bundesbank.de/te504ho2/.sqlnet.ora
2023-05-09 07:26:05.042 : nlstdts_trace_source:Parameter source was not loaded
2023-05-09 07:26:05.042 : nlstdts_trace_source:
2023-05-09 07:26:05.042 : nlstdtp_trace_pfile: -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
2023-05-09 07:26:05.042 : nlstdtp_trace_pfile:Successful parameter table load
2023-05-09 07:26:05.042 : nlstddp_dump_ptable: -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
2023-05-09 07:26:05.042 : nlstddp_dump_ptable:  DIAG_ADR_ENABLED = ON
2023-05-09 07:26:05.042 : nlstddp_dump_ptable:  TRACE_LEVEL_CLIENT = USER
2023-05-09 07:26:05.042 : nlstddp_dump_ptable:  WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /opt/shared/shsdb/operation/etc/oracle_wallet)))
2023-05-09 07:26:05.042 : nlstddp_dump_ptable:  ADR_BASE = /tmp/mylogs
2023-05-09 07:26:05.042 : nlstddp_dump_ptable:  SQLNET.WALLET_OVERRIDE = TRUE
2023-05-09 07:26:05.042 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION ENDS ---
2023-05-09 07:26:05.042 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION FOLLOWS ---
2023-05-09 07:26:05.042 : nlstddl_do_alter_log:Log stream will be "/tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/alert/log.xml"
2023-05-09 07:26:05.042 : nlstddl_do_alter_log:Log stream validation not requested
2023-05-09 07:26:05.042 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION ENDS ---

2023-05-09 07:26:05.043 : nrigbni:Unable to get data from navigation file tnsnav.ora
2023-05-09 07:26:05.069 : nigconcbs:Can't Get Oracle SID Name
2023-05-09 07:26:13.370 : nnftmlf_make_local_addrfile:construction of local names file failed
2023-05-09 07:26:13.371 : nnftmlf_make_system_addrfile:system names file is /opt/sas/share/ora/tnsnames.ora
2023-05-09 07:26:13.373 : snldlldl:Couldn't load shared library nque
2023-05-09 07:26:13.373 : snldlldl:Err: /opt/oraclient/product/19.0.0/client3_1/lib/libnque19.so: undefined symbol: ssMemFree
2023-05-09 07:26:13.373 : niotns:Not trying to enable dead connection detection.
2023-05-09 07:26:13.373 : niotns:Calling address: (DESCRIPTION=(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name1)(PORT=1721))(ADDRESS=(PROTOCOL=TCP)(HOST=host_name2)(PORT=1721)))(CONNECT_DATA=(SERVICE_NAME=TEST_PRIM.WORLD)(SERVER=DEDICATED)(CID=(PROGRAM=python3)(HOST=origin_host)(USER=origin_os_user))))
2023-05-09 07:26:13.377 : nscall:connecting...
2023-05-09 07:26:13.380 : nsc2addr:(DESCRIPTION=(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(ADDRESS=(PROTOCOL=TCP)(HOST=10.204.8.23)(PORT=1721)(HOSTNAME=host_name1))(CONNECT_DATA=(SERVICE_NAME=TEST_PRIM.WORLD)(SERVER=DEDICATED)(CID=(PROGRAM=python3)(HOST=origin_host)(USER=origin_os_user))))
2023-05-09 07:26:13.380 : nttbnd2addr:using host IP address: 10.204.8.23
2023-05-09 07:26:13.380 : nstoSetupTimeout:ATO enabled for ctx=0x0x12dc4e0, val=10000(millisecs)
2023-05-09 07:26:13.380 : nstoUpdateActive:Active timeout is 0 (see nstotyp)
2023-05-09 07:26:13.381 : nsopen:opening transport...
2023-05-09 07:26:13.381 : nttcni:Tcp conn timeout = 60000 (ms)
2023-05-09 07:26:13.381 : nttcni:trying to connect to socket 9.
2023-05-09 07:26:13.381 : nstoControlTTO:Setup TTO: ATO=10000 TTO=60000
2023-05-09 07:26:13.381 : nsnainit:inf->nsinfflg[0]: 0x41 inf->nsinfflg[1]: 0x41
2023-05-09 07:26:13.381 : nsopen:global context check-in (to slot 0) complete
2023-05-09 07:26:13.381 : nscall:deferring connect attempt; at stage 104
2023-05-09 07:26:13.383 : nttctl:set TCP_NODELAY on 9
2023-05-09 07:26:13.383 : nscon:doing connect handshake...
2023-05-09 07:26:13.383 : nscon:connect id = 0xa4e4
2023-05-09 07:26:13.383 : nscon:sending NSPTCN packet
2023-05-09 07:26:13.383 : nscon:sending 286 bytes connect data
2023-05-09 07:26:13.383 : nsdo:286 bytes to NS buffer
2023-05-09 07:26:13.383 : nsevdcall:exit
2023-05-09 07:26:13.425 : nscon:got NSPTRS packet
2023-05-09 07:26:13.425 : nsevdcall:exit
2023-05-09 07:26:13.425 : nttctl:set TCP_NODELAY on 9
2023-05-09 07:26:13.425 : nscon:connect id = 0xa4e4
2023-05-09 07:26:13.425 : nscon:sending NSPTCN packet
2023-05-09 07:26:13.425 : nscon:sending 286 bytes connect data
2023-05-09 07:26:13.425 : nsdo:286 bytes to NS buffer
2023-05-09 07:26:13.425 : nsevdcall:exit
2023-05-09 07:26:13.426 : nscon:got NSPTAC packet
2023-05-09 07:26:13.426 : nscon:connect handshake is complete

When running with oracledb 1.3.0 it looks similar in the beginning, but then it just stops at nigconcbs:Can't Get Oracle SID Name:

Trace file /tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/trace/ora_550243_140676284045184.trc
2023-05-09 07:45:46.259 : nlstddt_do_alter_trace:Version 19.3.0.0.0
2023-05-09 07:45:46.271 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION FOLLOWS ---
2023-05-09 07:45:46.271 : nlstddt_do_alter_trace:New trace stream is /tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/trace/ora_550243_140676284045184.trc
2023-05-09 07:45:46.271 : nlstddt_do_alter_trace:New trace level is 4
2023-05-09 07:45:46.271 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION ENDS ---
2023-05-09 07:45:46.271 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION FOLLOWS ---
2023-05-09 07:45:46.271 : nlstdts_trace_source:Attempted load of system pfile source /opt/sas/share/ora/sqlnet.ora
2023-05-09 07:45:46.271 : nlstdts_trace_source:Parameter source loaded successfully
2023-05-09 07:45:46.271 : nlstdts_trace_source:
2023-05-09 07:45:46.271 : nlstdts_trace_source:Attempted load of local pfile source /home/in.bundesbank.de/te504ho2/.sqlnet.ora
2023-05-09 07:45:46.271 : nlstdts_trace_source:Parameter source was not loaded
2023-05-09 07:45:46.271 : nlstdts_trace_source:
2023-05-09 07:45:46.271 : nlstdtp_trace_pfile: -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
2023-05-09 07:45:46.271 : nlstdtp_trace_pfile:Successful parameter table load
2023-05-09 07:45:46.271 : nlstddp_dump_ptable: -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
2023-05-09 07:45:46.271 : nlstddp_dump_ptable:  DIAG_ADR_ENABLED = ON
2023-05-09 07:45:46.271 : nlstddp_dump_ptable:  TRACE_LEVEL_CLIENT = USER
2023-05-09 07:45:46.271 : nlstddp_dump_ptable:  WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /opt/shared/shsdb/operation/etc/oracle_wallet)))
2023-05-09 07:45:46.271 : nlstddp_dump_ptable:  ADR_BASE = /tmp/mylogs
2023-05-09 07:45:46.271 : nlstddp_dump_ptable:  SQLNET.WALLET_OVERRIDE = TRUE
2023-05-09 07:45:46.271 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION ENDS ---
2023-05-09 07:45:46.271 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION FOLLOWS ---
2023-05-09 07:45:46.271 : nlstddl_do_alter_log:Log stream will be "/tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/alert/log.xml"
2023-05-09 07:45:46.271 : nlstddl_do_alter_log:Log stream validation not requested
2023-05-09 07:45:46.272 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION ENDS ---

2023-05-09 07:45:46.284 : nrigbni:Unable to get data from navigation file tnsnav.ora
2023-05-09 07:45:46.672 : nigconcbs:Can't Get Oracle SID Name

There are no further lines after that, so no connect descriptor is shown.

@anthony-tuininga
Copy link
Member

@glM26, the level of trace provided is insufficient to figure out the issue. Can you change the line in sqlnet.ora to say trace_level_client=support and rerun? Thanks!

@glM26
Copy link
Author

glM26 commented May 11, 2023

@anthony-tuininga I changed trace_level_client to support and reran the program:
oracledb 1.2.2 (first 216 lines):

Trace file /tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/trace/ora_3969654_140075820574592.trc
2023-05-11 07:43:53.547 : nlstddt_do_alter_trace:Version 19.3.0.0.0
2023-05-11 07:43:53.554 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION FOLLOWS ---
2023-05-11 07:43:53.554 : nlstddt_do_alter_trace:New trace stream is /tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/trace/ora_3969654_140075820574592.trc
2023-05-11 07:43:53.554 : nlstddt_do_alter_trace:New trace level is 16
2023-05-11 07:43:53.554 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION ENDS ---
2023-05-11 07:43:53.554 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION FOLLOWS ---
2023-05-11 07:43:53.554 : nlstdts_trace_source:Attempted load of system pfile source /opt/sas/share/ora/sqlnet.ora
2023-05-11 07:43:53.554 : nlstdts_trace_source:Parameter source loaded successfully
2023-05-11 07:43:53.554 : nlstdts_trace_source:
2023-05-11 07:43:53.554 : nlstdts_trace_source:Attempted load of local pfile source /home/te504ho2/.sqlnet.ora
2023-05-11 07:43:53.554 : nlstdts_trace_source:Parameter source was not loaded
2023-05-11 07:43:53.554 : nlstdts_trace_source:
2023-05-11 07:43:53.554 : nlstdtp_trace_pfile: -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
2023-05-11 07:43:53.554 : nlstdtp_trace_pfile:Successful parameter table load
2023-05-11 07:43:53.554 : nlstddp_dump_ptable: -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
2023-05-11 07:43:53.554 : nlstddp_dump_ptable:  DIAG_ADR_ENABLED = ON
2023-05-11 07:43:53.554 : nlstddp_dump_ptable:  TRACE_LEVEL_CLIENT = SUPPORT
2023-05-11 07:43:53.554 : nlstddp_dump_ptable:  WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /opt/shared/shsdb/operation/etc/oracle_wallet)))
2023-05-11 07:43:53.554 : nlstddp_dump_ptable:  ADR_BASE = /tmp/mylogs
2023-05-11 07:43:53.554 : nlstddp_dump_ptable:  SQLNET.WALLET_OVERRIDE = TRUE
2023-05-11 07:43:53.554 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION ENDS ---
2023-05-11 07:43:53.554 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION FOLLOWS ---
2023-05-11 07:43:53.554 : nlstddl_do_alter_log:Log stream will be "/tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/alert/log.xml"
2023-05-11 07:43:53.554 : nlstddl_do_alter_log:Log stream validation not requested
2023-05-11 07:43:53.554 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION ENDS ---

2023-05-11 07:43:53.554 : nlstdipi:entry
2023-05-11 07:43:53.555 : nlstdipi:exit
2023-05-11 07:43:53.555 : nigini:entry
2023-05-11 07:43:53.555 : nigini:Count in the NL global area is now 1
2023-05-11 07:43:53.555 : nigini:Count in NI gbl area now: 1
2023-05-11 07:43:53.555 : nrigbi:entry
2023-05-11 07:43:53.555 : nrigbni:entry
2023-05-11 07:43:53.555 : nrigbni:Unable to get data from navigation file tnsnav.ora
2023-05-11 07:43:53.555 : nrigbni:exit
2023-05-11 07:43:53.555 : nrigbi:exit
2023-05-11 07:43:53.555 : nigini:exit
2023-05-11 07:43:53.576 : nigini:entry
2023-05-11 07:43:53.576 : nigini:Count in the NL global area is now 3
2023-05-11 07:43:53.576 : nigini:Count in NI gbl area now: 3
2023-05-11 07:43:53.576 : nigini:exit
2023-05-11 07:43:53.576 : niqname:Using nnfsn2a() to build connect descriptor for (possibly remote) database.
2023-05-11 07:43:53.576 : nnfgiinit:entry
2023-05-11 07:43:53.576 : nncpcin_maybe_init:default name server domain is [root]
2023-05-11 07:43:53.576 : nnfgiinit:Installing read path
2023-05-11 07:43:53.576 : nnfgsrsp:entry
2023-05-11 07:43:53.576 : nnfgsrsp:Obtaining path parameter from names.directory_path or native_names.directory_path
2023-05-11 07:43:53.576 : nnfgsrsp:Parmeter names.directory_path not found, setting path to compiled in default
2023-05-11 07:43:53.576 : nnfgspd:entry
2023-05-11 07:43:53.576 : nnfgspd:    setting element TNSNAMES
2023-05-11 07:43:53.576 : nnfgspd:    setting element HOSTNAME
2023-05-11 07:43:53.576 : nnfgspd:    setting element EZCONNECT
2023-05-11 07:43:53.577 : nnfgspd:    setting element LDAP
2023-05-11 07:43:53.577 : nnfgspd:Path set
2023-05-11 07:43:53.577 : nnfgspd:exit
2023-05-11 07:43:53.577 : nnfun2a:entry
2023-05-11 07:43:53.577 : nlolgobj:entry
2023-05-11 07:43:53.577 : nnfgrne:entry
2023-05-11 07:43:53.577 : nnfgrne:Going though read path adapters
2023-05-11 07:43:53.577 : nnfgrne:Switching to TNSNAMES adapter
2023-05-11 07:43:53.577 : nnftboot:entry
2023-05-11 07:43:53.577 : nlpaxini:entry
2023-05-11 07:43:53.577 : nlpaxini:exit
2023-05-11 07:43:53.577 : nnftmlf_make_local_addrfile:entry
2023-05-11 07:43:53.577 : nnftmlf_make_local_addrfile:construction of local names file failed
2023-05-11 07:43:53.577 : nnftmlf_make_local_addrfile:exit
2023-05-11 07:43:53.577 : nlpaxini:entry
2023-05-11 07:43:53.577 : nlpaxini:exit
2023-05-11 07:43:53.577 : nnftmlf_make_system_addrfile:entry
2023-05-11 07:43:53.577 : nnftmlf_make_system_addrfile:system names file is /opt/sas/share/ora/tnsnames.ora
2023-05-11 07:43:53.577 : nnftmlf_make_system_addrfile:exit
2023-05-11 07:43:53.577 : nnftboot:exit
2023-05-11 07:43:53.577 : nnftrne:entry
2023-05-11 07:43:53.577 : nnftrne:Original name: TEST.WORLD
2023-05-11 07:43:53.577 : nnfttran:entry
2023-05-11 07:43:53.577 : nnfttran:exit
2023-05-11 07:43:53.577 : nnftrne:Using tnsnames.ora address (DESCRIPTION = (CONNECT_TIMEOUT=10)(RETRY_COUNT=3) (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST= host_name1)(PORT=1721)) (ADDRESS=(PROTOCOL=TCP)(HOST= host_name2)(PORT=1721))) (CONNECT_DATA=(SERVICE_NAME=TEST_PRIM.WORLD)(SERVER=DEDICATED))) for name TEST.WORLD
2023-05-11 07:43:53.578 : nnftrne:exit
2023-05-11 07:43:53.578 : nnfgrne:exit
2023-05-11 07:43:53.578 : nlolgserv:entry
2023-05-11 07:43:53.578 : nnfggav:entry
2023-05-11 07:43:53.578 : nnftgav:entry
2023-05-11 07:43:53.578 : nnftgav:exit
2023-05-11 07:43:53.578 : nnfgfrm:entry
2023-05-11 07:43:53.578 : nnftfrm:entry
2023-05-11 07:43:53.578 : nnftfrm:exit
2023-05-11 07:43:53.578 : nnfgfrm:exit
2023-05-11 07:43:53.578 : nlolgserv:exit
2023-05-11 07:43:53.578 : nlolgobj:exit
2023-05-11 07:43:53.578 : nlolfmem:entry
2023-05-11 07:43:53.578 : nlolfmem:exit
2023-05-11 07:43:53.579 : nigtrm:Count in the NI global area is now 2
2023-05-11 07:43:53.579 : nigtrm:Count in the NL global area is now 2
2023-05-11 07:43:53.579 : nigini:entry
2023-05-11 07:43:53.579 : nigini:Count in the NL global area is now 3
2023-05-11 07:43:53.579 : nigini:Count in NI gbl area now: 3
2023-05-11 07:43:53.579 : nigini:exit
2023-05-11 07:43:53.579 : niqname:Using nnfsn2a() to build connect descriptor for (possibly remote) database.
2023-05-11 07:43:53.579 : nnfun2a:entry
2023-05-11 07:43:53.579 : nlolgobj:entry
2023-05-11 07:43:53.579 : nnfgrne:entry
2023-05-11 07:43:53.579 : nnfgrne:Going though read path adapters
2023-05-11 07:43:53.579 : nnfgrne:Switching to TNSNAMES adapter
2023-05-11 07:43:53.579 : nnftrne:entry
2023-05-11 07:43:53.579 : nnftrne:Original name: TEST.WORLD
2023-05-11 07:43:53.579 : nnfttran:entry
2023-05-11 07:43:53.579 : nnfttran:exit
2023-05-11 07:43:53.579 : nnftrne:Using tnsnames.ora address (DESCRIPTION = (CONNECT_TIMEOUT=10)(RETRY_COUNT=3) (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST= host_name1)(PORT=1721)) (ADDRESS=(PROTOCOL=TCP)(HOST= host_name2)(PORT=1721))) (CONNECT_DATA=(SERVICE_NAME=TEST_PRIM.WORLD)(SERVER=DEDICATED))) for name TEST.WORLD
2023-05-11 07:43:53.579 : nnftrne:exit
2023-05-11 07:43:53.579 : nnfgrne:exit
2023-05-11 07:43:53.579 : nlolgserv:entry
2023-05-11 07:43:53.579 : nnfggav:entry
2023-05-11 07:43:53.579 : nnftgav:entry
2023-05-11 07:43:53.579 : nnftgav:exit
2023-05-11 07:43:53.579 : nnfgfrm:entry
2023-05-11 07:43:53.579 : nnftfrm:entry
2023-05-11 07:43:53.579 : nnftfrm:exit
2023-05-11 07:43:53.579 : nnfgfrm:exit
2023-05-11 07:43:53.579 : nlolgserv:exit
2023-05-11 07:43:53.579 : nlolgobj:exit
2023-05-11 07:43:53.579 : nlolfmem:entry
2023-05-11 07:43:53.579 : nlolfmem:exit
2023-05-11 07:43:53.579 : nigtrm:Count in the NI global area is now 2
2023-05-11 07:43:53.579 : nigtrm:Count in the NL global area is now 2
2023-05-11 07:43:53.579 : nigini:entry
2023-05-11 07:43:53.579 : nigini:Count in the NL global area is now 3
2023-05-11 07:43:53.579 : nigini:Count in NI gbl area now: 3
2023-05-11 07:43:53.579 : nigini:exit
2023-05-11 07:43:53.579 : niqname:Hst is already an NVstring.
2023-05-11 07:43:53.579 : niqname:Inserting CID.
2023-05-11 07:43:53.579 : nigtrm:Count in the NI global area is now 2
2023-05-11 07:43:53.579 : nigtrm:Count in the NL global area is now 2
2023-05-11 07:43:53.579 : nigini:entry
2023-05-11 07:43:53.579 : nigini:Count in the NL global area is now 3
2023-05-11 07:43:53.579 : nigini:Count in NI gbl area now: 3
2023-05-11 07:43:53.579 : nigini:exit
2023-05-11 07:43:53.579 : niqname:Hst is already an NVstring.
2023-05-11 07:43:53.579 : niqname:Inserting CID.
2023-05-11 07:43:53.580 : niotns:entry
2023-05-11 07:43:53.580 : niotns:niotns: setting up interrupt handler...
2023-05-11 07:43:53.580 : nigsui:entry
2023-05-11 07:43:53.580 : nigsui:Set User Interrupt: hdl=0, prc=0xe37ee860, ctx=0xcb73c0.
2023-05-11 07:43:53.580 : nigsui:exit (0)
2023-05-11 07:43:53.580 : snsgblini:Max no of descriptors supported is 65535
2023-05-11 07:43:53.580 : snsgblini:exit
2023-05-11 07:43:53.580 : snldlldl:Couldn't load shared library nque
2023-05-11 07:43:53.580 : snldlldl:Err: /opt/bbk/oraclient/product/19.0.0/client3_1/lib/libnque19.so: undefined symbol: ssMemFree
2023-05-11 07:43:53.580 : snsbitini_ts:entry
2023-05-11 07:43:53.581 : snsbitini_ts:normal exit
2023-05-11 07:43:53.581 : snsbitini_ts:entry
2023-05-11 07:43:53.581 : snsbitini_ts:normal exit
2023-05-11 07:43:53.581 : niotns:Not trying to enable dead connection detection.
2023-05-11 07:43:53.581 : niotns:Calling address: (DESCRIPTION=(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name1)(PORT=1721))(ADDRESS=(PROTOCOL=TCP)(HOST=host_name2)(PORT=1721)))(CONNECT_DATA=(SERVICE_NAME=TEST_PRIM.WORLD)(SERVER=DEDICATED)(CID=(PROGRAM=python3)(HOST=origin_host)(USER=te504ho2))))
2023-05-11 07:43:53.581 : nsgettrans_bystring:entry
2023-05-11 07:43:53.581 : nsgettrans_bystring:exit
2023-05-11 07:43:53.581 : nscall:entry
2023-05-11 07:43:53.581 : nsmal:entry
2023-05-11 07:43:53.581 : nsmal:288 bytes at 0xcd1dd0
2023-05-11 07:43:53.581 : nsmal:normal exit
2023-05-11 07:43:53.585 : nscall:connecting...
2023-05-11 07:43:53.585 : nlad_hp:entry
2023-05-11 07:43:53.585 : nlad_hp: processing other nodes
2023-05-11 07:43:53.585 : nlad_hp:entry
2023-05-11 07:43:53.585 : nlad_hp:Result: (CONNECT_TIMEOUT=10)
2023-05-11 07:43:53.585 : nlad_hp:entry
2023-05-11 07:43:53.585 : nlad_hp:Result: (RETRY_COUNT=3)
2023-05-11 07:43:53.585 : nlad_hp:entry
2023-05-11 07:43:53.585 : nlad_hp: processing other nodes
2023-05-11 07:43:53.585 : nlad_hp:entry
2023-05-11 07:43:53.585 : nlad_hp:address processing
2023-05-11 07:43:53.585 : nlad_hp:Result: (ADDRESS=(PROTOCOL=TCP)(HOST=host_name1)(PORT=1721))
2023-05-11 07:43:53.585 : nlad_hp:entry
2023-05-11 07:43:53.585 : nlad_hp:address processing
2023-05-11 07:43:53.585 : nlad_hp:Result: (ADDRESS=(PROTOCOL=TCP)(HOST=host_name2)(PORT=1721))
2023-05-11 07:43:53.585 : nlad_hp:Result: (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name1)(PORT=1721))(ADDRESS=(PROTOCOL=TCP)(HOST=host_name2)(PORT=1721)))
2023-05-11 07:43:53.585 : nlad_hp:entry
2023-05-11 07:43:53.585 : nlad_hp:Result: (CONNECT_DATA=(SERVICE_NAME=TEST_PRIM.WORLD)(SERVER=DEDICATED)(CID=(PROGRAM=python3)(HOST=origin_host)(USER=te504ho2)))
2023-05-11 07:43:53.585 : nlad_hp:Result: (DESCRIPTION=(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name1)(PORT=1721))(ADDRESS=(PROTOCOL=TCP)(HOST=host_name2)(PORT=1721)))(CONNECT_DATA=(SERVICE_NAME=TEST_PRIM.WORLD)(SERVER=DEDICATED)(CID=(PROGRAM=python3)(HOST=origin_host)(USER=te504ho2))))
2023-05-11 07:43:53.585 : nlad_expand_hst:Expanding hostname1
2023-05-11 07:43:53.585 : snlinGetAddrInfo:entry
2023-05-11 07:43:53.587 : snlinGetAddrInfo:exit
2023-05-11 07:43:53.587 : snlinGetNameInfo:entry
2023-05-11 07:43:53.587 : snlinGetNameInfo:exit
2023-05-11 07:43:53.587 : nlad_expand_hst:Adding IP 10.204.8.23

oracledb 1.3.0 (complete log-file:

Trace file /tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/trace/ora_3970404_139690443376512.trc
2023-05-11 07:45:05.557 : nlstddt_do_alter_trace:Version 19.3.0.0.0
2023-05-11 07:45:05.565 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION FOLLOWS ---
2023-05-11 07:45:05.565 : nlstddt_do_alter_trace:New trace stream is /tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/trace/ora_3970404_139690443376512.trc
2023-05-11 07:45:05.565 : nlstddt_do_alter_trace:New trace level is 16
2023-05-11 07:45:05.565 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION ENDS ---
2023-05-11 07:45:05.565 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION FOLLOWS ---
2023-05-11 07:45:05.565 : nlstdts_trace_source:Attempted load of system pfile source /opt/sas/share/ora/sqlnet.ora
2023-05-11 07:45:05.565 : nlstdts_trace_source:Parameter source loaded successfully
2023-05-11 07:45:05.565 : nlstdts_trace_source:
2023-05-11 07:45:05.565 : nlstdts_trace_source:Attempted load of local pfile source /home/te504ho2/.sqlnet.ora
2023-05-11 07:45:05.565 : nlstdts_trace_source:Parameter source was not loaded
2023-05-11 07:45:05.565 : nlstdts_trace_source:
2023-05-11 07:45:05.565 : nlstdtp_trace_pfile: -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
2023-05-11 07:45:05.565 : nlstdtp_trace_pfile:Successful parameter table load
2023-05-11 07:45:05.565 : nlstddp_dump_ptable: -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
2023-05-11 07:45:05.565 : nlstddp_dump_ptable:  DIAG_ADR_ENABLED = ON
2023-05-11 07:45:05.565 : nlstddp_dump_ptable:  TRACE_LEVEL_CLIENT = SUPPORT
2023-05-11 07:45:05.565 : nlstddp_dump_ptable:  WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /opt/shared/shsdb/operation/etc/oracle_wallet)))
2023-05-11 07:45:05.565 : nlstddp_dump_ptable:  ADR_BASE = /tmp/mylogs
2023-05-11 07:45:05.565 : nlstddp_dump_ptable:  SQLNET.WALLET_OVERRIDE = TRUE
2023-05-11 07:45:05.566 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION ENDS ---
2023-05-11 07:45:05.566 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION FOLLOWS ---
2023-05-11 07:45:05.566 : nlstddl_do_alter_log:Log stream will be "/tmp/mylogs/oradiag_te504ho2/diag/clients/user_te504ho2/host_885234334_110/alert/log.xml"
2023-05-11 07:45:05.566 : nlstddl_do_alter_log:Log stream validation not requested
2023-05-11 07:45:05.566 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION ENDS ---

2023-05-11 07:45:05.566 : nlstdipi:entry
2023-05-11 07:45:05.566 : nlstdipi:exit
2023-05-11 07:45:05.566 : nigini:entry
2023-05-11 07:45:05.566 : nigini:Count in the NL global area is now 1
2023-05-11 07:45:05.566 : nigini:Count in NI gbl area now: 1
2023-05-11 07:45:05.566 : nrigbi:entry
2023-05-11 07:45:05.566 : nrigbni:entry
2023-05-11 07:45:05.566 : nrigbni:Unable to get data from navigation file tnsnav.ora
2023-05-11 07:45:05.566 : nrigbni:exit
2023-05-11 07:45:05.566 : nrigbi:exit
2023-05-11 07:45:05.566 : nigini:exit
2023-05-11 07:45:05.587 : nigini:entry
2023-05-11 07:45:05.587 : nigini:Count in the NL global area is now 3
2023-05-11 07:45:05.587 : nigini:Count in NI gbl area now: 3
2023-05-11 07:45:05.587 : nigini:exit
2023-05-11 07:45:05.587 : nigconcbs:entry
2023-05-11 07:45:05.587 : nigconcbs:Can't Get Oracle SID Name
2023-05-11 07:45:05.587 : nigtrm:Count in the NI global area is now 2
2023-05-11 07:45:05.587 : nigtrm:Count in the NL global area is now 2

anthony-tuininga added a commit that referenced this issue May 13, 2023
@anthony-tuininga
Fixed bug when using external authentication with a TNS alias (#178).
82d17ac
@anthony-tuininga
Copy link
Member

anthony-tuininga commented May 13, 2023 •
edited

Thanks for the traces. That was helpful.

I have pushed a patch that should correct this issue and a relevant test case. If you are able to build from source you can verify that it corrects your issue as well.

Someone else noted that if you do this it will also work (without the patch):

conn = oracledb.connect(dsn="@tns_alias")

@glM26
Copy link
Author

glM26 commented May 15, 2023

@anthony-tuininga thank you for help, I can confirm that both suggestions (building the newest version from source as well as adding @ in front of the dsn) solved my issue.
Do you know when a new version of oracledb containing the fix will be released?

@anthony-tuininga
Copy link
Member

Thanks, @glM26. We will discuss internally but generally we like to make a release that contains bug fixes within a few weeks at most. I will post here when a release is made that contains this patch.

anthony-tuininga added a commit that referenced this issue Jun 15, 2023
@anthony-tuininga
Fixed bug when using external authentication with a TNS alias (#178).
636be62
@anthony-tuininga
Copy link
Member

This has been fixed in python-oracledb 1.3.2 which was just released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working patch available
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@cjbj @anthony-tuininga @glM26