Flashback feature in Oracle 11g

flashbackoracleoracle-11g-r2

I am using Oracle 11gR1. As a part of a project, I have to execute multiple update statements by disabling a few constraints (triggers and check constraints) which could result in a corrupted database.

If the database gets corrupted, I have to re-import it entirely, which is an additional task that is time consuming.

I have very little knowledge about the Oracle Flashback feature. Will Flashback help me to avoid these unnecessary imports? Can I set some stable point and switch back to there if anything goes wrong?

Can someone please give me brief overview on this feature?

If it fits my requirements, I will try to learn.

Best Answer

Firstly, you must enable flashback. In SQL*Plus as SYSDBA:

shutdown immediate;
startup mount;
alter database flashback on;
shutdown immediate;
startup;

Next before your load operation, create a restore point:

create restore point before_load;

Now do you work, and when you're done:

shutdown immediate;
startup mount;
flashback database to restore point before_load;
shutdown immediate;
startup;

And your data is back as it was. You can repeat this as many times as you like, and when you are done, drop the restore point. HOWEVER the commenters are correct; you should leave the constraints enabled and just defer them.

Related Question