无备份和归档的库undo损坏的恢复方法

场景：oracle 10g 归档未开启，数据库没有有效的备份。由于undo数据文件损坏，数据库宕掉不能启动。

1、正常启动，发现undo文件损坏
SQL> startup
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size                  2025232 bytes
Variable Size             788531440 bytes
Database Buffers         2952790016 bytes
Redo Buffers               14749696 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/prod/undotbs02.dbf'

2、mount 状态创建pfile
SQL> startup mount
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size                  2025232 bytes
Variable Size             788531440 bytes
Database Buffers         2952790016 bytes
Redo Buffers               14749696 bytes
Database mounted.

SQL>  create pfile='/u01/app/oracle/prod_pfile.ora' from spfile;

3、编辑Pfile （关键步骤）

在pfile 中加上 *._allow_resetlogs_corruption=true和 *._corrupted_rollback_segments=_SYSSMU*$
vi prod_pfile.ora

*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=_SYSSMU1$
*._corrupted_rollback_segments=_SYSSMU2$
*._corrupted_rollback_segments=_SYSSMU3$
*._corrupted_rollback_segments=_SYSSMU4$
*._corrupted_rollback_segments=_SYSSMU5$
*._corrupted_rollback_segments=_SYSSMU6$
*._corrupted_rollback_segments=_SYSSMU7$
*._corrupted_rollback_segments=_SYSSMU8$
*._corrupted_rollback_segments=_SYSSMU9$
*._corrupted_rollback_segments=_SYSSMU10$


prod.__db_cache_size=2952790016
prod.__java_pool_size=33554432
prod.__large_pool_size=16777216
prod.__shared_pool_size=704643072
prod.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.background_dump_dest='/u01/app/oracle/admin/prod/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/prod/control01.ctl','/u01/app/oracle/oradata/prod/control02.ctl','/u01/app/oracle/oradata/p
rod/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/prod/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1247805440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3745513472
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/prod/udump'


注：关于*._corrupted_rollback_segments中损坏的回滚段，一般情况下我们应该是不知道的。我想唯一能做的就是
在库是正常的情况下，经常去查查当前有哪些回滚段，然后把名称备份下来，当真正出现损坏的时候，全部贴进去。
所以这种办法是不科学的，存在严重弊端的，要想数据库安全，还是得开归档，做备份。
SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU1$                      ONLINE

对于这个参数*._corrupted_rollback_segments 不赋予具体的值，而用_SYSSMU*$ 代替我不知道可以不可，没有实验过

4、启动库
SQL>  startup pfile='/u01/app/oracle/prod_pfile_new.ora'

5、然后创建新的undo表空间，并删除原来的undo
SQL> create undo tablespace  undo3 datafile '/u01/app/oracle/oradata/prod/undotbs03.dbf' size 50M reuse autoextend on;

Tablespace created.
SQL> alter system set undo_tablespace= undo3 ;

System altered.
SQL> alter tablespace undo offline normal;   

Tablespace altered.

SQL>
SQL>
SQL> drop tablespace undo including contents and datafiles ;

Tablespace dropped.

6、关闭库，修改Pfile 中的*.undo_tablespace
*.undo_tablespace='UNDOTBS1'
改为：*.undo_tablespace='undo3'

7、启动库，并创创建新的spfile
SQL> startup pfile='/u01/app/oracle/prod_pfile.ora'
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size                  2025232 bytes
Variable Size             788531440 bytes
Database Buffers         2952790016 bytes
Redo Buffers               14749696 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> create spfile from pfile='/u01/app/oracle/prod_pfile.ora' ;

至此,一个没有做过备份和归档的库就被恢复了。但是正如前面所说，这种方法也不一定很靠谱，备份才是王道。