Oracle Database Discussions

Hi,

I'm trying to decide on the best way to backup some production data in case a release fails and we need to roll back the changes. This would be for data updates only, not schema changes. We have extremely limited access to production, and another team handles all the RMAN backups. The people who actually implement our releases are also pretty prone to mistakes.

I have thought of two options. The tables we want to backup are about 9 MB total in size (it's about 10 codes tables).

1. Create a bu table for each of the tables before the release. So for JENNSTABLE, we would create a JENNSTABLE_BU using CTAS. If we need to revert, we can drop JENNSTABLE and rename the JENNSTABLE_BU accordingly. The bu table would remain in production until the next release, where it would be reused again. This would be really easy to script and therefore avoid any mistakes by the production support team who implements our instructions. We would also be able to determine what values changed by querying the bu table at any time (currently old values are not retained anywhere).

2. Use datapump to export the 10 tables, then truncate the tables and import the previously created files to restore the original data. I'm hesitant to use this method because I've never used datapump before, and as we don't have access to the servers, creating file system files makes me a little nervous. If I used a bu table, I can logon to the database and at least tell if it exists.


Are there any preferred methods for doing this (besides restoring a table w/ RMAN)? Is there a best practice? Any advice is appreciated!

-Jenn
Oracle 10g
UNIX Solaris