oracle重建UNDO表空间

1.查看数据库undo表空间的dbf文件是否大于25G，如果大于我们则重建。

-rw-r----- 1 oracle oinstall  26G 2011-05-10 10:10 /opt/oracle/oradata/CMCCWAP/undotbs02.dbf

2.查看数据库的默认UNDO表空间（红色部分显示为UNDOTBS2，说明当前使用的是表空间为：UNDOTBS2）

SQL> show parameter undo;

NAME                                TYPE       VALUE

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

undo_management                     string     AUTO

undo_retention                      integer    900

undo_tablespace                     string     UNDOTBS2

3.创建新的UNDO空间UNDOTB1

SQL> create undo tablespace UNDOTBS1 datafile '/opt/oracle/oradata/CMCCWAP/undotbs01.dbf' size 200m autoextend on;

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

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

5.查看数据库的默认表空间是否已经切换到新创建的表空间，如下红色部分使用的是UNDOTBS1，说明已经迁移到新的表空间

SQL> show parameter undo;

NAME                                TYPE       VALUE

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

undo_management                     string     AUTO

undo_retention                      integer    900

undo_tablespace                     string     UNDOTBS1

6.确保所有在UNDOTBS2的undo segment都已offline,如下红色部分表示还未offline

SQL> select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs;

SEGMENT_NAME                  STATUS          TABLESPACE_NAME

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

SYSTEM                        ONLINE          SYSTEM

_SYSSMU1$                     OFFLINE         UNDOTBS2

_SYSSMU2$                     OFFLINE         UNDOTBS2

_SYSSMU3$                     OFFLINE         UNDOTBS2

_SYSSMU4$                     OFFLINE         UNDOTBS2

_SYSSMU5$                     OFFLINE         UNDOTBS2

_SYSSMU6$                     OFFLINE         UNDOTBS2

_SYSSMU7$                     ONLINE          UNDOTBS2

_SYSSMU8$                     OFFLINE         UNDOTBS2

_SYSSMU9$                     OFFLINE         UNDOTBS2

_SYSSMU10$                    OFFLINE         UNDOTBS2

SEGMENT_NAME                  STATUS          TABLESPACE_NAME

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

_SYSSMU11$                    OFFLINE         UNDOTBS2

_SYSSMU12$                    ONLINE          UNDOTBS1

_SYSSMU13$                    ONLINE          UNDOTBS1

_SYSSMU14$                    ONLINE          UNDOTBS1

_SYSSMU15$                    ONLINE          UNDOTBS1

_SYSSMU16$                    ONLINE          UNDOTBS1

17 rows selected.

当oracle在没有undo tablespace时会使用system表空间作为undo tablespace.

7.当上述UNDOTBS2全部offline后，删除原来的UNDO tablespace(如果不删除在备份时会报错)

SQL> drop tablespace UNDOTBS2 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: '/opt/oracle/oradata/CMCCWAP/undotbs02.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 '/opt/oracle/oradata/CMCCWAP/undotbs02.dbf' offline;

Database altered.

删除