undo backup optimization does not work on 11.2.0.1?

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!

Posted

in

by

Tags:

Comments

2 responses to “undo backup optimization does not work on 11.2.0.1?”

  1. admin Avatar
    admin

    In backup undo optimization, RMAN excludes undo not needed for recovery from the backup, that is, for transactions which have already been committed. For example, a user updates the salaries table in the USERS tablespace. The change is written to the USERS tablespace, while the before image of the data is written to the UNDO tablespace. The user commits. A subsequent RMAN backup of the UNDO tablespace does not include the undo information for the salary changes as a restore of this backup would already have the committed data.

    The undo excluded is based on commit of user and an internally specified period of time. Backup undo optimization is built-in RMAN behavior and cannot be disabled. However, there are some restrictions:

    – Compatible parameter must be set to 11.0 or higher
    – Backup must use a disk or OSB channel
    – For ‘backup copy of ‘ or ‘backup datafilecopy’ the database must be open for undo optimization to be used.

    RMAN> list backup of datafile 3;

    List of Backup Sets
    ===================
    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ——- —- — ———- ———– ———— —————
    58 Full 73.10M DISK 00:00:29 14-JAN 00:07:00
    BP Key: 69 Status: AVAILABLE Compressed: NO Tag: TAG20070114T000631
    Piece Name: /backups/v110/V110_40i7ejo7_1_1
    List of Datafiles in backup set 58
    File LV Type Ckp SCN Ckp Time Name
    —- — —- ———- ————— —-
    3 Full 277406 14-JAN 00:06:31 /oradata/v110/undotbs01.dbf

    RMAN> backup datafile 3;

    Starting backup at 14-JAN 00:07:44
    using channel ORA_DISK_1
    using channel ORA_DISK_2
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00003 name=/oradata/v110/undotbs01.dbf
    channel ORA_DISK_1: starting piece 1 at 14-JAN 00:07:45
    channel ORA_DISK_1: finished piece 1 at 14-JAN 00:07:48
    piece handle=/backups/v110/V110_44i7ejqh_1_1 tag=TAG20070114T000744 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
    Finished backup at 14-JAN 00:07:48

    Starting Control File and SPFILE Autobackup at 14-JAN 00:07:48
    piece handle=/backups/v110/V110_c-1689207191-20070114-01 comment=NONE
    Finished Control File and SPFILE Autobackup at 14-JAN 00:07:50

    RMAN> list backup of datafile 3;

    List of Backup Sets
    ===================
    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ——- —- — ———- ———– ———— —————
    58 Full 73.10M DISK 00:00:29 14-JAN 00:07:00
    BP Key: 69 Status: AVAILABLE Compressed: NO Tag: TAG20070114T000631
    Piece Name: /backups/v110/V110_40i7ejo7_1_1
    List of Datafiles in backup set 58
    File LV Type Ckp SCN Ckp Time Name
    —- — —- ———- ————— —-
    3 Full 277406 14-JAN 00:06:31 /oradata/v110/undotbs01.dbf

    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ——- —- — ———- ———– ———— —————
    61 Full 12.60M DISK 00:00:02 14-JAN 00:07:47
    BP Key: 72 Status: AVAILABLE Compressed: NO Tag: TAG20070114T000744
    Piece Name: /backups/v110/V110_44i7ejqh_1_1
    List of Datafiles in backup set 61
    File LV Type Ckp SCN Ckp Time Name
    —- — —- ———- ————— —-
    3 Full 277477 14-JAN 00:07:45 /oradata/v110/undotbs01.dbf

    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ——- —- — ———- ———– ———— —————
    63 Full 536.00K DISK 00:00:01 14-JAN 00:51:05
    BP Key: 74 Status: AVAILABLE Compressed: NO Tag: TAG20070114T005104
    Piece Name: /backups/v110/V110_46i7embo_1_1
    List of Datafiles in backup set 63
    File LV Type Ckp SCN Ckp Time Name
    —- — —- ———- ————— —-
    3 Full 278620 14-JAN 00:51:04 /oradata/v110/undotbs01.dbf

  2. Luestone Avatar
    Luestone

    我在11.1.0.7.0也发现了这个问题。
    确实是静置1小时后,能够备份出很小的undotbs来。
    但是尝试了一下直接调系统时间重启库,再做备份,还是没有效果。
    请问这个1小时和undo retention一样是不是也是内部计时,必须要真走了1小时的时间变化才能生效吗?

Leave a Reply to Luestone Cancel reply

Your email address will not be published. Required fields are marked *