oracle警告日志报错transaction recovery问题，对回滚段进行dump，查询出异常的对象

本文链接：https://blog.youkuaiyun.com/weixin_41702082/article/details/81324751

本文介绍了一种通过Oracle数据库内部视图和工具进行故障排查的方法，包括如何定位未完成的事务、确定涉及的对象及其所在表空间，并进一步分析这些对象的空间使用情况。

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

select b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks,
a.ktuxesta txstatus
from x$ktuxe a, undo$ b
where a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

USEG INSTID STATUS XID_USN XID_SLOT XID_SEQ UNDOBLOCKS TXSTATUS
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------------
_SYSSMU176_2201824257$ 2 3 176 21 204 3 ACTIVE

ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU176_2201824257$';

select * from v$diag_info WHERE name='Default Trace File';
INST_ID NAME VALUE
---------- ------------------------------- --------------------------------
2 Default Trace File /oracle/11.2.0/db/diag/rdbms/icare/icare2/trace/icare2_ora_32646.trc

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x15 10 0x90 0x00cc 0x0002 0x0004.9b797d7f 0x23805ee1 0x0000.000.00000000 0x00000003 0x23805ede 0

Definitions:
State#10 means active transaction.
dba points to starting UNDO block address.
usn: Undo segment number
usn.index.wrap# gives transaction id.
Comment:
根据dba一列去推算
select to_number('23805ee1','xxxxxxxxxxxxx') from dual;
TO_NUMBER('23805EE1','XXXXXXXXXXXXX')
-------------------------------------
595615457

----------------------------
fileID: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(595615457) from x$dual;
blockID:select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(595615457) from x$dual;
alter system dump datafile <fileID> block <blockID>;
----------------------------

SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(595615457) from x$dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(595615457)
-----------------------------------------------
142

SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(595615457) from x$dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(595615457)
------------------------------------------------
24289

alter system dump datafile 142 block 24289;

