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

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;

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.

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 CheatsheetOracle 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).