RMAN backups using TDP for Oracle - incremental forever like TSM?

[nandystam]

Hi folks

I admit I am very much a noob when it comes to RMAN and Oracle backups, all I generally do is setup and configure TDP for Oracle and let our DBA's handle the rest.

What I want to know is if Oracle backups of RMAN can be configured to use an 'incremental forever' strategy like TSM uses for regular file backups?

As it stands, our DBAs do daily incremental backups, weekly full backups, monthly full backup, and a yearly full backup. This seems rather excessive?

From the TSM side of things, I know that every time RMAN does a backups to TSM, it has a unique filename, thus remains active forever until the DBA's run an RMAN script to delete it. Hence our management class has a VEREXISTS setting of 1, and the rest are set to 0 for the backup copy group. What I don't know is if each file represents an entire table, a portion, a single row of data, or something else.

However, this sounds a lot like an 'incremental forever' approach? All I'd like to do is eliminate redundant backups where they are not necessary, and the strategy used by our DBA's seems to be very 'old school'.

Thanks in advance for your help!

 

[gimlet]

TSM doesn't manage expiring and deleting TDP backups; that is all done with RMAN. So you're stuck working with your DBAs. ;^)

 

[nandystam]

TSM doesn't manage expiring and deleting TDP backups; that is all done with RMAN. So you're stuck working with your DBAs. ;^)

Yeah I know

I guess what I'm thinking is that given the way RMAN and TSM operate, the only time another full backup of the DB would be needed is just before they choose to expire data?

So for example, if they do an initial full backup of their DB, and decide they want to retain the data for 92 days, as long as they do daily incrementals then the only time another full backup would really be needed is just before the 92 day expiry period (say day 90 or 91) ?

I'm not really seeing the benefits of doing a full backup, then incrementals, then a weekly full, a monthly full, and yearly full. It seems like an old school strategy that just isn't needed in TSM/RMAN ? A better approach would be to do a full backup, incrementals for say 1-3 months, another full backup (and expire all the data prior), incrementals for 1-3 months, another full backup and expiry etc. That way there is always a history of 1-3 months of data. If they want 12 months retention then maybe quarterly fulls (and incrementals in between) may also work.

I guess it comes down to business needs but it I get the distinct feeling our DBA's are thinking in a way that would doesn't make for efficient use of TSM..

 

[moon-buddy]

A better approach would be to do a full backup, incrementals for say 1-3 months, another full backup (and expire all the data prior), incrementals for 1-3 months, another full backup and expiry etc. That way there is always a history of 1-3 months of data. If they want 12 months retention then maybe quarterly fulls (and incrementals in between) may also work.

This is a dangerous thought!

Once or twice daily fulls is needed for DR and ease of recovery!

If you do what you said, imagine restoring 3 months worth of archive logs before you recover your database! By using RMAN, TSM is relegated to a role of keeper as TSM does not control expiration directly.

 

[nandystam]

This is a dangerous thought!

Once or twice daily fulls is needed for DR and ease of recovery!

You mean once or twice daily full backups of the DB?

If you do what you said, imagine restoring 3 months worth of archive logs before you recover your database! By using RMAN, TSM is relegated to a role of keeper as TSM does not control expiration directly.

I did specify 1-3 months :tongue:

What would be the bottleneck in this case - the time taken to restore from tape or the time taken to recover the database once you have all the archive logs?

I certainly don't profess to be an expert with Oracle or RMAN, so if you could explain the process involved in rebuilding the database from archive logs that might help me be able to see things from the DBA perspective?

 

[gimlet]

Moon-buddy is right. The point of backups is to restore. The way RMAN restores work is that the full is restored, and then the archive logs are applied, rolling the database forward. If your full backups are too far apart, this will take forever. Worse if you run intO a bad tape or other corruption along the way.

Oracle backups can be a bit confusing at first. I did a blog entry on some of the basics a while ago: http://www.lanigera.com/wordpress/2011/02/oracle-and-tdp-part-one/

Good luck,

Tom

 

[nandystam]

