Copy and Rename (Clone) a Database

The original DB: mydb1
The new DB: oradb1

1) Copy mydb1 data files and init.ora
a. Obtain db datafile names:

select file_name from dba_data_files
FILE_NAME
--------------------------------------------
/opt/app/oracle/oradata/mydb1/system01.dbf
/opt/app/oracle/oradata/mydb1/undotbs01.dbf
/opt/app/oracle/oradata/mydb1/user01.dbf


b. Shutdown the DB, cp and/or ftp above files to different location

cp above files to location(after shutdown the db):

/opt/app/oracle/oradata/oradb1/


c. Copy and edit init.ora for new database

Copy the initmydb1.ora to initoradb1.ora;

edit initoradb1.ora;

create bdump cdump udump etc directories


2) Create the script that will re-create the controlfile

sql> startup mount
sql> alter database backup controlfile to trace;


in USER_DUMP_DEST find the correct trace file,

create a script (cr_ctl.sql) from it;
change 'reuse' to 'set'; specify resetlogs

make any necessary edits to cr_ctl.sql, e.g. pathname etc

3) Create a password file under $ORACLE_HOME/dbs (could be optional)

% orapwd file=orapworadb1 password=oracle entries=5

(note: not necessary if using OS authentication method)


4) Set ORACLE_SID = oradb1

run the cr_ctl.sql script

% sqlplus /nolog
% connect / as sydba
% startup nomount

% @cr_ctl.sql -- database should be in startup in nomount mode

% create spfile from pfile; (not for 8i)

% alter database open resetlogs;

5) add a temp file

alter tablespace temp add tempfile '/opt/app/oracle/oradata/oradb1/temp01.dbf' size 30M


DBID problem fix: (ref: http://bmoshier.net/OracleStuff/ChangeDBID8i9i2.html )


Oracle 9i Release 2 (9.2.0.1)

In Oracle 9i Release 2 (9.2.0.1), Oracle provides the ability to change the DBID and/or DBNAME through a new feature, the DBNEWID utility, and works outside of RMAN. Complete documentation on this utility is in the Oracle 9i Database Utilities Release 2 (9.2) part number A96642-01 manual.

Oracle 8i

In Oracle 8i, Oracle provides the procedure: dbms_backup_recovery.zeroDbid for this purpose. While Oracle does not formally support it, Oracle note 174625.1 covers its usage.

If you are using RMAN and choose to use the following method based on Note 174625.1, please keep in mind Notes 105832.6 and 1062736.6 Should RMAN already know about the original and clone databases, you will need to deregister and re-register the databases with RMAN.

The basic concept is:

With the database in mount but NOT open mode. Use dbms_backup_recover.zeroDbid to zero the DBID. Recreate the control files to assign a new DBID to the database.

Step by Step Instructions

To Change an Oracle Database Identifier (DBID) for 8i

- Connect to the database in SYSDBA mode.

- Perform a clean shutdown. Do NOT abort or force the instance. This shutdown is the same as when you are going to do a cold / closed database backup. You may wish to perform a cold / closed database backup at this time, as a measure of protection.
shutdown immediate

- Startup the database in mount mode
statup mount

- Check the present (soon to be old) database ID (DBID)
select dbid, name
from v$database;

DBID NAME
---------- ---------
578456994 SNEAKERS

Elapsed: 00:00:00.20

- Generate the create controlfile statement into a trace file.
alter database backup controlfile to trace;

- Generate a new DBID using the zeroDbid function of dbms_backup_restore
execute dbms_backup_restore.zeroDbid(0)

- Get another clean shutdown.
shutdown normal

- Delete the controlfiles (Unix: rm; Windows erase).

- Start up the database in nomount mode.
startup nomount

- Recreate the control files modifying it for your database and possible new name (if this is a new clone). You can only use the instructions for recreating the controlfile (create controlfile).
Change from:

CREATE CONTROLFILE REUSE DATABASE "SNEAKERS" NORESETLOGS NOARCHIVELOG

Change to:

CREATE CONTROLFILE SET DATABASE RESETLOGS NOARCHIVELOG

Use only the CREATE CONTROLFILE command from this trace file. Remove all the other statements (e.g. recover, etc.)

- Open the database.
alter database open resetlogs;

- Check the new DBID. It should be different.
select dbid, name
from v$database;

DBID NAME
---------- ---------
689567005 SNEAKERS

Elapsed: 00:00:00.20