10 Replies Latest reply: Mar 29, 2010 11:41 PM by Aman.... RSS

    UNDO segment dump

    Parul Garg-Oracle
      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
          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
            Parul Garg-Oracle
            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
              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
                Parul Garg-Oracle
                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
                  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
                    Parul Garg-Oracle
                    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
                      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
                        Parul Garg-Oracle
                        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....
                          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
                            Parul Garg-Oracle
                            Thanks Aman ...