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，如需轉載請自行聯系原作者