Row level restore with Oracle Recovery Manager (RMAN)

backuporacleoracle-11g-r2restorerman

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?

Best Answer

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')
);
Related Question