Since 11.1 RMAN has had a silent new feature – RMAN Backup Undo Optimization. This feature will exclude undo from committed transactions (after undo_retention time has also passed) from backups, possibly making the undo tablespace backup much smaller. The documentation just says that it will work for disk backups and Oracle Secure Backup tape backups. Since lately I’m been playing around a lot with image copy backups I wanted to find out if this feature only works with backupsets or does it also work for incrementally refreshed image copies.<\/p>\n
I first thought that it cannot possibly work with image copies, since image copies should be exact datafile copies, but on the other hand when you refresh and image copy, then you at first also have to create incremental backupset of the changes that you then apply to the image copy, so maybe the optimization is applied silently there also \ud83d\ude42 Would be really good. Better to test it out. Fingers crossed.<\/p>\n
I’m using 12.1.0.2 on OEL 7.2.<\/p>\n
Before taking the test I created an image copy from my undo tablespace (309 338 112 bytes):<\/p>\n
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'image_copy_backup' TABLESPACE UNDOTBS1;\n-rw-r-----+ 1 oracle oinstall 309338112 Dec 28 05:06 data_D-ORCL_I-1433672784_TS-UNDOTBS1_FNO-3_04qvtmir\n<\/code><\/pre>\nYes I know, my filesystem dates were wrong at that point \ud83d\ude42 Ignore this, NTP wasn’t running on the storage box.<\/p>\n
Also a level 0 uncompressed backupset of the same tablespace (207 110 144 bytes, so it has already been optimized, but I’m interested in the next incremental backup size):<\/p>\n
RMAN> BACKUP INCREMENTAL LEVEL 0 TABLESPACE UNDOTBS1;\n-rw-r-----+ 1 oracle oinstall 207110144 Dec 28 05:16 0kqvtpaj_1_1\n<\/code><\/pre>\nNext I ran a large UPDATE statement and committed it immediately. I also had snapper running to catch the amount of undo my update caused. Snapper reported that my update generated 146MB of undo:<\/p>\n
STAT, undo change vector size , 146 042 740\n<\/code><\/pre>\nNow immediately I run incremental backup for both, backupset and to incrementally update the image copy.
\nBACKUP INCREMENTAL LEVEL 1 TABLESPACE UNDOTBS1<\/strong> command produced file named 0mqvtpkf_1_1 and command BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘image_copy_backup’ tablespace undotbs1<\/strong> produced file named 0oqvtpm2_1_1. As you can see, both are almost equally as big and close to the reported undo change vector size.
\nNo surprise herem undo optimization did not kick in since undo_retention time has not yet passed.<\/p>\n-rw-r-----+ 1 oracle oinstall 151470080 Dec 28 05:21 0mqvtpkf_1_1\n-rw-r-----+ 1 oracle oinstall 181190656 Dec 28 05:22 0oqvtpm2_1_1\n<\/code><\/pre>\nThen I deleted both these files and removed them from RMAN catalog.<\/p>\n
After 30 minutes or so (my undo_retention time is 600 = 10 minutes) I ran the backup commands again:<\/p>\n
RMAN> backup incremental level 1 tablespace undotbs1;\n\nStarting backup at 07-MAR-16\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=45 device type=DISK\nchannel ORA_DISK_1: starting incremental level 1 datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00003 name=\/u01\/app\/oracle\/oradata\/ORCL\/datafile\/o1_mf_undotbs1_cfvpb5hx_.dbf\nchannel ORA_DISK_1: starting piece 1 at 07-MAR-16\nchannel ORA_DISK_1: finished piece 1 at 07-MAR-16\npiece handle=\/nfs\/backup\/orcl\/14qvtsgf_1_1 tag=TAG20160307T230238 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 07-MAR-16\n\nRMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'image_copy_backup' tablespace undotbs1;\n\nStarting backup at 07-MAR-16\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=61 device type=DISK\nchannel ORA_DISK_1: starting incremental level 1 datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00003 name=\/u01\/app\/oracle\/oradata\/ORCL\/datafile\/o1_mf_undotbs1_cfvpb5hx_.dbf\nchannel ORA_DISK_1: starting piece 1 at 07-MAR-16\nchannel ORA_DISK_1: finished piece 1 at 07-MAR-16\npiece handle=\/nfs\/backup\/orcl\/16qvtsj0_1_1 tag=IMAGE_COPY_BACKUP comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07\nFinished backup at 07-MAR-16\n<\/code><\/pre>\nThis can’t be good.. regular backupset took only 1 second to execute and taking an incremental backup for image copy refresh took 7 seconds.
\nLooking at the file sizes the difference is clear – 1,7MB for the incremental backup and 181MB (no change) for the image copy refresh:<\/p>\n
-rw-r-----+ 1 oracle oinstall 1794048 Mar 7 23:02 14qvtsgf_1_1\n-rw-r-----+ 1 oracle oinstall 181567488 Mar 7 23:04 16qvtsj0_1_1\n<\/code><\/pre>\nSo the backup undo optimization works, but only if you use backupsets.<\/p>\n","protected":false},"excerpt":{"rendered":"
Since 11.1 RMAN has had a silent new feature – RMAN Backup Undo Optimization. This feature will exclude undo from committed transactions (after undo_retention time has also passed) from backups, possibly making the undo tablespace backup much smaller. The documentation just says that it will work for disk backups and Oracle Secure Backup tape backups. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4,44],"class_list":["post-300","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle","tag-rman"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/300","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=300"}],"version-history":[{"count":1,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/300\/revisions"}],"predecessor-version":[{"id":301,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/300\/revisions\/301"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=300"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=300"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=300"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}