数据库恢复实例一:重建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' size2048m;

 

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.确保所有在UNDOTBS1的undo 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;