When DNS name resolves to multiple IP addresses, driver attempts a connection to only one IP address (thin mode)

[ilmarkerm]

1. What versions are you using?

Database: 19.17.0
platform.platform: Linux-5.4.17-2136.314.6.2.el8uek.x86_64-x86_64-with-glibc2.28
sys.maxsize > 2**32: True
platform.python_version: 3.9.13
oracledb.version: 1.2.2

2. Is it an error or a hang or a crash?

Incorrect behaviour.
Inconsistent behaviour compared to other drivers (JDBC, thick).

3. What error(s) or behavior you are seeing?

If database hostname resolves to multiple IP addresses, thin driver only attepts to connect to one IP address.
But according to MAA Client Failover Best Practices whitepaper - if database hostname resolves to multiple IP addresses, it sould behave the same way as all addresses are listed in ADDRESS_LIST section and in case of failure failover to the next IP.
Ref:
https://www.oracle.com/technetwork/database/availability/client-failover-2280805.pdf
page 10... quote:
"This impacts the 3 SCAN IP addresses the same way as if those 3 IP addresses were listed explicitly in the connect descriptor.
This means that if the initial connection requests to the first randomly-assigned SCAN IP address fails, the
connection will failover to another SCAN IP address, and will continue to do so, till it iterates the complete address list."

In thick mode driver behaves correctly.

4. Does your application call init_oracle_client()?

No. Using thin mode.

5. Include a runnable Python script that shows the problem.

In this example, the database hostname resolves to multiple IP addresses, but service is accessible only from one IP address.

$ host oracle.db.example.org
oracle.db.example.org has address 10.10.33.55
oracle.db.example.org has address 10.10.33.56
oracle.db.example.org has address 10.10.33.57
oracle.db.example.org has address 10.10.33.58

Most of the time, connection fails (occasionally, when by accident the correct IP is selected, connection succeeds):

import oracledb
db = oracledb.connect(
       user="t1",
       password="t1",
       dsn="""(description=(failover=on)(connect_timeout=2)(transport_connect_timeout=1 sec)
            (address_list=(load_balance=on)
                (address=(protocol=tcp)(host=oracle.db.example.org)(port=1521))
            )(connect_data=(service_name=databaseservice.domain)))"""
    )

oracledb.exceptions.OperationalError: DPY-6001: cannot connect to database. Service "databaseservice.domain" is not registered with the listener at host "oracle.db.example.org" port 1521. (Similar to ORA-12514)

But when listing all IP addresses in ADDRESS_LIST section, connection is always successful and no exception is raised:

import oracledb
db = oracledb.connect(
       user="t1",
       password="t1",
       dsn="""(description=(failover=on)(connect_timeout=2)(transport_connect_timeout=1 sec)
            (address_list=(load_balance=on)
                (address=(protocol=tcp)(host=10.10.33.55)(port=1521))
                (address=(protocol=tcp)(host=10.10.33.56)(port=1521))
                (address=(protocol=tcp)(host=10.10.33.57)(port=1521))
                (address=(protocol=tcp)(host=10.10.33.58)(port=1521))
            )(connect_data=(service_name=databaseservice.domain)))"""
    )

[cjbj]

Thanks for the report.

If you want to follow MAA you would need to be using Thick mode, since the Thin mode doesn't support AC or TAC.

[ilmarkerm]

Thanks for the report.

If you want to follow MAA you would need to be using Thick mode, since the Thin mode doesn't support AC or TAC.

Link to MAA was just a link to documentation that describes the desired behaviour - not about following MAA.

My use case involves data guard standbys that are included in the same DNS name as primary.

Another use case would be RAC SCAN addresses - they are also just multiple listeners and multiple IP addresses behind a same DNS name. If you restart one SCAN listener then after startup there is a breif preiod of time when this SCAN listener does not have any services registered yet - while other SCAN listeners do.

[cjbj]

@ilmarkerm when python-oracledb 3.0 lands, you should try it. There have been changes to IP address resolution.

[cjbj]

Closing - please reopen if any cases are still outstanding.