ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据库恢复实例一:重建undo表空间

数据库恢复实例一:重建undo表空间

原创 Linux操作系统 作者:jifei0611 时间:2008-12-10 14:20:56 0 删除 编辑

数据库恢复实例一:重建undo表空间

数据库错误如下:

ORA-01116:error in opening database file 2
ORA-01110:datafile 2:'/u02/oradata/sfcsys/undotbs01.dbf'
ORA-27041:unable to open file
Linux Error 2:No such file or directory
Additional information:3

根据判断提示判断为UNDO损坏

决定通过重建UNDO表空间来解决这个问题

 

1.创建新的UNDO空间UNDOTB2

SQL>  create undo tablespace undotbs2 datafile  '/u02/oradata/sfcsys/undo2.dbf' size 2048m;

 

2.将数据库的默认表空间切换到新创建的表空间

SQL>  alter system set undo_tablespace=undotbs2 scope=both;

SQL> show parameter undo;

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

 

3.确保所有在UNDOTBS1undo segment都已offline

SQL>  select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs;

SEGMENT_NAME                   STATUS           TABLESPACE_NAME

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

SYSTEM                         ONLINE           SYSTEM

_SYSSMU1$                      OFFLINE          UNDOTBS1

_SYSSMU2$                      OFFLINE          UNDOTBS1

_SYSSMU3$                      OFFLINE          UNDOTBS1

_SYSSMU4$                      OFFLINE          UNDOTBS1

_SYSSMU5$                      OFFLINE          UNDOTBS1

_SYSSMU6$                      OFFLINE          UNDOTBS1

_SYSSMU7$                      OFFLINE          UNDOTBS1

_SYSSMU8$                      OFFLINE          UNDOTBS1

_SYSSMU9$                      OFFLINE          UNDOTBS1

_SYSSMU10$                     OFFLINE          UNDOTBS1

 

SEGMENT_NAME                   STATUS           TABLESPACE_NAME

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

_SYSSMU11$                     OFFLINE          UNDOTBS1

_SYSSMU12$                     OFFLINE          UNDOTBS1

_SYSSMU13$                     OFFLINE          UNDOTBS1

_SYSSMU14$                     ONLINE           UNDOTBS2

_SYSSMU15$                     ONLINE           UNDOTBS2

_SYSSMU16$                     ONLINE           UNDOTBS2

17 rows selected.

上面红色记录说明oracle在没有undo tablespace时会使用system表空间作为undo tablespace.

 

4.删除原来的UNDO tablespace(如果不删除在备份时会报错)

SQL> drop tablespace undotbs1 including contents and datafiles;

drop tablespace undotbs1 including contents and datafiles

*

ERROR at line 1:

ORA-01116: error in opening database file 2

ORA-01110: data file 2: '/u02/oradata/sfcsys/undotbs01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

offline data file 2

SQL> alter database datafile '/u02/oradata/sfcsys/undotbs01.dbf' offline;

 

Database altered.

删除

SQL>  drop tablespace undotbs1 including contents and datafiles;

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

请登录后发表评论 登录
全部评论

注册时间:2008-01-12

  • 博文量
    143
  • 访问量
    282064