Moon-buddy is right. The point of backups is to restore. The way RMAN restores work is that the full is restored, and then the archive logs are applied, rolling the database forward. If your full backups are too far apart, this will take forever. Worse if you run intO a bad tape or other corruption along the way.

Oracle backups can be a bit confusing at first. I did a blog entry on some of the basics a while ago: http://www.lanigera.com/wordpress/2011/02/oracle-and-tdp-part-one/

Good luck,

Tom

That helps explain it a lot better - thanks. Did you end up doing a part two in your blog?

If RMAN restores work based on using the last full and rolling forward with archive logs, what is the point of incremental backups? Wouldn't the archive logs effectively be the 'incremental' backup? With our SAP systems backed up via TDP for SAP and RMAN, there are daily incrementals, weekly full, and twice daily archive log backups. I'm not seeing the point of daily incrementals?

I know this is easier said than done, but how far apart would you suggest between full backups?

Cheers

 

[moon-buddy]

We do one full backup daily and several archive log backups in between fulls - BUT THIS DEPENDS ON YOUR BUSINESS NEEDS AND RPO/RTO.

The number of archive log backup depends on the database size and/or how busy it is. With one setting, if the logs go beyond 50 MB, a log backup is triggered; or you can do this regardless of log size every hour or so.

Forget the idea of Incremental backups as this does not apply to RMAN controlled backups. Incremental backups are for files or similar structures. Databases does not fall into this category.

 

[gimlet]

Nandystan,

I apparently lost the draft of part 2. I'll rewrite and post it soonish. I think moon-buddy answered your other questions pretty well.

Tom

 

[GregE]

If RMAN restores work based on using the last full and rolling forward with archive logs, what is the point of incremental backups? Wouldn't the archive logs effectively be the 'incremental' backup?

No, there are really 3 types of RMAN "incrementals." A Level 0 incremental is what we're all here calling a "full" though really it's not exactly a full. Full is actually another RMAN backup type. Then there is "Level 1 differential" and "Level 1 cumulative." A level 1 backup is a differential by default if "cumulative" is not specified in the command.

#
# Full - the only difference between a level 0 incremental backup and a "full" backup is that a full backup is never included in an incremental strategy. A "full" DB backup is it's own island.
#
backup full database;


#
# Level 0 Incremental - the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a "full" backup would
#
backup incremental level 0 database;


#
# Level 1 Incremental - Differential - backs up all blocks that have changed since the most recent incremental backup at level 1 (cumulative or differential) or level 0. Differential is the default.
#
backup incremental level 1 differential database;
..or..
backup incremental level 1 database; (differential is implied)

If you took a Level 0 backup on Sunday, and a Level 1 Differential everyday during the week, and your DB needed restoring Thursday, you'd restore Sunday's level 0, and Level 1 differential from Mon, Tue, and Wed. (and of course your archive logs that would also hopefully be part of your strategy, taken on frequent intervals, to get you as close to the failure time as possible.)

#
# Level 1 Incremental - Cumulative - backup all blocks changed since the most recent incremental backup at level 0
#
backup incremental level 1 cumulative database;

If you took a Level 0 backup on Sunday, and a Level 1 Differential on Mon, Tue, Wed........and a Level 1 Cumulative on Thurs, and your DB needed restoring on Saturday, you'd restore your Level 0 from Sunday, your Level 1 Cumulative from Thurs (because it includes everything that has changed since Sunday), and your Level 1 Differentials from Friday. (and of course your archive logs that would also hopefully be part of your strategy, taken on frequent intervals, to get your as close to the moment of failure time as possible.

So you mentioned "incremental forever." That doesn't exist with RMAN. But "incrementals" do, but are just termed as "Level" 0 or 1.

As moon-buddy said, your time between your Level 0 incrementals (what we are calling "full") is business-specific. Say you have a database that barely changes and you do a Level 0 incremental once a week, with only logarchives during the week, and that DB creates only 10-20 logarchives. You may in that case be perfectly fine with the Weekly Level 0 and periodic archive log backup only. It's business requirements that drive it, but hopefully this info can help you in your discussions with your data owners.