我使用Base Database服务在Oracle Cloud上创建了一个数据库系统。我可以使用sqlplus从同一虚拟云网络中的VM连接到它：
sqlplus system/mysecretpw@10.10.3.16:1521/master_bla_ams.databases.nwname.oraclevcn.com
这个很好用。如果我想从python连接，我得到DPY-4011: the database or network closed the connection [Errno 104] Connection reset by peer。我尝试了各种不同的连接调用，没有一个工作：

# Using service_name
db_connection = oracledb.connect(user="system", password="mysecretpw", host="10.10.3.16", port="1521", service_name="master_bla_ams.databases.nwname.oraclevcn.com")
# Using dsn, also tried this with hostname instead of IP, exactly like the connection string shown in the Oracle Cloud UI
db_connection = oracledb.connect(user="system", password="mysecretpw", dsn="10.10.3.16:1521/master_bla_ams.databases.nwname.oraclevcn.com")
# Using dsn with the long connection string from the cloud UI
cs="(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.3.16)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=master_bla_ams.databases.nwname.oraclevcn.com)))"
db_connection=oracledb.connect(user="system", password="mysecretpw", dsn=cs)

从同一VM连接sqlplus可以正常工作。如何使用python-oracledb进行连接？完整追溯：

2023-06-07 20:56:16.972 [socket: 3] Sending packet:
0000 : 00 4A 00 00 01 00 00 00 |.J......|
0008 : 01 3F 01 2C 04 01 20 00 |.?.,....|
0016 : FF FF 4F 98 00 00 00 01 |..O.....|
0024 : 00 F8 00 4A 00 00 00 00 |...J....|
0032 : 84 84 00 00 00 00 00 00 |........|
0040 : 00 00 00 00 00 00 00 00 |........|
0048 : 00 00 00 00 00 00 00 00 |........|
0056 : 00 00 00 00 20 00 00 00 |........|
0064 : FF FF 00 00 00 00 00 00 |........|
0072 : 00 01                   |..      |

2023-06-07 20:56:16.972 [socket: 3] Sending packet:
0000 : 01 02 00 00 06 00 00 00 |........|
0008 : 00 00 28 44 45 53 43 52 |..(DESCR|
0016 : 49 50 54 49 4F 4E 3D 28 |IPTION=(|
0024 : 52 45 54 52 59 5F 43 4F |RETRY_CO|
0032 : 55 4E 54 3D 33 29 28 54 |UNT=3)(T|
0040 : 52 41 4E 53 50 4F 52 54 |RANSPORT|
0048 : 5F 43 4F 4E 4E 45 43 54 |_CONNECT|
0056 : 5F 54 49 4D 45 4F 55 54 |_TIMEOUT|
0064 : 3D 33 29 28 41 44 44 52 |=3)(ADDR|
0072 : 45 53 53 3D 28 50 52 4F |ESS=(PRO|
0080 : 54 4F 43 4F 4C 3D 74 63 |TOCOL=tc|
0088 : 70 29 28 48 4F 53 54 3D |p)(HOST=|
0096 : 31 30 2E 31 30 2E 33 2E |10.10.3.|
0104 : 31 36 29 28 50 4F 52 54 |16)(PORT|
0112 : 3D 31 35 32 31 29 29 28 |=1521))(|
0120 : 43 4F 4E 4E 45 43 54 5F |CONNECT_|
0128 : 44 41 54 41 3D 28 53 45 |DATA=(SE|
0136 : 52 56 49 43 45 5F 4E 41 |RVICE_NA|
0144 : 4D 45 3D 6D 61 73 74 65 |ME=maste|
0152 : 72 5F 62 6C 61 5F 61 6D |r_bla_am|
0160 : 73 2E 64 61 74 61 62 61 |s.databa|
0168 : 73 65 73 2E 6E 77 6E 61 |ses.nwna|
0176 : 6D 65 2E 6F 72 61 63 6C |me.oracl|
0184 : 65 76 63 6E 2E 63 6F 6D |evcn.com|
0192 : 29 28 43 49 44 3D 28 50 |)(CID=(P|
0200 : 52 4F 47 52 41 4D 3D 2F |ROGRAM=/|
0208 : 75 73 72 2F 62 69 6E 2F |usr/bin/|
0216 : 70 79 74 68 6F 6E 33 29 |python3)|
0224 : 28 48 4F 53 54 3D 61 61 |(HOST=aa|
0232 : 61 61 61 61 61 61 61 61 |aaaaaaaa|
0240 : 74 29 28 55 53 45 52 3D |t)(USER=|
0248 : 75 62 75 6E 74 75 29 29 |ubuntu))|
0256 : 29 29                   |))      |

2023-06-07 20:56:16.981 [socket: 3] Receiving packet:
0000 : 00 08 00 0A 0B 00 00 00 |........|

