掉電引起的ORA-1172錯誤解決過程（三）

由於UPS故障，導致機房連續多次掉電，問題解決後，發現一臺本地測試資料庫開啟時報錯，ORA-1172、ORA-1151錯誤。

掉電引起的ORA-1172錯誤解決過程（一）：http://yangtingkun.itpub.net/post/468/465223

掉電引起的ORA-1172錯誤解決過程（二）：http://yangtingkun.itpub.net/post/468/465868

開啟資料庫後的處理：

 

 

在前一篇文章中已經成功開啟資料庫，其實這時從目標上已經基本完成了，只需透過EXP或者EXPDP工具將資料庫中的使用者匯出，重建資料庫，然後匯入資料即可。

不過對於恢復來說，還有很多可以做的，檢查資料庫的回滾段狀態：

SQL> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS
  2  FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME    OWNER  TABLESPACE_NAME      STATUS
--------------- ------ -------------------- ----------------
SYSTEM          SYS    SYSTEM               ONLINE
_SYSSMU1$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU2$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU3$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU4$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU5$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU6$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU7$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU8$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU9$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU10$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU11$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU12$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU13$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU14$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU15$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU16$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU17$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU18$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU19$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU20$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU21$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU22$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU23$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU24$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU25$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU26$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU27$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU28$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU29$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU30$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU31$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU32$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU33$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU34$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU35$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU36$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU37$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU38$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU39$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU40$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU41$      PUBLIC UNDOTBS1             OFFLINE

42 rows selected.

可以發現除了SYSTEM回滾段，其他回滾段均為OFFLINE狀態，這時所有的DML操作均回報錯：

SQL> DELETE TEST.T;
DELETE TEST.T
            *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'GPO'

下面建立一個新的UNDO表空間，使得ORACLE有可用的UNDO表空間：

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/data/oradata/test08/undotbs21.dbf'           
  2  SIZE 4096M;

Tablespace created.

下面修改初始化引數檔案，改變UNDO表空間為UNDOTBS2，並將UNDO管理設定為AUTO模式，註釋掉隱含引數_corrupted_rollback_segments：

*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS2’

在關閉資料庫時出現了異常：

SQL> SHUTDOWN IMMEDIATE

等待了幾個小時，SHUTDOWN IMMEDIATE方式仍然無法關閉資料庫，檢查alert檔案發現資訊如下：

Tue Jun 10 17:02:41 2008
Starting background process EMN0
EMN0 started with pid=16, OS id=15734
Tue Jun 10 17:02:41 2008
Shutting down instance: further logons disabled
Tue Jun 10 17:02:41 2008
Stopping background process CJQ0
Tue Jun 10 17:02:41 2008
Stopping background process MMNL
Tue Jun 10 17:02:42 2008
Stopping background process MMON
Tue Jun 10 17:02:43 2008
Shutting down instance (immediate)
License high water mark = 44
Tue Jun 10 17:02:43 2008
Stopping Job queue slave processes
Tue Jun 10 17:02:43 2008
Job queue slave processes stopped
All dispatchers and shared servers shutdown
Tue Jun 10 17:02:50 2008
Process OS id : 15693 alive after kill
Errors in file /opt/ora10g/admin/test08/udump/test08_ora_15629.trc

在另外的會話以SYSDBA登陸，利用SHUTDOWN ABORT關閉資料庫，SHUTDOWN IMMEDIATE的會話資訊如下：

ORA-03113: end-of-file on communication channel
SQL> STARTUP PFILE=/home/oracle/inittest08.ora
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> CONN / AS SYSDBA
Connected to an idle instance.
SQL> STARTUP PFILE=/home/oracle/inittest08.ora
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

資料庫可以正常啟動。下面刪除UNDOTBS1表空間即可：

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DELETE TEST.T;

4051072 rows deleted.

SQL> COMMIT;

Commit complete.

不過由於資料庫本身已經處於異常狀態，後臺仍然可以經常發現大量壞塊：

Errors in file /opt/ora10g/admin/test08/bdump/test08_smon_19485.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 32529)
ORA-01110: data file 1: '/data/oradata/test08/system01.dbf'
Wed Jun 11 08:58:29 2008
WARNING: inbound connection timed out (ORA-3136)
Wed Jun 11 09:02:29 2008
Hex dump of (file 3, block 37871) in trace file /opt/ora10g/admin/test08/bdump/test08_m000_19556.trc
Corrupt block relative dba: 0x00c093ef (file 3, block 37871)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00c093ef
 last change scn: 0x0001.81e5b9f5 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb56b0601
 check value in block header: 0x9fc
 computed block checksum: 0xdd4
Reread of rdba: 0x00c093ef (file 3, block 37871) found same corrupted data
Hex dump of (file 3, block 35683) in trace file /opt/ora10g/admin/test08/bdump/test08_m000_19556.trc
Corrupt block relative dba: 0x00c08b63 (file 3, block 35683)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00c08b63
 last change scn: 0x0001.856e48fc seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x7ead0601
 check value in block header: 0x1214
 computed block checksum: 0x3404
Reread of rdba: 0x00c08b63 (file 3, block 35683) found same corrupted data
Hex dump of (file 3, block 40608) in trace file /opt/ora10g/admin/test08/bdump/test08_m000_19556.trc
Corrupt block relative dba: 0x00c09ea0 (file 3, block 40608)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00c09ea0
 last change scn: 0x0001.856e48fc seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x65c20601
 check value in block header: 0x9ef1
 computed block checksum: 0x7a6b
Reread of rdba: 0x00c09ea0 (file 3, block 40608) found same corrupted data
Wed Jun 11 09:02:30 2008
Corrupt Block Found
         TSN = 2, TSNAME = SYSAUX
         RFN = 3, BLK = 37871, RDBA = 12620783
         BJN = 8933, BJD = 8933, BJECT = WRH$_SQLTEXT, SUBOBJECT =
         SEGMENT WNER = SYS, SEGMENT TYPE = Table Segment
Corrupt Block Found
         TSN = 2, TSNAME = SYSAUX
         RFN = 3, BLK = 35683, RDBA = 12618595
         BJN = 8943, BJD = 8943, BJECT = WRH$_SQL_BIND_METADATA, SUBOBJECT =
         SEGMENT WNER = SYS, SEGMENT TYPE = Table Segment
Wed Jun 11 09:07:30 2008
Errors in file /opt/ora10g/admin/test08/bdump/test08_smon_19485.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 32529)
ORA-01110: data file 1: '/data/oradata/test08/system01.dbf'
Wed Jun 11 09:07:32 2008
Corrupt Block Found
         TSN = 2, TSNAME = SYSAUX
         RFN = 3, BLK = 40608, RDBA = 12623520
         BJN = 8939, BJD = 8939, BJECT = WRH$_SQL_PLAN, SUBOBJECT =
         SEGMENT WNER = SYS, SEGMENT TYPE = Table Segment

因此，雖然資料庫已經可以使用，但是為了防止資料庫的進一步損壞，還是透過匯出、重建、匯入的方式比較穩妥。