Answer

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
By
Published: 22 Mar 2007
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.

Dig Deeper on Oracle database backup and recovery

SearchDataManagement
SearchBusinessAnalytics
SearchSAP
SearchSQLServer
TheServerSide.com
SearchDataCenter
SearchContentManagement
SearchHRSoftware
Close