If Oracle unfortunately encounters undo corruption in operation, the best approach is full recovery, but if there is no backup, you can take an unconventional approach (taking advantage of the hidden parameters of Oracle) and if undo contains uncommitted transactions at this time, can result in a little bit of data loss (which is generally tolerable) and no data loss if there are no uncommitted transactions. The main steps are:
1. Modify the Undo Table space management for manual;
2. Set the hidden parameter (_offline_rollback_segments or _corrupted_rollback_segments) to identify the affected rollback segment so that Oracle ignores uncommitted transactions on it;
3. Manually delete the affected rollback segment and undo table space, and then rebuild the new undo table space;
4. Restore Undo table space is managed automatically.
The experiment is as follows:
Step 1.
If the undo data file is corrupted, the database can only go to the Mount state, and the following error occurs when open:
Ora-01157:cannot identify/lock data file 14-see DBWR trace file
Ora-01110:data file: ' I:\INTEL_DATA\O06DMS0\UNDO1. O06dms0 '
Description The undo file is corrupted or missing, offline the file to open the database:
sql> ALTER DATABASE DataFile ' I:\INTEL_DATA\O06DMS0\UNDO1. O06dms0 ' offline drop;
sql> ALTER DATABASE open;
The purpose of opening the database is to find the affected rollback segment:
Sql> select Segment_name,status from Dba_rollback_segs;
Segment_name STATUS
-------- ---------------------- ----------------
SYSTEM ONLINE
_syssmu10_1201331463$ OFFLINE
_syssmu9_2926456744$ OFFLINE
_syssmu8_640224757$ OFFLINE
_syssmu7_3984293596$ OFFLINE
_syssmu6_3694658906$ OFFLINE
_syssmu5_3475919656$ OFFLINE
_syssmu4_168502732$ OFFLINE
_syssmu3_1987193959$ OFFLINE
_syssmu2_3908286755$ OFFLINE
_syssmu1_3281912951$ OFFLINE
Sql> Show Parameter Undo
NAME TYPE VALUE
--------------------------------- --- ----------- -------------
Undo_management string AUTO
Undo_retention Integer 900
Undo_tablespace string Undo1
To close the database:
sql> shutdown immediate;
Step 2.
To create a temporary pfile:
sql> create pfile= ' H:\initO06DMS0.ora ' from SPFile;
Modify Pfile as follows:
*.undo_management= ' Manual '--undo Table space Management method modified to Manual
*.undo_tablespace= ' Undo2 '--Specify a new undo table space
*._offline_rollback_segments= (' _syssmu10_1201331463 $ ', ' _syssmu9_2926456744$ ', ' _syssmu8_640224757 $ ', ' _SYSSMU7_ 3984293596$ ', ' _syssmu6_3694658906 $ ', ' _syssmu5_3475919656$ ', ' _syssmu4_168502732 $ ', ' _syssmu3_1987193959$ ', ' _ syssmu2_3908286755 $ ', ' _syssmu1_3281912951$ ')--all affected rollback segments are listed here
and restart the database by changing pfile:
sql> startup pfile= ' H:\initO06DMS0.ora '