Cloning a Database

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:

First, the clone.
Second (if you really want to) is the renaming of the Database.

Assumptions:
- New DB Name is: DIEGO
- New File location: /u01/app/oracle/oradata/DIEGO

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.

Step 1: The Actual Cloning

• 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 '/my_backup_location/create_DB_DIEGO.sql';
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:

mv initSID.ora /u01/app/oracle/product/10.2/dbs/initDIEGO.ora

• 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 read:
CREATE CONTROLFILE SET DATABASE DIEGO RESETLOGS ARCHIVELOG

o Also edit all the file locations mentioned in the trace file so that they point to appropriate clone locations. For example, when it says LOGFILE GROUP 1 'D:\ODATA\BLAH\LOG1A.RDO’, change that to read:
LOGFILE GROUP 1 ‘/u01/app/oracle/oradata/DIEGO/LOG1A.RDO'.
Perform that for all the file location references.
Also remove the lines for "RECOVER ..." and "ALTER DATABASE OPEN ..."

• Define the new $ORACLE_SID variable
export ORACLE_SID=DIEGO

• Edit the initDIEGO.ora file. Make sure you edit:

o CONTROL_FILES= (point to where you want the clone's Control Files created)
o LOG_ARCHIVE_DEST_1= (and variants) (the source archives must not be overwritten by the clone's)
o USER_DUMP_DEST & BACKGROUND_DUMP_DEST (source trace files must not be overwritten by the clone)
o INSTANCE_NAME

• Start sqlplus, connect SYS AS SYSDBA (internal) and run the create controlfile script.
create spfile from pfile;
startup nomount;
@Create_DB_DIEGO.sql

• 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 command:
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;

Step 2: Changing the Database Name (optional)

• While connected to the Clone database and type:
shutdown immediate;
startup mount;
exit;

$ nid target=/

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:
    /u01/app/oracle/oradata/DIEGO/control01.ctl
    /u01/app/oracle/oradata/DIEGO/control02.ctl
    /u01/app/oracle/oradata/DIEGO/control03.ctl

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.

• Connect to the Clone database and type:
startup mount;
alter database open resetlogs;
create spfile from pfile;

• 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 /etc/oratab
echo "DIEGO:/u01/app/oracle/product/10.2:N" >> /etc/oratab