How to rollback database change in production after few hours or days

oracle

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.

Best Answer

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.

Related Question