Solved

Recover Oracle Using Multiple RMAN backupsets

Posted on 2009-05-06
2
531 Views
Last Modified: 2013-12-18
Environment is Oracle Enterprise Edition 10.2.0.3, Solaris 10.

Full cold backups of our 3TB data warehouse are done with RMAN when possible. Sometimes, that is only once each month, due to system maintenance by Unix Ops or other business priorities.

I do not have enough space to archivelog. We spit out 350G of logs (each 1.2M) every week. We have approximately 580 tablespaces and about 630 datafiles. (I inherited that!!). Most of our partitioned tables are in monthly partitions, and they are never archived off. Only our daily partitioned tables have a limited retention policy of 90 days.

Currently, our RMAN backup takes about 13 hours, and the backupset is stored on a storage array where no other production files exist. The compressed backupset uses about 600m.

Due to the fact that "the business" sometimes comes out of the woodwork and asks to update or modify huge partitioned tables (we have about 30 of them) that contain data going back as far as 1998, I have never been able to get a commitment to mark some of these tablespaces as read only. Now, I have been given a list of about 140 tablespaces that can be marked read only.

So, I plan to do a new "baseline" RMAN backup this Sunday, after which I want to mark the approved tablespaces as read only. Future RMAN backups should skip the read only tablespaces, so long as I retain the original baseline backup. Are there any circumstances where that would not be true? If our storage array went up in flames and we lost everything, how would I go about recovering using the baseline and the most recent backup that contains everything except the read only tablespaces?

Would I be better off creating a separate backup of only the tablespaces that are going to be marked as read only, then another backup AFTER they have been marked?

Most of the literature I have explored deals with shops that use archivelogging, and I have not discovered a recovery scenario that describes how I could recover the entire database in a case like ours.

Thanks in advance.

Mike

NA

Open in new window

0
Comment
Question by:groucho47
2 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 24319227
Create separate backups for the read-only tablespaces and KEEP them.
Do regular backups of the rest of the db.
Do incremental backups (set block-tracking for speed).
 
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
dates - loop 12 68
How to free up undo space? 3 43
Component is listed with a Protocol more than once 3 32
Database Design Dilemma 6 40
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question