oracle undo表空間管理

朋友的資料庫壞了，非歸檔模式，沒有備份，要我幫忙修復一下。

環境：
windows 2003
oracle 9.2.0.1
noarchivelog

故障行為：
資料庫執行時，直接拔電導致無法啟動。

我把他發來的資料庫檔案在本地建了個庫，然後啟動，檢查故障資訊。

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 14 13:49:00 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect sys/manager as sysdba
Connected.
SQL> startup pfile='d:\oracle92\ora92\database\inittest.ora'
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup pfile='d:\oracle92\ora92\database\inittest.ora'
ORACLE instance started.

Total System Global Area   93395628 bytes
Fixed Size                   453292 bytes
Variable Size              75497472 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-03113: end-of-file on communication channel

SQL>

檢視alert log：

Beginning crash recovery of 1 threads
Tue Feb 14 13:50:53 2006
Started recovery at
 Thread 1: logseq 368, block 1462, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 1 Seq 368 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE92\ORADATA\TEST\REDO01.LOG
***
Corrupt block relative dba: 0x0080000e (file 2, block 14)
Fractured block found during media/instance recovery
Data in bad block -
 type: 2 format: 2 rdba: 0x0080000e
 last change scn: 0x0000.0646b03b seq: 0x3 flg: 0x04
 consistency value in tail: 0xec0b0203
 check value in block header: 0x2790, computed block checksum: 0x7ca0
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0080000e (file 2, block 14) found same corrupted data
***
Corrupt block relative dba: 0x0080078e (file 2, block 1934)
Fractured block found during media/instance recovery
Data in bad block -
 type: 2 format: 2 rdba: 0x0080078e
 last change scn: 0x0000.064a39c9 seq: 0x1 flg: 0x04
 consistency value in tail: 0xac2e0201
 check value in block header: 0x23b8, computed block checksum: 0xf3e9
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0080078e (file 2, block 1934) found same corrupted data
***
Corrupt block relative dba: 0x008005ee (file 2, block 1518)
Fractured block found during media/instance recovery
Data in bad block -
 type: 2 format: 2 rdba: 0x008005ee
 last change scn: 0x0000.064a43e1 seq: 0x3 flg: 0x04
 consistency value in tail: 0x49c30201
 check value in block header: 0x96f7, computed block checksum: 0x1bab
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x008005ee (file 2, block 1518) found same corrupted data
***
Corrupt block relative dba: 0x0080056e (file 2, block 1390)
Fractured block found during media/instance recovery
Data in bad block -
 type: 2 format: 2 rdba: 0x0080056e
 last change scn: 0x0000.064a3e68 seq: 0x1 flg: 0x04
 consistency value in tail: 0x4c190203
 check value in block header: 0x4470, computed block checksum: 0x6a36
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0080056e (file 2, block 1390) found same corrupted data
***
Corrupt block relative dba: 0x0080066e (file 2, block 1646)
Fractured block found during media/instance recovery
Data in bad block -
 type: 2 format: 2 rdba: 0x0080066e
 last change scn: 0x0000.064a4549 seq: 0x1 flg: 0x04
 consistency value in tail: 0x4c2b0201
 check value in block header: 0x8a18, computed block checksum: 0x195d
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0080066e (file 2, block 1646) found same corrupted data
***
Corrupt block relative dba: 0x0080047e (file 2, block 1150)
Fractured block found during media/instance recovery
Data in bad block -
 type: 2 format: 2 rdba: 0x0080047e
 last change scn: 0x0000.064a58ff seq: 0x1 flg: 0x04
 consistency value in tail: 0x90e50201
 check value in block header: 0xd69c, computed block checksum: 0x4bbd
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0080047e (file 2, block 1150) found same corrupted data
***
Corrupt block relative dba: 0x008003fe (file 2, block 1022)
Fractured block found during media/instance recovery
Data in bad block -
 type: 2 format: 2 rdba: 0x008003fe
 last change scn: 0x0000.064a56b3 seq: 0x1 flg: 0x04
 consistency value in tail: 0x8ff30203
 check value in block header: 0x9d2b, computed block checksum: 0x7280
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x008003fe (file 2, block 1022) found same corrupted data
***
Corrupt block relative dba: 0x0080027e (file 2, block 638)
Fractured block found during media/instance recovery
Data in bad block -
 type: 2 format: 2 rdba: 0x0080027e
 last change scn: 0x0000.064a5231 seq: 0x3 flg: 0x04
 consistency value in tail: 0x90f90201
 check value in block header: 0x2282, computed block checksum: 0x7a6c
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0080027e (file 2, block 638) found same corrupted data
Tue Feb 14 13:50:57 2006
Ended recovery at
 Thread 1: logseq 368, block 55848, scn 0.105557616
 817 data blocks read, 122 data blocks written, 54386 redo blocks read