less *new.trc| grep objn
dbwrid: 2 obj: -1 objn: 0 tsn: 14 afn: 142 hint: f
* Rec #0x1 slt: 0x15 objn: 93442(0x00016d02) objd: 539261 tblspc: 5(0x00000005)
* Rec #0x2 slt: 0x15 objn: 96798(0x00017a1e) objd: 517352 tblspc: 5(0x00000005)
* Rec #0x3 slt: 0x15 objn: 122959(0x0001e04f) objd: 517358 tblspc: 5(0x00000005)
* Rec #0x4 slt: 0x15 objn: 213371(0x0003417b) objd: 517355 tblspc: 5(0x00000005)
* Rec #0x5 slt: 0x15 objn: 486646(0x00076cf6) objd: 517361 tblspc: 5(0x00000005)
* Rec #0x6 slt: 0x15 objn: 26187(0x0000664b) objd: 495340 tblspc: 9(0x00000009)
* Rec #0x7 slt: 0x15 objn: 26188(0x0000664c) objd: 517207 tblspc: 5(0x00000005)
* Rec #0x8 slt: 0x15 objn: 26189(0x0000664d) objd: 517210 tblspc: 5(0x00000005)
* Rec #0x9 slt: 0x15 objn: 26192(0x00006650) objd: 517216 tblspc: 5(0x00000005)
* Rec #0xa slt: 0x15 objn: 26193(0x00006651) objd: 517230 tblspc: 5(0x00000005)
* Rec #0xb slt: 0x15 objn: 26194(0x00006652) objd: 517251 tblspc: 5(0x00000005)
* Rec #0xc slt: 0x15 objn: 26195(0x00006653) objd: 517254 tblspc: 5(0x00000005)
* Rec #0xd slt: 0x15 objn: 26196(0x00006654) objd: 517257 tblspc: 5(0x00000005)
* Rec #0xe slt: 0x15 objn: 26197(0x00006655) objd: 517334 tblspc: 5(0x00000005)
* Rec #0xf slt: 0x15 objn: 26198(0x00006656) objd: 517337 tblspc: 5(0x00000005)
* Rec #0x10 slt: 0x15 objn: 76674(0x00012b82) objd: 517349 tblspc: 5(0x00000005)
* Rec #0x11 slt: 0x15 objn: 80542(0x00013a9e) objd: 517346 tblspc: 5(0x00000005)
* Rec #0x12 slt: 0x15 objn: 87396(0x00015564) objd: 517343 tblspc: 5(0x00000005)
* Rec #0x13 slt: 0x15 objn: 93442(0x00016d02) objd: 539261 tblspc: 5(0x00000005)
* Rec #0x14 slt: 0x15 objn: 96798(0x00017a1e) objd: 517352 tblspc: 5(0x00000005)
* Rec #0x15 slt: 0x15 objn: 122959(0x0001e04f) objd: 517358 tblspc: 5(0x00000005)
* Rec #0x16 slt: 0x15 objn: 213371(0x0003417b) objd: 517355 tblspc: 5(0x00000005)
* Rec #0x17 slt: 0x15 objn: 486646(0x00076cf6) objd: 517361 tblspc: 5(0x00000005)
* Rec #0x18 slt: 0x15 objn: 26187(0x0000664b) objd: 495340 tblspc: 9(0x00000009)
* Rec #0x19 slt: 0x15 objn: 26188(0x0000664c) objd: 517207 tblspc: 5(0x00000005)
* Rec #0x1a slt: 0x15 objn: 26189(0x0000664d) objd: 517210 tblspc: 5(0x00000005)
* Rec #0x1b slt: 0x15 objn: 26192(0x00006650) objd: 517216 tblspc: 5(0x00000005)
* Rec #0x1c slt: 0x15 objn: 26193(0x00006651) objd: 517230 tblspc: 5(0x00000005)
* Rec #0x1d slt: 0x15 objn: 26194(0x00006652) objd: 517251 tblspc: 5(0x00000005)
* Rec #0x1e slt: 0x15 objn: 26195(0x00006653) objd: 517254 tblspc: 5(0x00000005)
* Rec #0x1f slt: 0x15 objn: 26196(0x00006654) objd: 517257 tblspc: 5(0x00000005)
* Rec #0x20 slt: 0x15 objn: 26197(0x00006655) objd: 517334 tblspc: 5(0x00000005)
* Rec #0x21 slt: 0x15 objn: 26198(0x00006656) objd: 517337 tblspc: 5(0x00000005)
* Rec #0x22 slt: 0x15 objn: 76674(0x00012b82) objd: 517349 tblspc: 5(0x00000005)
* Rec #0x23 slt: 0x15 objn: 80542(0x00013a9e) objd: 517346 tblspc: 5(0x00000005)
* Rec #0x24 slt: 0x15 objn: 87396(0x00015564) objd: 517343 tblspc: 5(0x00000005)
* Rec #0x25 slt: 0x15 objn: 93442(0x00016d02) objd: 539261 tblspc: 5(0x00000005)
* Rec #0x26 slt: 0x15 objn: 96798(0x00017a1e) objd: 517352 tblspc: 5(0x00000005)
* Rec #0x27 slt: 0x15 objn: 122959(0x0001e04f) objd: 517358 tblspc: 5(0x00000005)
* Rec #0x28 slt: 0x15 objn: 213371(0x0003417b) objd: 517355 tblspc: 5(0x00000005)
* Rec #0x29 slt: 0x15 objn: 486646(0x00076cf6) objd: 517361 tblspc: 5(0x00000005)
* Rec #0x2a slt: 0x15 objn: 538641(0x00083811) objd: 538641 tblspc: 15(0x0000000f)
* Rec #0x2b slt: 0x15 objn: 69762(0x00011082) objd: 494192 tblspc: 9(0x00000009)
* Rec #0x2c slt: 0x15 objn: 69763(0x00011083) objd: 517156 tblspc: 5(0x00000005)
* Rec #0x2d slt: 0x15 objn: 69764(0x00011084) objd: 517159 tblspc: 5(0x00000005)
* Rec #0x2e slt: 0x15 objn: 69765(0x00011085) objd: 517162 tblspc: 5(0x00000005)
* Rec #0x2f slt: 0x15 objn: 69766(0x00011086) objd: 517165 tblspc: 5(0x00000005)
* Rec #0x30 slt: 0x15 objn: 69767(0x00011087) objd: 517168 tblspc: 5(0x00000005)
* Rec #0x31 slt: 0x15 objn: 69768(0x00011088) objd: 517171 tblspc: 5(0x00000005)
* Rec #0x32 slt: 0x15 objn: 82564(0x00014284) objd: 517174 tblspc: 5(0x00000005)
* Rec #0x33 slt: 0x15 objn: 86774(0x000152f6) objd: 517180 tblspc: 5(0x00000005)
* Rec #0x34 slt: 0x15 objn: 94183(0x00016fe7) objd: 517192 tblspc: 5(0x00000005)
* Rec #0x35 slt: 0x15 objn: 99292(0x000183dc) objd: 517177 tblspc: 5(0x00000005)
* Rec #0x36 slt: 0x15 objn: 106345(0x00019f69) objd: 517189 tblspc: 5(0x00000005)
* Rec #0x37 slt: 0x15 objn: 278461(0x00043fbd) objd: 517198 tblspc: 5(0x00000005)
* Rec #0x38 slt: 0x15 objn: 294969(0x00048039) objd: 517186 tblspc: 5(0x00000005)
* Rec #0x39 slt: 0x15 objn: 352936(0x000562a8) objd: 517201 tblspc: 5(0x00000005)
* Rec #0x3a slt: 0x15 objn: 356970(0x0005726a) objd: 517204 tblspc: 5(0x00000005)
* Rec #0x3b slt: 0x15 objn: 69762(0x00011082) objd: 494192 tblspc: 9(0x00000009)
* Rec #0x3c slt: 0x15 objn: 71638(0x000117d6) objd: 71638 tblspc: 9(0x00000009)
* Rec #0x3d slt: 0x15 objn: 26187(0x0000664b) objd: 495340 tblspc: 9(0x00000009)
* Rec #0x3e slt: 0x15 objn: 26188(0x0000664c) objd: 517207 tblspc: 5(0x00000005)
* Rec #0x3f slt: 0x15 objn: 26189(0x0000664d) objd: 517210 tblspc: 5(0x00000005)

