CODEWITHSUNDEEP
sqloracle-databasebackuporacle10grman
crackity_jones
crackity_jones

Reputation: 1077

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

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.

Upvotes: 8

Views: 18374

Answers (5)

Rich Adams
Rich Adams

Reputation: 26574

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.

Upvotes: 5

robertpostill
robertpostill

Reputation: 3970

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

Upvotes: 2

Matthew Watson
Matthew Watson

Reputation: 14233

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

Upvotes: 0

Yas
Yas

Reputation:

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;

Upvotes: 6

Michael Ridley
Michael Ridley

Reputation: 10498

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.

Upvotes: 1

Related Questions