undo_management设置与隐含参数*._offline_rollback_segments和*._corrupted_rollback_segments关系

一 环境：

oracle版本：10.2.0.1.0

当前undo表空间：

SQL> show parameter undo;

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS4

当前系统内的还原表空间：

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name like '%UNDO%';

TABLESPACE_NAME                STATUS

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

UNDOTBS4                       ONLINE

当前undo表空间undotbs4的所有回滚段状况如下：

SQL> select * from v$rollname; （查询当前系统活动回滚段）

       USN NAME

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

         0 SYSTEM

         1 _SYSSMU1$

         2 _SYSSMU2$

         3 _SYSSMU3$

         4 _SYSSMU4$

         5 _SYSSMU5$

         6 _SYSSMU6$

         7 _SYSSMU7$

         8 _SYSSMU8$

         9 _SYSSMU9$

        11 _SYSSMU11$

11 rows selected.

SQL> select segment_id,segment_name,tablespace_name,status from dba_rollback_segs;（查询系统内所有回滚段信息）

SEGMENT_ID SEGMENT_NAME                   TABLESPACE_NAME                STATUS

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

         0 SYSTEM                         SYSTEM                         ONLINE

         1 _SYSSMU1$                      UNDOTBS4                       ONLINE

         2 _SYSSMU2$                      UNDOTBS4                       ONLINE

         3 _SYSSMU3$                      UNDOTBS4                       ONLINE

         4 _SYSSMU4$                      UNDOTBS4                       ONLINE

         5 _SYSSMU5$                      UNDOTBS4                       ONLINE

         6 _SYSSMU6$                      UNDOTBS4                       ONLINE

         7 _SYSSMU7$                      UNDOTBS4                       ONLINE

         8 _SYSSMU8$                      UNDOTBS4                       ONLINE

         9 _SYSSMU9$                      UNDOTBS4                       ONLINE

        11 _SYSSMU11$                     UNDOTBS4                       ONLINE

SQL> 

二 测试

当系统有一个还原表空间undotbs4

测试1 undo_management=auto ，用参数*._offline_rollback_segments和*._corrupted_rollback_segments标记当前undo表空间undotbs4的所有段，查询当前oracle所有活动回滚段。

测试2 undo_management=manual ，用参数*._offline_rollback_segments和*._corrupted_rollback_segments标记当前undo表空间undotbs4的所有段，查询当前oracle所有活动回滚段。

当系统有多个还原表空间，undotbs4、undotbs3、undotbs2、undotbs1

测试3 undo_management=auto ，用参数*._offline_rollback_segments和*._corrupted_rollback_segments标记当前undo表空间undotbs4的所有段，查询当前oracle所有活动回滚段。

测试4 undo_management=manual， 用参数*._offline_rollback_segments和*._corrupted_rollback_segments标记当前undo表空间undotbs4的所有段，查询当前oracle所有活动回滚段。

三实验过程

当系统仅有一个还原表空间undotbs4 

测试1：

initCRM.ora 内容如下：

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS4'

*.user_dump_dest='/oracle/app/admin/CRM/udump'

*._offline_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'

*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'

oracle启动过程如下：

SQL> startup pfile='/oracle/app/db1/dbs/initCRM.ora';

ORACLE instance started.

Total System Global Area  322961408 bytes

Fixed Size                  2020480 bytes

Variable Size              83889024 bytes

Database Buffers          230686720 bytes

Redo Buffers                6365184 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

告警文件错误信息如下：

Errors in file /oracle/app/admin/CRM/udump/crm_ora_9154.trc:

ORA-30012: undo tablespace 'UNDOTBS4' does not exist or of wrong type

Tue Nov 13 22:13:15 2012

Error 30012 happened during db open, shutting down database

USER: terminating instance due to error 30012

Instance terminated by USER, pid = 9154

ORA-1092 signalled during: ALTER DATABASE OPEN...

总结：库都启动不了，回滚段信息就不要看了。

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

测试2：

*.undo_management='MANUAL'

SQL>  startup pfile='/oracle/app/db1/dbs/initCRM.ora';

Database opened.

当前oracle所有活动回滚段状态如下:

 select segment_id,segment_name,tablespace_name,status from dba_rollback_segs

         1 _SYSSMU1$                      UNDOTBS4                       OFFLINE

         2 _SYSSMU2$                      UNDOTBS4                       OFFLINE

         3 _SYSSMU3$                      UNDOTBS4                       OFFLINE

         4 _SYSSMU4$                      UNDOTBS4                       OFFLINE

         5 _SYSSMU5$                      UNDOTBS4                       OFFLINE

         6 _SYSSMU6$                      UNDOTBS4                       OFFLINE

         7 _SYSSMU7$                      UNDOTBS4                       OFFLINE

         8 _SYSSMU8$                      UNDOTBS4                       OFFLINE

         9 _SYSSMU9$                      UNDOTBS4                       OFFLINE

        11 _SYSSMU11$                     UNDOTBS4                       OFFLINE

