Oracle undo表空间切换

Oracle的AUM（Auto Undo Management）从出生以来就经常出现只扩展,不收缩（shrink）的情况（通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性）,在实际的环境中遇到undo表空间为断上涨，报警短信不断。1.确认文件

SQL> select file_name,bytes/1024/1024 from dba_data_files2where tablespace_name like "UNDOTBS1";FILE_NAME--------------------------------------------------------------------------------BYTES/1024/1024---------------+ORADG/danaly/datafile/undotbs1.265.60017387527810

2.检查UNDO Segment状态

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks2from v$rollstat order by rssize; USNXACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024SHRINKS---------- ---------- --------------------- ---------------------- ---------- 00.000358582 .0003585820 20.071517944 .0715179440 30 .13722229.137222290 90.236984253 .2369842530100.625144958 .6251449580 511.22946167 1.229461670 801.27175903 1.271759030 411.27895355 1.278953550 701.56770325 1.567703250 102.02474976 2.024749760 60 2.96719362.9671936011 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,shrinks2 from v$rollstat order by rssize; USNXACTS STATUSRSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024SHRINKS---------- ---------- --------------- --------------------- ---------------------- ----------140 ONLINE .000114441 .0001144410190 ONLINE .000114441 .0001144410110 ONLINE .000114441 .0001144410120 ONLINE .000114441 .0001144410130 ONLINE .000114441 .0001144410200 ONLINE .000114441 .0001144410151 ONLINE .000114441 .0001144410160 ONLINE .000114441 .0001144410170 ONLINE .000114441 .0001144410180 ONLINE .000114441 .0001144410 00 ONLINE .000358582 .0003585820 USNXACTS STATUSRSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024SHRINKS---------- ---------- --------------- --------------------- ---------------------- ---------- 60 PENDING OFFLINE 2.96719362.9671936012 rows selected.

再看:

11:32:11 SQL> / USNXACTS STATUSRSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024SHRINKS---------- ---------- --------------- --------------------- ---------------------- ----------151 ONLINE .000114441 .0001144410110 ONLINE .000114441 .0001144410120 ONLINE .000114441 .0001144410130 ONLINE .000114441 .0001144410140 ONLINE .000114441 .0001144410200 ONLINE .000114441 .0001144410160 ONLINE .000114441 .0001144410170 ONLINE .000114441 .0001144410180 ONLINE .000114441 .0001144410190 ONLINE .000114441 .0001144410 00 ONLINE .000358582 .000358582011 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 UNDOTBS1Oralce 10g rac 归档模式修改Linux Oracle10gR2 创建ASM实例安装数据库相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）