Why RMAN daily incremental backups are taking so long???


Have you ever come across a weird problem with RMAN of taking too long to take even daily Level-1 incremental backups and backing up huge amount of data?

I’ve seen such behavior with some of our production Databases. even a daily incremental backup is having a size of more than 80% of the original and taking a time more than the full backup takes.

We’ll now breakdown the problem and see what could be the reasons and where to start the investigation.

1. As all of us know incremental backups are the backups of the blocks which are changed from last successful incremental backups. If it’s having huge size then no.of blocks are getting changed daily and Oracle is not having any option but to back that block up.

So, where to start our investigation about which objects are generating ‘huge deltas’ and being changed much??

Use the query below to know the number of blocks being changed file wise…

SQL> select file#, avg(datafile_blocks) blocks,

2  avg(blocks_read) blocks_read,

3  avg(blocks_read/datafile_blocks)*100 pct_read,

avg(blocks) blocks_backed_up,

4    5  (avg(blocks)/avg(datafile_blocks))*100 pct_backed_up

from v$backup_datafile

6    7  where incremental_level = 1

8  group by file#

9  order by file#

From the above query you’ll get the no.of blocks being backed up(pct_backed_up) from each file and get into the details like which objects resides on that file via the tablespace_name etc…

2. What if it’s taking so long to complete the backup operation? Whom we have to suspect?

There are lot of areas of concern like tape drive’s performance, if it’s on disk the disk controller’s performance, the OS level issues as RMAN is finally an OS level process etc…

But from the Database point of view we can use the above query and if we see 100% for all the files in the pct_read, then we could confirm that the Database is reading each and every block and wasting lot of time.

First of all why it’s doing so? It’s because RMAN has to visit each block and see if it got modified or not and backup if it’s modified. that’s the way the algorithm has been devised (a linear search)…

So is there anyway we could make it a bit efficient??

Yes, absolutely yes!!! Oracle has gifted us with a functionality called BCT (Block Change Tracking). Please Google that word to get very good article and information about that.

Please enable BCT and you’ll be amazed by the results as I do!!!

We’ll discuss on how to enable BCT and some hidden facts about BCT etc in my next article…

Keep reading!!!





2 Responses to Why RMAN daily incremental backups are taking so long???

  1. Pingback: Enable BCT (Block Change Tracking) « queryCSM

  2. Pingback: Backup optimization in RMAN | queryCSM

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: