This discussion is archived
10 Replies Latest reply: Mar 29, 2010 9:41 PM by Aman.... RSS

UNDO segment dump

ParulGarg Explorer
Currently Being Moderated
Hi everyone ,

My transaction view shows me one transaction going on :-

SQL> select ubafil,ubablk,ubarec from v$transaction;

UBAFIL UBABLK UBAREC
---------- ---------- ----------
3 47460 5


And from dba_rollback_segs table i know the segment_name for corresponding block_id and file_id ..

SQL> select segment_name,file_id,block_id from dba_rollback_segs where tablespace_name ='UNDOTBS1';

SEGMENT_NAME FILE_ID BLOCK_ID
------------------------------ ---------- ----------
SYSSMU11192467665$ 3 9
SYSSMU21192467665$ 3 25
SYSSMU31192467665$ 3 41
SYSSMU41192467665$ 3 57
SYSSMU51192467665$ 3 73
SYSSMU61192467665$ 3 89
SYSSMU71192467665$ 3 105
SYSSMU81192467665$ 3 121
SYSSMU91192467665$ 3 137
SYSSMU101192467665$ 3 153

10 rows selected.

Now i want to dump the UNDO record entry which contains the transactionn details ..
How do i map it with the segment_name .. I mean segment_name contains block_id and v$transaction contains UBABlk address ..
And how can i dump it ?
  • 1. Re: UNDO segment dump
    Lakmal Rajapakse Expert
    Currently Being Moderated
    try:
     select segment_name from dba_extents where file_id = 3
     and 47460  between block_id and block_id + blocks -1
  • 2. Re: UNDO segment dump
    ParulGarg Explorer
    Currently Being Moderated
    Hi,

    Thanks for the reply .. It gives me the segment name ...

    SQL> select segment_name from dba_extents where file_id = 3
    2 and 47460 between block_id and block_id + blocks -1
    3 ;

    SEGMENT_NAME
    -------------------------------------------------------------------

    SYSSMU51192467665$

    How can i dump this segment ? And if i want to dump only that particular UNDO entry , can i do it ?
  • 3. Re: UNDO segment dump
    Lakmal Rajapakse Expert
    Currently Being Moderated
    use the following command:
    alter system dump datafile <number datafile> block min <first number> block max <last block>
    Why do you want a dump of the block?
  • 4. Re: UNDO segment dump
    ParulGarg Explorer
    Currently Being Moderated
    Hi ,

    I want to study the working of UNDO and REDO .. And so wanted to study them block by block.
    Thanks a lot for your help ..

    Just one last question ,

    Does
    alter system dump undo header '_SYSSMU5_1192467665$';

    dumps only segment header of the segment and not the entries ?

    Your command to dump particular block entry works completely fine .. Thanks for that ..
  • 5. Re: UNDO segment dump
    Girish Sharma Guru
    Currently Being Moderated
    Parul,

    If you need to dump an undo block use this syntax:
    ALTER SYSTEM DUMP undo block RBS_NAME xid XIDUSN, XIDSLOT, XIDSQN;

    For interpretation the trace file, I will read below thread:
    alter system dump undo header interpretation And answer by Robert sir.

    Related links are:
    http://www.ixora.com.au/q+a/undo.htm
    http://www.ixora.com.au/scripts/rbs.htm
    http://www.dbasupport.com/forums/showthread.php?t=35107
    http://www.scribd.com/doc/2713817/Oracle-AUTOMATIC-UNDO-INTERNALS

    Please give plenty of time to read the above link, because you are reading UNDO; oracle's one of the most important, complex and interesting topic.

    Regards
    Girish Sharma
  • 6. Re: UNDO segment dump
    ParulGarg Explorer
    Currently Being Moderated
    Hi Girish ,

    Whats the difference between dumping data block by the method mentioned by you and by

    allter system dump datafile <number datafile> block <<block number>>

    Can you elaborate on this ?

    Thanks,
    Parul
  • 7. Re: UNDO segment dump
    Girish Sharma Guru
    Currently Being Moderated
    I mentioned ALTER SYSTEM DUMP undo block RBS_NAME xid XIDUSN, XIDSLOT, XIDSQN;
    means you are going to dump directly of undo datafile and said block, you are saying alter system dump undo block (i am going to dump undo block); while;
    If you says : allter system dump datafile <number datafile> block <<block number>>; means you are going to dump any datafile's block with datafile number and block. So, the difference is ...dump undo block and ...dump datafile. As far as technical differences, i have'nt gone till now to see the exact difference, but i think there will no change in the trace file as such, because at last you wish to see the dump of block.

    Dumping of undo is divided into following topics:
    (A) Undo Header
    (B) Undo Segment
    (B) Undo Block

    Regards
    Girish Sharma
  • 8. Re: UNDO segment dump
    ParulGarg Explorer
    Currently Being Moderated
    I am sorry if it sounds like a stupid question ... But i am new to this area ...
    Just one thing , UNDO tablespace also finally resides in the datafiles right ?
    And when i give the file number of undotablespace file in alter system dump datafile << file number >> ,
    it will dump undo file contents , isn't it ?

    Atleast that gave me the undo contents as output , when i tried !!

    Something like this :-


    * Rec #0x1 slt: 0x20 objn: 5965(0x0000174d) objd: 5965 tblspc: 1(0x00000001)
    * Layer: 10 (Index) opc: 21 rci 0x00
    Undo type: Regular undo Last buffer split: No
    Temp Object: No
    Tablespace Undo: No
    rdba: 0x00c0b962
    *-----------------------------
    index general undo (branch) operations
    KTB Redo
    op: 0x05 ver: 0x01
    compat bit: 4 (post-11) padding: 0
    op: R itc: 2
    Itl Xid Uba Flag Lck Scn/Fsc
    0x01 0x0005.005.00001a42 0x00c0b963.07ba.01 C--- 0 scn 0x0000.0089a0fc
    0x02 0x0007.006.00001a23 0x00c08ab1.0675.27 C--- 0 scn 0x0000.0089831c
    Dump kdige : block dba :0x00814976, seghdr dba: 0x00801003
    make leaf block empty
    (2): 01 00


    .....
  • 9. Re: UNDO segment dump
    Aman.... Oracle ACE
    Currently Being Moderated
    Parul,

    If you are going to dump the datafile, you are going to see the file's contents. What you need to dump is the transaction table and segment header of Undo. The datafile contents would just have the undo records. You may also want to peek into x$ktuxe and x$ktcxb tables.

    HTH
    Aman....
  • 10. Re: UNDO segment dump
    ParulGarg Explorer
    Currently Being Moderated
    Thanks Aman ...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points