2023-06-07 20:56:16.982 [socket: 3] Sending packet:
0000 : 00 4A 00 00 01 00 00 00 |.J......|
0008 : 01 3F 01 2C 04 01 20 00 |.?.,....|
0016 : FF FF 4F 98 00 00 00 01 |..O.....|
0024 : 00 F8 00 4A 00 00 00 00 |...J....|
0032 : 84 84 00 00 00 00 00 00 |........|
0040 : 00 00 00 00 00 00 00 00 |........|
0048 : 00 00 00 00 00 00 00 00 |........|
0056 : 00 00 00 00 20 00 00 00 |........|
0064 : FF FF 00 00 00 00 00 00 |........|
0072 : 00 01                   |..      |

2023-06-07 20:56:16.982 [socket: 3] Sending packet:
<same as 2023-06-07 20:56:16.972>

2023-06-07 20:56:16.982 [socket: 3] Receiving packet:
0000 : 00 2D 00 00 02 00 00 00 |.-......|
0008 : 01 3E 04 01 00 00 00 00 |.>......|
0016 : 01 00 00 00 00 2D D5 00 |.....-..|
0024 : 00 00 00 00 00 00 00 00 |........|
0032 : 00 00 20 00 00 00 FF FF |........|
0040 : 00 00 00 00 01          |.....   |

2023-06-07 20:56:16.983 [socket: 3] Sending packet:
0000 : 00 00 00 0B 0C 00 00 00 |........|
0008 : 01 00 02                |...     |

2023-06-07 20:56:16.983 [socket: 3] Sending packet:
0000 : 00 00 00 1D 06 00 00 00 |........|
0008 : 00 00 01 06 00 70 79 74 |.....pyt|
0016 : 68 6F 6E 2D 6F 72 61 63 |hon-orac|
0024 : 6C 65 64 62 00          |ledb.   |

Traceback (most recent call last):
  File "src/oracledb/impl/thin/packet.pyx", line 195, in oracledb.thin_impl.ReadBuffer._get_data_from_socket
ConnectionResetError: [Errno 104] Connection reset by peer

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

Traceback (most recent call last):
  File "/home/ubuntu/folder/oracle_query.py", line 29, in <module>
    main()
  File "/home/ubuntu/folder/oracle_query.py", line 16, in main
    db_connection=oracledb.connect(user="system", password="mysecretpw", dsn=cs)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/oracledb/connection.py", line 1008, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/oracledb/connection.py", line 130, in __init__
    impl.connect(params_impl)
  File "src/oracledb/impl/thin/connection.pyx", line 314, in oracledb.thin_impl.ThinConnImpl.connect
  File "src/oracledb/impl/thin/connection.pyx", line 202, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src/oracledb/impl/thin/connection.pyx", line 173, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src/oracledb/impl/thin/connection.pyx", line 114, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "src/oracledb/impl/thin/protocol.pyx", line 221, in oracledb.thin_impl.Protocol._connect_phase_two
  File "src/oracledb/impl/thin/protocol.pyx", line 343, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 321, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 389, in oracledb.thin_impl.Protocol._receive_packet
  File "src/oracledb/impl/thin/packet.pyx", line 559, in oracledb.thin_impl.ReadBuffer.receive_packet
  File "src/oracledb/impl/thin/packet.pyx", line 358, in oracledb.thin_impl.ReadBuffer._receive_packet_helper
  File "src/oracledb/impl/thin/packet.pyx", line 197, in oracledb.thin_impl.ReadBuffer._get_data_from_socket
  File "/home/ubuntu/.local/lib/python3.10/site-packages/oracledb/errors.py", line 118, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.DatabaseError: DPY-4011: the database or network closed the connection
[Errno 104] Connection reset by peer

1条答案

按热度按时间

xytpbqjk1#

使用SQL*Plus或类似工具连接到数据库并执行：

select network_service_banner from v$session_connect_info;

如果输出包含如下内容（提到加密校验和适配器和/或加密服务适配器），则您使用的是Oracle的Native Network Encryption（NNE）：

Encryption service for Linux: Version 21.0.1.0.0 - Production
AES256 Encryption service adapter for Linux: Version 21.0.1.0.0 - Production
Crypto-checksumming service for Linux: Version 21.0.1.0.0 - Production
SHA1 Crypto-checksumming service adapter for Linux: Version 21.0.1.0.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 21.0.0.0.0 - Production
Authentication service for Linux: Version 21.0.1.0.0 - Production

**要连接到此数据库，您需要执行以下操作之一：

禁用NNE，这很可能是在数据库sqlnet.ora文件中配置的，请参阅此处。如果需要加密，请启用TLS。
或者通过添加对init_oracle_client（）的调用，在密集模式下使用python-oracledb

为了进行比较，在没有NNE的数据库上执行相同的查询将返回如下内容：

TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.1.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.1.0.0 - Production

赞(0）回复(0）举报 2023-06-22

无法使用python-oracledb连接到OCI上的基本数据库服务

1条答案

相关问题

热门标签

最新问答