Oracle ROLLBACK SEGMENT CORRUPTION with _OFFLINE & _CORRUPTED_ROLLBACK_SEGMENTS options

Oracle ROLLBACK SEGMENT CORRUPTION with _OFFLINE & _CORRUPTED_ROLLBACK_SEGMENTS options

A detail explanation on _OFFLINE_ROLLBACK_SEGMENTS

and _CORRUPTED_ROLLBACK_SEGMENTS

_OFFLINE_ROLLBACK_SEGMENTS Parameter

==============================

If the datafile related to rollback segment get corrupted, the first method go for the recovery from backup.

If your backup is not working at all, so last unsupported option to choose the parameter

_OFFLINE_ROLLBACK_SEGMENTS in init.ora.

What is this parameter ?

_OFFLINE_ROLLBACK_SEGMENTS is a unsupported init.ora parameter which can allow you to

cause logical database corruption. It is rarely required in Oracle7.3 onwards due to deferred transaction recovery.

When you required this parameter to set ?

When you start a database, SMON will recover all the uncommitted transaction. To do the recovery it will

check the rollback segment header first to read the transaction table. But if any reason SMON is unable to read

the header block of any online rollback segments then you required this parameter.

Unreadable rollback segment header may cause for block corruption, datafile corruption or datafile unavailable.

If tablespace is online but the rollback segment header block get corrupted, you need this parameter to start the database.

Because you have to stop SMON from performing transaction recovery for specific rollback segments.

While starting the database SMON will not check the transaction table only for those rollback segments under the

parameter _OFFLINE_ROLLBACK_SEGMENTS . You should choose this parameter only when all the transaction

related to this rollback segment is INACTIVE.

How to know the transaction is in inactive mode ?

Do the following steps

===============

1. Start the database with _OFFLINE_ROLLBACK_SEGMENTS first.

2. Find the header file number and header block number from the following query.

select segment_name,header_file,header_block from dba_segments where segment_name like '<rbs name>';

output looks below

===========

SEGMENT_NAME HEADER_FILE HEADER_BLOCK

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

RBS2_1 1 10533

3. dump the block with following command.

alter system dump datafile <header_file number> block <header_block no>;

4. observe for the uncommitted transaction from block dump like below. Observe the status field.

If it is '9' means transaction committed. but if the status value is '10' then it is an uncommitted transaction.

So this rollback segment is not a good candidate for _OFFLINE_ROLLBACK_SEGMENTS.

index state cflags wrap# uel scn dba parent xid nub

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

0x00 9 0x80 0x0064 0x0001 0x0000.004866ac 0x0040292c 0x0000.000.00000000 0x00000001

0x01 9 0x80 0x0064 0x0002 0x0000.004866ad 0x00000000 0x0000.000.00000000 0x00000000

0x02 9 0x80 0x0064 0x0003 0x0000.004866af 0x0040292c 0x0000.000.00000000 0x00000001

0x03 10 0x80 0x0064 0x0004 0x0000.004866b1 0x0040292c 0x0000.000.00000000 0x00000001

0x04 9 0x80 0x0064 0x0005 0x0000.004866b3 0x0040292c 0x0000.000.00000000 0x00000001

0x05 9 0x80 0x0064 0x0006 0x0000.004866b5 0x0040292c 0x0000.000.00000000 0x00000001

0x06 9 0x80 0x0064 0x0007 0x0000.004866b6 0x00000000 0x0000.000.00000000 0x00000000

5. After checking the step 4, if you find all the status field is '9', then drop the rollback segment immediately.

6. Stop the database, take the parameter out from init.ora and start the database. So your dictionary is perfect.

you can continue your production job with this database.

7. After checking the step 4, if you find any status field is '10' , then you should immediately shutdown the database.

Start the database with "_corrupted_rollback_segments". And put the name of those rollback segment names

where did you find the value of status='10'.

8. There is a logical corruption. So you should drop the rollback segment And do a complete export and import of this

database to make it supported.

DATAFILE TRANSACTION

_offline_rollback_segments ONLINE/OFFLINE/RECOVERY INACTIVE

_corrupted_rollback_segments ONLINE/OFFLINE/RECOVERY ACTIVE

_offline_rollback_segments ONLINE

ACTIVE * you can use this

option, but better option to

choose

_corrupted_rollback_segments

or follow the above steps

*********************************************************************

ORACLE will not support a database that has used the

_CORRUPTED_ROLLBACK_SEGMENTS parameter.

*********************************************************************

IMPORTANT: If this corruption happened with system rollback segment, there is no way to use any of this parameter.

You must have to recover database from old backup.

Differences between these parameters

There are differences between these two parameters. If the rollback segment is listed in the

_offline parameter list, the transaction table is still read accessible. This is important for delayed block

cleanout. If a select statement reads a data block with an open ITL which points to the transaction table

of the _offline rollback segment, the table is still checked.

If the transaction is committed, delayed block clean out occurs. If the transaction is uncommitted,

it will generate ORA-1578. It will not allow to select the block.

If the rollback segment is listed in the _corrupted parameter list, the transaction table is not read accessible.

All transactions are assumed COMMITTED and delayed block cleanout will occur but there is

no consistent read view of uncommitted transactions (logical corruption).