作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


Oracle 10g的UNDO_RETENTION管理增强:

在AUM模式下,UNDO_RETENTION参数用以控制事务提交以后Undo保留的时间。该参数以秒为单位,9iR1初始值为900秒,在Oracle 9iR2增加为10800秒。但是这是一个NO Guaranteed的限制。也就是说,如果有其他事务需要回滚空间,而空间出现不足时,这些信息仍然会被覆盖。

从Oracle 10g开始,如果设置UNDO_RETENTION为0,那么Oracle启动自动调整以满足最长运行查询的需要。当然如果空间不足,那么Oracle满足最大允许的长时间查询。而不再需要用户调整。当设置undo_retention为0后,在警告日志文件中可以看到“Autotune of undo retention is turned on.”信息。

这个新特性的引入伴随着几个新的隐含初始化参数,主要的参数有2个:

sys@TQGZS> select ksppinm,ksppdesc
  2  from x$ksppi where ksppinm like '%&var%';
Enter value for var: undo_autotune
old   2: from x$ksppi where ksppinm like '%&var%'
new   2: from x$ksppi where ksppinm like '%undo_autotune%'
KSPPINM              KSPPDESC
-------------------- --------------------------------------------------
_undo_autotune       enable auto tuning of undo_retention
sys@TQGZS> /
Enter value for var: collect_undo_stats
old   2: from x$ksppi where ksppinm like '%&var%'
new   2: from x$ksppi where ksppinm like '%collect_undo_stats%'
KSPPINM              KSPPDESC
-------------------- --------------------------------------------------
_collect_undo_stats  Collect Statistics v$undostat

这两个参数缺省都是打开的。

很多时候我们希望前镜像数据能够尽量,而不是被覆盖,Oracle 10g对于Undo增加了Guarantee控制,也就是说,用户可以指定Undo表空间必须满足UNDO_RETENTION限制。通过如下命令可以修改Undo表空间的新属性:

alter tablespace undotbs1 retention guarantee|noguarantee;

设置期望的保留时间,修改Undo表空间属性,就可以使Undo表空间运行在Guarantee模式:

sys@TQGZS> show parameter undo_retention
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900
sys@TQGZS> select tablespace_name,contents,retention from dba_tablespaces
  2  where tablespace_name like 'UNDO%';
TABLESPACE_NAME                CONTENTS  RETENTION
------------------------------ --------- -----------
UNDOTBS1                       UNDO      NOGUARANTEE
UNDO_SMALL                     UNDO      NOGUARANTEE
sys@TQGZS> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
sys@TQGZS> select tablespace_name,contents,retention from dba_tablespaces
  2  where tablespace_name like 'UNDO%';
TABLESPACE_NAME                CONTENTS  RETENTION
------------------------------ --------- -----------
UNDOTBS1                       UNDO      GUARANTEE
UNDO_SMALL                     UNDO      NOGUARANTEE

将Undo表空间自动扩展属性取消进行如下测试:

sys@TQGZS> select tablespace_name,contents,retention from dba_tablespaces                   
  2  where tablespace_name like 'UNDO%';                                              
TABLESPACE_NAME                CONTENTS  RETENTION
------------------------------ --------- -----------
UNDOTBS1                       UNDO      NOGUARANTEE
UNDO_SMALL                     UNDO      NOGUARANTEE
sys@TQGZS> select name,bytes/1024/1024 MB from v$datafile where name like '%undo%';
NAME                                                       MB
-------------------------------------------------- ----------
/u01/oracle/oradata/tqgzs/undotbs01.dbf                   424
/u01/oracle/oradata/tqgzs/undo_small.dbf                    2
sys@TQGZS> alter database datafile '/u01/oracle/oradata/tqgzs/undo_small.dbf' autoextend off;
Database altered.
sys@TQGZS> alter system set undo_tablespace = undo_small;
System altered.
sys@TQGZS> alter tablespace undo_small retention guarantee;
Tablespace altered.
sys@TQGZS> select tablespace_name,contents,retention from dba_tablespaces
  2  where tablespace_name like 'UNDO%';                          
TABLESPACE_NAME                CONTENTS  RETENTION
------------------------------ --------- -----------
UNDOTBS1                       UNDO      NOGUARANTEE
UNDO_SMALL                     UNDO      GUARANTEE
sys@TQGZS> show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDO_SMALL

尝试循环小批量删除数据,在GUARANTEE设置下,很快出现ORA-30036错误

sys@TQGZS> conn tq/tq
Connected.
tq@TQGZS> select count(*) from test;
  COUNT(*)
----------
   1635808
tq@TQGZS> begin
  2  for i in 1 .. 1000 loop
  3  delete from test where rownum <1001;
  4  commit;
  5  end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_SMALL'
ORA-06512: at line 3
tq@TQGZS> select count(*) from test;
  COUNT(*)
----------
   1633808

而在修改了Undo表空间retention属性后,删除可以顺利完成:

tq@TQGZS> conn / as sysdba
Connected.
sys@TQGZS> alter tablespace undo_small retention noguarantee;
Tablespace altered.
sys@TQGZS> select tablespace_name,contents,retention from dba_tablespaces
  2  where tablespace_name like 'UNDO%';
TABLESPACE_NAME                CONTENTS  RETENTION
------------------------------ --------- -----------
UNDOTBS1                       UNDO      NOGUARANTEE
UNDO_SMALL                     UNDO      NOGUARANTEE
sys@TQGZS> conn tq/tq
Connected.
tq@TQGZS> begin
  2  for i in 1 .. 1000 loop
  3  delete from test where rownum <1001;
  4  commit;
  5  end loop;
  6  end;
  7  /
PL/SQL procedure successfully completed.

这就是GUARANTEE与NOGUARANTEE的不同。

- The End -