使用 oci 连接 oracle rac 报错 ORA-25408: can not safely replay call

最新推荐文章于 2024-01-02 14:54:45 发布

数据库人生

最新推荐文章于 2024-01-02 14:54:45 发布

阅读量7.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： # oracle ha 文章标签： ORA-25408 oci failover taf

本文链接：https://blog.csdn.net/ctypyb2002/article/details/96968942

oracle ha 专栏收录该内容

2 篇文章

订阅专栏

本文探讨了在Oracle RAC环境中遇到ORA-25408错误的情况，详细分析了错误产生的原因及应对策略，特别是针对PL/SQL程序单元、DML和DDL操作在故障转移后的处理方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

os: centos 7.6
db: oracle 19.3

oracle 19.3 rac 环境,连接实例执行sql时提示 ORA-25408: can not safely replay call

rac taf service

$ srvctl add service -db orclp -pdb pdb1  -service srv_workload_pdb1_taf   -preferred orclp1,orclp2 -role PRIMARY  -notification true -tafpolicy BASIC -failovertype SESSION -failovermethod BASIC -failoverdelay 10 -failoverretry 150 ;
$ srvctl add service -db orclp -pdb pdb2  -service srv_workload_pdb2_taf   -preferred orclp1,orclp2 -role PRIMARY  -notification true -tafpolicy BASIC -failovertype SESSION -failovermethod BASIC -failoverdelay 10 -failoverretry 150 ;

$ srvctl start service -db orclp -service srv_workload_pdb1_taf ;
$ srvctl start service -db orclp -service srv_workload_pdb2_taf ;

rac 信息

$ cat /etc/hosts

#################################
192.168.56.101       nodea1
192.168.56.201       nodea1-vip
192.168.156.101      nodea1-priv

192.168.56.102   	 nodea2
192.168.56.202       nodea2-vip
192.168.156.102      nodea2-priv

#scan ip
192.168.56.221   	 clustera-scan
192.168.56.222   	 clustera-scan
192.168.56.223   	 clustera-scan

$ srvctl config scan
SCAN name: uatpayclustera-scan, Network: 1
Subnet IPv4: 192.168.56.0/255.255.255.0/eth0, static
Subnet IPv6: 
SCAN 1 IPv4 VIP: 192.168.56.221
SCAN VIP is enabled.
SCAN 2 IPv4 VIP: 192.168.56.222
SCAN VIP is enabled.
SCAN 3 IPv4 VIP: 192.168.56.223
SCAN VIP is enabled.

$ sqlplus / as sysdba;

SQL> set lines 500;
set pages 500;
show parameter listener;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
forward_listener		     string
listener_networks		     string
local_listener			     string	 (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.201)(PORT=1521))
remote_listener 		     string	 clustera-scan:1521

$ srvctl add service -db orcl -pdb pdb1 -service srv_pdb1 -preferred orcl1,orcl2 \
-role PRIMARY  -notification true \
-tafpolicy BASIC -failovertype SESSION -failovermethod BASIC -failoverdelay 10 -failoverretry 150 ;

$ srvctl start service -db orcl -service srv_pdb1

$ cat $ORACLE_HOME/network/admin/tnsnames.ora

tns_orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = clustera-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = srv_pdb1)
    )
  )

出错提示

使用 sqlplus 登录后,执行 plsql 匿名块一段时间后,报错 ORA-25408: can not safely replay call,排查后发现是执行sql的那台oracle实例发成了重启.
重启的原因另外分析,这里主要是描述下 ORA-25408: can not safely replay call 错误.

$ sqlplus peiyb/peiybpeiyb@tns_orcl

SQL> DECLARE 
   
  lv_begin_date_str varchar2(10):='2019-07-23';-- 2018-07-11 2019-01-01 2019-05-01
  lv_end_date_str   varchar2(10):='2019-07-23';-- 2018-12-31 2019-04-30 2019-07-20
  
BEGIN
  for c_i in (
      select level as le,
            to_char(to_date(lv_begin_date_str,'yyyy-mm-dd')+ level -1,'yyyy-mm-dd') as date_str
       from dual
    connect by level <= to_date(lv_end_date_str,'yyyy-mm-dd')- to_date(lv_begin_date_str,'yyyy-mm-dd')+1 
       
   )
   loop
      INSERT INTO tmp_t1 (
          id,
		  name,
          CREATION_DATE
         )
      select
             tmp_t1.nextval,
             name,
             po.creation_date,--8
        from tmp_t0 po
       where 1=1
         and po.creation_date between to_date(c_i.date_str||' 00:00:00','yyyy-mm-dd hh24:mi:ss')
                                  and to_date(c_i.date_str||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
        ;
        
       commit;
   end loop;   
END ;
/

*
ERROR at line 1:
ORA-25408: can not safely replay call

分析

$ oerr ora 25408
25408, 00000, "can not safely replay call"
// *Cause:  The connection was lost while doing this call. It may not be
//          safe to replay it after failover.
// *Action: Check to see if the results of the call have taken place, and then
//          replay it if desired.
//

ORA-25408
One may receive ORA-25408: can not safely replay call when using failover. This will depend on the failover type and on what you are executing.

AF only works for idle sessions and SELECT statements. The following operations will give an error (user program must restart the operation after fail-over):

PL/SQL program units - stored procedures, functions, packages
DML - INSERT, UPDATE, DELETE, SELECT … FOR UPDATE
DDL - CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, etc.

For example if you are doing an insert and the database goes down and failover occurs, “ORA-25408 can not safely replay call” is expected and the application should handle this exception and re-execute the insert.

If you are in a transaction and failover occurs you will also receive an error message and you have to handle this and issue a rollback.

In case you have configured failover to be of type SESSION, you will not able able to recover your selects either and you will need to replay them.

参考:
http://www.orafaq.com/wiki/ORA-25408