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
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.
Continue Reading
