oracle 11g rac undo表空间切换
来源：互联网 发布：windows凭据 普通凭据 编辑：程序博客网 时间：2024/05/04 15:09
系统环境： 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 _SYSSMU13_1762171886$ ONLINE
UNDOTBS2 _SYSSMU12_4222198645$ ONLINE
UNDOTBS2 _SYSSMU11_4155293491$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS3 _SYSSMU30_2992889604$ ONLINE
UNDOTBS3 _SYSSMU29_3198754354$ ONLINE
UNDOTBS3 _SYSSMU28_3953353629$ ONLINE
UNDOTBS3 _SYSSMU27_2811255809$ ONLINE
UNDOTBS3 _SYSSMU26_3953879438$ ONLINE
UNDOTBS3 _SYSSMU25_3485703372$ ONLINE
UNDOTBS3 _SYSSMU24_1121934957$ ONLINE
UNDOTBS3 _SYSSMU23_153918182$ ONLINE
UNDOTBS3 _SYSSMU22_1502226063$ ONLINE
UNDOTBS3 _SYSSMU21_3331829347$ ONLINE
32 rows selected.
删除 undotbs1
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
此时，节点1的undo为undotbs3,为了看起舒服，再切换为undotbs1
创建undotbs1
SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '+DATA' SIZE 5000M;
Tablespace created.
修改数据库undo为 undotbs1
SQL> alter system set undo_tablespace=UNDOTBS1 scope=both;
System altered.
查看数据库回滚段的status发现undotbs1online undotbs3 offline
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU31_1711600031$ ONLINE
UNDOTBS1 _SYSSMU9_2798512696$ ONLINE
UNDOTBS1 _SYSSMU8_995173842$ ONLINE
UNDOTBS1 _SYSSMU7_3917959301$ ONLINE
UNDOTBS1 _SYSSMU6_2267393390$ ONLINE
UNDOTBS1 _SYSSMU5_1243198418$ ONLINE
UNDOTBS1 _SYSSMU4_2867323$ ONLINE
UNDOTBS1 _SYSSMU3_4052593799$ ONLINE
UNDOTBS1 _SYSSMU2_3024038969$ ONLINE
UNDOTBS1 _SYSSMU1_2935730364$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
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 _SYSSMU13_1762171886$ ONLINE
UNDOTBS2 _SYSSMU12_4222198645$ ONLINE
UNDOTBS2 _SYSSMU11_4155293491$ ONLINE
UNDOTBS3 _SYSSMU30_2992889604$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS3 _SYSSMU29_3198754354$ OFFLINE
UNDOTBS3 _SYSSMU28_3953353629$ OFFLINE
UNDOTBS3 _SYSSMU27_2811255809$ OFFLINE
UNDOTBS3 _SYSSMU26_3953879438$ OFFLINE
UNDOTBS3 _SYSSMU25_3485703372$ OFFLINE
UNDOTBS3 _SYSSMU24_1121934957$ OFFLINE
UNDOTBS3 _SYSSMU23_153918182$ OFFLINE
UNDOTBS3 _SYSSMU22_1502226063$ OFFLINE
UNDOTBS3 _SYSSMU21_3331829347$ OFFLINE
31 rows selected.
删除undotbs3
SQL> drop tablespace undotbs3 including contents and datafiles;
Tablespace dropped.
SQL>
节点2 操作类似：
s[oracle@db2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 17 15:25:38 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> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
创建undotbs3 5000m,用来替换undotbs2
SQL> CREATE UNDO TABLESPACE "UNDOTBS3" DATAFILE '+DATA' SIZE 500m;
Tablespace created.
修改系统undo为undotbs3
SQL> alter system set undo_tablespace=UNDOTBS3 scope=both;
System altered.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS3
查看数据库回滚段的 status,发现undotbs2 offline, undotbs3 online
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU31_1711600031$ ONLINE
UNDOTBS1 _SYSSMU9_2798512696$ ONLINE
UNDOTBS1 _SYSSMU8_995173842$ ONLINE
UNDOTBS1 _SYSSMU7_3917959301$ ONLINE
UNDOTBS1 _SYSSMU6_2267393390$ ONLINE
UNDOTBS1 _SYSSMU5_1243198418$ ONLINE
UNDOTBS1 _SYSSMU4_2867323$ ONLINE
UNDOTBS1 _SYSSMU3_4052593799$ ONLINE
UNDOTBS1 _SYSSMU2_3024038969$ ONLINE
UNDOTBS1 _SYSSMU1_2935730364$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20_2568447873$ OFFLINE
UNDOTBS2 _SYSSMU19_4150900536$ OFFLINE
UNDOTBS2 _SYSSMU18_1622692891$ OFFLINE
UNDOTBS2 _SYSSMU17_2591770417$ OFFLINE
UNDOTBS2 _SYSSMU16_387766918$ OFFLINE
UNDOTBS2 _SYSSMU15_420029824$ OFFLINE
UNDOTBS2 _SYSSMU14_1191035681$ OFFLINE
UNDOTBS2 _SYSSMU13_1762171886$ OFFLINE
UNDOTBS2 _SYSSMU12_4222198645$ OFFLINE
UNDOTBS2 _SYSSMU11_4155293491$ OFFLINE
UNDOTBS3 _SYSSMU32_1846650689$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS3 _SYSSMU28_3009640607$ ONLINE
UNDOTBS3 _SYSSMU27_2601671938$ ONLINE
UNDOTBS3 _SYSSMU26_1750999348$ ONLINE
UNDOTBS3 _SYSSMU25_1760351227$ ONLINE
UNDOTBS3 _SYSSMU24_4215245524$ ONLINE
UNDOTBS3 _SYSSMU23_2874026201$ ONLINE
UNDOTBS3 _SYSSMU22_2783794725$ ONLINE
UNDOTBS3 _SYSSMU21_2564029858$ ONLINE
UNDOTBS3 _SYSSMU10_548360867$ ONLINE
31 rows selected.
删除undo undotbs2
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
此时，节点2 undo为 undotbs3
为了使用舒服，方便，再做一次切换，undotbs3切换为undotbs2
创建undo undotbs2
SQL> SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA' SIZE 5000M;
Tablespace created.
修改数据库undo 为undotbs2
SQL> alter system set undo_tablespace=UNDOTBS2 ;
System altered.
查看节点2 undo 回滚段的 status，发现undotbs2为online, unotbs3为offline
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU31_1711600031$ ONLINE
UNDOTBS1 _SYSSMU9_2798512696$ ONLINE
UNDOTBS1 _SYSSMU8_995173842$ ONLINE
UNDOTBS1 _SYSSMU7_3917959301$ ONLINE
UNDOTBS1 _SYSSMU6_2267393390$ ONLINE
UNDOTBS1 _SYSSMU5_1243198418$ ONLINE
UNDOTBS1 _SYSSMU4_2867323$ ONLINE
UNDOTBS1 _SYSSMU3_4052593799$ ONLINE
UNDOTBS1 _SYSSMU2_3024038969$ ONLINE
UNDOTBS1 _SYSSMU1_2935730364$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20_4267337343$ ONLINE
UNDOTBS2 _SYSSMU19_2533506319$ ONLINE
UNDOTBS2 _SYSSMU18_3937924111$ ONLINE
UNDOTBS2 _SYSSMU17_1745024019$ ONLINE
UNDOTBS2 _SYSSMU16_3155049813$ ONLINE
UNDOTBS2 _SYSSMU15_1100134579$ ONLINE
UNDOTBS2 _SYSSMU14_1445631384$ ONLINE
UNDOTBS2 _SYSSMU13_3714158714$ ONLINE
UNDOTBS2 _SYSSMU12_2799301969$ ONLINE
UNDOTBS2 _SYSSMU11_2193728667$ ONLINE
UNDOTBS3 _SYSSMU32_1846650689$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS3 _SYSSMU28_3009640607$ OFFLINE
UNDOTBS3 _SYSSMU27_2601671938$ OFFLINE
UNDOTBS3 _SYSSMU26_1750999348$ OFFLINE
UNDOTBS3 _SYSSMU25_1760351227$ OFFLINE
UNDOTBS3 _SYSSMU24_4215245524$ OFFLINE
UNDOTBS3 _SYSSMU23_2874026201$ OFFLINE
UNDOTBS3 _SYSSMU22_2783794725$ OFFLINE
UNDOTBS3 _SYSSMU21_2564029858$ OFFLINE
UNDOTBS3 _SYSSMU10_548360867$ OFFLINE
31 rows selected.
删除undotbs3及datafiles
SQL> drop tablespace undotbs3 including contents and datafiles;
Tablespace dropped.
查询当前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 5000 EXPIRED 10 .63
.01
UNDOTBS1 5000 UNEXPIRED 10 .63
.01
UNDOTBS2 5000 EXPIRED 7 .44
.01
Tablespace Name TS Size(MB) UNDO Stat Used Extents Used Size(MB)
------------------------------ ----------- --------- ------------ -------------
Used Rate(%)
------------
UNDOTBS2 5000 UNEXPIRED 14 .88
.02
- oracle 11g rac undo表空间切换
- Oracle 11g Undo 表空间切换
- oracle undo表空间切换
- ORACLE在线切换undo表空间
- Oracle切换undo表空间操作步骤
- ORACLE在线切换undo表空间
- ORACLE在线切换undo表空间
- undo表空间切换
- 切换UNDO表空间
- oracle表空间－－－undo表空间切换
- oracle 10g undo表空间使用率居高不下bug
- oracle 10g undo表空间使用率居高不下bug
- oracle 10g undo表空间使用率居高不下bug
- 11g Rac 切换
- oracle 10g 11g rac 虚拟环境切换
- Oracle切换undo表空间操作步骤（转）
- ORACLE UNDO表空间
- Oracle UNDO表空间
- Android使用OrmLite数据库框架 之 使用表配置文件
- 3.1 CMMI3级——11个PA简述
- 收藏一些规范化输入输出的PHP函数
- 写自己的ANDROIDSDK 参考资料1
- mysql触发器的实战经验
- oracle 11g rac undo表空间切换
- python doc 简要介绍
- 使用严苛模式打破Android4.0以上平台应用中UI主线程的“独断专行”
- 分布式数据库组件——Database Link
- ETL
- sql server 压缩日志或者数据文件
- 栈和队列题目
- 阿里云笔试题—求矩阵交集
- OCP-1Z0-051-V9.02-67题
