Thanks for the question, Arun.
Asked: April 18, 2025 - 1:16 am UTC
Last updated: April 23, 2025 - 8:03 am UTC
Version: 19c
Viewed 1000+ times
You Asked
Hello ASK TOM team,
Recently our client has come out with a requirement to be able to rollback database part of any application deployment. Now in our case, the application deployment can mean any or all of the following from a database perspective:
a) PL/SQL code (functions, packages, stored procedures) add/change/delete
b) Tables add/change/drop
c) Index add/change/drop
d) Table data insert/update/delete
e) Possibly other database application schema objects add/change/delete like views, triggers etc.
Being a DBA,my first thought was flashback database, but we are brainstorming if any other Oracle features like OWM/EBR would be better/easier choice. The idea is that the new version of application objects co-exist with the previous one. If testing indicates any issue, we rollback to the previous version. I am not conversant at all with OWM/EBR so cannot compare these with flashback. Any advise will be appreciated.
Thanks
and Connor said...
These options are not necessarily mutually exclusive ... but they generally all come down to the issue of user data. Let me give some examples to explain
1)
- The system is down (or read-only)
- You've just deployed the next release, which is new objects, code and even modifications to data
- Something terrible just happened with your deployment - its not going as you expected
Solution: Flashback database is perfect. Its quick, everything you you've just done is erased, no costly restore from backup and its like you never started the deployment at all... There is no issue with data because the only data changes in question were part of the deployment anyway. You live to fight another day :-)
2)
- The system is running and actively taking user transactions
- You're deploying the next release which is just changes to code, eg new tables, new triggers, new plsql etc.
- Something terrible just happened with your deployment - its not going as you expected
Solution: EBR is a great fit here. You would have deployed into the new edition, and when it call went wrong, you drop that edition. The application continues without incident.
3)
- The system is running and actively taking user transactions
- You're deploying the next release
- Your deployment is finished. It seems fine, but 2 hours later you discover a serious bug
Solution: Flashback really can't be used, because you now have 2 hours worth of user transactions. You can't simply throw that away. EBR *might* be a good fit, as long as the data that's been taken in the past 2 hours is readily usable in the old edition.
But in most circumstances, once you've opened your system and are taking user transactions, generally the only way to solve a deployment issue is to "move forward", ie, prepare another deployment to resolve the problem.
OWM is more designed as a developer convenience in non-production database and thus I would not consider it as a production deployment technique.