恢复测试
数据库非正常关闭，有活动事务，回滚表空间的数据文件损坏
不使用recover datafile进行恢复
SQL> Shutdown abort;
#删除undo空间的数据文件
SQL> startup
ORACLE instance started.
Total System Global Area 704643072 bytes
Fixed Size 2006896 bytes
Variable Size 184549520 bytes
Database Buffers 511705088 bytes
Redo Buffers 6381568 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'M:\WM\ UNDOTBS01.DBF'
SQL> shutdown abort
ORACLE instance shut down.
#修改pfile
*.undo_tablespace='UNDOTBS1'
*.undo_management='AUTO'
++++++++++++++++++++++++++++++++++++++>>>
*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
*._CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
SQL> startup mount
ORACLE instance started.
Total System Global Area 704643072 bytes
Fixed Size 2006896 bytes
Variable Size 184549520 bytes
Database Buffers 511705088 bytes
Redo Buffers 6381568 bytes
Database mounted.
SQL> alter database datafile 'M:\WMS\UNDOTBS01.DBF' offline;
Database altered.
SQL> alter database datafile 'M:\WM\UNDOTBS02.DBF' offline;
Database altered.
SQL> alter database open;
Database altered.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU2$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU3$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU4$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU5$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU6$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU7$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU8$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU9$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU10$ UNDOTBS1 NEEDS RECOVERY
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS1 OFFLINE
_SYSSMU12$ UNDOTBS1 OFFLINE
_SYSSMU13$ UNDOTBS1 OFFLINE
_SYSSMU14$ UNDOTBS1 OFFLINE
_SYSSMU15$ UNDOTBS1 OFFLINE
_SYSSMU16$ UNDOTBS1 OFFLINE
_SYSSMU17$ UNDOTBS1 OFFLINE
_SYSSMU18$ UNDOTBS1 OFFLINE
19 rows selected.
SQL> drop rollback segment "_SYSSMU1$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU2$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU3$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU4$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU5$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU6$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU7$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU8$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU9$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU10$";
Rollback segment dropped.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU11$ UNDOTBS1 OFFLINE
_SYSSMU12$ UNDOTBS1 OFFLINE
_SYSSMU13$ UNDOTBS1 OFFLINE
_SYSSMU14$ UNDOTBS1 OFFLINE
_SYSSMU15$ UNDOTBS1 OFFLINE
_SYSSMU16$ UNDOTBS1 OFFLINE
_SYSSMU17$ UNDOTBS1 OFFLINE
_SYSSMU18$ UNDOTBS1 OFFLINE
9 rows selected.
SQL> create undo tablespace undo2 datafile 'M:\wm\undo_2.dbf' size 10M;
Tablespace created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
#修改pfile为
+++++++++++++++++++++
*.undo_management='auto'
*.undo_tablespace='undo2'
#*._CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
+++++++++++++++++++++
SQL> startup
ORACLE instance started.
Total System Global Area 704643072 bytes
Fixed Size 2006896 bytes
Variable Size 184549520 bytes
Database Buffers 511705088 bytes
Redo Buffers 6381568 bytes
Database mounted.
Database opened.
SQL> spool off
#分析所有对像，防止有坏的数据块
SQL> select 'analyze table '||table_name ||' validate structure cascade ' from dba_tables;
