Search Oracle Blogs

List of Blogs maintained by Paweł Barut.

Sunday, September 10, 2006

Rolling Back the DDL

Assume You were asked if You can rollback changes in tables in Oracle database, You would answer that it is not possible. DDL always makes commit before and after statement. But wait a minute: there is solution for that. In 10g we have Flashback feature. This feature can be extremely useful when you are running application patch on database. You can use this scenario:
  1. issue command to set restore point:
  2. apply application patch
  3. test system after patch
  4. if ok, release restore point:
    DROP RESTORE POINT before_patch;
  5. if something go wrong, flashback to restore point:
Let me explain some issues. CREATE RESTORE POINT command has two versions: with and without GUARANTEE FLASHBACK DATABASE. When you enter GUARANTEE clause, then you must be sure that Flashback area is big enough to store all changed blocks. And at the end you must drop this restore point. Otherwise you will run into out of space in flashback area. You must also be aware that FLASHBACK DATABASE has impact on all schemas in database, so be careful.
Moreover you can use Restore Point while upgrading database 10g R1 to 10g R2. It can be very time saving in case of failure during upgrade process.
Restore Points can be also usefull in application testing process. You can define restore point, run test procedures, save results, restore to to saveponit and test again ...
To check if that you have set restore point you can run query:
select * from v$restore_point;
Hope you will find it useful,
Cheers, Paweł

Related articles:


Anonymous said...

Rememnber to set compatibility. otherwise you get:
ORA-38784: Cannot create restore point 'SERVIS'.
ORA-24081: compatible parameter needs to be or greater

FunkHo said...


How do you propose to use a restore point while upgrading from 10g R1 to 10g R2 when restore points are a feature introduced in 10g R2??

Would you do a "mount upgrade" using the 10.2 binaries and create a restore point right away?

Paweł Barut said...

What I would do:
1. before installing 10gR2 binaries i would setup flashback mode for database
2. install 10gR2 binaries
3. startup upgrade
4. set compatibility to 10.2
5. set restore point
6. run upgrade script.
I must say that I did not test it in practice, but it should work. Also this document by Lawrence To, Joe Meeks and Jeffrey McCormick confirm that.

Anonymous said...

This is a very interesting feature, but in my opinion the cases of usages is restricted to some limitations. Firts of all the database where the application resides must be the only one on the database, otherwise after if after patch something goes wrong and a restore point is necessary, the other applications will be affected (what if some new data on other applications has been inserted during application patch?). In addition to run those commands it is probably necessary to have DBA privileges, and it is not common to be a developer with those privileges. However it is very interesting indeed. Thanks. Bizzo


Copyright © Paweł Barut
Printing from DOS to USB Printer