What methods are available to create a snapshot of an oracle database for testing purposes?

Question

I am new to databases and have just been assigned to work with Oracle. We want to create a snapshot of the database for testing purposes that will allow us to revert back once we're done.

I'm a bit confused as to what my options are.

I've looked into creating a standby. It seems like that may be overkill for what we need and the pages describing how to create one are confusing to one with as little experience as I.

Questions

• Is a standby the best fit or is import/export a viable option?
• Are there other methods?.

Thanks!

Answer 1

If with "export/import" you mean the "traditional" exp and imp commands, than I'd say there is a better solution:

expdp and impdp are much faster and much more flexible when copying a snapshot of a database (or single schema).

If the database is not huge (i.e. Terabytes) then expdp and impdp is the easiest way to achieve this. They are easily scriptable and can be started using SQL also. So no commandline access to the servers is needed (provided the dump files are stored on a shared storage).

Answer 2

In addition to the impdp/expdb option already mentioned, you could restore/recover the database from your backups to a new location and then enable flashback database to revert the restored database anytime you want. The advantages of this method are that it will make sure your backups are good, verify and improve your recovery procedures, and allow for faster reverting. The downside is that it will take longer when you need a fresh copy. Here is an overview of Flashback Database from the documentation.

Flashback Database

Flashback Database is similar to conventional point-in-time recovery in its effects. It enables you to return a database to its state at a time in the recent past. Flashback Database is much faster than point-in-time recovery because it does not require restoring datafiles from backup and requires applying fewer changes from the archived redo logs.

You could also look into a storage based solution. Many offer a snapshot that initially takes little space and only grows as the snapshot and the source diverge. The database on the snapshot will behave as though a shutdown abort was done and so will require a bit of recovery (automatic).

Another option is to do a physical to virtual migration of the system and then have the virtualization product do the snapshot/revert.

Answer 3

Is the DB you want to copy running with RMAN? If so, using RMAN's 'duplicate' command could also be a quick way to clone your DB. Otherwise, using impdp/expdp is the best option and fairly easy to execute.