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?

Improve this question
2
  • 1
    There's no nice way of doing this with rman. You'd have to do a point in time table space recovery to a different database, then work from that. Sounds like you'd be better off putting DELETE triggers on the affected tables that move the rows to archive tables, or re-architect the app to use is_deleted (or similar) flags, rather than actually deleting the rows. Delete triggers are probably easier, as they'd be transparent to the application.
    – Philᵀᴹ
    Commented Oct 29, 2012 at 13:21
  • As an alternative to manually writing triggers a flashback archive does the same thing - especially on 11.2
    – user1822
    Commented Oct 29, 2012 at 15:02

2 Answers 2

Reset to default
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')
);
Improve this answer
5
  • Whilst this is true, it won't solve the problem if the database is very busy (hence requiring a massive undotbs to satisfy the retention target) and the recovery operations are performed some time after the rows are deleted. Having said that, you did mention that in your answer :)
    – Philᵀᴹ
    Commented Oct 29, 2012 at 13:58
  • Indeed, I would guess it won't accomplish the goal of eliminating the export, but even if it can only be used to replace a small number of imports due to space limitations, the time savings from not having to do the import would be worth it.
    – Leigh Riffel
    Commented Oct 29, 2012 at 14:05
  • 1
    I'm a little confused. Is flashback data stored in the fast recovery area, or does it live in the undo tablespace? The UNDO_RETENTION parameter is mentioned, but I thought that was related to retention of transaction rollback/undo segments.
    – Roy
    Commented Oct 30, 2012 at 15:30
  • 1
    Oracle uses Flashback to describe several very different features, some of which use the Flash(Fast) Recovery Area. For example, Flashback Database does, but Flashback Drop and Flashback Query do not.
    – Leigh Riffel
    Commented Oct 30, 2012 at 15:57
  • 1
    You are correct that UNDO is used to maintain the unchanged version of changed blocks for in progress transactions, but Oracle designed their Flashback Query feature to take advantage of retaining the data longer. See docs.oracle.com/cd/E11882_01/server.112/e25494/… and docs.oracle.com/cd/E11882_01/appdev.112/e25518/…. This is a feature unique to Oracle - dba.stackexchange.com/questions/359/….
    – Leigh Riffel
    Commented Oct 30, 2012 at 15:57
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.

Improve this answer
2
  • Thanks for the tip, although with a list price of $11,500 per CPU licence, I don't think a little backup/restore convenience alone can defend the cost of Advanced Compression Option.
    – Roy
    Commented Oct 30, 2012 at 15:38
  • @Roy agreed, expensive for anecdotal use, but might be interesting to record all activity over a sufficiently large number of tables.
    – Vincent Malgrat
    Commented Oct 30, 2012 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.