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.

删除