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

因此，虽然数据库已经可以使用，但是为了防止数据库的进一步损坏，还是通过导出、重建、导入的方式比较稳妥。

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。