Sunday, 4 February 2018

How to Recover Data (Without a Backup) - How to Restore a Whole Table in oracle apps EBS R12. Recover all the data. Using Flashback Table, you can return a whole table to an earlier state.

How to Recover Data (Without a Backup) - How to Restore a Whole Table in oracle apps EBS R12. Recover all the data. Using Flashback Table, you can return a whole table to an earlier state. 


--------------

flashback table ap_invoices_all to timestamp <when it was good>;

flashback table "AP"."AP_INVOICES_ALL" to timestamp systimestamp - interval '5' hour;


alter table "AP"."AP_INVOICES_ALL"  enable row movement;

----------------
How to Restore a Whole Table
It's a classic rookie mistake: running a delete without a where clause. And then committing it!

Here you need to recover all the data. Using Flashback Table, you can return a whole table to an earlier state. All you need to do is run:

  flashback table <table> to timestamp <when it was good>;
For example, execute:

  flashback table orders to timestamp systimestamp - interval '1' hour;
And Oracle restores the table its state one hour ago. Handy if you’ve just deleted all the rows!

To use this, you must enable row movement:

  alter table <table> enable row movement;
If you haven’t done this, you’ll get the following error:

  ORA-08189: cannot flashback the table because row movement is not enabled
This is great if you’ve accidentally deleted or updated the whole table. But if there are only a handful of rows you need to recover it’s excessive. You’ve used stick of dynamite to kill an ant.

Even if you need to recover a large section of a table, flashing it back loses any changes made after the time you’re restoring it to. In most production systems there will be new rows you want to keep!


So this is handy for worst-case scenarios. It’s also useful for returning a table to a known state after testing. But for small finger trouble issues, it’s more likely you need to recover a handful of rows.

No comments:

Post a Comment

How to change Employee Number Generation from Manual to Automatic in Oracle HRMS (When attempting to apply for a job in iRecruitment)

When attempting to apply for a job in iRecruitment, the following error occurs: ERROR: You must enter an Application Number. Solution: How t...