Recovery from corrupted/lost Rollback datafile

Note: If you are not confident please contact Oracle support to do rollback segment recoveries, Because you may loose active transactions. You may end-up in rebuilding the database again.

There are two situations

    1. Database is up and running, it detected corrupt rollback segments.
    2. While starting database you get rollback segment corruption error.

Database is up and running, rollback segment recovery

Don't shutdown the database. If transactions are heavy create one more rollback segment, and bring the corrupt rollback segment to offline.

Method-I: Database is running in Archivelog mode

    1. Offline the corrupt datafile
    2. Svrmgrl> connect internal
    3. Svrmgrl> ALTER DATABASE DATAFILE 'corrupt_rollback_datafile_path' OFFLINE;
    4. Restore datafile from lastest backup
    5. Svrmgrl> RECOVER DATAFILE 'corrupt_rollback_datafile_path;
    6. Apply archive logs and complete media recovery.
    7. Restore the datafile to online
    8. Svrmgrl> ALTER DATABASE DATAFILE 'corrupt_rollback_datafile_path ONLINE;

 

Method-II: Database is either in Archivelog mode or not in archivelog mode.

Offline the rollback tablespace segments

Svrmgrl> ALTER ROLLBACK SEGMENT segment_name OFFLINE;

Check for any pending transactions

Svrmgrl> SLECT SEGMENT_NAME, XACTS, rs.STATUS

>FROM V$ROLLSTAT, DBA_ROLLBACK_SEGS RS

>WHERE TABLESPACE_NAME='ROLLBACK' AND

>SEGMENT_ID = USN;

XACTS column must be zero for offline or offline pending segments .

Kill the sessions holding the corrupted segments

Svrmgrl> SELECT se.USERNAME, se.SID, se.SERIAL#, rn.NAME

>FROM V$SESSION se, V$TRANSACTION tr, V$ROLLNAME rn

>WHERE rn.NAME in ('pending_offline_rollback_segments');

Svrmgrl> ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

Drop all offline corrupted segments

Svrmgrl> DROP ROLLBACK SEGMENT offlline_rollback-segment;

Drop the ROLLBACK tablespace

Svrmgrl> DROP TABLESPACE ROLLBCK INCLUDING CONTENTS;

Recreate ROLLBACK tablespace

Svrmgrl> CREATE TABLESPACE ROLLBACK

>DATAFILE '/…/…/rollback01.dbf' SIZE 100M

>DEFAULT STORAGE (INITIAL 2M NEXT2M MINEXTENTS 5 PCTINCREASE 0);

Recreate rollback segments

Svrmgrl> CREATE ROLLBACK SEGMENT R01

>TABLESPACE ROLLBACK

>STORAGE (OPTIMAL 10M);

Bring rollback segments online.

Svrmgrl> ALTER ROLLBACK SEGMENT R01 ONLINE;

Database can be brought down situation or During startup RBS corrupt errors

Method -I : When Database was cleanly shutdown before errors.

Solution 1.

Comment ROLLBACK_SEGMENTS parameter in initSID.ora file

Svrmgrl> STARTUP RESTRICT MOUNT

Drop the corrupt datafile

Svrmgrl> ALTER DATABASE DATAFILE 'corrupt_datafile_location'

>OFFLINE DROP;

Open the database

Svrmgrl> ALTER DATABASE OPEN

Drop the corrupt rollback tablespace

Svrmgrl> DROP TABLESPACE ROLLBACK INCLUDING CONTENTS;

Recreate the ROLLBACK tablespace

Recreate rollback segments

Disable restricted session

Svrmgrl> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Solution 2.

Comment ROLLBACK_SEGMENTS parameter in initSID.ora file

Svrmgrl> STARTUP RESTRICT MOUNT

Drop the corrupt datafile

Svrmgrl> ALTER DATABASE DATAFILE 'corrupt_datafile_location'

> OFFLINE DROP;

Open the database

Svrmgrl> ALTER DATABASE OPEN;

You get errors, and you cannot open then database contact oracle support.

[Important: Consult Oracle support and proceed. Below steps should be

attempted only for testing purposes ]

Shutdown the database

Add the following line in initSID.ora file

_Corrupted_rollback_segments = (rbs1, rbs2,rbs3….,rbsN)

Start the database

Svrmgrl> STARTUP RESTRICT MOUNT

Drop the corrupt datafile

Svrmgrl> ALTER DATABASE DATAFILE 'corrupt_datafile_location'

> OFFLINE DROP;

Open the database

Svrmgrl> ALTER DATABASE OPEN

Drop the corrupt rollback tablespace

Svrmgrl> DROP TABLESPACE ROLLBACK INCLUDING CONTENTS;

Recreate the ROLLBACK tablespace

Recreate rollback segments

Disable restricted session

Svrmgrl> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Method -II: Database was not cleanly shutdown or crashed.

Contact oracle support for recovery, below procedure is for test.

Restore the corrupt datafile from latest backup

Start the database in restricted mount state.

Verify the status.

Svrmgrl> SELECT NAME, STATUS FROM V$DATAFILE;

If the file is offline, bring it online

Svrmgrl> ALTER DATABASE DATAFILE 'offline_datafile' ONLINE;

Recover Datafile

Svrmgrl> RECOVER DATAFILE 'restored_data_file';

Apply all archive log files to complete media recovery

Svrmgrl> ALTER DATABASE OPEN.