首页 » ORACLE 9i-23c » Troubleshooting ORA-01640: can not make tablespace read-only
Troubleshooting ORA-01640: can not make tablespace read-only
近期客户环境中有友商在测试XTTS时遇到了在把表空间修改为read only只读时遇到的ora-1640错误，说明与此表空间相关存在事务未结束，最终确认最在一个PREPARED状态的分布式事务，下面来分析一下这个案例。
SQL> select status from dba_tablespaces where tablespace_name='TBSANBOB'; STATUS --------- ONLINE
SQL> alter tablespace TBSANBOB read only;
alter tablespace TBSANBOB read only
*
ERROR at line 1:
ORA-01640: cannot make tablespace read-only with changes by in-doubt transactions
排查
SQL> select ses_addr,start_scnb,status,start_time from v$transaction;
SES_ADDR START_SCNB STATUS START_TIME
---------------- ---------- ---------------- --------------------
C0000017316AE768 1882952280 ACTIVE 04/15/20 10:13:25
C0000017212FE2E8 1884248324 ACTIVE 04/15/20 10:13:27
C000001731681C18 1884248831 ACTIVE 04/15/20 10:13:27
C0000017117BE1F0 1884245699 ACTIVE 04/15/20 10:13:25
C0000017511AF1B0 1882827786 ACTIVE 04/15/20 10:12:15
C0000017212FE2E8 1884249448 ACTIVE 04/15/20 10:13:27
-- 都是当前的事务
SQL> r
1* select ktuxeusn,ktuxeslt,ktuxesqn,ktuxesta,ktuxecfl,ktuxerdbf,ktuxerdbb,ktuxescnw*power(2,32)+ktuxescnb scsn from x$ktuxe where ktuxesta!='INACTIVE'
KTUXEUSN KTUXESLT KTUXESQN KTUXESTA KTUXECFL KTUXERDBF KTUXERDBB SCSN
--------------- --------------- --------------- ---------------- ------------------------ --------------- --------------- ---------------
365 24 1083834 ACTIVE NONE 219 441223 16692133545775
385 19 1122895 ACTIVE NONE 127 261485 16692133543395
390 29 2247829 ACTIVE NONE 164 207482 16692133545296
392 30 3884244 ACTIVE NONE 157 182080 16692133545691
1292 32 32481 ACTIVE NONE 169 25685 16692128674876
1432 23 7814970 PREPARED SCO|COL|REV|DEAD|EXTDTX 161 173121 16141570354765
1463 26 6047143 PREPARED SCO|COL|REV|DEAD|EXTDTX 165 353377 16158248618817
1465 22 8679947 PREPARED SCO|COL|REV|DEAD|EXTDTX 168 348144 16158248644802
--有几个相差很大的SCN事务，状态是PREPARED, “SCO|COL|REV|DEAD|EXTDTX”死分布式事务
SQL> select * from dba_2pc_pending;
no rows selected
SQL> select * from pending_trans$;
no rows selected
SQL> select thread#,FIRST_TIME,NEXT_TIME from v$archived_log where 16141570354765 between FIRST_CHANGE# and NEXT_CHANGE#;
no rows selected
-- 太久了，当前的控制文件中已经无该SCN对应的时间段
SQL> select un.name,inst#,un.status$,ktuxeusn,ktuxeslt,ktuxesqn,ktuxecfl from x$ktuxe tx,undo$ un where tx.ktuxeusn=un.us# and KTUXESTA='PREPARED';
NAME INST# STATUS$ KTUXEUSN KTUXESLT KTUXESQN KTUXECFL
------------------------------ --------------- --------------- --------------- --------------- --------------- ------------------------
_SYSSMU1432_1294752375$ 1 3 1432 23 7814970 SCO|COL|REV|DEAD|EXTDTX
_SYSSMU1463_478342203$ 1 3 1463 26 6047143 SCO|COL|REV|DEAD|EXTDTX
_SYSSMU1465_3879793806$ 1 3 1465 22 8679947 SCO|COL|REV|DEAD|EXTDTX
SQL> alter system dump undo block "_SYSSMU1432_1294752375$" xid 1432 23 7814970;
System altered.
-- trace file
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x778da1 0x000d 0x0f20.76aff895 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578759042
0x01 9 0x00 0x7788f0 0x000b 0x0f20.372d1827 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578629437
0x02 9 0x00 0x7780bf 0x0003 0x0f1f.d63f039c 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578413429
0x03 9 0x00 0x77869e 0x0016 0x0f1f.e6428916 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578456630
0x04 9 0x00 0x778ead 0x000c 0x0f20.e0d94272 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1579018252
0x05 9 0x00 0x77832c 0xffff 0x0f21.3400681a 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1579177888
0x06 9 0x00 0x778bbb 0x0008 0x0f21.3041efb0 0x2842a464 0x0000.000.00000000 0x00000009 0x00000000 1579174307
0x07 9 0x00 0x77896a 0x0005 0x0f21.32d4bd17 0x2842a466 0x0000.000.00000000 0x00000001 0x00000000 1579176644
0x08 9 0x00 0x7783d9 0x0007 0x0f21.304a7e67 0x2842a466 0x0000.000.00000000 0x00000001 0x00000000 1579174338
0x09 9 0x00 0x772168 0x0001 0x0f20.2113e173 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578586235
0x0a 9 0x00 0x7778a7 0x001e 0x0f21.08e75ed5 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1579104656
0x0b 9 0x00 0x7791c6 0x0014 0x0f20.4bad4025 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578672639
0x0c 9 0x00 0x778395 0x000a 0x0f20.f48d655b 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1579061454
0x0d 9 0x00 0x777b94 0x0015 0x0f20.8895e09d 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578802244
0x0e 9 0x00 0x7789c3 0x0011 0x0f21.3041ef6d 0x2842a460 0x0000.000.00000000 0x00000003 0x00000000 1579174307
0x0f 9 0x00 0x778102 0x0020 0x0f20.ad3034c5 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578888647
0x10 9 0x00 0x778c01 0x0006 0x0f21.3041ef9b 0x2842a466 0x0000.000.00000000 0x00000003 0x00000000 1579174307
0x11 9 0x00 0x7777f0 0x0010 0x0f21.3041ef8e 0x2842a463 0x0000.000.00000000 0x00000003 0x00000000 1579174307
0x12 9 0x00 0x77827f 0x000e 0x0f21.3041ef67 0x2842a45d 0x0000.000.00000000 0x00000003 0x00000000 1579174307
0x13 9 0x00 0x77859e 0x0002 0x0f1f.c46b7edb 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578370226
0x14 9 0x00 0x77855d 0x0000 0x0f20.63db4a3b 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578715840
0x15 9 0x00 0x77825c 0x000f 0x0f20.9b4f8205 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578845445
0x16 9 0x00 0x77898b 0x001a 0x0f1f.f85d787c 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578499832
0x17 2 0xb7 0x773f3a 0x0002 0x0eae.40912e4d 0x2842a441 0x0000.000.00000000 0x00000001 0x2842a441 0
0x18 9 0x00 0x778799 0x0021 0x0f21.3041ef45 0x2842a451 0x0000.000.00000000 0x00000003 0x00000000 1579174307
0x19 9 0x00 0x778a78 0x001b 0x0f21.3041ef58 0x2842a457 0x0000.000.00000000 0x00000003 0x00000000 1579174307
0x1a 9 0x00 0x777fe7 0x0009 0x0f20.0a7f57c1 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578543033
0x1b 9 0x00 0x778876 0x0012 0x0f21.3041ef61 0x2842a45a 0x0000.000.00000000 0x00000003 0x00000000 1579174307
0x1c 9 0x00 0x778365 0x0018 0x0f21.3041ef3d 0x2842a44e 0x0000.000.00000000 0x00000003 0x00000000 1579174307
0x1d 9 0x00 0x778d94 0x0004 0x0f20.ce6b7484 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578975051
0x1e 9 0x00 0x7768b3 0x001c 0x0f21.20c7c4eb 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1579147858
0x1f 9 0x00 0x778522 0x0013 0x0f1f.b43cc365 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578327025
0x20 9 0x00 0x777bb1 0x001d 0x0f20.be1c5448 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1578931849
0x21 9 0x00 0x773830 0x0019 0x0f21.3041ef4f 0x2842a454 0x0000.000.00000000 0x00000003 0x00000000 1579174307
EXT TRN CTL::
# state
State 9 means the transaction is invactive. 10 means active transaction. 2 means prepare transaction.
# slot 0x17 = xid 23
SQL> @dec 23
DEC HEX
----------------------------------- --------------------
23.000000 17
# undo block adress 0x2842a441 , to convert undo file and block,dump undo block
*-----------------------------
* Rec #0x3 slt: 0x17 objn: 20900(0x000051a4) objd: 65237 tblspc: 34(0x00000022)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 6
flg2: 1
*-----------------------------
uba: 0x2842a441.df12.01 ctl max scn: 0x0eae.3f816f42 prv tx scn: 0x0eae.3f94ad28
txn start scn: scn: 0x0eae.40912e37 logon user: 32
prev brb: 675456054 prev bcl: 0
Global transid: fmt: 0x00000028 len: 44 gtrid: 716d797933313630310000000000000000000000000000000000000000000000a9d206006707f55b1db00a00
proxy_id: 0x0598.017.00773f3a
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x5db44e2b hdba: 0x2c0a2212
itli: 11 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0
ncol: 13 nnew: 1 size: 6
col 2: [12] 55 4e 43 48 45 43 4b 5f 48 41 4c 4c
+++++++++++ Next block not in extent map - rollback segment has been shrunk.
+ WARNING + Block dba (file#, block#): 0,0x00000000
+++++++++++
SQL> select utl_raw.CAST_TO_VARCHAR2('554e434845434b5f48414c4c') V from dual;
V
-----------
UNCHECK_HALL
清理分布式事务
-- 以其中一个为例
SQL> rollback force '1432.23.7814970';
rollback force '1432.23.7814970'
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID 1432.23.7814970
SQL> exec dbms_transaction.PURGE_LOST_DB_ENTRY('1432.23.7814970');
BEGIN dbms_transaction.PURGE_LOST_DB_ENTRY('1432.23.7814970'); END;
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 87
ORA-06512: at line 1
SQL> alter system disable distributed recovery;
System altered.
QL> insert into pending_trans$(local_tran_id,global_tran_fmt,global_oracle_id,state,status,session_vector,reco_vector,type#,fail_time,reco_time)values('1432.23.7814970',306206,'','prepared','P',hextoraw('00000001'),hextoraw('00000000'),0,sysdate,sysdate);
1 row created.
SQL> insert into pending_sessions$ values('1432.23.7814970',1,hextoraw('05004F003A1500000104'),'C',0,30258592,'',146);
1 row created.
-- if pending_trans$.global_oracle_id is null
SQL> commit force '1432.23.7814970';
commit force '1432.23.7814970'
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [18124], [], [], [], [], [], [], [], [], [], [], []
SQL> delete pending_trans$;
1 row deleted.
SQL> insert into pending_trans$(local_tran_id,global_tran_fmt,global_oracle_id,state,status,session_vector,reco_vector,type#,fail_time,reco_time)values('1432.23.7814970',306206,'xxxxx.12345.1.2.3','prepared','P',hextoraw('00000001'),hextoraw('00000000'),0,sysdate,sysdate);
1 row created.
SQL> commit;
SQL> rollback force '1432.23.7814970';
Rollback complete.
-- repeat above
SQL> delete pending_trans$; ---NOTE: where local_tran_id
3 rows deleted.
SQL> delete pending_sessions$; ---NOTE: where local_tran_id
3 rows deleted.
SQL> commit;
Commit complete.
SQL> alter tablespace TBSANBOB read only;
Tablespace altered.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。