Crash recovery completed successfully
Tue Feb 14 13:50:58 2006
Thread 1 advanced to log sequence 369
Thread 1 opened at log sequence 369
  Current log# 2 seq# 369 mem# 0: D:\ORACLE92\ORADATA\TEST\REDO02.LOG
Successful open of redo thread 1.
Tue Feb 14 13:50:59 2006
SMON: enabling cache recovery
Tue Feb 14 13:51:00 2006
Errors in file d:\oracle92\admin\test\udump\db01gen_ora_1888.trc:
ORA-00600: internal error code, arguments: [2662], [0], [105557623], [0], [105590063], [8388633], [], []

Tue Feb 14 13:51:01 2006
Errors in file d:\oracle92\admin\test\udump\db01gen_ora_1888.trc:
ORA-00600: internal error code, arguments: [2662], [0], [105557623], [0], [105590063], [8388633], [], []

Tue Feb 14 13:51:01 2006
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 1888
ORA-1092 signalled during: ALTER DATABASE OPEN...
Tue Feb 14 13:56:02 2006
USER: terminating instance due to error 1092
Instance terminated by USER, pid = 1888

可知是檔案2發生錯誤，進而導致600錯誤。
因此，先檢視檔案2的名字，如下：

SQL> connect sys/manager as sysdba
Connected to an idle instance.
SQL> startup mount pfile='d:\oracle92\ora92\database\inittest.ora'
ORACLE instance started.

Total System Global Area   93395628 bytes
Fixed Size                   453292 bytes
Variable Size              75497472 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> select file#,status,name from v$datafile;

     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
         1 SYSTEM
D:\ORACLE92\ORADATA\TEST\SYSTEM01.DBF

         2 ONLINE
D:\ORACLE92\ORADATA\TEST\UNDOTBS01.DBF

         3 ONLINE
D:\ORACLE92\ORADATA\TEST\CWMLITE01.DBF


     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
         4 ONLINE
D:\ORACLE92\ORADATA\TEST\DRSYS01.DBF

         5 ONLINE
D:\ORACLE92\ORADATA\TEST\EXAMPLE01.DBF

         6 ONLINE
D:\ORACLE92\ORADATA\TEST\INDX01.DBF


     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
         7 ONLINE
D:\ORACLE92\ORADATA\TEST\ODM01.DBF

         8 ONLINE
D:\ORACLE92\ORADATA\TEST\TOOLS01.DBF

         9 ONLINE
D:\ORACLE92\ORADATA\TEST\USERS01.DBF


     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
        10 ONLINE
D:\ORACLE92\ORADATA\TEST\XDB01.DBF

        11 ONLINE
D:\ORACLE92\ORADATA\TEST\PMS.ORA

        12 ONLINE
D:\ORACLE92\ORADATA\TEST\FYBX.ORA


12 rows selected.

可以看到，損壞的檔案2是undotbs01.dbf,
檢視資料，undotbs損壞或丟失時可以採用隱含引數臨時啟動資料庫，然後進行修復。

