in an Oracle 11.2 Database, Enterprise Edition, we are running calculations divided into 3 different "modules". These modules can be started on their own, but not parallel, so in between users are able to confirm the calculations and proceed with the next step.
Now I want to find a proper way to "save" and "restore" the tables to allow "a rollback" to the definied start before the module.
I have read the Oracle-Documentation about (garantueed) restore points but i think its way too much. Within its time of usage the process is more or less exclusive, but not garantueed. So i cannot rollback the complete database.
I found some information about the OracleWorkspaceManager but I am not sure if this tool would be something I could use.
Finally, I simply want to save and maybe restore 6 or 7 different tables. Is there an "easy" way to do this? Alternatively I am thinking of materialized views to store the information "before" each step. But that would create dependencies into the workflow I cannot image right now.
Thank you for your help.
Best Answer
IF you just want to be able to restore a few tables and the volume is not to high, why not just make copies of them?
You do need to set up good auditing and security around all your tables to prevent data loss, in the event that users did manual updates on tables that are going to be restored because of a process failure or something similar.
It could be that despite that failure, the user modifications are still needed and valid. Best would be to prevent any user changes in the period where processes run that could cause the need for a restore.