2

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.

Improve this question
2
  • 4
    You shouldn't disable the constraints in the first place. Then you could simply rollback everything if something is wrong
    – user1822
    Commented Jul 6, 2012 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?
    – Justin Cave
    Commented Jul 6, 2012 at 6:30

1 Answer 1

Reset to default
10

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.

Improve this answer
5
  • 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.
    – GrumpyMonkey
    Commented Jul 7, 2012 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
    Commented Jul 9, 2012 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
    – GrumpyMonkey
    Commented Jul 9, 2012 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
    Commented Jun 24, 2013 at 13:07
  • Also, if you're on 11gR2 or greater, there is no need to cycle the database to enable flashback. The database can remain open. You do however need to ALTER DATABASE OPEN RESETLOGS; still after performing the flashback.
    – Dustin
    Commented Jun 16, 2023 at 15:41

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.