Python oracledb连接Oracle 11g遇DPY-4011错误求助

问题代码 #

import oracledb
import os

user = 'system'
password = 'admin123'
port = 1521
service_name = 'xe'
oracle_server_addr = 'localhost'

conn_string = "{oracle_server_addr}:{port}/{service_name}".format(oracle_server_addr=oracle_server_addr, port=port, service_name=service_name)

print(conn_string)

with oracledb.connect(user=user, password=password, dsn=conn_string) as conn:
    with conn.cursor() as cursor:
        sql = """select sysdate from dual"""
        for r in cursor.execute(sql):
            print(r)

运行报错信息 #

conn_string: localhost:1521/xe

Traceback (most recent call last):
  File "src/oracledb/impl/thin/connection.pyx", line 353, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "src/oracledb/impl/thin/protocol.pyx", line 207, in oracledb.thin_impl.Protocol._connect_phase_one
  File "src/oracledb/impl/thin/protocol.pyx", line 386, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 365, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/messages.pyx", line 1835, in oracledb.thin_impl.ConnectMessage.process
  File "src/oracledb/impl/thin/buffer.pyx", line 845, in oracledb.thin_impl.Buffer.read_uint32
  File "src/oracledb/impl/thin/packet.pyx", line 235, in oracledb.thin_impl.ReadBuffer._get_raw
  File "src/oracledb/impl/thin/packet.pyx", line 588, in oracledb.thin_impl.ReadBuffer.wait_for_packets_sync
  File "src/oracledb/impl/thin/transport.pyx", line 306, in oracledb.thin_impl.Transport.read_packet
  File "/home/acme/.local/lib/python3.10/site-packages/oracledb/errors.py", line 162, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.DatabaseError: DPY-4011: the database or network closed the connection

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/tmp/workspace/sql_ping/sql_ping.py", line 16, in <module>
    with oracledb.connect(user=user, password=password, dsn=conn_string) as conn:
  File "/home/acme/.local/lib/python3.10/site-packages/oracledb/connection.py", line 1134, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File "/home/acme/.local/lib/python3.10/site-packages/oracledb/connection.py", line 523, in __init__
    impl.connect(params_impl)
  File "src/oracledb/impl/thin/connection.pyx", line 449, in oracledb.thin_impl.ThinConnImpl.connect
  File "src/oracledb/impl/thin/connection.pyx", line 445, in oracledb.thin_impl.ThinConnImpl.connect
  File "src/oracledb/impl/thin/connection.pyx", line 411, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src/oracledb/impl/thin/connection.pyx", line 392, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src/oracledb/impl/thin/connection.pyx", line 358, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "/home/acme/.local/lib/python3.10/site-packages/oracledb/errors.py", line 162, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=emkjRug2341/ymv7uTC0Bg==).
DPY-4011: the database or network closed the connection

环境信息 #

数据库与客户端在同一主机：

• 数据库：Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit（localhost），Ubuntu系统，用户system，密码admin123，端口1521，服务名xe，已尝试localhost、本地IP、127.0.0.1作为主机地址
• 应用：Ubuntu系统，Python 3.10，oracledb 2.0.1/2.0.0，thin模式
• 本地无防火墙、代理、杀毒软件

已尝试的解决方法 #

• Java thin模式可正常连接
• DBeaver可正常连接
• 降级oracledb到1.2.1、1.3.2、1.4.2版本，报错：oracledb.exceptions.NotSupportedError: DPY-3010: connections to this database server version are not supported by python-oracledb in thin mode
• 添加disable_oob=True参数，错误依旧

解决方案 #

1. 切换为oracledb Thick模式 #

Oracle 11g不在python-oracledb Thin模式的官方支持范围内（仅支持12.1及以上版本），但Thick模式兼容旧版本数据库，操作步骤：

• 下载安装对应Oracle 11g的Instant Client（推荐11.2.0.4版本）
• 在代码中启用Thick模式并指定Instant Client路径（若已配置系统环境变量LD_LIBRARY_PATH可省略路径参数）：

import oracledb
import os

# 启用Thick模式
oracledb.init_oracle_client(lib_dir="/path/to/instantclient_11_2")

user = 'system'
password = 'admin123'
port = 1521
service_name = 'xe'
oracle_server_addr = 'localhost'

conn_string = f"{oracle_server_addr}:{port}/{service_name}"

with oracledb.connect(user=user, password=password, dsn=conn_string) as conn:
    with conn.cursor() as cursor:
        sql = "select sysdate from dual"
        for r in cursor.execute(sql):
            print(r)

2. 检查数据库端SQLNET.ORA配置 #

• 确认SQLNET.ALLOWED_LOGON_VERSION_SERVER设置为11或更低，避免因版本不匹配导致连接被拒绝
• 检查是否存在TCP.VALIDNODE_CHECKING等IP限制配置，如有需将本地IP加入允许列表

3. 调整连接字符串格式 #

尝试使用SID格式（冒号分隔）替代服务名格式：

conn_string = f"{oracle_server_addr}:{port}:{service_name}"

部分旧Oracle环境中，SID与服务名同名，但连接格式需区分。

内容的提问来源于stack exchange，提问作者JRichardsz