SQL> select * from v$rollname;

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

当系统有多个还原表空间时进行如下测试:

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name like '%UNDO%';

UNDOTBS1                       ONLINE

UNDOTBS2                       ONLINE

UNDOTBS3                       ONLINE

undo_management                      string      MANUAL

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

测试3

        12 _SYSSMU12$                     UNDOTBS1                       OFFLINE

        13 _SYSSMU13$                     UNDOTBS1                       OFFLINE

        14 _SYSSMU14$                     UNDOTBS1                       OFFLINE

        15 _SYSSMU15$                     UNDOTBS1                       OFFLINE

        16 _SYSSMU16$                     UNDOTBS1                       OFFLINE

        17 _SYSSMU17$                     UNDOTBS1                       OFFLINE

        18 _SYSSMU18$                     UNDOTBS1                       OFFLINE

        19 _SYSSMU19$                     UNDOTBS1                       OFFLINE

        20 _SYSSMU20$                     UNDOTBS1                       OFFLINE

        21 _SYSSMU21$                     UNDOTBS1                       OFFLINE

        22 _SYSSMU22$                     UNDOTBS2                       OFFLINE

        23 _SYSSMU23$                     UNDOTBS2                       OFFLINE

        24 _SYSSMU24$                     UNDOTBS2                       OFFLINE

        25 _SYSSMU25$                     UNDOTBS2                       OFFLINE

        26 _SYSSMU26$                     UNDOTBS2                       OFFLINE

        27 _SYSSMU27$                     UNDOTBS2                       OFFLINE

        28 _SYSSMU28$                     UNDOTBS2                       OFFLINE

        29 _SYSSMU29$                     UNDOTBS2                       OFFLINE

        30 _SYSSMU30$                     UNDOTBS2                       OFFLINE

        31 _SYSSMU31$                     UNDOTBS2                       OFFLINE

        32 _SYSSMU32$                     UNDOTBS3                       OFFLINE

        33 _SYSSMU33$                     UNDOTBS3                       OFFLINE

        34 _SYSSMU34$                     UNDOTBS3                       OFFLINE

        35 _SYSSMU35$                     UNDOTBS3                       OFFLINE

        36 _SYSSMU36$                     UNDOTBS3                       OFFLINE

        37 _SYSSMU37$                     UNDOTBS3                       OFFLINE

        38 _SYSSMU38$                     UNDOTBS3                       OFFLINE

        39 _SYSSMU39$                     UNDOTBS3                       OFFLINE

        40 _SYSSMU40$                     UNDOTBS3                       OFFLINE

        41 _SYSSMU41$                     UNDOTBS3                       OFFLINE

        42 _SYSSMU42$                     UNDOTBS4                       ONLINE

42 rows selected.

        42 _SYSSMU42$

总结:事实上当我们用参数做如下设置时

*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$','_SYSSMU42$'

*._offline_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$','_SYSSMU42$'

启动后，oracle会产生另一个非系统回滚段_SYSSMU43$如下： 

        43 _SYSSMU43$

SQL> select segment_id,segment_name,tablespace_name,status from dba_rollback_segs

  2  ;

          。。。。。。。。。。。。。。。此处省略数行。。。。。。。。。

        43 _SYSSMU43$                     UNDOTBS4                       ONLINE

43 rows selected.

也就是说当undo_management=auto时，即使用隐含参数_corrupted_rollback_segments和_offline_rollback_segments标记当前undo表空间所有回滚段，数据库启动时oracle也总会创建一个非系统回滚段，直到undo表空间容量耗尽（猜测）

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

测试4：

initCRM.ora参数如下

SQL> select segment_id,segment_name,tablespace_name,status from dba_rollback_segs;

        42 _SYSSMU42$                     UNDOTBS4                       OFFLINE

        43 _SYSSMU43$                     UNDOTBS4                       OFFLINE

实验完毕

总结如下：当我们用隐含参数_offline_rollback_segments、_corrupted_rollback_segments处理回滚段某些问题的时候，undo_management=manual为好。

本文转自 zhangxuwl 51CTO博客，原文链接：http://blog.51cto.com/jiujian/1059870，如需转载请自行联系原作者