oracle更换undo,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 0

11 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 0

12 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 0

11 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