重庆思庄Oracle、Redhat认证学习论坛

标题: Oracle切换undo表空间操作步骤 [打印本页]

作者: mahan 时间: 2025-3-2 14:16
标题: Oracle切换undo表空间操作步骤
操作系统版本及数据库版本如下：

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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)