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.

  • 4
    You shouldn't disable the constraints in the first place. Then you could simply rollback everything if something is wrong Jul 6 '12 at 6:26
  • 4
    Can you simply create the constraints as deferrable, defer them before you issue the DML, and then rollback if the constraints fail to validate at the end of the transaction? Jul 6 '12 at 6:30

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

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

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;

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.

  • A word of warning about just creating a restore point, there is a limit on how much flashback is held. Use create retore point before_Load guaranteed flashback database. Also make sure your the size of yor recovery area and disk space is sufficient to hold your changes. If you run out of space it can easily mean you cannot restore from it. Jul 7 '12 at 12:07
  • @GrumpyMonkey I am working with around 15GB database. How much (Approximately)minimum and maximum disk space required to make use of restore points.
    – madhu
    Jul 9 '12 at 4:21
  • 1
    @madhu, this on is very much a it depends issue. Flashback logs changes just like archive logs So the volume of changes will dictate how much space you'll need. For example I did some work on an 80gb database but it generated 250gb of flashback logs, but that was some pretty extreme changes to the database. Hopefully you have a good idea of what needs to be done and set the flashback recovery area appropriately. Then keep an eye on the flashback recovery area usage view to monitor its usage of that area Jul 9 '12 at 20:54
  • The startup; won't work as you should open the database with resetlogs. So after shutdown immediate the correct sequence of commands should be as follows: startup mount;alter database open resetlogs;
    – user25294
    Jun 24 '13 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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