上周四同事告知有一台基于Oracle的Content Manager，其Instance总是时时的crashed

登陆数据库，尝试open报错
SQL> startup
ORACLE instance started.

Total System Global Area 1392508928 bytes
Fixed Size                  2072808 bytes
Variable Size             738201368 bytes
Database Buffers          637534208 bytes
Redo Buffers               14700544 bytes
Database mounted.
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [71], [75], [], [], [], [],
[]

此时数据库已经不能 正常打开。

观察alert的日志，发现之前有大量的ora600的错误:
Errors in file c:\oracle\admin\icmnlsdb\udump\icmnlsdb_ora_5176.trc:
ORA-00600: internal error code, arguments: [4194], [71], [75], [], [], [], [], []
Thu Mar 20 14:59:02 2008
Doing block recovery for file 2 block 2894
Block recovery from logseq 134, block 9 to scn 5045164
Thu Mar 20 14:59:02 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 134 Reading mem 0
  Mem# 0: C:\ORACLE\ORADATA\ICMNLSDB\ICMNLSDB_REDO1_2.LOG
Block recovery stopped at EOT rba 134.10.0
Block recovery completed at rba 134.10.0, scn 0.5045163
Doing block recovery for file 2 block 137
Block recovery from logseq 134, block 9 to scn 5045162
Thu Mar 20 14:59:03 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 134 Reading mem 0
  Mem# 0: C:\ORACLE\ORADATA\ICMNLSDB\ICMNLSDB_REDO1_2.LOG
Block recovery completed at rba 134.10.0, scn 0.5045163
......
对于这个错误，有个细节:Doing block recovery for file 2 block 2894
得知应该是undo表空间的问题，当然对于ora600的4194错误大部分也是和undo相关的。

由于这是个新测试库还没有正式备份，只是简单的跑了一些TC,因此决定用隐含参数启动数据库并重新建undo表空间

通过联合查询dba_extents视图可以找出相应block 2894的segment_name(_SYSSMU9$)

修改spfile文件(通过spfile创建)，加入隐含参数将这个undo segment隔离：
_corrupted_rollback_segments='_SYSSMU9$'

关闭instance,并重新加载pfile启动
SQL> startup
ORACLE instance started.

Total System Global Area 1392508928 bytes
Fixed Size                  2072808 bytes
Variable Size             738201368 bytes
Database Buffers          637534208 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

并尝试重建undo表空间
SQL> select file_id,file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- ------------------------------
         1 C:\ORACLE\ORADATA\ICMNLSDB\ICM
           NLSDB_SYSTEM_01.DBF

         2 C:\ORACLE\ORADATA\ICMNLSDB\ICM
           NLSDB_UNDOTBS1_01.DBF

         3 C:\ORACLE\ORADATA\ICMNLSDB\ICM
           NLSDB_SYSAUX_01.DBF

         4 C:\ORACLE\ORADATA\ICMNLSDB\ICM
           NLSDB_ICMLFQ32_01.DBF

   FILE_ID FILE_NAME
---------- ------------------------------

         5 C:\ORACLE\ORADATA\ICMNLSDB\ICM
           NLSDB_ICMLNF32_01.DBF

         6 C:\ORACLE\ORADATA\ICMNLSDB\ICM
           NLSDB_ICMLSNDX_01.DBF

         7 C:\ORACLE\ORADATA\ICMNLSDB\ICM
           NLSDB_ICMLSTXT_01.DBF

         8 C:\ORACLE\ORADATA\ICMNLSDB\ICM

   FILE_ID FILE_NAME
---------- ------------------------------
           NLSDB_ICMSFQ04_01.DBF

         9 C:\ORACLE\ORADATA\ICMNLSDB\ICM
           NLSDB_ICMVFQ04_01.DBF


9 rows selected.

SQL> show parameter undo_tablespace;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
undo_tablespace                      string
UNDOTBS1

SQL> create undo tablespace undotbs2
  2  datafile 'C:\ORACLE\ORADATA\ICMNLSDB\ICMNLSDB_UNDOTBS2_02.DBF' size 500M;

Tablespace created.

SQL> alter database datafile 'C:\ORACLE\ORADATA\ICMNLSDB\ICMNLSDB_UNDOTBS2_02.DB
F' autoextend on next 1M maxsize unlimited;

Database altered.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

此时重新关闭数据库，并修改pfile，将undo_tablespace修改为undotbs2，并将隐含参数_corrupted_rollback_segments去掉.
重建spfile启动数据库，此时工作一切正常。

由于undo表空间被重建，那么就需要考虑一些问题，可能在做instance recovery的时候,对于consistent read所需要通过undo来构造读一致时，undo已经不存在所导致的ora-01555，以及在做flashback的时候用到undo data不存在导致的闪回不成功，等等这些问题是需要酌情考虑的。