Backup Undo Optimization是11g的新特性之一，RMAN将避免备份撤销表空间上那些已提交事务的撤销数据。且该特性无法被禁用(You can enable and disable backup optimization, but backup undo optimization is built-in behavior.)。 我们在11.2.0.1版本上具体测试一下这个新特性:
根据以上情况我提交了SR，ORACLE GCS给出的回复:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | 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吗？ */ |
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。这是典型的开发部门和文档撰写部门间没有充分交流造成的问题！
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | /* 尝试等待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! |