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!
|
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277135
