Q
Manage Learn to apply best practices and optimize your operations.

Backup and recovery strategy for very large databases

I have two very large databases and I'm looking for a good backup/recovery strategy. One is an OLTP Oracle 9.2.0.5 10TB database and the other is an Oracle 10.2 data warehouse that is 12TB.

Brian Peasland
Hello Brian, I have two very large databases that I am responsible for. One is an OLTP Oracle 9.2.0.5 10TB database and the other is an Oracle 10.2 data warehouse that is 12TB. I'm looking for a good backup/recovery strategy. Currently I do not have a good enough tape backup. According to the SAs they can only back up up four mount points at a time. I have a total of 30 mount points that I am spreading my RMAN level 0 backup to; they are also backing incremental changes on the other mount points. I am generating too many archivelogs to use RMAN, so I am compressing them and moving them to another drive. At least 1TB daily of archivelogs. I have scratch drives that I put exports to of the critical tables so that I can restore them when requested; these are also partitioned and they are also compressed. The RMAN backups exclude the partitioned tables. These are actually going to the data warehouse daily and the others are easily reproducible. Just re-run the data load. Any suggestions? This was supposed to be a proof-of-concept database. Now it's production. Thanks in advance.

Obviously, with large databases, it takes a very long time to back them up. The best course of action is to put...

all of your static data into READ ONLY tablespaces. For a 10TB database, it is highly unlikely that all of your tables are changing regularly. So put static tables in tablespaces apart from tables which undergo DML. Then backup those tablespaces in RMAN with the BACKUP TABLESPACE command. Next time you do a full or incremental database backup, tell RMAN to SKIP READ ONLY tablespaces. This way, you are not backing up your static data over and over again.

Another strategy is to spread out your tablespace backups over multiple nights. Have RMAN back up some tablespaces tonight and other tablespaces tomorrow night.

The RMAN documentation also has many hints on how to tune RMAN for better performance.

This was last published in March 2007

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Meet all of our Oracle Database / Applications experts

View all Oracle Database / Applications questions and answers

Start the conversation

Send me notifications when other members comment.

Register

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close