How do I clone a database from one machine to another - when the filesystems are different ?

posted Mar 17, 2011, 11:04 AM by Sachchida Ojha

Cloning a database is normally done with the following sequence:

  1. Copy the datafiles to the target node
  2. Produce a CREATE CONTROLFILE command for the cloned database
  3. Start the target database in NOMOUNT mode
  4. Run the create controlfile command created in (2)
  5. Issue "recover database using backup controlfile until cancel"
  6. Provide the recovery processe with archived redo logs until you are happy
  7. Cancel the recovery and issue "alter database open resetlogs"
  8. Re-create or re-add any TEMPFILES used for temporary tablespaces

This is all quite straightforward even if you have to rename the database, and any of the files in the database. The secret is in step (2). On the source database, you can run

SQL> alter database backup controlfile to trace

at which point you will get a full "create controlfile" command dumped out to the user_dump_dest directory. It will look something like

CREATE CONTROLFILE REUSE DATABASE "SOURCEDB" NORESETLOGS ARCHIVELOG
...
LOGFILE
GROUP 1 '/disk1/log1.dbf' SIZE 50M,
...
DATAFILE
'/disk2/system01.dbf',
'/disk3/rbs01.dbf',
...
'/diskN/last_file01.dbf'
CHARACTER SET WE8ISO8859P1;

For cloning this database, you will alter

  • The REUSE to SET
  • The SOURCEDB to NEWDB (that is, the new name of the target database)
  • The NORESETLOGS to RESETLOGS

For each file listed under the DATAFILE clause, if you will be renaming the file as part of the cloning process, then you simply update the file name to the new name in the CREATE CONTROLFILE command.

Comments