正确切换UNDO TABLESPACE
有些时候,我们需要切换undo tablespace,也就是说用一个新的undo tablespace来替换目前的undo tablespace,切换前必须保证被切换的undo tablespace上没有活动或悬挂的事务,如果有事务活动,那么切换将有可能导致数据库hang. 下面给出一个切换的例子:
将现有的undo_b切换到新建的undo_c
1、确认要切换的undo tablespace
SQL> show parameter undo_tablespace
NAME TS Type VALUE
———————————— ———— ——————————
undo_tablespace string UNDO_B
2、创建新的undo tablespace
SQL> create undo tablespace undo_c datafile size 10M autoextend on next 10M extent management local autoallocate;
Tablespace created.
3、确认当前undo tablespace没有活动的事务
set linesize 120
SQL> col tablespace_name format a15 heading “Undo.ts.name”
SQL> col segment_name format a20 heading “Undo.Seg.Name”
SQL> col ktuxesta heading “Transaction|State”
SQL> col ktuxecfl format a15 heading “Transaction|desc”
SQL> col ktuxesiz heading “Transaction|Size(Blocks)”
SQL> col trans format a15 heading “Transactions|Num”
SQL> SELECT a.tablespace_name,a.segment_name,b.ktuxesta,b.ktuxecfl,b.ktuxeusn||’.'||b.ktuxeslt||’.'||b.ktuxesqn trans
2 FROM dba_rollback_segs a,
3 x$ktuxe b
4 WHERE a.segment_id = b.ktuxeusn
5 AND a.tablespace_name = UPPER(‘&tsname’)
6 AND b.ktuxesta <> ‘INACTIVE’
7 /
Enter value for tsname: undo_b
old 5: AND a.tablespace_name = UPPER(‘&tsname’)
new 5: AND a.tablespace_name = UPPER(‘undo_b’)
no rows selected
如果有活动的事务,类似于下面的输出,必须等待所有事务结束后,在进行切换:
Enter value for tsname: undo_b
old 5: AND a.tablespace_name = UPPER(‘&tsname’)
new 5: AND a.tablespace_name = UPPER(‘undo_b’)
Transaction Transaction Transactions
Undo.ts.name Undo.Seg.Name State desc Num
————— ——————– —————- ————— —————
UNDO_B _SYSSMU27$ ACTIVE NONE 27.5.10
4、在确认没有活动的事务后,执行切换
SQL> alter system set undo_tablespace=undo_c;
System altered.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
—————- ——————————– ——————————
undo_tablespace string UNDO_C
5、确认undo_b的所有undo segment全部offline;
SQL> select file_id from dba_data_files where tablespace_name=’UNDO_B’;
FILE_ID
———-
7
SQL> select segment_name,status from dba_rollback_segs where file_id=7;
Undo.Seg.Name STATUS
——————– —————-
_SYSSMU21$ OFFLINE
_SYSSMU22$ OFFLINE
_SYSSMU23$ OFFLINE
_SYSSMU24$ OFFLINE
_SYSSMU25$ OFFLINE
_SYSSMU26$ OFFLINE
_SYSSMU27$ OFFLINE
_SYSSMU28$ OFFLINE
_SYSSMU29$ OFFLINE
_SYSSMU30$ OFFLINE
10 rows selected.
这个过程还可以从alert日志观察:
Successfully onlined Undo Tablespace 10.
Undo Tablespace 9 successfully switched out.
这里的9和10代表tablespace的number.
SQL> select ts#,name from v$tablespace;
TS# Tablespace Name
———- —————
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
4 USERS
3 TEMP
7 IMPTEST
8 UNDO_A
9 UNDO_B
10 UNDO_C
切换完毕.