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

掉電引起的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

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

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。