修改init檔案,加入
*._allow_resetlogs_corruption=true
（注：允許在資料庫檔案SCN不一致的情況下啟動資料庫）
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
（注：允許在rollback segments損壞的情況下啟動資料庫）

SQL> shutdown abort
ORACLE instance shut down.
SQL>  startup pfile='d:\oracle92\ora92\database\inittest.ora'
ORACLE instance started.

Total System Global Area   93395628 bytes
Fixed Size                   453292 bytes
Variable Size              75497472 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

啟動成功，檢視下當前的rollback segments
SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU1$                      NEEDS RECOVERY
_SYSSMU2$                      NEEDS RECOVERY
_SYSSMU3$                      NEEDS RECOVERY
_SYSSMU4$                      NEEDS RECOVERY
_SYSSMU5$                      NEEDS RECOVERY
_SYSSMU6$                      NEEDS RECOVERY
_SYSSMU7$                      NEEDS RECOVERY
_SYSSMU8$                      NEEDS RECOVERY
_SYSSMU9$                      NEEDS RECOVERY
_SYSSMU10$                     NEEDS RECOVERY

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU11$                     ONLINE

12 rows selected.

新建一重做表空間undo
SQL> create undo tablespace undo datafile 'D:\oracle92\oradata\test\undo01.dbf' size 50M reuse autoe
xtend on;

Tablespace created.

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU1$                      OFFLINE
_SYSSMU2$                      NEEDS RECOVERY
_SYSSMU3$                      NEEDS RECOVERY
_SYSSMU4$                      NEEDS RECOVERY
_SYSSMU5$                      NEEDS RECOVERY
_SYSSMU6$                      NEEDS RECOVERY
_SYSSMU7$                      NEEDS RECOVERY
_SYSSMU8$                      NEEDS RECOVERY
_SYSSMU9$                      NEEDS RECOVERY
_SYSSMU10$                     NEEDS RECOVERY

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU11$                     ONLINE
_SYSSMU12$                     OFFLINE
_SYSSMU13$                     OFFLINE
_SYSSMU14$                     OFFLINE
_SYSSMU15$                     OFFLINE
_SYSSMU16$                     OFFLINE
_SYSSMU17$                     OFFLINE
_SYSSMU18$                     OFFLINE
_SYSSMU19$                     OFFLINE
_SYSSMU20$                     OFFLINE
_SYSSMU21$                     OFFLINE

22 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

修改init檔案
*.undo_tablespace=undo

SQL>  startup pfile='d:\oracle92\ora92\database\inittest.ora'
ORACLE instance started.

Total System Global Area   93395628 bytes
Fixed Size                   453292 bytes
Variable Size              75497472 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

刪除損壞的undotbs1表空間：
SQL> alter tablespace undotbs1 offline normal;

Tablespace altered.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL>  select * from v$recover_file;

no rows selected

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

修改init檔案,註釋引數
#*._allow_resetlogs_corruption=true
#*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

SQL>  startup pfile='d:\oracle92\ora92\database\inittest.ora'
ORACLE instance started.

Total System Global Area   93395628 bytes
Fixed Size                   453292 bytes
Variable Size              75497472 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL>
至此資料庫已經成功修復。

**********************************************************************************
需要提醒的是，在刪除損壞的重做表空間時，一定要先offline，
否則註釋掉隱含引數後就會出現下面的情況。

SQL>  startup pfile='d:\oracle92\ora92\database\inittest.ora'
ORACLE instance started.

Total System Global Area   93395628 bytes
Fixed Size                   453292 bytes
Variable Size              75497472 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

檢視alert log，本例中會發現下面的資訊，oracle標記剛才刪除的
重做表空間需要恢復，這時就無法去掉隱含引數了。
 ......
 drop tablespace UNDOTBS1 including contents and datafiles
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
......

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