We are using Oracle 12c in production. Lets say there was release that went to production on Sunday and then some hours or some days later(e.g. Tuesday) we realized that we need to rollback the changes we did, assume there were DDL schema changes, along with DML changes which could be inserts, updates, deletes.

What is the best practice to rollback the changes? we can not restore database from backup because backup was from Sunday and there is data from Sunday to lets say Tuesday.

Just want to know what is the best practice for rolling back database changes in Oracle 12c.

  • 1
    If you rollback the changes, what should happen with the data changes that were made to the database since Sunday? They will be lost.
    – miracle173
    Commented Jul 30, 2020 at 6:30
  • You may want to clarify your question and indicate that you only want to undo DDL (table changes, view changes,constraints, triggers etc). But that you want to keep all data updates (DML) done after the DDL changes. Commented Jul 30, 2020 at 6:45
  • 1
    Undoing DDL in this scenario is only possible by applying an “uninstall” script as proposed in the first answer. Commented Jul 30, 2020 at 6:48
  • 1
    Did the DDL concern table containing datas modified during this Sunday to Tuesday period ?
    – tdaget
    Commented Jul 30, 2020 at 11:04
  • 1
    Is any reverse-script corresponding to your apply-script available ? In all case you have to do a step by step analyse and fully test this undo-script to ensure a data-loss well accepted by all
    – tdaget
    Commented Jul 30, 2020 at 11:11

3 Answers 3


In your scenario, the best OPTION is to use FLASHBACK DATABASE. It is as simple as it gets:

1.Create a restore point flashback database guarantee

create restore point my_save_point guarantee flashback database;

2.You apply your changes in Production, DDLs and DMLs. After some QA tests you realise something went wrong and you want to go to the save point

flashback datatabase to my_save_point ;

That last command will rewind the database to that moment in time, making a rollback of all the changes done from the moment you create the restore point.

I used a lot restore points for roll-outs to Production when they contain substantial changes that are very difficult to rollback using scripts.


Your options are limited, and your odds of preserving the DML transactions are low:

  1. Create an uninstall script. Your only chance that I can see would be to devise and run a custom uninstall script to undo whatever DDL was performed and return everything to its former state. Depending on whether or not any DML is dependent on the DDL (like for example the size of a column changing), this may not work.

  2. Point-in-time recovery from backup. If you are following best practices - especially for production - you should be running in archivelog mode and using RMAN to perform incremental backups every day. This would ideally (assuming you still have enough backup history) allow you to do a point-in-time recovery to just before the DDL was applied. You will lose all of your DML transactions this way, but that is unavoidable if you are restoring from a backup.

  3. Recovery from full backup. If you can't do a point-in-time recovery then you're looking at having to restore from the last full backup - either RMAN or datapump dump - before the DDL, which will also imply (from the sound of it) significant data loss.

  4. Flashback Database. Even if you had flashback configured and enough history to actually do this after so many days, and assuming you meet all the requirements for flashback, it would still undo everything, including the DML.

Bottom line here is that if you must preserve the data, then you'll have to fix your schema through another release (like the undo script), not a rollback or a restore from backup.


Lets say there was release that went to production on Sunday and then some hours or some days later (e.g. Tuesday) we realized that we need to rollback the changes ...

Sorry to say it, but two days is a very long time to discover a problem serious enough to even consider rolling back.

we can not restore database from backup ... there is data from Sunday to lets say Tuesday.

Correct. If you roll back, you lose that data.

Can you ask your customers to "redo" everything they've done in the intervening two days? Of course not. The only existing record of the work they did is the one in your database. You cannot blithely destroy that data by rolling back.

You might have to

  1. "Freeze" the Business,
  2. Create a duplicate database in which to hold the current data,
  3. Roll back the "real" database,
  4. Reverse engineer the data added between Sunday and Tuesday from the "new" structures into the "old" ones,
  5. Get the business to check that everything looks "right", then
  6. "Unfreeze" the business.

If you're very lucky, you might be able to skip steps #1 and #6, but you'll need to do a fair amount of analysis up front to work out whether that's the case or not. If not, the pressure on doing it quickly will ramp up astronomically.

#4 is the really worrying one.

That bit's never been done before (not against these two sets of data structures, anyway) and anything that's a "first" is a huge Risk and and a big "unknown" in terms of how long it might take.

Make sure you have antacids on hand. You're probably going to need them.

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.