Oracle中undo表空间的切换

查看操作系统：

SQL>  !cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.4 (Maipo)
查看数据库版本：

SQL> set linesize 400
SQL> select * from v$version;

BANNER                                                                                                   CON_ID
---------------------------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production                                  0
PL/SQL Release 12.1.0.2.0 - Production                                                                        0
CORE    12.1.0.2.0      Production                                                                            0
TNS for Linux: Version 12.1.0.2.0 - Production                                                                0
NLSRTL Version 12.1.0.2.0 - Production                                                                        0

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

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

SQL> col FILE_NAME  format a100
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                      4 /u01/app/oracle/oradata/orcl/undotbs01.dbf                                                                   220

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                   11

（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）

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

Tablespace created.

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

TABLESPACE_NAME         FILE_ID FILE_NAME                                                                                            TOTAL_SPACE
-------------------- ---------- ---------------------------------------------------------------------------------------------------- -----------
SYSTEM                        1 /u01/app/oracle/oradata/orcl/system01.dbf                                                                    790
SYSAUX                        3 /u01/app/oracle/oradata/orcl/sysaux01.dbf                                                                    730
USERS                         6 /u01/app/oracle/oradata/orcl/users01.dbf                                                                       5
UNDOTBS2                     11 /u01/app/oracle/oradata/orcl/undotbs02.dbf                                                                    10
UNDOTBS1                     12 /u01/app/oracle/oradata/orcl/undotbs01.dbf                                                                   220

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

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

System altered.

（2）此时检查UNDOTBS1处于ONLINE状态

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

TABLESPACE_NAME      STATUS             COUNT(*)
-------------------- ---------------- ----------
UNDOTBS1             ONLINE                   11
SYSTEM               ONLINE                    1
UNDOTBS2             OFFLINE                  11

（3）删除

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

no rows selected

SQL> Drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> 

 

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