oracle 11g rac undo表空间切换
系统环境： oracle linux 6.3 x64
数据库环境：oracle 11g r2 rac (2个节点)
undo 切换测试（2个节点UNDOTBS1 UNDOTBS2 分别从8000m 切换为 5000m）
连接1号节点
[oracle@db1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 17 15:15:26 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set linesize 300;
查询 undo_tablespace参数为静态参数
SQL> select name,issys_modifiable from v$parameter where name='undo_tablespace';
NAME ISSYS_MOD
-------------------------------------------------------------------------------- ---------
undo_tablespace IMMEDIATE
查询数据库undo使用情况
SQL> SELECT seg.tablespace_name "Tablespace Name", ts.bytes/1024/1024 "TS Size(MB)",
2 ue.status "UNDO Status", count(*) "Used Extents",
3 round(sum(ue.bytes)/1024/1024, 2) "Used Size(MB)",
4 round(sum(ue.bytes)/ts.bytes*100, 2) "Used Rate(%)"
5 FROM dba_segments seg, DBA_UNDO_EXTENTS ue,
6 (SELECT tablespace_name, sum(bytes) bytes
7 FROM dba_data_files GROUP BY tablespace_name) ts
8 WHERE ue.segment_NAME=seg.segment_NAME and seg.tablespace_name=ts.tablespace_name
9 GROUP BY seg.tablespace_name, ts.bytes, ue.status
10 ORDER BY seg.tablespace_name;
Tablespace Name TS Size(MB) UNDO Stat Used Extents Used Size(MB)
------------------------------ ----------- --------- ------------ -------------
Used Rate(%)
------------
UNDOTBS1 8000 UNEXPIRED 1072 4995.63
62.45
UNDOTBS2 8000 EXPIRED 1 8
.1
UNDOTBS2 8000 UNEXPIRED 814 832
10.4
创建undotbs3 5000m,用来替换undotbs1
SQL> CREATE UNDO TABLESPACE "UNDOTBS3" DATAFILE '+DATA' SIZE 500m;
Tablespace created.
修改系统1号节点 undo表空间为 undotbs3
SQL> alter system set undo_tablespace=UNDOTBS3 scope=both ;
System altered.
查看undo 回滚段status ,undotbs1 为offline,undotbs3为online
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU32_758492050$ OFFLINE
UNDOTBS1 _SYSSMU10_3826054871$ OFFLINE
UNDOTBS1 _SYSSMU9_4279480409$ OFFLINE
UNDOTBS1 _SYSSMU8_330426836$ OFFLINE
UNDOTBS1 _SYSSMU7_1488401252$ OFFLINE
UNDOTBS1 _SYSSMU6_2135419554$ OFFLINE
UNDOTBS1 _SYSSMU5_3201108017$ OFFLINE
UNDOTBS1 _SYSSMU4_416707568$ OFFLINE
UNDOTBS1 _SYSSMU3_2346309449$ OFFLINE
UNDOTBS1 _SYSSMU2_3865903276$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS1 _SYSSMU1_1700093001$ OFFLINE
UNDOTBS2 _SYSSMU20_2568447873$ ONLINE
UNDOTBS2 _SYSSMU19_4150900536$ ONLINE
UNDOTBS2 _SYSSMU18_1622692891$ ONLINE
UNDOTBS2 _SYSSMU17_2591770417$ ONLINE
UNDOTBS2 _SYSSMU16_387766918$ ONLINE
UNDOTBS2 _SYSSMU15_420029824$ ONLINE
UNDOTBS2 _SYSSMU14_1191035681$ ONLINE
UNDOTBS2
查看更多关于oracle 11g rac undo表空间切换的详细内容...