Oracle 11g Undo tablespace switchover

Oracle 11g Undo tablespace switchover -- 1. view the undo tablespace currently used by the instance and the undo related parameters SQL> show parameter undo NAME TYPE VALUE =----------- export undo_management string AUTOundo_retention integer 86400undo_tablespace string UNDOTBS2 -- 2, create undo tablespace UNDOTBS3 datafile + DATA/hxcx/datafile/undotbs3_01.dbf 'size 30G autoextend on next 100 m maxsize unlimi Ted, '+ DATA/hxcx/datafile/undotbs3_02.dbf' size 30G autoextend on next 100 m maxsize unlimited; create undo tablespace UNDOTBS3 datafile '+ DATA/hxcx/datafile/undotbs1_03.dbf' size 30G autoextend on next 100 m maxsize unlimited, '+ DATA/hxcx/datafile/undotbs1_04.dbf' size 30G autoextend on next 100 m maxsize unlimited; -- 3. Switch the current undo tablespace SQL of the instance> alter system set undo_tablespace = UNDOTBS3; system altered. [54526538] ***** active transactions found in undo Tablespace 4-moved to Pending Switch-Out state. [54526538] active transactions found/affinity dissolution incompletein undo tablespace 4 during switch-out.ALTER system set undo_tablespace = 'undotbs3' SCOPE = BOTH; mon Jun 03 09:49:53 2013 [43385080] Undo Tablespace 4 successfully switched out. -- alert. log indicates that there are still ongoing tasks in the current undo tablespace during the switchover (so it is best to avoid Transactions) -- 4. Check whether the undo tablespace switchover takes effect. SQL> show parameter undo NAME TYPE VALUE =----------- interval undo_management string AUTOundo_retention integer 86400undo_tablespace string UNDOTBS3SQL> -- the undo tablespace UNDOTBS3 hybrid rollback segment should is the online status set linesize 200 select SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS from dba_rollback_segs where tablespace_name = 'undotbs3 '; -- The original undo tablespace cannot be deleted immediately due to undo_retention. It can only be deleted after the rollback segments of the original undo tablespace change to offline. -- You can create an undo tablespace at any time and switch the current undo tablespace of the instance. However, the tablespace can be deleted only when all rollback segments in the original undo tablespace are offline. -- You can change the STATUS of the original undo tablespace by modifying undo_retention to set linesize 200 select SEGMENT_NAME, OWNER, TABLESPACE_NAME, status from dba_rollback_segs where tablespace_name = 'undotbs1 'and STATUS = 'offline '; select count (*) from dba_rollback_segs where tablespace_name = 'undotbs1 'and status = 'online'; -- the output value should be 0 -- 5. After confirming that all the original undo tablespace rollback segments are offline, set this tablespace to offline alter tablespace UNDOTBS1 offline; SQL> select TABLESPACE_NAME, STATUS, CONTENTS from dba_tablespaces where tablespace_name = 'undotbs2 '; TABLESPACE_NAME status contents tables --------- UNDOTBS2 online undo -- 6. Delete the original undo tablespace extreme data file drop tablespace UNDOTBS1 including contents and datafiles; alter tablespace rename UNDOTBS3 to UNDOTBS1; alter system set undo_tablespace = UNDOTBS2; SQL> alter system set undo_retention = 900; System altered.