Backing up / restoring a database for oacle 10g testing using sqlplus or rman

Question

Using Oracle 10g with our test server is the most efficient / easiest way to back up and restore the database at a static point, assuming that you always want to return to that point after backing up.

An example using the example will be as follows

• install and configure all software
• Change data to a test base point
• make a backup somehow (this is part of the question how to do this)
• perform testing
• return to the state of the 3rd state (return to the backup point, this is the other half of the question)

Optimally this will be done via sqlplus or rman or some other method used for scripting.

Answer 1

You do not need to backup in normal time. Just turn on the flashback database, create a guaranteed recovery point, run your tests and memories in the previously created recovery point.

The steps for this are:

• Running an instance in mount mode.

  mountup force mount;

• Create a recovery point.

  Back up the before_test database to restore the retrospective database

• Open the database.

  change the database open;

• Run your tests.

• Shutting down and installing the instance.

  shutdown immediately; autoload;

• Return to recovery point.

  flashback to restore the before_test point;

• Open the database.

  change the database open;

Answer 2

You can use the function in Oracle called Flashback , which allows you to create a recovery point with which you can easily go back after you have passed the test.

Quote from the site,

The Flashback database is similar to the "rewind" button for your database. This provides database time during recovery without having to back up the database for recovery. when you eliminate the time it takes to restore a database backup from tape, the database point during recovery quickly.

Answer 3

In my experience, import / export is probably the way to go. Export creates a logical snapshot of your database, so you won’t find it useful for large databases or demanding performance requirements. However, it works great for taking snapshots and why not use it on multiple machines.

I used it in a rails project to get a prod snapshot that we could exchange between developers for integration testing, and we did the work in rake scripts. We wrote a small sqlplus script that destroyed the database, then imported the dump file on top.

Some articles you can check: OraFAQ Cheatsheet Oracle Wiki

Oracle apparently no longer likes imp / exp in favor of the data pump , when we used the data pump, we needed things that we could (i.e. SYSDBA privileges that we could not get in a common environment). So take a look, but don't be discouraged, if the data pump is not your bag, the old imp / exp is still there :)

I cannot recommend RMAN for this kind of thing, because RMAN requires a lot of configuration and will need to be configured in the database (it also has its own backup database, which is a pain in the notorious for recovering pure metal).

Answer 4

If you use a file system that supports snapshots of copy to write, you can configure the database to the desired state. Then turn off everything and take a snapshot of the file system. Then proceed with the testing, and when you are ready to start, you can cancel the picture. This might be easier than other options if you have a file system that supports snapshots.

Answer 5

Decision

@Michael Ridley is great for scripting and will work with any version of the oracle. A.

This is exactly what I am doing, I have a script that rides on a weekly basis

• Rollback file system
• Apply production archive logs
• Take FS's New Advance Database Snapshot
• Reset magazines
• Apply masking of "preproduction" data.
• Take a new snapshot "Post-Data-Masking" (allows you to roll back data after masking)
• Open database

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

For this I use ZFS.

This method can also be used for your applications or even for the entire "environment" (for example, you can "roll back" your entire environment with one (script) command.

If you use 10g, the first thing you probably want to learn is Flashback, as it is built into the database.