Manage

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.

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

All
News
Get Started
Evaluate
Manage
Problem Solve

Solving common Oracle error codes guide

Related Q&A from Brian Peasland

Managing Oracle connections from third-party apps

Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading

Perfect the Oracle RMAN duplicate 10g command

One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading

Managing SQL parent table-child table relations

Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading

Have a question for an expert?

Please add a title for your question

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

Meet all of our Oracle Database / Applications experts

View all Oracle Database / Applications questions and answers

SearchDataManagement

A comparison of open source, real-time data streaming platforms

With so many real-time data streaming platforms, how do you know which one is right for your organization? Experts compare Spark ...

Data virtualization tools promote anywhere, anytime data access

This online handbook examines data virtualization software and how organizations are deploying and using the technology as part ...

Data virtualization benefits seen in unified views, IT agility

Through in-place integration, data virtualization platforms can provide wider access to data and simplify security and governance...

SearchBusinessAnalytics

Social media analytics applications live and die by the data

This handbook provides insight and advice on how to use analytics to get information on customer sentiment and marketing ...

Social media analytics best practices tempered by privacy laws

Social media provides a fertile landscape of information and insight into consumer behavior, but collecting and analyzing that ...

How the Salesforce acquisition will affect Tableau users

In the wake of Salesforce's acquisition of Tableau, it remains to be seen whether Tableau users will be pushed to the cloud and ...

SearchSAP

A breakdown of 10 commonly used SAP ECC components

Whether you've already implemented SAP ECC or your company is considering expanding its use of the on-premises ERP system, it's ...

SAP NS2 execs on why secure cloud services are the future

In this Q&A, SAP NS2 executives discuss how the company helps national defense, intelligence and security entities move to the ...

New Onapsis service helps uncover SAP security issues

Security firm Onapsis released its Business Risk Illustration, which helps companies assess and deal with SAP security ...

SearchSQLServer

Key features to create a SQL Server audit trail in databases

SQL Server offers a set of built-in auditing tools that can help make the process of tracking logins and other database ...

Check SQL Server Query Store performance impact before using

Many IT teams hesitate to use SQL Server Query Store due to performance concerns. Consultant Andy Warren offers tips on how to ...

SQL Server vs. MySQL: Learn the differences

MySQL and Microsoft SQL Server relational databases have their pros and cons. Weigh the differences between SQL Server and MySQL ...

TheServerSide.com

Pivotal Spring Runtime extends Java support, company lifeline

Pivotal's distribution of OpenJDK, the open source version of Java, joins the ranks of Amazon, Azul, IBM, Red Hat and others that...

Master Git basics and branch into DVCS

This Git guide focuses on some of the most basic information and tips. With a solid foundation, developers will be better ...

Don't let plugins open up more Jenkins vulnerabilities

Let's explore the Blue Ocean, Config File Provider and Groovy plugins with security vulnerabilities. Here's how to address them, ...

SearchDataCenter

China's supercomputing systems soar from 21 to 219 in a decade

The U.S. dominates in total supercomputing performance, but lags behind China in the overall number of supercomputers. U.S. x86 ...

Data center cooling systems advance, incorporate natural sources

Despite concerns around water-based cooling technology, organizations are developing new hardware setups that rely on fjords and ...

A 6-step guide for hardware disposal

Asset tracking, information logging and security measures should all be a part of hardware decommissioning. Don't miss a step ...

SearchContentManagement

Nuxeo Insight Cloud adds AI content services to platform

Nuxeo adds AI content services to its content management suite in hopes of spurring citizen data scientists to create custom data...

Content collaboration tools sharpen focus on customer demands

Content collaboration software is necessary to create and share content among business users in and out of the office. Here are ...

NLG tools powered by AI energize, customize, optimize content

Though in its early stages, natural language generation automates content collaboration platforms to streamline employee ...

SearchHRSoftware

Social learning takes its place in employee retention efforts

Employees are more likely to stay at jobs where they can learn new skills or improve old ones. For companies, that means ...

Generation Z workplace may emphasize human contact

Generation Z may demand more human-to-human contact with managers and co-workers. Surveys suggest that text messaging will also ...

Workplace violence drives interest in mass notifications

The Virginia Beach shooting that killed 12 may help motivate employers to deploy mass notification systems as well as programs to...

Dig Deeper on Oracle database backup and recovery

Related Q&A from Brian Peasland

Have a question for an expert?

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.