sqlplus查看表结构_技术篇：服务器表空间损坏的修复

相信大家遇到过断电之后服务器就宕机或者错误频繁出现，小编之前也是找不到原因，排除各种可能之后确实是服务器断电之后备用电源没起作用造成的，下面就这一问题的解决方法说下，供大家借鉴。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 sysdbaConnected.SQL> startup pfile='d:oracle92ora92databaseinittest.ora'ORA-01081: cannot start already-running ORACLE - shut it down firstSQL> shutdown abortORACLE instance shut down.SQL> startup pfile='d:oracle92ora92databaseinittest.ora'ORACLE instance started.

Total System Global Area 93395628 bytesFixed Size 453292 bytesVariable Size 75497472 bytesDatabase Buffers 16777216 bytesRedo Buffers 667648 bytesDatabase mounted.ORA-03113: end-of-file on communication channel

SQL>

查看alert log：

Beginning crash recovery of 1 threadsTue Feb 14 13:50:53 2006Started recovery at Thread 1: logseq 368, block 1462, scn 0.0Recovery of Online Redo Log: Thread 1 Group 1 Seq 368 Reading mem 0 Mem# 0 errs 0: D:ORACLE92ORADATATESTREDO01.LOG***Corrupt block relative dba: 0x0080000e (file 2, block 14)Fractured block found during media/instance recoveryData 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 recoveryData in bad block - type: 2 format: 2 rdba: 0x0080078e last change scn: 0x0000.064a39c9 seq: 0x1 flg: 0x04 consistency value in tail: 0xac2e0201 completed successfullyTue Feb 14 13:50:58 2006Thread 1 advanced to log sequence 369Thread 1 opened at log sequence 369 Current log# 2 seq# 369 mem# 0: D:ORACLE92ORADATATESTREDO02.LOGSuccessful open of redo thread 1.Tue Feb 14 13:50:59 2006SMON: enabling cache recoveryTue Feb 14 13:51:00 2006Errors in file d:oracle92admintestudumpdb01gen_ora_1888.trc:ORA-00600: internal error code, arguments: [2662], [0], [105557623], [0], [105590063], [8388633], [], []

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

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

可知是文件2发生错误，进而导致600错误。因此，先查看文件2的名字，如下：

SQL> connect sys/manager as sysdbaConnected to an idle instance.SQL> startup mount pfile='d:oracle92ora92databaseinittest.ora'ORACLE instance started.

Total System Global Area 93395628 bytesFixed Size 453292 bytesVariable Size 75497472 bytesDatabase Buffers 16777216 bytesRedo Buffers 667648 bytesDatabase mounted.

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

FILE# STATUS---------- -------NAME-------------------------------------------------------------------------------- 1 SYSTEMD:ORACLE92ORADATATESTSYSTEM01.DBF

2 ONLINED:ORACLE92ORADATATESTUNDOTBS01.DBF

3 ONLINED:ORACLE92ORADATATESTCWMLITE01.DBF

FILE# STATUS---------- -------NAME-------------------------------------------------------------------------------- 4 ONLINED:ORACLE92ORADATATESTDRSYS01.DBF

5 ONLINED:ORACLE92ORADATATESTEXAMPLE01.DBF

6 ONLINED:ORACLE92ORADATATESTINDX01.DBF

FILE# STATUS---------- -------NAME-------------------------------------------------------------------------------- 7 ONLINED:ORACLE92ORADATATESTODM01.DBF

8 ONLINED:ORACLE92ORADATATESTTOOLS01.DBF

9 ONLINED:ORACLE92ORADATATESTUSERS01.DBF

FILE# STATUS---------- -------NAME-------------------------------------------------------------------------------- 10 ONLINED:ORACLE92ORADATATESTXDB01.DBF

11 ONLINED:ORACLE92ORADATATESTPMS.ORA

12 ONLINED:ORACLE92ORADATATESTFYBX.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 abortORACLE instance shut down.SQL> startup pfile='d:oracle92ora92databaseinittest.ora'ORACLE instance started.

Total System Global Area 93395628 bytesFixed Size 453292 bytesVariable Size 75497472 bytesDatabase Buffers 16777216 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.

启动成功，查看下当前的rollback segmentsSQL> 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.

新建一重做表空间undoSQL> create undo tablespace undo datafile 'D:oracle92oradatatestundo01.dbf' size 50M reuse autoextend 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 immediateDatabase closed.Database dismounted.ORACLE instance shut down.

修改init文件*.undo_tablespace=undo

SQL> startup pfile='d:oracle92ora92databaseinittest.ora'ORACLE instance started.

Total System Global Area 93395628 bytesFixed Size 453292 bytesVariable Size 75497472 bytesDatabase Buffers 16777216 bytesRedo Buffers 667648 bytesDatabase 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 immediateDatabase 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:oracle92ora92databaseinittest.ora'ORACLE instance started.

Total System Global Area 93395628 bytesFixed Size 453292 bytesVariable Size 75497472 bytesDatabase Buffers 16777216 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL>至此数据库已经成功修复。

这个图是另一个案例，这个就更直观了，希望可以帮到需要的朋友。