AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2017-11-16 1232
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
一 环境：
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
11 rows selected.
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：
initCRM.ora 内容如下：
*.undo_management='MANUAL'
*.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.
Database opened.
当前oracle所有活动回滚段状态如下:
 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                       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
11 rows selected.
SQL> select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
当系统有多个还原表空间时进行如下测试:
SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name like '%UNDO%';
TABLESPACE_NAME                STATUS
------------------------------ ---------
UNDOTBS1                       ONLINE
UNDOTBS2                       ONLINE
UNDOTBS3                       ONLINE
UNDOTBS4                       ONLINE
SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS4
----------------------------------------------------------------------------------------------------------
测试3
initCRM.ora 内容如下：
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS4'
*.user_dump_dest='/oracle/app/admin/CRM/udump'
*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'
*._offline_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.
Database opened.
当前oracle所有活动回滚段状态如下:
SEGMENT_ID SEGMENT_NAME                   TABLESPACE_NAME                STATUS
---------- ------------------------------ ------------------------------ ----------------
         0 SYSTEM                         SYSTEM                         ONLINE
         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
        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.
SQL> select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
        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$如下： 
SQL> select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
        43 _SYSSMU43$
  
SQL> select segment_id,segment_name,tablespace_name,status from dba_rollback_segs
  2  ;
SEGMENT_ID SEGMENT_NAME                   TABLESPACE_NAME                STATUS
---------- ------------------------------ ------------------------------ ----------------
         0 SYSTEM                         SYSTEM                         ONLINE
         1 _SYSSMU1$                      UNDOTBS4                       OFFLINE
         2 _SYSSMU2$                      UNDOTBS4                       OFFLINE
         3 _SYSSMU3$                      UNDOTBS4                       OFFLINE
          。。。。。。。。。。。。。。。此处省略数行。。。。。。。。。
        43 _SYSSMU43$                     UNDOTBS4                       ONLINE
43 rows selected.
也就是说当undo_management=auto时，即使用隐含参数_corrupted_rollback_segments和_offline_rollback_segments标记当前undo表空间所有回滚段，数据库启动时oracle也总会创建一个非系统回滚段，直到undo表空间容量耗尽（猜测）
------------------------------------------------------------------------------------------------------------------------------------------------
测试4：
initCRM.ora参数如下
*.undo_management='MANUAL'
*.undo_tablespace='UNDOTBS4'
*.user_dump_dest='/oracle/app/admin/CRM/udump'
*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'
*._offline_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.
Database opened.
当前oracle所有活动回滚段状态如下:
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                       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
        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                       OFFLINE
        43 _SYSSMU43$                     UNDOTBS4                       OFFLINE
43 rows selected.
SQL> select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
实验完毕
总结如下：当我们用隐含参数_offline_rollback_segments、_corrupted_rollback_segments处理回滚段某些问题的时候，undo_management=manual为好。









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

文章标签：
数据库备份
关系型数据库
Oracle
数据库
余二五
目录
相关文章
长征2号
|
数据库
ORA-02062: distributed recovery处理方法
长征2号
1612 0
科技小能手
|
前端开发 关系型数据库 数据库
_CORRUPTED_ROLLBACK_SEGMENTS隐藏参数
科技小能手
1160 0
科技小能手
|
SQL
_OFFLINE_ROLLBACK_SEGMENTS隐藏参数
科技小能手
1085 0
科技小能手
|
SQL Oracle 关系型数据库
了解你所不知道的SMON功能(五):Recover Dead transaction
科技小能手
1445 0
科技小能手
EVENT:10218 dump uba of applied undo
科技小能手
767 0
科技小能手
|
SQL Oracle 关系型数据库
Examine 11g automatic block Corruption recovery
科技小能手
1221 0
科技小能手
|
SQL Perl
undo backup optimization does not work on 11.2.0.1?
科技小能手
1159 0
科技小能手
了解你所不知道的SMON功能(八):Transaction Recover
科技小能手
1031 0
余二五
|
Oracle 关系型数据库 数据库
Oracle中的ORA-01548: active rollback segment '_SYSSMU1$' found
余二五
1404 0
小麦苗
|
SQL Oracle 关系型数据库
Oracle 11gR2新特性--延迟段创建（Deferred Segment Creation）
Oracle 11gR2新特性--延迟段创建（Deferred Segment Creation） 真题1、 什么是延迟段创建（Deferred Segment Creation）？ 答案：在Oracle 11.2中，当创建一个空表或者空分区时，为了加快创建速度，Oracle并不会立即分配初始段和空间，实际的表段（Table Segement）被延迟到第一行数据插入时创建。
小麦苗
2727 0