Oracle切换undo表空间操作步骤

mahan

操作系统版本及数据库版本如下：

SQL> !cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.5 (Santiago)



SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production





查看当前使用的undo表空间信息

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS1



SQL> select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                   FILE_ID       FILE_NAME                                     TOTAL_SPACE

------------------------------ ---------------------------------------- ---------------------------------------- ----------

UNDOTBS1                                3        /u01/app/oracle/oradata/orcl/undotbs01.dbf    60





1、数据库状态静止时（无DML操作期间）执行UNDO表空间切换（由UNDOTBS1切换为UNDOTBS2）

（1）创建新的undo表空间UNDOTBS2

SQL>  create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 10M;

Tablespace created.



（2）切换UNDOTBS2为新的undo表空间

SQL> alter system set undo_tablespace = undotbs2 scope=both;

System altered.



（3）此时数据库处于静止状态，无任何DML操作，查看UNDOTBS1已经处于OFFLINE状态

SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;

TABLESPACE_NAME                STATUS             COUNT(*)

------------------------------ ---------------- ----------

UNDOTBS1                       OFFLINE                  10

SYSTEM                         ONLINE                    1

UNDOTBS2                       ONLINE                   10



（4）检查确认UNDOTBS1中没有ONLINE的segment

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';

no rows selected



（5）删除旧的UNDOTBS1

SQL> Drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.



（6）至此，undo表空间由UNDOTBS1成功切换为UNDOTBS2.





2、数据库中有DML操作期间，切换UNDO表空间步骤（由UNDOTBS2切换为UNDOTBS1）

session 1正在执行如下操作：

conn test/test

create table test (name varchar(2));

insert into test values ('zhangsan');

commit;

update test set name='lisi' where name='zhangsan';

此时未提交



session 2开始切换undo表空间操作

（1）确认当前使用的undo表空间

SQL>  show parameter undo_tablespace

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS2



（2）查看当前undo表空间的所有SELMENT均为ONLINE状态

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';

STATUS           SEGMENT_NAME

---------------- ------------------------------

ONLINE           _SYSSMU11_3423735372$

ONLINE           _SYSSMU12_567660877$

ONLINE           _SYSSMU13_100563780$

ONLINE           _SYSSMU14_1447748955$

ONLINE           _SYSSMU15_478708454$

ONLINE           _SYSSMU16_3309423900$

ONLINE           _SYSSMU17_525951688$

ONLINE           _SYSSMU18_130984470$

ONLINE           _SYSSMU19_3964826557$

ONLINE           _SYSSMU20_994913344$

10 rows selected.



（3）创建新的undo表空间UNDOTBS1

SQL> create undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 10M;

Tablespace created.



（4）设置UNDOTBS1为默认undo表空间

SQL> alter system set undo_tablespace = undotbs1 scope=both;

System altered.



（5）此时检查UNDOTBS2中任然有一个SEGMENT处于ONLINE状态

SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;

TABLESPACE_NAME                STATUS             COUNT(*)

------------------------------ ---------------- ----------

UNDOTBS1                       ONLINE                   10

SYSTEM                         ONLINE                    1

UNDOTBS2                       OFFLINE                   9

UNDOTBS2                       ONLINE                    1



SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';

STATUS           SEGMENT_NAME

---------------- ------------------------------

ONLINE           _SYSSMU15_478708454$



故此时不能盲目删除UNDOTBS2表空间，否则将报错或导致数据不一致；甚至会导致数据库故障（或许~大概~有可能。。。）

此时需等待UNDOTBS2变为OFFLINE后才可执行删除该表空间的操作。

等待ing...



（6）此时回到session 1执行commit;，再回到session 2查看UNDOTBS2的状态变为OFFLINE

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';

no rows selected



SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;

TABLESPACE_NAME                STATUS             COUNT(*)

------------------------------ ---------------- ----------

UNDOTBS1                       ONLINE                   10

SYSTEM                         ONLINE                    1

UNDOTBS2                       OFFLINE                  10



（7）此时删除UNDOTBS2即可

SQL> Drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.



SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900



undo_tablespace                      string      UNDOTBS1





（8）至此，undo表空间由UNDOTBS2成功切换为UNDOTBS1