Troubleshooting Database Transaction Recovery

Concepts

Transaction recovery involves rolling back all uncommitted transactions of a failed instance. These are “in-progress” transactions that did not commit and that Oracle needs to undo. It is possible for uncommitted transactions to get saved to disk. In this case, Oracle uses undo data to reverse the effects of any changes that were written to the datafiles but not yet committed.

Oracle applies undo blocks to roll back uncommitted changes in data blocks that were either written before the crash or introduced by redo application during cache recovery. This normally happens during the Roll backward phase when the DB is restarted. Transaction Recovery can be performed by either the Server process which initiated the Transaction or the SMON process (in case the Server process is dead).

SMON process takes over the recovery when
• Server process is dead / crashed.
• Instance itself is crashed

Undo blocks (whether in rollback segments or automatic undo tablespaces) record database actions that may need to be undone during certain database operations. In database recovery, the undo blocks roll back the effects of uncommitted transactions previously applied by the rolling forward phase.

After the roll forward, any changes that were not committed must be undone. Oracle applies undo blocks to roll back uncommitted changes in data blocks that were either written before the failure or introduced by redo application during cache recovery. This process is called rolling back or transaction recovery.

Modes of Transaction Recovery

1.Parallel Transaction Recovery

Recovery occurs in parallel mode. Several parallel slave processes will be spawned and will be involved in recovery. This is also termed as Fast Start Parallel Rollback. The background process SMON acts as a coordinator and rolls back a set of transactions in parallel using multiple server processes. To enable Parallel recovery mode, set the parameter FAST_START_PARALLEL_ROLLBACK to LOW / HIGH.

Values:

•FALSE - Parallel rollback is disabled
•LOW - Limits the maximum degree of parallelism to 2 * CPU_COUNT
•HIGH -Limits the maximum degree of parallelism to 4 * CPU_COUNT

2. Serial Transaction Recovery

This mode recovers the transaction sequentially. Many of the times, serial transaction recovery will be faster. Setting the FAST_START_PARALLEL_ROLLBACK parameter to false will enable the serial transaction recovery.

Detection of Transaction Recovery

select ktuxeusn  USN, ktuxeslt Slot, ktuxesqn  Seq, ktuxesta State, ktuxesiz Undo 
from x$ktuxe  
where ktuxesta <> 'INACTIVE'  
and ktuxecfl like '%DEAD%' 
order by ktuxesiz  asc;

===========

ktuxeusn –  Undo Segment Number

ktuxeslt   –  Slot number

ktuxesqn –  Sequence

ktuxesta  –  State

ktuxesiz  –  Undo Blocks Remaining

ktuxecfl   –  Flag

=========== 

  USN       SLOT        SEQ         STATE          UNDO

-------    ----------    -------     ----------    ----------

     9            9         335         ACTIVE         10337

Identify the Time for transaction recovery to complete

select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo", 
         decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))  "Estimated time to complete"  
from v$fast_start_transactions;

 

  USN      STATE               Total       Done      ToDo      Estimated time to Complete

-------- ----------------     --------   --------   --------    ---------------------------------

    5    RECOVERING        16207     14693      1514     11-may-2012 08:05:40

    4    RECOVERED          7453       7453         0        11-may-2012 08:05:16

    9    RECOVERED         10337      10337       0         11-may-2012 08:05:16