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.

刪除

SQL> drop tablespace undotbs1 including contents and datafiles;