How To Get Faster Oracle RMAN Performance with Oracle Database 12_c_
_by Paul Guerin (@"top.gun")
_
Incremental backups of datafile sections improve overall backup performance, which leads to the highest levels of Oracle Database backup performance.
In Oracle Database 12_c_, there are several new features for Oracle Recovery Manager (Oracle RMAN). These include the SYSBACKUP privilege and the ability to execute SQL DDL commands natively in Oracle RMAN. Additionally, the ability to take incremental backups of a datafile in sections can now improve backup and recovery performance.
However, to take advantage of incremental backups of a datafile in sections, an incremental backup strategy needs to be implemented. A suitable incremental backup strategy suggested by Oracle is as follows:
- An incremental level 0 backup is taken monthly.
- A cumulative incremental level 1 backup is taken weekly.
- A differential incremental level 1 backup is taken daily.
Also for an online backup, the incremental backup must be complemented by an archived redo log file backup so that the database can be made consistent after recovery. For database recovery, however, the earlier incremental level 1 backups and archived redo log file backups might not be required.
To prove that the earlier incremental level 1 backups and archived redo log file backups will not be required for a restore and recover to the present time, execute the command below:
RESTORE DATABASE PREVIEW SUMMARY UNTIL TIME 'SYSDATE';
Moreover, if the incremental and archived redo log file backups occur daily, as suggested by Oracle, then you never need to apply more than a day of archived redo log files for a complete recovery. You can confirm that you never need to apply more than a day of archived redo log files by using the following command:
REPORT NEED BACKUP DAYS 1;
Using an incremental backup strategy alone can result in faster performance simply by replacing incremental level 0 backups with incremental level 1 backups.
Incremental Backups
An incremental backup strategy doesn't always lead to outstanding performance. Performance can be adversely affected if a large datafile has many changed blocks and just a single thread in a channel is used to process the datafile. This problem can arise especially with BIGFILE-type datafiles that have many changed blocks. So an incremental level 1 backup, which processes only changed blocks one block at a time, can take a long time to complete.
So the performance of an incremental level 1 backup of a whole database, or even of just a single datafile, can be impacted when many changed blocks are present. An example of an incremental level 1 backup of a single datafile is as follows:
RUN{ ALLOCATE CHANNEL ch1 DEVICE TYPE DISK; ALLOCATE CHANNEL ch2 DEVICE TYPE DISK; ALLOCATE CHANNEL ch3 DEVICE TYPE DISK; ALLOCATE CHANNEL ch4 DEVICE TYPE DISK; BACKUP AS COMPRESSED BACKUPSET CUMULATIVE INCREMENTAL LEVEL 1 CURRENT CONTROLFILE TAG 'weekly - controlfile' DATAFILE 7 TAG 'weekly - cumulative inc1' ; }
Below, the log of the backup shows that only a single channel is used for the datafile even though four were allocated. This is normal behavior, because generally a datafile can be backed up only by a single channel.
.. channel ch1: starting piece 1 at Fri 05Feb2016 22:04:25 ..
If multiple channels can be used for a single datafile, there might be the potential for an improvement in backup performance.
Incremental Backups in Sections
Fortunately in Oracle Database 12_c_, multiple channels per datafile can be employed when the SECTION SIZE clause is used. If a database contains, say, one datafile of 32 GB, then four channels—that is, CEIL(32G/10G)—can process each datafile section individually. Each datafile section then becomes a piece of a single backup set.
An incremental level 1 backup of a 32 GB datafile using the SECTION SIZE clause is as follows:
RUN{ ALLOCATE CHANNEL ch1 DEVICE TYPE DISK; ALLOCATE CHANNEL ch2 DEVICE TYPE DISK; ALLOCATE CHANNEL ch3 DEVICE TYPE DISK; ALLOCATE CHANNEL ch4 DEVICE TYPE DISK; BACKUP AS COMPRESSED BACKUPSET CUMULATIVE INCREMENTAL LEVEL 1 CURRENT CONTROLFILE TAG 'weekly - controlfile' DATAFILE 7 TAG 'weekly - cumulative inc1' SECTION SIZE 10G # new for RMAN incremental level 1 backup ; }
Now the log of the backup shows that all four channels allocated are used to process the datafile, and they start at about the same time. The 32 GB datafile is processed in four sections concurrently, so effectively the four sections are backed up in parallel.
.. backing up blocks 1 through 1310720 channel ch1: starting piece 1 at Fri 05Feb2016 22:10:37 .. backing up blocks 1310721 through 2621440 channel ch2: starting piece 2 at Fri 05Feb2016 22:10:38 .. backing up blocks 2621441 through 3932160 channel ch3: starting piece 3 at Fri 05Feb2016 22:10:39 .. backing up blocks 3932161 through 4066176 channel ch4: starting piece 4 at Fri 05Feb2016 22:10:41 ..
Potentially, the total backup elapsed time will be greatly reduced if there is a physical backup device for each channel. Similarly, the same engagement of physical backup devices in parallel can result in a quicker restore and recovery.
Conclusion
There you have it—incremental backups of datafile sections improve overall backup performance, which leads to the highest levels of Oracle Database backup performance.
See Also
About the Author
Paul Guerin is an internationally recognized thought leader on Oracle Database and a performance architect. He has been the principle consultant for many blue-chip companies from Australia, France, Hong Kong, and the United States. Moreover he has presented at some of the world's leading Oracle conferences, including Oracle OpenWorld 2013. Guerin is based from a global delivery center in Southeast Asia and is a participant in the Oracle ACE program and a highly respected expert on the Oracle community platform. Follow him at @"top.gun"