Dealing with rollback segment corruption when you encounter Error encountered while recovering trans

这篇文章主要是处理坏的回滚段方法，比较经典的一篇文章

  PURPOSE
-------

This article explain you how to deal with rollback segment corruption

SCOPE & APPLICATION
-------------------
Intended for Internal use.

Dealing with rollback segment corruption when you see error 
" Error 600 encountered while recovering transaction "
 
Symptomps:
----------

In the alert log file the following error is reported :-

Error 600 encountered while recovering transaction (8, 20).


Steps to solve the problem
------------------------------

1.The error gives the (usn,slot)


  Error 600 encountered while recovering transaction (8, 20).


  The two arugments in the recovering transaction are (usn,slot) 


  Where usn ---> Undo segment number
        slot ---> The slot within the undo which contains the active transaction.

2.Find the undo segment name and dump the undo segment.


  Sql > Select usn,name from v$rollname where usn=8; 


  Sql > alter system dump undo header "name"


  If automatic undo management 


  Sql >   alter system dump undo header "_SYSSMU8$" ;


  This will generate trace file in user_dump_dest


3.In the trace file search for the Keyword "TRN TBL"


 TRN TBL::
 
 index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
 ------------------------------------------------------------------------------------------------
 0x00    9    0x00  0x21eb1  0x0023  0x0000.d28c43e9  0x00000000  0x0000.000.00000000  0x00000000   0x00000000
 
 .
 .
 .
 .
 
 0x14   10    0x90  0x21e6d  0x0000  0x0000.d28c4437  0x0080007e  0x0000.000.00000000  0x00000001   0x00000000 <<<==(3)
 0x15    9    0x00  0x21e30  0x0020  0x0000.d28c4064  0x00800484  0x0000.000.00000000  0x00000003   0x00000000




  Note the second column "state" specifies the status of the rollback segment .


   If 10 then there is an active transaction.


  The column dba in that active transaction gives us the block containing the active transaction.


   In this case the dba--->0x0080007e


4.Convert this dba into file# and block# using webiv tool.(ODBA - DBA Calculator)


    In this case dba--->0x0080007e = file# 2 and block# 126


5.Dump block to get information about the objects having active transaction.


  Use the following command to dump  the block


  Sql  > Alter system dump datafile 2 block 126 ;


  Trace file will look like the following 


  UNDO BLK:  
  xid: 0x0008.01c.00021ef1  seq: 0x4ad4 cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000 <<<===(6)
 
   Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
  ---------------------------------------------------------------------------
  0x01 0x0014     


 
  *-----------------------------
  * Rec #0x1  slt: 0x1c  objn: 132622(0x0002060e)  objd: 132622  tblspc: 7(0x00000007)<<<===(6)
  *       Layer:  10 (Index)   opc: 21   rci 0x00   <<<=== (7) 
  Undo type:  Regular undo   Last buffer split:  No 
  Temp Object:  No 
  Tablespace Undo:  No 
  rdba: 0x0080007d
  *-----------------------------
  


6. irb :- Index of first record we need to consider in case of a rollback(active transaction record id)


  So in this case the "irb" is 0x1  . search on 0x1 in the trace file to goto Rec #0x1 .


  Now in the Rec #0x1 you have an rci which indicates the next undo record to apply within the undo chain.


  Note: If rci is 0x00 that indicates end of the undo chain within the undo block.


  For more details of undo Chain refer to Note 281504.1




7. This has an object_id of 132622 note her objn points of object_id and objd points to data_object_id


    So check whether the object is an Index or a table.


8.If it is an Index do the following :-


  a) Get the ddl information from dbms_metadata.get_ddl('index',Index name)
      or
     exp with rows=n to get the ddl for the index 


   b) Then drop and recreate the Index .


      Drop index <index name>;




9.If its a Table the best option is restore the old backup and perform an recovery.


  However if there is no backup or the database is in No Archive log mode then
  Use the unsupported parameter as explained below.
  However this should considered as a last resort and  should be used only after 
  explaining the customer the consequence of this parameter and with customer consent.




  a) Create a New Undo tablespace . 


     Eg:
     CREATE UNDO TABLESPACE <tablespace>
       DATAFILE '<path/filename>' size 100m;


  b) Make it the default one using the following command.


   alter system set undo_tablespace= <new tablespace name>


  c) Put the following parameter in the init.ora


      _OFFLINE_ROLLBACK_SEGMENTS=<UNDO SEGMENT NAME > 
      
     So in this case it would be 


     _OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU8$


  d) Startup the database 


  e) Drop the undo segment 


     Drop rollback segment "_SYSSMU8$"


Take an export of the database and recreate the database