Backup/Restore database for oracle 10g testing using sqlplus or rman

Question

Using Oracle 10g with our testing server what is the most efficient/easy way to backup and restore a database to a static point, assuming that you always want to go back to the given point once a backup has been created.

A sample use case would be the following

1. install and configure all software
2. Modify data to the base testing point
3. take a backup somehow (this is part of the question, how to do this)
4. do testing
5. return to step 3 state (restore back to backup point, this is the other half of the question)

Optimally this would be completed through sqlplus or rman or some other scriptable method.

Answer 1

You could use a feature in Oracle called Flashback which allows you to create a restore point, which you can easily jump back to after you've done testing.

Quoted from the site,

Flashback Database is like a 'rewind button' for your database. It provides database point in time recovery without requiring a backup of the database to first be restored. When you eliminate the time it takes to restore a database backup from tape, database point in time recovery is fast.

Answer 2

If you are using a filesystem that supports copy-on-write snapshots, you could set up the database to the state that you want. Then shut down everything and take a filesystem snapshot. Then go about your testing and when you're ready to start over you could roll back the snapshot. This might be simpler than other options, assuming you have a filesystem which supports snapshots.

Answer 3

Hi,

From my experience import/export is probably the way to go. Export creates a logical snapshot of your DB so you won't find it useful for big DBs or exacting performance requirements. However it works great for making snapshots and whatnot to use on a number of machines.

I used it on a rails project to get a prod snapshot that we could swap between developers for integration testing and we did the job within rake scripts. We wrote a small sqlplus script that destroyed the DB then imported the dump file over the top.

Some articles you may want to check: OraFAQ Cheatsheet Oracle Wiki

Oracle apparently don't like imp/exp any more in favour of data pump, when we used data pump we needed things we couldn't have (i.e. SYSDBA privileges we couldn't get in a shared environment). So take a look but don't be disheartened if data pump is not your bag, the old imp/exp are still there :)

I can't recommend RMAN for this kind of thing becuase RMAN takes a lot of setup and will need config in the DB (it also has its own catalog DB for backups which is a pain in the proverbial for a bare metal restore).

Answer 4

@Michael Ridley solution is perfectly scriptable, and will work with any version of oracle.

This is exactly what I do, I have a script which runs weekly to

1. Rollback the file system
2. Apply production archive logs
3. Take new "Pre-Data-Masking" FS snapshot
4. Reset logs
5. Apply "preproduction" data masking.
6. Take new "Post-Data-Masking" snapshot (allows rollback to post masked data)
7. Open database

This allows us to keep our development databases close to our production database.

To do this I use ZFS.

This method can also be used for your applications, or even you entire "environment" (eg, you could "rollback" your entire environment with a single (scripted) command.

If you are running 10g though, the first thing you'd probably want to look into is Flashback, as its built into the database.