Subject: Rollback, Transactions, and Logical Inconsistencies Type: BULLETIN Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 05-NOV-1999 Last Revision Date: 12-NOV-1999 Language: USAENG PURPOSE You encounter a situation where a certain Rollback segment (RBS) cannot be dropped due to the presence of referencing one or more pending transactions. Quite often this is an issue with distributed database. Sometimes, remote sites lose references to distributed transactions due to manual operations, and thus, some nodes in the session tree need manual recovery. On some occasions it may be necessary to force the offline status of certain RBS containing active dangling transactions that we cannot manage with standard methods. Forcing the dropping of RBSs introduces logical inconsistencies into the database. Logical inconsistencies may have serious consequences, and such an action must only occur as a last resort. SCOPE & APPLICATION The scope of this article is to suggest a way to localize and possibly recover from this kind of corruption. RELATED DOCUMENTS V7: ROLLBACK SEGMENT CORRUPTION - HOW TO RECOVER ORA-2019 ORA-2058 ORA-2068 ORA-2050: FAILED DISTRIBUTED TRANSACTIONS [NOTE:29733.1] QREF: REDO Opcode - Quick Reference ORA-1578 and Data Block Corruption in Oracle7 ODBA - Calculating DBA, File#, Block# and ROWIDs [NOTE:69464.1] ROLLBACK SEGMENT CONFIGURATION & TIPS Oracle7 Server Distributed Systems Manual Volume 1 Oracle7 Server Distributed Systems Manual Volume 2 How to estimate the impact of database logical inconsistencies introduced by forcing offline active rollback segments: Analysis: ========= Logical inconsistencies are hard to detect, and are not shown using database administration tools. Logical inconsistencies are only detectable by understanding the logical design of the database, application, and analysis of the data in the schemas, using the business rules of the application. Detecting and restoring logical inconsistencies in a database can be very expensive. Actions: ======== Identify OBJECTS and DATA referenced in dangling active transaction contained in an un-droppable RBS. In this note, we present a practical example using a split screen layout, in order to show commands and output. This example was tested using Sun Solaris version 2.6 and Oracle RDBMS version 7.3.4.3. SYS Session |SCOTT Session -------------------------------------------|-------------------------------- | select * from emp | where empno=7499; | | EMPNO ENAME JOB | ---------- ---------- --------- | 7499 ALLEN SALESMAN | | | update emp | set ename='RICCARDO' | where empno=7499; | | EMPNO ENAME JOB | ---------- ---------- --------- +)let's show RBS involved in this update | 7499 RICCARDO SALESMAN | select name,xacts, waits | from v$rollstat s, v$rollname n |-------------------------------- where s.usn=n.usn; | | | NAME XACTS WAITS | ------------------ ---------- | SYSTEM 0 0 | R01 0 0 | R02 0 0 | R03 1 0 <-note | R04 0 0 | | +)let's se who use wich RBS | | SELECT r.name "RB NAME ", | p.pid "ORACLE PID", | p.spid "SYSTEM PID ", | NVL(p.username,'NO TRANSACTION') | FROM v$lock l, v$process p, | v$rollname r | WHERE l.sid = p.pid(+) | AND TRUNC (l.id1(+)/65536) = r.usn | AND l.type(+) = 'TX' | AND l.lmode(+) = 6 | ORDER BY r.name; | | RB NAME PID SYSTEM PI NVL(P.USERNAME | -------- --- --------- -------------- | R01 NO TRANSACTION | R02 NO TRANSACTION | R03 7 9338 oracle | R04 NO TRANSACTION | SYSTEM NO TRANSACTION | | | +) and now get 'R03' Segment header | | select header_file, header_block | from dba_segments | where segment_name = 'R03'; | | HEADER_FIL HEADER_BLO | ---------- ---------- | 2 4102 | | +)we can use WebIV tool to | perform conversion | and obtain the required DBA : | | | to compute the DBA of the Oracle | block that contains the segment | header click on 'convert' | (Sun Solaris/Oracle V7) _______ | 2 4102 |CONVERT| | V SPLIT DBA =File#,Block ------- | = ===== === ============ |0 V7 6,10 13422183= 2,4102 | | | <-----------------------------------------------| +)perform database block dump | of computed DBA | | alter session set events | 'immediate trace name | blockdump level 134221830'; | | (rb_03_header_file.trc) | (file will go in USER_DUMP_DEST) | (...) | TRN TBL:: | +)identify the TRANSACTION TABLE | in the trace file |index state dba and analyze it . |------------------------ | 0x00 9 0x08001595 | 0x01 9 0x080015ab Transaction Table | 0x02 9 0x080015cc state 0 = IDLE | 0x03 9 0x080015b4 1 = Collecting | 2 = Prepared | 0x04 9 0x080015ca 3 = Committed | 0x05 9 0x0800158c 4 = Forced Abort | 5 = Forced Commit | 0x06 9 0x080015bf 6 = Forced Mixed | 0x07 9 0x08001589 7 = try again later | 0x08 9 0x080015c6 9 = No TX (Committed) | 0x09 9 0x080015cc 10= Active local TX | 0x0a 9 0x0800158d cflags 1 = TX has started storing | 0x0b 9 0x080015b2 collecting information | 0x0c 9 0x080015c4 | 0x0d 9 0x080015c5 2 = TX has forced the coll. | 0x0e 9 0x080015b6 information | 0x0f 9 0x080015c9 4 = Prepared TX needs | 0x10 9 0x080015cb distributed recovery | 0x11 9 0x080015ca 10= Rollback failed on this TX | 0x12 9 0x080015ca mark SMON for recover | 0x13 9 0x0800159b 20= TX has rolled back its up | 0x14 9 0x080015c9 wrap# is incremented TX slot reuse. | 0x15 9 0x080015cb uel ? | 0x16 9 0x080015c5 scn SCN for the TX prepare / commit | 0x17 9 0x080015ca dba is DBA of HEAD of the REDO - | 0x18 9 0x080015cb Ie: The MOST RECENT CHANG | 0x19 9 0x080015a3 | 0x1a 9 0x080015c9 --- 0x1b 10 0x080015cc | | 0x1c 9 0x080015c9 | | 0x1d 9 0x080015b0 The 'state' is <----------- | 0x1e 9 0x080015cc '10' -> Active Local Tran | 0x1f 9 000 0x00000 | | +) once identified our transaction, | we can continue with a block | dump of the interesting | section of RBS | | (HEX DBA) (DEC DBA) | 0x080015cc -> become -> 134223308 | | +) now we can proceed with specific | block(s) dump. We know that it will | contain 'undo' |(note that this dump will be associated with active transaction | appended to the previous | trace if you use the same session) alter session set events | 'immediate trace name | blockdump level 134223308'; | | | |(head of block dump of RBS 'R03') +) analyze various UNDO records | ************************ (...) | UNDO BLK: slt: TX slot in UNDO seg hdr | xid: 0x0006.01b.0000003a obj: Object this record affects | tsn: Tablespace Number this affects | Rec Offset Rec Off opc: OpCode (See [NOTE:29733.1] | ------------------------ dba: This is the DBA of the PREVIOUS | 0x01 0x077a 0x02 0x0 (...) undo block for this TX. | 0x06 0x0595 0x07 0x0 Zero DBA implies this is 1 undo | 0x0b 0x035e 0x0c 0x0 block for TX (ie:last undo to | be applied). | *----------------------- | * Rec #0x1 slt: 0x09 o Analyzing the information | * Layer: 11 (Row) from the undo: | *----------------------- ------------------------- | uba: 0x080015cb.0003.07 | KDO undo record: If the transactions are all marked as | KTB Redo, op: L itl: xi committed, flg:C---, or C-U-, | fl then there are no uncommitted | KDO Op code: QMD xtype: transactions. | itli: 1 ispac: 0 maxfr Drop the rollback | tabn: 0 lock: 0 nrow: 1 segment. DROP ROLLBACK SEGMENT name; | slot[0]: 56 If the flag was C--- it is possible that | (...) you may not need to rebuild, | as all of the transactions may indeed | have been committed. | However, if the flag was C-U-, the | SCN has been | estimated and it is possible | that we are not looking at the most | (B.Dump show various recent version of the rollback | 'UNDO' RECORDs) segment. | incarnation # 4185 | *----------------------------- | | Layer: 11 (Row Access) / opc: 1 Interpret Undo Record (Undo) / obj: 1095 / ________________________________/ / | * Rec #0xc slt: 0x1b obj:1095 (0x00000447) tablespace: 3 (0x00000003) | * Layer: 11 (Row) opc: 1 rci 0x00 dba: 0x00000000 | *----------------------------- | | uba: 0x080015cc.0003.0b ctl max scn: 0x04eb.1411e8b3 (...) \______________________________________________ | +)locate record that refers to our transaction | KDO undo record: look for key 'op: Z' --------------------> | KTB Redo, op: Z | | +)when selected the record, we can determe | the database OBJECT wich 'undo' informations | refers to . | | | | select * | from obj$ | where obj#=1095 | | OBJ# OWNER# NAME | ---------- ---------- ----- | 1095 11 EMP ---- | | | | | it's our table | 'EMP' | | +)now we have to decide wich rows of EMP 'undo' | infos refers to, we need DBA again ----------> | | ___________________________________________| / | KDO Op code: URP xtype: XA bdba: 0x10000373 hdba: 0x10000372 | itli: 2 ispac: 0 maxfr: 1177 \_____________________________________________| | | | | | bdba: 0x10000373 | | | | | using support tool ... | | | | | | | (File: 0x4 Block: 0x373) | | | | | | +)get the slot# which 'undo' info | is referred to ------------------------> | _____________________________________________| / | tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0 \____________________________________________| | | | | slot: 1 (0x1) | | +)we can now build the ROWID, | remember that ROWID on 7.x is formatted | as : | | ROWID: BLOCK.ROW-SLOT.FILE | | hence ... | | | ROWID: 0x373.0x1.0x4 | -------------------- | | In fact : | | select rowid,ename | from emp | | ROWID ENAME | ------------------ ---------- | 00000373.0000.0004 SMITH | 00000373.0001.0004 RICCARDO <--- Ok. | 00000373.0002.0004 WARD | (...) | | +)we have now localized where potentially | logical inconsistencies may hit: | OBJECT ('EMP') and modified | ROW ('00000373.0001.0004' ) | | +)to be complete, we need also the COLUMN and | the ORIGINAL DATA . For that, we analyze the | last part of 'undo' record : | | ______________________________________________| | ncol: 8 nnew: 1 size: -3 | col 1: [ 5] 41 4c 4c 45 4e | incarnation # 44 \_____________________________________________ | 'col 1' ------------> | '[ 5] 41 4c 4c 45 4e] | | | | | | | 5 characters | | | | | 'ALLEN' expressed in DB CharSet | (US7ASCII) | | ----------------------------------------------------- Conclusion: =========== In certain situations it is possible to determine exactly which OBJECT is modified, which (rows,col) are modified, and also the data existing before the modification was made. We are also able to determine the impact for database logical consistencies of the action of forcing the RBS into the offline status and then dropping it. Search Words: ============= _offline_rollback_segments, _corrupted_rollback_segments, corruption