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.