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
UNDOTB