Cloning a Database from Hot or Cold Backup
Cloning a database
means create an identical copy of a database,
either on the same machine as the original, or on a different one.
Additionally, you will need to change the Database name and the Instance name (those are both optional steps if the clone is being created on a different machine).
What follows is therefore a set of instructions that takes things one-step at a time:
You can do it all in one go.
But, if different machines are involved, there's strictly no
requirement for steps 2 and 3 -so I've kept them separate.
Of course I'm assuming that the Oracle executables and environment variables (like ORACLE_BASE and ORACLE_HOME) are already on place.
• If your
database is not in ARCHIVE
LOG mode, take an off-line database backup. If in ARCHIVELOG MODE, you
can either take a hot or cold database backup. If you perform a hot
backup, at the end type:
alter system switch logfile;
• In the source database, issue the following command:
ALTER DATABASE BACKUP CONTROLFILE
TO TRACE as
create pfile from spfile;
• Copy the
initSID.ora file to '/my_backup_location';
• Copy the backuped
files, init.ora files, the control file trace
and the archived logs (if any) to the clone location
scp /my_backup_location/* oracle@NEW_BOX:/u01/app/oracle/oradata/DIEGO/.
• On the new Box: Move
all the files to the proper locations. Example:
• On the new Box: Edit the Control File trace script made earlier and:
o Clear out the junk at the top, so that the first line reads STARTUP NOMOUNT.
o Edit this statement to
CREATE CONTROLFILE SET DATABASE DIEGO RESETLOGS ARCHIVELOG
• Define the new
• Edit the initDIEGO.ora file. Make sure you edit:
• Start sqlplus,
connect SYS AS SYSDBA (internal) and run the create
create spfile from pfile;
• If the clone DB
is re-created from a Hot Backup, then perform a database recovery using:
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
You'll be prompted to supply the 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
ALTER DATABASE OPEN RESETLOGS;
• Add TEMP Tablespace
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/DIEGO/TEMP.dbf'
SIZE 600M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 13767M;
• [OPTIONAL] Change
the global name of the new database to avoid problems with
networked application and replication:
ALTER DATABASE RENAME GLOBAL_NAME = DIEGO;
• While connected to the
Clone database and type:
$ nid target=/• Connect to the Clone database and type:
DBNEWID: Release 10.2.0.4.0 - Production on Sun Sep 21 16:55:20 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database DIEGO (DBID=2153372661)
Connected to server version 10.2.0
Control Files in database:
Change database ID of database DIEGO? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2153372661 to 2820171320
Control File /u01/app/oracle/oradata/DIEGO/control01.ctl - modified
Control File /u01/app/oracle/oradata/DIEGO/control02.ctl - modified
Control File /u01/app/oracle/oradata/DIEGO/control03.ctl - modified
Datafile /u01/app/oracle/oradata/DIEGO/system01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/DIEGO/undotbs01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/DIEGO/sysaux01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/DIEGO/users01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/DIEGO/tools01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/DIEGO/temp01.dbf - dbid changed
Control File /u01/app/oracle/oradata/DIEGO/control01.ctl - dbid changed
Control File /u01/app/oracle/oradata/DIEGO/control02.ctl - dbid changed
Control File /u01/app/oracle/oradata/DIEGO/control03.ctl - dbid changed
Instance shut down
Database ID for database DIEGO changed to 2820171320.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
• If you care about your
clone, you should now perform a clean shutdown and backup, because the
resetlogs that was issued will have rendered all prior backups and
archives of the primary system completely useless as a way of
recovering the clone.
• Also update
echo "DIEGO:/u01/app/oracle/product/10.2:N" >> /etc/oratab