[20181031]模拟ora-01591错误.txt

--//分布式事务使用dblink更新多个数据库的事务,最常见的ORA-01591错误.

$ oerr ora 1591

01591, 00000, "lock held by in-doubt distributed transaction %s"

// *Cause:  Trying to access resource that is locked by a dead two-phase commit

//          transaction that is in prepared state.

// *Action: DBA should query the pending_trans$ and related tables, and attempt

//          to repair network connection(s) to coordinator and commit point.

//          If timely repair is not possible, DBA should contact DBA at commit

//          point if known or end user for correct outcome, or use heuristic

//          default if given to issue a heuristic commit or abort command to

//          finalize the local portion of the distributed transaction.

--//通过简单的模拟产生这个错误.

1.环境:

--//客户端:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create PUBLIC database link test033 connect to scott identified by btbtms using '192.168.100.33:1521/test:DEDICATED';

Database link created.

SCOTT@book> create table deptx as select * from deptx;

Table created.

SCOTT@book> select sysdate from dual@test033;

SYSDATE

-------------------

2018-10-31 08:55:34

--//OK,连接没有问题.

SCOTT@book> commit;

Commit complete.

--//服务端:

SCOTT@test> @ &r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- ----------------------------------------------------------------

x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SCOTT@test> create table depty as select * from dept;

Table created.

SCOTT@book> select * from deptx;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

SCOTT@book> select * from depty@test033;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

2.一些解析与说明:

--//这次不再使用alter system disable distributed recovery;.

--//"COMMIT COMMENT 'ORA-2PC-CRASH-TEST-N'"这个命令，是Oracle用来强制分布式事务失败，4代表非提交点的分布式事务失败在

--//prepare后。其它数值代表的意思如下：

1 Crash commit point after collect

2 Crash non-commit-point site after collect

3 Crash before prepare (non-commit-point site)

4 Crash after prepare (non-commit-point site)

5 Crash commit point site before commit

6 Crash commit point site after commit

7 Crash non-commit-point site before commit

8 Crash non-commit-point site after commit

9 Crash commit point site before forget

10 Crash non-commit-point site before forget

--//实际上COMMIT COMMENT 'ORA-2PC-CRASH-TEST-7'就应该模拟出来,测试看看.

3.测试:

SCOTT@book> update deptx         set loc = lower(loc) where deptno=10;

1 row updated.

SCOTT@book> update depty@test033 set loc = lower(loc) where deptno=20;

1 row updated.

SCOTT@book> COMMIT COMMENT 'ORA-2PC-CRASH-TEST-7';

COMMIT COMMENT 'ORA-2PC-CRASH-TEST-7'

*

ERROR at line 1:

ORA-02054: transaction 9.30.2968 in-doubt

ORA-02059: ORA-2PC-CRASH-TEST-7 in commit comment

SCOTT@book> @ &r/xid

XIDUSN_XIDSLOT_XIDSQN

------------------------------

no rows selected

--//已经没有事务.

SCOTT@book> select local_tran_id, GLOBAL_TRAN_ID, state from dba_2pc_pending;

no rows selected

SCOTT@book> select * from deptx where deptno=10;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     new york

SCOTT@book> select * from depty@test033 where deptno=20;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        20 RESEARCH       dallas

--//这种方式不行.换1个方式测试.

4.测试连接异常中断:

--//客户端:

SCOTT@book> @ &r/xid

XIDUSN_XIDSLOT_XIDSQN

------------------------------

no rows selected

SCOTT@book> update deptx         set loc = upper(loc) where deptno=10;

1 row updated.

SCOTT@book> update depty@test033 set loc = upper(loc) where deptno=20;

1 row updated.

SCOTT@book> @ &r/spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50

---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------

       274         19 28023                    DEDICATED 28024       21          8 alter system kill session '274,19' immediate;

--//当前连接进程号=28024.

--//服务端: (我以前提过v$session.process记录的是客户端进程号.)

SCOTT@test> select spid,program from v$process where addr in (select paddr from v$session where process=28024);

SPID   PROGRAM

------ --------------------------

12619  oracle@xxxx

# lsof -P -i -n |grep -e 12619 -e COMMAND

COMMAND     PID    USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME

oracle    12619  oracle   14u  IPv4 28066050      0t0  TCP 192.168.100.33:1521->192.168.100.78:16778 (ESTABLISHED)

