Oracle undo資料表空間切換

Oracle的AUM(Auto Undo Management)從出生以來就經常出現只擴充,不收縮(shrink)的情況(通常我們可以設定足夠的UNDO資料表空間大小,然後取消其自動擴充屬性),在實際的環境中遇到undo資料表空間為斷上漲，警示簡訊不斷。

1.確認檔案

SQL> select file_name,bytes/1024/1024 from dba_data_files 2 where tablespace_name like 'UNDOTBS1';FILE_NAME--------------------------------------------------------------------------------BYTES/1024/1024---------------+ORADG/danaly/datafile/undotbs1.265.600173875 27810

2.檢查UNDO Segment狀態

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks 2 from v$rollstat order by rssize; USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS---------- ---------- --------------------- ---------------------- ---------- 0 0 .000358582 .000358582 0 2 0 .071517944 .071517944 0 3 0 .13722229 .13722229 0 9 0 .236984253 .236984253 0 10 0 .625144958 .625144958 0 5 1 1.22946167 1.22946167 0 8 0 1.27175903 1.27175903 0 4 1 1.27895355 1.27895355 0 7 0 1.56770325 1.56770325 0 1 0 2.02474976 2.02474976 0 6 0 2.9671936 2.9671936 011 rows selected.

3.建立新的UNDO資料表空間

SQL> create undo tablespace undotbs2;Tablespace created.

4.切換UNDO資料表空間為新的UNDO資料表空間

SQL> alter system set undo_tablespace=undotbs2 scope=both;System altered.

此處使用spfile需要注意，以前曾經記錄過這樣一個案例:Oracle診斷案例-Spfile案例一則
5.等待原UNDO資料表空間所有UNDO SEGMENT OFFLINE

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks 2 from v$rollstat order by rssize; USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS---------- ---------- --------------- --------------------- ---------------------- ---------- 14 0 ONLINE .000114441 .000114441 0 19 0 ONLINE .000114441 .000114441 0 11 0 ONLINE .000114441 .000114441 0 12 0 ONLINE .000114441 .000114441 0 13 0 ONLINE .000114441 .000114441 0 20 0 ONLINE .000114441 .000114441 0 15 1 ONLINE .000114441 .000114441 0 16 0 ONLINE .000114441 .000114441 0 17 0 ONLINE .000114441 .000114441 0 18 0 ONLINE .000114441 .000114441 0 0 0 ONLINE .000358582 .000358582 0 USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS---------- ---------- --------------- --------------------- ---------------------- ---------- 6 0 PENDING OFFLINE 2.9671936 2.9671936 012 rows selected.

再看:

11:32:11 SQL> / USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS---------- ---------- --------------- --------------------- ---------------------- ---------- 15 1 ONLINE .000114441 .000114441 0 11 0 ONLINE .000114441 .000114441 0 12 0 ONLINE .000114441 .000114441 0 13 0 ONLINE .000114441 .000114441 0 14 0 ONLINE .000114441 .000114441 0 20 0 ONLINE .000114441 .000114441 0 16 0 ONLINE .000114441 .000114441 0 17 0 ONLINE .000114441 .000114441 0 18 0 ONLINE .000114441 .000114441 0 19 0 ONLINE .000114441 .000114441 0 0 0 ONLINE .000358582 .000358582 011 rows selected.Elapsed: 00:00:00.00

6.刪除原UNDO資料表空間

11:34:00 SQL> drop tablespace undotbs1 including contents;Tablespace dropped.Elapsed: 00:00:03.13

以下是自己實際操作;

-- 操作步驟
1.建立undo資料表空間
create undo tablespace undotbs2 datafile '/dev/vgdata01/rlv_ora_log11';
alter tablespace undotbs2 add datafile '/dev/vgdata01/rlv_ora_log12';
alter tablespace undotbs2 add datafile '/dev/vgdata01/rlv_ora_log13';
2.切換undo資料表空間
alter system set undo_tablespace=undotbs2 scope=both;
3.刪除原資料表空間
drop tablespace undotbs1 including contents;
4.原資料表空間及資料檔案：
/dev/vgdata01/rlv_ora_rbs01 UNDOTBS1