回滾段表空間資料檔案損壞解決方法

症狀：回滾表空間資料檔案顯示離線，需要恢復,資料檔案已經損壞,但回滾表空間聯機正常,發出命令使資料檔案聯機時,出現以下錯誤

 

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/orasys/oracle/oradata/orasvr2/undotbs01.dbf'

 

檢視系統回滾段的情況

SELECT segment_name,tablespace_name,owner,status FROM dba_rollback_segs;

結果: _SYSSMU1$ 到 _SYSSMU10$ 的狀態都是 "NEED RECOVERY"

 

解決過程：

-bash-2.05b$ sqlplus '/as sysdba'

 

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Aug 1 15:41:28 2006

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

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

 

-bash-2.05b$ ls

control01.ctl  indx01.dbf    temp01.dbf     users01.dbf

drsys01.dbf    redo01.log    tools01.dbf    xdb01.dbf

example01.dbf  system01.dbf  undotbS01.dbf

 

-bash-2.05b$ mv undotbs01.dbf undotbs01.dbf.xxx

 

-bash-2.05b$ sqlplus /nolog

 

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Aug 1 15:41:28 2006

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

 

SQL> connect / as sysdba

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  143725064 bytes

Fixed Size                   451080 bytes

Variable Size             109051904 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: '/orasys/oracle/oradata/orasvr2/undotbs01.dbf'

 

 

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     10800

undo_suppress_errors                 boolean     FALSE

undo_tablespace                      string      UNDOTBS1

 

SQL> alter system set undo_management = manual scope=spfile;

 

System altered.

 

SQL> shutdown

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

 

-bash-2.05b$ sqlplus /nolog

 

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Aug 1 15:41:28 2006

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

 

SQL> connect / as sysdba;

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 

Total System Global Area  143725064 bytes

Fixed Size                   451080 bytes

Variable Size             109051904 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2:

 

SQL> alter database datafile 

'/orasys/oracle/oradata/orasvr2/undotbs01.dbf'   offline drop;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> drop tablespace undotbs1;

ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace 

發現有活動的事物還在undo表空間,不能刪除undo表空間

SQL> create pfile from spfile;

File created

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

編輯initorasvr2.ORA 引數

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$) 

SQL>create spfile from pfile;

     File Created

SQL>startup;

ORACLE instance started

Total System Global Area  101784276 bytes

Fixed Size                   453332 bytes

Variable Size              75497472 bytes

Database Buffers           25165824 bytes

Redo Buffers                 667648 bytes

Alter database mount

Alter database open

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> create UNDO tablespace undotbs1

datafile '/orasys/oracle/oradata/orasvr2/undotbs01.dbf' size 

250m  autoextend on next 1m maxsize 1024m;

Tablespace created.

SQL> alter system set undo_management = auto scope=spfile;

System altered.

 

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

把加入的這段引數去掉：_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$) 

重新啟動資料庫後即可

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS

------------------------------ ----------------

SYSTEM                         ONLINE

_SYSSMU1$                      ONLINE

_SYSSMU2$                      ONLINE

_SYSSMU3$                      ONLINE

_SYSSMU4$                      ONLINE

_SYSSMU5$                      ONLINE

_SYSSMU6$                      ONLINE

_SYSSMU7$                      ONLINE

_SYSSMU8$                      ONLINE

_SYSSMU9$                      ONLINE

_SYSSMU10$                     ONLINE

11 rows selected