--//也可以对上.

# kill -9 12619

SCOTT@book> commit ;

commit

*

ERROR at line 1:

ORA-02054: transaction 10.2.24501 in-doubt

ORA-03150: end-of-file on communication channel for database link

ORA-02063: preceding line from TEST033

SCOTT@book> @ &r/xid

XIDUSN_XIDSLOT_XIDSQN

------------------------------

no rows selected

SCOTT@book> select local_tran_id, GLOBAL_TRAN_ID, state from dba_2pc_pending;

no rows selected

--//这样也不行.

5.测试(服务端数据库异常关闭)

SCOTT@book>  @ &r/xid

XIDUSN_XIDSLOT_XIDSQN

------------------------------

no rows selected

SCOTT@book> update deptx         set loc = upper(loc) where deptno=10;

1 row updated.

SCOTT@book> update depty@test033 set loc = upper(loc) where deptno=20;

1 row updated.

--//在服务端异常关闭:

SYS@test> shutdown abort ;

ORACLE instance shut down.

--//补充说明:正常关闭shutdown immediate也是一样能演示这个问题.

SCOTT@book> commit ;

commit

*

ERROR at line 1:

ORA-02054: transaction 10.23.24502 in-doubt

ORA-03150: end-of-file on communication channel for database link

ORA-02063: preceding line from TEST033

SCOTT@book> select local_tran_id, GLOBAL_TRAN_ID, state from dba_2pc_pending;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID            STATE

---------------------- ------------------------- ----------------

10.23.24502            BOOK.e6127bf4.10.23.24502 prepared

--//OK,这次模拟出来了.

SCOTT@book> update deptx         set loc = upper(loc) where deptno=10;

update deptx         set loc = upper(loc) where deptno=10

*

ERROR at line 1:

ORA-01591: lock held by in-doubt distributed transaction 10.23.24502

--//重新启动服务端:

SYS@test> startup

ORACLE instance started.

Total System Global Area  486539264 bytes

Fixed Size                  2084872 bytes

Variable Size             360714232 bytes

Database Buffers          113246208 bytes

Redo Buffers               10493952 bytes

Database mounted.

Database opened.

SCOTT@test> select local_tran_id, GLOBAL_TRAN_ID, state from dba_2pc_pending;

no rows selected

SCOTT@test> select * from depty where deptno=20;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        20 RESEARCH       dallas

--//客户端:

SCOTT@book> select local_tran_id, GLOBAL_TRAN_ID, state from dba_2pc_pending;

no rows selected

---//可以发现只要服务端数据库起来,这个问题自然消失,自己rollback.

--//再次重复这个测试(过程略):

SCOTT@book> select local_tran_id, GLOBAL_TRAN_ID, state from dba_2pc_pending;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID          STATE

---------------------- ----------------------- ----------------

5.21.1953              BOOK.e6127bf4.5.21.1953 prepared

--//解决ora-01591错误:我个人总喜欢先尝试提交:

SCOTT@book> commit force '5.21.1953';

Commit complete.

SCOTT@book> select local_tran_id, GLOBAL_TRAN_ID, state from dba_2pc_pending;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID          STATE

---------------------- ----------------------- ----------------

5.21.1953              BOOK.e6127bf4.5.21.1953 forced commit

--//现在修改已经没有问题:

SCOTT@book> update deptx         set loc = upper(loc) where deptno=10;

1 row updated.

SCOTT@book> commit ;

Commit complete.

SCOTT@book> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.21.1953')

BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.21.1953'); END;

*

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_TRANSACTION", line 97

ORA-06512: at line 1

--//权限不够.

SYS@book> set transaction use rollback segment SYSTEM;

Transaction set.

SYS@book>  exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.21.1953');

PL/SQL procedure successfully completed.

SCOTT@book> select local_tran_id, GLOBAL_TRAN_ID, state from dba_2pc_pending;

no rows selected

--//不过再不行,参照网上的解决方法,实际上删除一些基表的内容:

set transaction use rollback segment system;

--delete from dba_2pc_pending where local_tran_id = '1.34.240088';

delete from sys.pending_trans$ where local_tran_id ='1.34.240088';

delete from pending_sessions$ where local_tran_id = '1.34.240088';

delete from pending_sub_sessions$ where local_tran_id = '1.34.240088';

commit;

--//不行,还给人为pending_trans$插入记录.网上有详细的介绍.