Oracle] Resolves how to undo corruption without a backup

Oracle Unfortunately encountered undo corruption in operation, of course, the best way is full recovery, but if there is no backup in the case of undo damage how to do? The following is for you to introduce the solution to this situation, the need for friends under the reference

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 on open:
ora-01157:cannot identify/lock data files 14-see dbwr trace file
ORA-01 110:data file: ' I:intel_datao06dms0undo1. O06dms0 '
indicates that the undo file is corrupted or missing, offline the file to open the database:
sql> ALTER DATABASE datafile ' I:INTEL_DATAO06DMS0UNDO1. 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.
Create a temporary pfile:
sql> Create pfile= ' H:inito06dms0.ora ' from SPFile;
Modify Pfile as follows:
*.undo_management= ' manual '   --undo table space Management modified to manual
*.undo_tablespace= ' Undo2 '     --Specifies 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$ ',   List all affected rollback segments here
and restart the database by changing pfile:
sql> startup pfile= ' H:inito06dms0.ora '

Step 3.
Manually delete affected rollback segments:
Sql>drop rollback segment "_syssmu10_1201331463$";
Sql>drop rollback segment "_syssmu9_2926456744$";
Sql>drop rollback segment "_syssmu8_640224757$";
Sql>drop rollback segment "_syssmu7_3984293596$";
Sql>drop rollback segment "_syssmu6_3694658906$";
Sql>drop rollback segment "_syssmu5_3475919656$";
Sql>drop rollback segment "_syssmu4_168502732$";
Sql>drop rollback segment "_syssmu3_1987193959$";
Sql>drop rollback segment "_syssmu2_3908286755$";
Sql>drop rollback segment "_syssmu1_3281912951$";
Delete the old undo table space manually:
sql> drop tablespace undo1 including contents;
rebuild the new undo tablespace:
sql> Create undo tablespace undo2 DataFile ' I:intel_datao06dms0undo2. O06dms0 ' size 100m;
Create a new spfile, overwriting the old spfile:
sql> create SPFile from pfile= ' H:inito06dms0.ora ';
Close database:
sql> shutdown immediate;

Step 4.
Start the database with the original spfile:
sql> startup;
restore undo Tablespace Management to Automatic:
sql> alter system set undo_management= ' Auto ' scope=spfile;
suppress settings for hidden parameters:
sql> alter system reset "_offline_rollback_segments" scope=spfile;
reboot to take effect:
sql> shutdown immediate;
sql> startup
sql> show parameter undo

name                                  type         VALUE
------------------------------------------------------------------
undo_management                       string      AUTO
undo_retention                         integer     900
undo_tablespace                       string       Undo2

finally check:
sql> select Segment_name,status from Dba_rollback_segs;

segment_name                    STATUS
----------------------------------------------
system                           online
_syssmu40_1968985325$          Online
_ syssmu39_4040503138$          ONLINE
_syssmu38_4059847715$           ONLINE
_syssmu37_2692202156$           ONLINE
_syssmu36_2617425201$           Online
_syssmu35_1133967719$          online
_SYSSMU34 _1916939664$          ONLINE
_syssmu33_99444166$             ONLINE
_syssmu32_162619813$            ONLINE
_syssmu31_830375278$            ONLINE