2023年6月8日 04:25:11go评论58阅读模式

英文:

Can not connect with python-oracledb to Base Database Service on OCI

问题

I have created a database system on Oracle Cloud using the Base Database service. I can connect to it from a VM in the same virtual cloud network using sqlplus:

sqlplus system/mysecretpw@10.10.3.16:1521/master_bla_ams.databases.nwname.oraclevcn.com

This works fine. If I want to connect from python, I get DPY-4011: the database or network closed the connection [Errno 104] Connection reset by peer. I've tried various different connect calls, none of them work:

# 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)

Connecting with sqlplus from the same VM works fine. How can I connect using python-oracledb?

Full traceback:

2023-06-07 20:56:16.972 [socket: 3] Sending packet:
0000 : 00 4A 00 00 01 00 00 00 |.J......|
...
# (Truncated for brevity)
...
oracledb.exceptions.DatabaseError: DPY-4011: the database or network closed the connection
[Errno 104] Connection reset by peer

英文:

I have created a database system on Oracle Cloud using the Base Database service. I can connect to it from a VM in the same virtual cloud network using sqlplus:

sqlplus system/mysecretpw@10.10.3.16:1521/master_bla_ams.databases.nwname.oraclevcn.com

# Using service_name
db_connection = oracledb.connect(user=&quot;system&quot;, password=&quot;mysecretpw&quot;, host=&quot;10.10.3.16&quot;, port=&quot;1521&quot;, service_name=&quot;master_bla_ams.databases.nwname.oraclevcn.com&quot;)
# 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=&quot;system&quot;, password=&quot;mysecretpw&quot;, dsn=&quot;10.10.3.16:1521/master_bla_ams.databases.nwname.oraclevcn.com&quot;)
# Using dsn with the long connection string from the cloud UI
cs=&quot;(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)))&quot;
db_connection=oracledb.connect(user=&quot;system&quot;, password=&quot;mysecretpw&quot;, dsn=cs)

Connecting with sqlplus from the same VM works fine. How can I connect using python-oracledb?
Full traceback:

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:
&lt;same as 2023-06-07 20:56:16.972&gt;

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 |.&gt;......|
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 &quot;src/oracledb/impl/thin/packet.pyx&quot;, 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 &quot;/home/ubuntu/folder/oracle_query.py&quot;, line 29, in &lt;module&gt;
    main()
  File &quot;/home/ubuntu/folder/oracle_query.py&quot;, line 16, in main
    db_connection=oracledb.connect(user=&quot;system&quot;, password=&quot;mysecretpw&quot;, dsn=cs)
  File &quot;/home/ubuntu/.local/lib/python3.10/site-packages/oracledb/connection.py&quot;, line 1008, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File &quot;/home/ubuntu/.local/lib/python3.10/site-packages/oracledb/connection.py&quot;, line 130, in __init__
    impl.connect(params_impl)
  File &quot;src/oracledb/impl/thin/connection.pyx&quot;, line 314, in oracledb.thin_impl.ThinConnImpl.connect
  File &quot;src/oracledb/impl/thin/connection.pyx&quot;, line 202, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File &quot;src/oracledb/impl/thin/connection.pyx&quot;, line 173, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File &quot;src/oracledb/impl/thin/connection.pyx&quot;, line 114, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File &quot;src/oracledb/impl/thin/protocol.pyx&quot;, line 221, in oracledb.thin_impl.Protocol._connect_phase_two
  File &quot;src/oracledb/impl/thin/protocol.pyx&quot;, line 343, in oracledb.thin_impl.Protocol._process_message
  File &quot;src/oracledb/impl/thin/protocol.pyx&quot;, line 321, in oracledb.thin_impl.Protocol._process_message
  File &quot;src/oracledb/impl/thin/protocol.pyx&quot;, line 389, in oracledb.thin_impl.Protocol._receive_packet
  File &quot;src/oracledb/impl/thin/packet.pyx&quot;, line 559, in oracledb.thin_impl.ReadBuffer.receive_packet
  File &quot;src/oracledb/impl/thin/packet.pyx&quot;, line 358, in oracledb.thin_impl.ReadBuffer._receive_packet_helper
  File &quot;src/oracledb/impl/thin/packet.pyx&quot;, line 197, in oracledb.thin_impl.ReadBuffer._get_data_from_socket
  File &quot;/home/ubuntu/.local/lib/python3.10/site-packages/oracledb/errors.py&quot;, 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

得分: 1

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

select network_service_banner from v$session_connect_info;

如果输出包含类似以下内容（提到 Crypto-checksumming 适配器和/或 Encryption 服务适配器），那么你正在使用 Oracle 的本地网络加密（NNE）：

TCP/IP NT Protocol Adapter for Linux: Version 21.0.0.0.0 - Production
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() 来在 Thick 模式下使用 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

英文:

Connect to the DB using SQL*Plus or similar and execute:

select network_service_banner from v$session_connect_info;

If the output contains something like the following (mentioning a Crypto-checksumming adapter and/or Encryption service adapter) then you are using Oracle's 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

To connect to this database you need to do either of:

Disable NNE, which is most likely configured in the database sqlnet.ora file, see here. Enable TLS if you want encryption.
Or use python-oracledb in Thick mode by adding a call to init_oracle_client()

For comparison, the same query on a database without NNE will return something like:

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

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

无法连接到 OCI 上的 Base 数据库服务，使用 python-oracledb。

问题

答案1

ORA-02287: 在Oracle XML创建中不允许使用序列号，XMLAGG

无法使用Connection类连接数据库。

如何从Oracle SQL的同一列中获取单独的值

将 “Adding ROWID to an Oracle View” 翻译为中文：在 Oracle 视图中添加 ROWID

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论