0

We're approaching the end of 2012 and I still find myself relying on database exports to maintain the ability to do row level restore operations. While this is a proven method, it sure would be nice to get rid of all the export operations hogging database resources a significant number of hours each day.

A typical scenario would be that a customer accidentally deletes a project or document from an authoring system. To fix this I will need to locate and put back those deleted rows.

I'll pull up a database dump of the customer's schema from a date before the accidental delete, import the data to a new schema. Now I can identify the lost data, and select it back into the production schema.

I realize that you can do this directly with flash recovery, but there's a limit to how much data we can keep in the fast recovery area, and customers tend to not always figure out their mistakes within the allotted time.

Is there a good way to accomplish these kinds of tasks with the use of Oracle RMAN?

Roy
  • 1,060
  • 5
  • 16
  • 39

2 Answers2

3

The answer to your question is no, however....

It sounds like a flashback query is what you need. Query the data as of a time when it existed and when it returns the correct data, insert it into the current table. This solution does require space in the UNDO tablespace sufficient to meet your UNDO_RETENTION requirements. It also doesn't use RMAN, but is significantly simpler than importing the entire schema.

INSERT INTO T1 (
   SELECT * FROM T1 AS OF TIMESTAMP sysdate-1 
   WHERE MyDateColumn = to_date('05/25/2011','MM/DD/YYYY')
);
Leigh Riffel
  • 23,884
  • 17
  • 80
  • 155
2

Your requirements look like a good case for Oracle's Flashback Data Archive (Total Recall) feature.

Disclaimer: I haven't used this feature yet.

The description reads:

A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

You can enable it at the table level. You can specify an undo retention ; although I can't find the limit it is probably larger than you need since the goal is to keep records for legal purposes.

There is an example of a scenario that might apply to your specific case: Using Flashback Data Archive to Recover Data.

Oracle Total Recall is part of the Advanced Compression Option, available on the enterprise edition.

Vincent Malgrat
  • 4,484
  • 18
  • 23