Thin via SCAN listener fails with DPY-6000 -- SERVICE_NAME not passed to local listener #39
Description
- What versions are you using?
listeners: 18, database: 18
platform.platform: Windows-10-10... (We see the same error from Lunix clients)
sys.maxsize > 2**32: True
platform.python_version: 3.10.5
oracledb.version: 1.0.2
- Is it an error or a hang or a crash?
error
- What error(s) or behavior you are seeing?
if we replace the SCAN address with the local listener address, the connection functions without error.
Traceback (most recent call last): File "C:\apps\tmp\test.py", line 38, in <module> with oracledb.connect(user=un, password=pw, dsn=cs) as connection: File "C:\apps\Python\Python310\lib\site-packages\oracledb\connection.py", line 1000, in connect return conn_class(dsn=dsn, pool=pool, params=params, **kwargs) File "C:\apps\Python\Python310\lib\site-packages\oracledb\connection.py", line 128, in __init__ impl.connect(params_impl) File "src\oracledb\impl/thin/connection.pyx", line 347, in oracledb.thin_impl.ThinConnImpl.connect File "src\oracledb\impl/thin/connection.pyx", line 163, in oracledb.thin_impl.ThinConnImpl._connect_with_params File "src\oracledb\impl/thin/connection.pyx", line 129, in oracledb.thin_impl.ThinConnImpl._connect_with_description File "src\oracledb\impl/thin/protocol.pyx", line 241, in oracledb.thin_impl.Protocol._process_message File "src\oracledb\impl/thin/connection.pyx", line 234, in oracledb.thin_impl.ThinConnImpl._connect_with_address File "src\oracledb\impl/thin/protocol.pyx", line 150, in oracledb.thin_impl.Protocol._connect_phase_one File "src\oracledb\impl/thin/protocol.pyx", line 262, in oracledb.thin_impl.Protocol._process_message File "src\oracledb\impl/thin/protocol.pyx", line 241, in oracledb.thin_impl.Protocol._process_message File "src\oracledb\impl/thin/messages.pyx", line 1578, in oracledb.thin_impl.ConnectMessage.process File "C:\apps\Python\Python310\lib\site-packages\oracledb\errors.py", line 103, in _raise_err raise exc_type(_Error(message)) from cause oracledb.exceptions.OperationalError: DPY-6000: cannot connect to database. Listener refused connection. (Similar to ORA-12504) the local listener logs the following:
22-JUL-2022`` 10:53:48 * (CONNECT_DATA=(SERVICE_NAME=)(SERVER=dedicated)(CID=(PROGRAM=C:\apps\Python\Python310\python.exe)(HOST=___)(USER=___))) * establish * 12504 TNS-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA TNS-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA - Does your application call init_oracle_client()?
no, we wish to use thin.
- Include a runnable Python script that shows the problem.
# test.py import oracledb import os oracledb.defaults.config_dir = "C:/apps" un = os.environ.get('USER_NAME') pw = os.environ.get('PASSWORD') cs = os.environ.get('CONNECT_STRING') with oracledb.connect(user=un, password=pw, dsn=cs) as connection: with connection.cursor() as cursor: sql = """select sysdate from dual""" for r in cursor.execute(sql): print(r) ConnectParams(user='', proxy_user=None, host='host..org', port=1521, protocol='tcp', https_proxy=None, https_proxy_port=0, service_name='mydb.___.org', sid=None, server_type=None, cclass=None, purity=0, expire_time=0, retry_count=0, retry_delay=0, tcp_connect_timeout=60.0, ssl_server_dn_match=True, ssl_server_cert_dn=None, wallet_location=None, events=False, mode=0, disable_oob=False, stmtcachesize=20, edition=None, tag=None, matchanytag=False, config_dir='C:\apps\', appcontext=None, shardingkey=None, supershardingkey=None, debug_jdwp=None)
have tested many connection options -- all end with the DPY-6000 unless a local listener is used.
our SCAN listener configuration may be at fault here, but we have other thin connections connecting via our SCAN listeners without problems.