Clone database from one machine to another

Procedure to copy or clone a database from one machine to another:

• If your database is not in ARCHIVE LOG mode, take an off-line database backup. If in ARCHIVELOG MODE, you can either take an on-line or off-line database backup.

• On your production database, start SQL*Plus and connect to "SYS AS SYSDBA" (internal) and do a:

SQL> alter database backup controlfile to trace;

• Edit the controlfile trace (in $ORACLE_BASE/admin/$ORACLE_SID>/udump) and remove the lines until you get to the 'CREATE CONTROLFILE ..' statement. Edit this statement to read:

CREATE CONTROLFILE SET DATABASE "new_db_name" RESETLOGS
ARCHIVELOG

• Move this script to the new machine. Edit this script to update the directories for the datafiles and redo log files. Also, comment out the lines for "RECOVER ..." and "ALTER DATABASE OPEN ..."

• Restore/ or copy the backup and the archived logs (if any) to the new machine.

• Copy your INIT.ORA file to the new machine. Ensure your controlfile and archive destinations in your SPFILE/ ini$ORACLE_SID.ora are properly set on the new machine.

• Set your Oracle Environment by running ". oraenv" on Unix or Linux.

• Start sqlplus, connect SYS AS SYSDBA (internal) and run the create controlfile script.

• Perform a database recovery using

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

You'll be prompted to supply an archived log file, Restore that file to the archive log destination (if not already there) and continue. Repeat for all archived files till you get to the last one. At that point specify 'CANCEL'.

• Issue the command:

SQL> ALTER DATABASE OPEN RESETLOGS;

• Change the global name of the new database to avoid problems with networked applicationa and replication:

SQL> ALTER DATABASE RENAME GLOBAL_NAME = new_db_name;

• Include the new database into your backup strategy.

You're all done.