How to duplicate an Oracle instance?

Question

How can I duplicate an Oracle instance? Does anyone have any idea how to do so?

Answer 1

Assuming you want the schema and data duplicated, use the exp and imp commands to export your database, then import it as another user using the FROMUSER and TOUSER parameters.

Answer 2

Create a template based on your existing instance. You can then create other instances.

Answer 3

Well, presumably you have a backup (surely!), so just test your backup recovery on your test server.

To be slightly more serious, it depends what version you are using, newer versions of RMAN make it pretty easy I believe, older versions, you basically do it as a backup recover.

How I've done it in the past, is basically

1. copy backup data files
2. create init file
3. create a new controlfile is the command 'CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG'
4. Apply archivelogs and then open with resetlogs

Here is an article which explains the process with a bit more detail

Answer 4

A minor comment on your terminology - "instance" is actually the set of processes running on the database server host and you want to duplicate the "database".

As someone else mentioned, the best way is to start with an RMAN backup of the original database. However, since Oracle 9 RMAN has had the "DUPLICATE DATABASE" command, which takes care of a lot of housekeeping that used to be necessary if you just made a copy by restoring a production backup (e.g. resetting DBID, changing data and log file locations in the control file, setting database GLOBAL_NAME, etc.).

If you're not using RMAN, and the database is on the small side, you can script something that puts each tablespace in hot backup mode, copies the datafiles for that tablespace to a backup location, and then takes the tablespace out of hot backup mode. You now have a recoverable backup that can be moved to another host for archive log application. This definitely has a performance impact on the original database and should be your last resort.