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 do not need to take a backup at your base time. Just enable flashback database, create a guaranteed restore point, run your tests and flashback to the previously created restore point.

The steps for this would be:

1. Startup the instance in mount mode.

   startup force mount;

2. Create the restore point.

   create restore point before_test guarantee flashback database;

3. Open the database.

   alter database open;

4. Run your tests.

5. Shutdown and mount the instance.

   shutdown immediate; startup mount;

6. Flashback to the restore point.

   flashback database to restore point before_test;

7. Open the database.

   alter database open;

Answer 2

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 3

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

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 5

@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.