Oracle TAC与Python DML操作RAC故障转移异常问题求助

我在测试Oracle TAC与Python的结合使用，环境为Python 3.11，通过oracledb厚模式建立连接。目前遇到的问题是：执行查询操作时，Oracle RAC节点故障可正常切换至另一节点；但执行DML操作时，连接会直接断开，无法完成故障转移。经测试，相同场景在sqlplus和Java环境下可正常运行，已确认数据库及服务配置无误。

相关信息 #

Python代码

import oracledb
import db_config
import threading
import time

# Create a Connection Pool
pool = oracledb.create_pool(user=db_config.user,password=db_config.pw,dsn=db_config.dsn, min=4, max=5,increment=1,events=True,getmode=oracledb.POOL_GETMODE_NOWAIT)


def run_update():
    with pool.acquire() as conn:
        with conn.cursor() as cursor:
            conn.autocommit = False
            print("run_update(): beginning execute...")
            cursor.execute("SELECT sys_context('USERENV','INSTANCE_NAME') AS Instance FROM dual")
            res, = cursor.fetchone()
            print("Connected to instance: " +res)
            input("Press the Enter key to continue: ") 
            print("run_update(): beginning update...")
            sql = """SELECT UNIQUE CLIENT_DRIVER
                FROM V$SESSION_CONNECT_INFO
                 WHERE SID = SYS_CONTEXT('USERENV', 'SID')"""
            for r, in cursor.execute(sql):
             print(r)
            # populate table with a few rows
            stmt = "Update test SET v=UPPER(v) Where ID=:n"
            try:
                for i in range(5000):
                   cursor.execute(stmt, {'n': i})
                   if i == 250:
                     print("kill node")
                     input("Press the Enter key to continue: ") 
                     i = i + 1
            except oracledb.DatabaseError as e:
                print(e)
            else:
             cursor.execute("SELECT sys_context('USERENV','INSTANCE_NAME') AS Instance FROM dual")
             res, = cursor.fetchone()
             print("after stop connected to instance: " +res)
    

thread1 = threading.Thread(target=run_update)
thread1.start()


print("All done!")

服务配置

Service name: pdb_tac
Server pool:
Cardinality: 2
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Global: false
Commit Outcome: true
Failover type: AUTO
Failover method:
Failover retries: 1
Failover delay: 3
Failover restore: AUTO
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: pdb
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 600 seconds
Drain timeout: 30 seconds
Stop option: immediate
Session State Consistency: AUTO
GSM Flags: 0
Service is enabled
Preferred instances: orcl1,orcl2
Available instances:
CSS critical: no
Service uses Java: false

连接字符串

'(DESCRIPTION=(CONNECT_TIMEOUT=60)(RETRY_COUNT=30)(RETRY_DELAY=3)(TRANSPORT_CONNECT_TIMEOUT=4)(FAILOVER=ON)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME = pdb_tac.localdomain)))'

错误信息

run_update(): beginning execute...
Connected to instance: orcl2
Press the Enter key to continue:
run_update(): beginning update...
python-oracledb thk : 1.4.2
kill node
Press the Enter key to continue:
DPY-4011: the database or network closed the connection
DPI-1080: connection was closed by ORA-41429

解决方案 #

1. 启用TAF（Transparent Application Failover）配置

当前服务配置中TAF policy specification: NONE是核心问题。查询操作仅依赖连接层面的故障转移即可，但DML属于有状态事务，需要TAF维护会话状态并完成故障转移。

修改服务的TAF策略：

EXEC DBMS_SERVICE.MODIFY_SERVICE('pdb_tac', FAILOVER_TYPE => 'SESSION', FAILOVER_METHOD => 'BASIC', TAF_POLICY => 'BASIC');

或使用srvctl命令：

srvctl modify service -db your_db_name -service pdb_tac -failovertype SESSION -failovermethod BASIC -tafpolicy BASIC

修改后重启服务：

srvctl stop service -db your_db_name -service pdb_tac
srvctl start service -db your_db_name -service pdb_tac

2. 调整事务异常处理逻辑

在DML操作的异常捕获中，针对连接断开类错误添加重试机制，从断点处恢复事务：

except oracledb.DatabaseError as e:
    error_obj, = e.args
    print(f"Error occurred: {error_obj.message}")
    # 匹配RAC节点故障相关错误码
    if error_obj.code in [41429, 12537, 12541]:
        print("Attempting failover retry...")
        with pool.acquire() as new_conn:
            with new_conn.cursor() as new_cursor:
                new_conn.autocommit = False
                # 从故障发生的位置开始重试DML
                for j in range(i, 5000):
                    new_cursor.execute(stmt, {'n': j})
                new_conn.commit()
                res, = new_cursor.execute("SELECT sys_context('USERENV','INSTANCE_NAME') AS Instance FROM dual").fetchone()
                print(f"Retry succeeded, connected to instance: {res}")

3. 升级oracledb驱动版本

当前使用的python-oracledb thk : 1.4.2存在部分RAC故障转移兼容性问题，建议升级至最新稳定版：

pip install --upgrade oracledb

4. 完善连接字符串的故障转移参数

在连接字符串中明确指定TAF模式，增强故障转移的可靠性：

'(DESCRIPTION=(CONNECT_TIMEOUT=60)(RETRY_COUNT=30)(RETRY_DELAY=3)(TRANSPORT_CONNECT_TIMEOUT=4)(FAILOVER=ON)(FAILOVER_MODE=(TYPE=SESSION,METHOD=BASIC))(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME = pdb_tac.localdomain)))'

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