rollback segment手动管理的意义

很多朋友在研究Oracle的Undo段管理的时候，都会看到很多的资料介绍手动管理，而不单单是自动管理，除了手动create/offline/online等手动处理外，他的意义在哪？
这里只说明一点，当在undo出现逻辑坏块时使用手动管理的方式实现实例恢复

还有其他的发现，以后再继续补充！

1）修改undo_management管理方式

[oracle@mydb02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 29 02:55:00 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter undo_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> alter system set undo_management='MANAUL' scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area  818401280 bytes
Fixed Size                  2257680 bytes
Variable Size             532679920 bytes
Database Buffers          281018368 bytes
Redo Buffers                2445312 bytes
Database mounted.
Database opened.
SQL> show parameter undo_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

2）创建手动回滚段

SQL> create tablespace test4 datafile '/u01/app/oracle/oradata/orcl/test04.dbf' size 1G autoextend on extent management local uniform size 16M;
Tablespace created.
SQL> create rollback segment test04 tablespace test4;
create rollback segment test04 tablespace test4
*
ERROR at line 1:
ORA-30574: Cannot create rollback segment in tablespace with AUTO segment space
management

SQL> create tablespace test1  datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 1G autoextend on extent management local uniform size 16M segment space management manual;
Tablespace created.
此处表明，segment的管理方式必须是手动才可以在表空间上创建回滚段。
SQL> create rollback segment test01 tablespace test1;
Rollback segment created.

SQL> col TABLESPACE_NAME for a15
SQL> col SEGMENT_NAME for a30
SQL>  select tablespace_name,segment_name,max_extents from dba_segments where segment_name='TEST01';

TABLESPACE_NAME SEGMENT_NAME                   MAX_EXTENTS
--------------- ------------------------------ -----------
TEST1           TEST01                               32765

SQL> set transaction use rollback segment test01;
set transaction use rollback segment test01
*
ERROR at line 1:
ORA-01598: rollback segment 'TEST01' is not online
默认在创建完成时是offline的，需要手动online
SQL> alter rollback segment test01 online;
Rollback segment altered.
3）启用回滚段
SQL> set transaction use rollback segment test01;
Transaction set.

SQL> delete from t01;
1 row deleted.

SQL> show parameter rollback_segments 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
rollback_segments                    string
SQL> startup force
ORACLE instance started.
Total System Global Area  818401280 bytes
Fixed Size                  2257680 bytes
Variable Size             532679920 bytes
Database Buffers          281018368 bytes
Redo Buffers                2445312 bytes
Database mounted.
Database opened.
SQL> show parameter undo_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> delete from t01;
1 row deleted.
检查当前事务所使用的rollbakc segment
col "rollback_seg_name" format a10
col SID format 9990
col "username" format a10
col "Program" format a80
col status format a6 trunc
SELECT r.name "rollback_seg_name",
 s.sid,
 s.serial#,
 s.username "username",
 t.status,
 t.cr_get,
 t.phy_io,
 t.used_ublk,
 t.noundo,
 substr(s.program, 1, 78) "Program"
 FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
 WHERE t.addr = s.taddr and t.xidusn = r.usn
 ORDER BY t.cr_get,t.phy_io;

 rollback_s   SID    SERIAL# username   STATUS     CR_GET     PHY_IO  USED_UBLK NOU Program

---------- ----- ---------- ---------- ------ ---------- ---------- ------------     ------
SYSTEM         1          5 SYS        ACTIVE        157          9          1 NO  sqlplus@mydb02 (TNS V1-V3)

SQL>  set transaction use  rollback segment test01;
 set transaction use  rollback segment test01
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
SQL> rollback;
Rollback complete.

SQL>  set transaction use  rollback segment test01;
 set transaction use  rollback segment test01
*
ERROR at line 1:
ORA-01598: rollback segment 'TEST01' is not online

SQL> alter rollback segment test01 online;
Rollback segment altered.
SQL>  set transaction use  rollback segment test01;
Transaction set.
防止指定的回滚段在重启实例后失效，可以将回滚段写入rollback_segments中
SQL> alter system set rollback_segments='TEST01' scope=spfile;
System altered.

SQL> startup force 
ORACLE instance started.
Total System Global Area  818401280 bytes
Fixed Size                  2257680 bytes
Variable Size             532679920 bytes
Database Buffers          281018368 bytes
Redo Buffers                2445312 bytes
Database mounted.
Database opened.
SQL> delete from t01;
1 row deleted.
SQL> 
SQL> col "rollback_seg_name" format a10
SQL> col SID format 9999
SQL> col "username" format a10
SQL> col "Program" format a80
SQL> col status format a6 trunc
SQL> SELECT r.name "rollback_seg_name",
  2  s.sid,
  3  s.serial#,
  4  s.username "username",
  5  t.status,
  6  t.cr_get,
  7  t.phy_io,
  8  t.used_ublk,
  9  t.noundo,
 10  substr(s.program, 1, 78) "Program"
 11  FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
 12  WHERE t.addr = s.taddr and t.xidusn = r.usn
 13  ORDER BY t.cr_get,t.phy_io;

rollback_s   SID    SERIAL# username   STATUS     CR_GET     PHY_IO  USED_UBLK NOU Program
---------- ----- ---------- ---------- ------ ---------- ---------- ---------- --- --------------------------------------------------------------------------------
TEST01         1          5 SYS        ACTIVE        155          9          1 NO  sqlplus@mydb02 (TNS V1-V3)

SQL> show parameter rollback_seg
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
rollback_segments                    string      TEST01
transactions_per_rollback_segment    integer     5
SQL> show parameter undo_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
明白undo手动管理的原理的意义是什么：
SQL> alter system set undo_management='AUTO' scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area  818401280 bytes
Fixed Size                  2257680 bytes
Variable Size             532679920 bytes
Database Buffers          281018368 bytes
Redo Buffers                2445312 bytes
Database mounted.
show Database opened.
SQL> parameter undo_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
此时如果发现逻辑坏块，可以通过设置隐含参数来实现undo段的手动管理,直接手动删除会报错
SQL> drop rollback segment "_SYSSMU5_898567397$";   
drop rollback segment "_SYSSMU5_898567397$"  
*   
ERROR 位于第 1 行:   
ORA-30025: 不允许 DROP 段 '_SYSSMU5_898567397$' (在撤消表空间中)  
SQL> alter system set "_smu_debug_mode"=4;
System altered.
检查出现逻辑坏块的undo段状态，若为online则需要offline
SQL> select SEGMENT_NAME,OWNER ,STATUS  from dba_rollback_Segs;
SEGMENT_NAME                   OWNER  STATUS
------------------------------ ------ ------
SYSTEM                         SYS    ONLINE
_SYSSMU10_1197734989$          PUBLIC ONLINE
_SYSSMU9_1650507775$           PUBLIC ONLINE
_SYSSMU8_517538920$            PUBLIC ONLINE
_SYSSMU7_2070203016$           PUBLIC ONLINE
_SYSSMU6_1263032392$           PUBLIC ONLINE
_SYSSMU5_898567397$            PUBLIC ONLINE
_SYSSMU4_1254879796$           PUBLIC ONLINE
_SYSSMU3_1723003836$           PUBLIC ONLINE
_SYSSMU2_2996391332$           PUBLIC ONLINE
_SYSSMU1_3724004606$           PUBLIC ONLINE
TEST01                         SYS    OFFLIN
12 rows selected.
SQL>  alter rollback segment "_SYSSMU5_898567397$" offline;

Rollback segment altered.
SQL> select SEGMENT_NAME,OWNER ,STATUS  from dba_rollback_Segs where SEGMENT_NAME='_SYSSMU5_898567397$';
SEGMENT_NAME                   OWNER  STATUS
------------------------------ ------ ------
_SYSSMU5_898567397$            PUBLIC OFFLIN

SQL> drop rollback segment "_SYSSMU5_898567397$" ;

在回滚段上有transaction时，也是可以offline的