select owner,object_type,object_name from dba_objects where object_id in (93442,96798,122959,213371,486646,26187,26188,26189,26192,26193,26194,26195,26196,26197,26198,76674,80542,87396,93442,96798,122959,213371,486646,26187,26188,26189,26192,26193,26194,26195,26196,26197,26198,76674,80542,87396,93442,96798,122959,213371,486646,538641,69762,69763,69764,69765,69766,69767,69768,82564,86774,94183,99292,106345,278461,294969,352936,356970,69762,71638,26187,26188,26189) order by object_type,object_name;

OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------------------ ------------------------------
ICARE INDEX IDX_ISPUT
ICARE INDEX IDX_PATIENTCHARGE_02
ICARE INDEX IDX_PATIENTCHARGE_ACTIVEDAT
ICARE INDEX IDX_PATIENTCHARGE_ORDEREXECID
ICARE INDEX IDX_PATIENTCHARGE_ORDERID_CHR
ICARE INDEX IDX_PATIENTCHARGE_PCHARGEIDORG
ICARE INDEX IDX_PATIENTCHARGE_PSTATUS
ICARE INDEX IDX_PATIENTCHARGE_REGISTERID
ICARE INDEX IDX_PATIENT_UNITPRICE
ICARE INDEX IDX_PCHARGE_CREATDAT
ICARE INDEX IDX_PMD_AREAPUTINDEX
ICARE INDEX IDX_PMD_ORDEREXECID
ICARE INDEX IDX_PMD_ORIGPUTMEDDETAIL
ICARE INDEX IDX_PUTMEDDETAIL_AREAID
ICARE INDEX IDX_PUTMEDDETAIL_CREATEDATE
ICARE INDEX IDX_PUTMEDDETAIL_PUTSEQ
ICARE INDEX IDX_PUTMEDDETAIL_RETURNBILLNO
ICARE INDEX IDX_PUTMED_ORDERID
ICARE INDEX INX_PUTDATE
ICARE INDEX I_IDX_BPC_CHARGEITEMID
ICARE INDEX I_IDX_BPC_RETURNMEDBILLNO
ICARE INDEX I_IDX_CALCCATEID
ICARE INDEX I_IDX_CLACAREA
ICARE INDEX I_IDX_DOSETYPEID_CHR
ICARE INDEX I_IDX_OBP_CHARGEDOCTORID
ICARE INDEX I_IDX_OP_REGID
ICARE INDEX I_IDX_PATIENTCHARGE_INHOSFLAG
ICARE INDEX I_IDX_PMD_PCHARGEID
ICARE INDEX I_IDX_PUTMEDDETAIL_1
ICARE INDEX I_INX_PATIENTCHARGE_STATUS_INT
ICARE INDEX PK_BIH_OPR_PUTMEDDETAIL
ICARE INDEX XPKT_OPR_BIH_PATIENTCHARGE
ICARE TABLE T_BIH_OPR_PUTMEDDETAIL
ICARE TABLE T_OPR_BIH_ORDER
ICARE TABLE T_OPR_BIH_ORDERSTOPAPPLY
ICARE TABLE T_OPR_BIH_PATIENTCHARGE

