Backup Undo Optimization是11g的新特性之一,RMAN将避免备份撤销表空间上那些已提交事务的撤销数据。且该特性无法被禁用(You can enable and disable backup optimization, but backup undo optimization is built-in behavior.)。 我们在11.2.0.1版本上具体测试一下这个新特性:
?
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吗? */
根据以上情况我提交了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。这是典型的开发部门和文档撰写部门间没有充分交流造成的问题!
?
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!