Long-Term Oracle Backups

[GregE]

I realize this is strictly an Oracle question, but maybe some of you who interact with your Oracle DBA's know how your long-term strategy works.

We want to take a monthly backup of Oracle databases, and keep those for 12 years. It would all be done with RMAN (the "keep until" clause for anyone interested). That clause tells RMAN to NOT apply normal retention settings to that backup. So it wouldnt expire until 12 years has passed, yet would have no effect on the normal backup of the database and it's retention settings.

But, the data owners only need certain tables kept for 12 years, not the entire DB. Surely we can put specific tables into an RMAN backup script, but for a long term storage of the Oracle DB, does this suffice? Do we need the entire DB? Or can we just backup specific tables, and RMAN will backup the necessary core things (controlfiles of course) in order for that backup to actually be useful if ever needed?

 

[moon-buddy]

To the best of my Oracle knowledge, and in conjunction with TSM and RMAN, you need the whole DB in order to recover.

If you need just specific portions of the database, then it would be a lot simple to do a dump and extract of the portions you really need.

 

[GregE]

Thanks. What strategies have you seen/used for long term Oracle retention? The monthly "full" Oracle db backup for lots of DBs would be a royal pain and a resource hog, though we may have to do that. Have you worked an environment where some other things are done in order to not keep so much data (much of it duplicated month after month)?

 

[moon-buddy]

Thanks. What strategies have you seen/used for long term Oracle retention? The monthly "full" Oracle db backup for lots of DBs would be a royal pain and a resource hog, though we may have to do that. Have you worked an environment where some other things are done in order to not keep so much data (much of it duplicated month after month)?

One option is by using DB dumps for long term retention. Just bear in mind that these are PIT backups and the real purpose is just that - for retention.

We have done this a lot in the past wherein we do FULL dumps at month-end, and use the incrementals in between month-ends for any restore (DR related) requirements.