Backup Undo Optimization是11g的新特性之一，RMAN将避免备份撤销表空间上那些已提交事务的撤销数据。且该特性无法被禁用(You can enable and disable backup optimization, but backup undo optimization is built-in behavior.)。
我们在11.2.0.1版本上具体测试一下这个新特性:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
/* 为了避免undo自动调优干扰我们的测试，修改_undo_autotune参数禁用该特性 */
SQL> alter system set "_undo_autotune"=false;
System altered.
/* 创建一个新的undo表空间，清理现场 */
SQL> create undo tablespace UNDOTEST datafile size 500M autoextend on next 50M maxsize unlimited;
Tablespace created.
SQL> alter system set undo_tablespace=UNDOTEST;
System altered.
/* 列出相关的undo参数，可以看到这里undo_retention参数设为极短的10s */
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 10
undo_tablespace string UNDOTEST
RMAN> list backup;
specification does not match any backup in the repository
RMAN> backup tablespace UNDOTEST;
RMAN> list backup of tablespace UNDOTEST;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 1.90M DISK 00:00:00 25-AUG-10
/* undo表空间初始的备份大小为1.9M */
SQL> conn maclean/maclean
Connected.
SQL> select count(*) from YOUYUS;
COUNT(*)
----------
579808
/* YOUYUS表有大约60万条数据，我们批量删除这些数据，将产生大量的undo*/
SQL> delete YOUYUS;
579808 rows deleted.
/* 此时再次执行备份undo表空间操作 */
RMAN> backup tablespace UNDOTEST;
RMAN> list backup of tablespace UNDOTEST;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 134.43M DISK 00:00:03 25-AUG-10
/* 在存在大量active undo数据的情况下，备份文件增大到134M */
SQL> commit;
Commit complete.
SQL> exec dbms_lock.sleep(20);
PL/SQL procedure successfully completed.
SQL> select status,sum(blocks) from dba_undo_extents group by status;
STATUS SUM(BLOCKS)
--------- -----------
UNEXPIRED 2696
EXPIRED 32936
/* commit后等待20s,确定没有active的撤销段 */
RMAN> backup tablespace UNDOTEST;
RMAN> list backup of tablespace UNDOTEST;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 134.49M DISK 00:00:02 25-AUG-10
/* 备份文件还要大于commit前，undo backup optimization居然没有起作用？ */
/* 这个会是BUG吗？ */
根据以上情况我提交了SR，ORACLE GCS给出的回复:
Bug 6399468: UNDO OPTIMIZATION
====> Undo optimization was changed from ‘all undo not needed’ to ‘undo not in use older than 1 hour’
In unpublished bug 6399468 DEV has confirmed the Undo optimization was changed from ‘all undo not needed’ to ‘undo not in use older than 1 hour’. For your last test case please wait for 1 hour and try backup again.
居然又是一个unpublished的BUG，Oracle DEV部门确认了backup undo optimization所避免备份的是1个小时以上未被尝试使用的undo，而非所有不再需要的undo。这是典型的开发部门和文档撰写部门间没有充分交流造成的问题！
/* 尝试等待3600s */ SQL> exec dbms_lock.sleep(3600); PL/SQL procedure successfully completed. /* 3600s还真漫长....... */ RMAN> backup tablespace UNDOTEST; RMAN> list backup of tablespace UNDOTEST; BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 15.01M DISK 00:00:00 25-AUG-10 /* 备份集缩小到15m,undo backup optimization起到了作用！*/ That's great!
Leave a Reply