select distinct table_name from dba_indexes where index_name in ('IDX_ISPUT','IDX_PATIENTCHARGE_02','IDX_PATIENTCHARGE_ACTIVEDAT','IDX_PATIENTCHARGE_ORDEREXECID','IDX_PATIENTCHARGE_ORDERID_CHR','IDX_PATIENTCHARGE_PCHARGEIDORG','IDX_PATIENTCHARGE_PSTATUS','IDX_PATIENTCHARGE_REGISTERID','IDX_PATIENT_UNITPRICE','IDX_PCHARGE_CREATDAT','IDX_PMD_AREAPUTINDEX','IDX_PMD_ORDEREXECID','IDX_PMD_ORIGPUTMEDDETAIL','IDX_PUTMEDDETAIL_AREAID','IDX_PUTMEDDETAIL_CREATEDATE','IDX_PUTMEDDETAIL_PUTSEQ','IDX_PUTMEDDETAIL_RETURNBILLNO','IDX_PUTMED_ORDERID','INX_PUTDATE','I_IDX_BPC_CHARGEITEMID','I_IDX_BPC_RETURNMEDBILLNO','I_IDX_CALCCATEID','I_IDX_CLACAREA','I_IDX_DOSETYPEID_CHR','I_IDX_OBP_CHARGEDOCTORID','I_IDX_OP_REGID','I_IDX_PATIENTCHARGE_INHOSFLAG','I_IDX_PMD_PCHARGEID','I_IDX_PUTMEDDETAIL_1','I_INX_PATIENTCHARGE_STATUS_INT','PK_BIH_OPR_PUTMEDDETAIL','XPKT_OPR_BIH_PATIENTCHARGE');

TABLE_NAME
------------------------------
T_BIH_OPR_PUTMEDDETAIL
T_OPR_BIH_PATIENTCHARGE

select segment_name,sum(bytes)/1024/1024 from dba_segments where segment_name in ('T_BIH_OPR_PUTMEDDETAIL','T_OPR_BIH_ORDER','T_OPR_BIH_ORDERSTOPAPPLY','T_OPR_BIH_PATIENTCHARGE') group by segment_name order by 2;

SEGMENT_NAME SUM(BYTES)/1024/1024
--------------------------------------------------------------------------------- --------------------
T_OPR_BIH_ORDERSTOPAPPLY 76
T_BIH_OPR_PUTMEDDETAIL 5400
T_OPR_BIH_ORDER 7763
T_OPR_BIH_PATIENTCHARGE 36158