Oracle DBA – A lifelong learning experience

Reducing RMAN backup time for unevenly sized tablespaces

Posted by John Hallas on April 6, 2010

The following note was written by Kevin Cardew after we had discussed how to reduce the backup time of some of our large databases, especially when they constrained by most of the data being stored in a single bigfile tablespace .

Our build standards require that 11g (11.1.0.7) databases are built using BIGFILE tablespaces, however this has led to one or two problems with RMAN backups. In several of the databases the deployed application, third party supplied, has all the objects deployed to a single tablespace. The result of this is that an increasing amount of the database size resides in a single data file. During the backup RMAN balances the load by attempting to divide the backup volume (data files) over the available channels to maximise backup speed. Given the unbalanced nature of the data files, this results in a single channel being allocated to the largest data file and the rest load balanced across the remaining channels. Therefore the overall backup time is determined by the time taken to backup a single data file.

Using the standard RMAN script below the backup of the database (225Gb total size, with one data file accounting for 134Gb of this space, the time taken was approximately 5.5hrs

run {

allocate channel ‘dev_0′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

allocate channel ‘dev_1′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

allocate channel ‘dev_2′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

allocate channel ‘dev_3′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

allocate channel ‘dev_4′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

allocate channel ‘dev_5′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

backup incremental level 0

format ‘PROD_SID_online_full<SID_%s:%t:%p>.dbf’

database;

sql ‘alter system archive log current';

backup

format ‘PROD_SID_online_full<SID_%s:%t:%p>.dbf’

archivelog all;

backup

format ‘PROD_SID_online_full<SID_%s:%t:%p>.dbf’

current controlfile;

}

Given that a 5hr plus window was unacceptable, the use of the new 11GR1 feature to multi-channel the backup of a data files was tested. The three largest data files were determined and the RMAN script re-written to utilise the SECTION SIZE component of the backup command. The resultant RMAN script can be seen below.

run {

allocate channel ‘dev_0′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

allocate channel ‘dev_1′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

allocate channel ‘dev_2′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

allocate channel ‘dev_3′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

allocate channel ‘dev_4′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

allocate channel ‘dev_5′ type ‘sbt_tape’

parms ‘ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=SID,OB2BARLIST=PROD_SID_online_full,OB2BARHOSTNAME=server.domain)';

configure exclude for tablespace ‘MGMT_TABLESPACE';

configure exclude for tablespace ‘UNDOTBS1′;

configure exclude for tablespace ‘UNDOTBS2′;

backup incremental level 0

format ‘PROD_SID_online_full<SID_%s:%t:%p>.dbf’

database;

configure exclude for tablespace ‘MGMT_TABLESPACE’ clear;

configure exclude for tablespace ‘UNDOTBS1′ clear;

configure exclude for tablespace ‘UNDOTBS2′ clear;

backup

section size 8000m datafile ‘+DATA/SID/datafile/mgmt.dbf';

backup

section size 5000m datafile ‘+DATA/SID/datafile/undotbs1.267.686154199′;

backup

section size 5000m datafile ‘+DATA/SID/datafile/undotbs2.261.686154203′;

sql ‘alter system archive log current';

backup

format ‘PROD_SID_online_full<SID_%s:%t:%p>.dbf’

archivelog all not backed up 2 times;

backup

format ‘PROD_SID_online_full<SID_%s:%t:%p>.dbf’

current controlfile;

}

This reduced the overall backup time from 5.5hrs to 3.5hrs.

How it works

The SECTION SIZE option allocates to each channel a block range to backup, the amount of backup volume for each range being the SECTION SIZE. A backup piece is produced for each block range and once complete the channel is allocated the next block range until such time as the whole data file is backed up.

Problems encountered

It should be noted that the SECTION SIZE option means that the backup command will backup the whole data file, that is the allocated size not the used, therefore when calculating the SECTION SIZE it is best if the section size is a factor of the data files size divided by the number of channels. In addition care should be taken not to make the SECTION SIZE too large as this can have the unexpected result of making the backup single channel as the data file increases. For example if 6 channels were to be used on a 132Gb data file a SECTION SIZE of 22Gb would not be unreasonable, the backup would be evenly spread maximising output speed. If the data file however increased to 154Gb this would result in an additional backup piece 22Gb in size running single channel.

The best approach is to set the section size to a value that still allows the backup to result in a single channel, to allow for data file growth, but at a size where the overall speed is not too badly affected.

The second problem with this mechanism is that the SECTION SIZE needs to be regularly reviewed as the data file grow as and incorrectly specified SECTION SIZE can result in numerous backup pieces which have to be maintained in the Control File, Recover Catalogue (if in use) and the Media Manger (if in use).

Given the inital benefits we have seen I am sure that we will be working towards optimising this method.

About these ads

2 Responses to “Reducing RMAN backup time for unevenly sized tablespaces”

  1. […] http://jhdba.wordpress.com/2010/04/06/reducing-rman-backup-time-for-unevenly-sized-tablespaces/ […]

  2. […] 8-How to use 11G SECTION SIZE option for decreasing backup time John Hallas-Reducing RMAN backup time for unevenly sized tablespaces/ […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 217 other followers

%d bloggers like this: