ITPub博客

回滚段表空间数据文件损坏解决方法

原创 Linux操作系统 作者:paulyibinyi 时间:2007-12-14 16:12:37 0 删除 编辑

症状:回滚表空间数据文件显示脱机,需要恢复,数据文件已经损坏,但回滚表空间联机正常,发出命令使数据文件联机时,出现以下错误

 

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

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-600/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
oracle 10g ocm oracle 10g/11g/12c ocp aix 6.1 administrator,ogg expert,ITSS 技术交流群 201703254 微信公众号 paulyibin 探讨技术,开心工作 电话 13719354869 ,深入研究数据库和开始研究big data

注册时间:2007-12-11

  • 博文量
    905
  • 访问量
    6485088