ora600 4194解決一例

上週四同事告知有一臺基於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不存在導致的閃回不成功，等等這些問題是需要酌情考慮的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-216927/，如需轉載，請註明出處，否則將追究法律責任。