oracle 11g rac undo表空间切换


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