As of Oracle 8.1.5, rman has a new command called duplicate which will allow DBAs to clone a database AND change the DBID.
Remember that as of Oracle 8, a new identity attribute was added to each database called the DBID, which is generated at create database time and cannot be changed.
A standard database clone, (ie by copying the datafiles & re-creating the control files) creates a copy database with the same DBID as the original, an effect which causes numerous problems later with backup utilities such as RMAN.
This procedure describes how to clone a DB and change the DBID. In this example, our source DB is called SEED, the DB we want to create is named TRNG, and our recovery catalog DB is called RCAT. Before continuing, make sure all init.ora files are created, tnsnames.ora/listener.ora, and password files entries are created for all three databases. Also each init.ora file must have REMOTE_LOGIN_PASSWORD_FILE=exclusive as you will need to connect as internal remotely. Finally, make sure CONTROL_FILES is set to a different destination in initTRNG.ora.
0. Determine your Database character set, and set NLS_LANG properly. If you're using UTF8, then NLS_LANG must be AMERICAN_AMERICA.UTF8
1. Create a recovery catalog. This step is probably the most annoying. But the Clone process involves rman, and in order for rman to work, it needs a recovery catalog. A recovery catalog is small Oracle database which stores information about all backups. You can keep this DB VERY small (200M is fine), and it only requires a system tablespace. After creating the database, create a user called 'rman' and grant the following privs to it.
grant dba to rman; grant recovery_catalog_owner to rman;
Now startup rman and connect as this user:
rman connect rcvcat 'rman/rman'; create catalog; *(for O816 & O817 only)
* Note if you are on O815, the CREATE CATALOG doesn't exist, you must instead run $ORACLE_HOME/rdbms/admin/catrman.sql as rman.
The last command builds the recovery catalog tables in the rman schema. One this step is done, you're ready to start the clone.
2. Use rman to make a copy of the Source Database.
Set ORACLE_SID=SEED, shutdown SEED, bring it up in MOUNT mode. then execute the following:
$ rman
connect target 'internal/kernel'
connect rcvcat 'rman/rman@RCAT'
run {
allocate channel c1 type disk format '/u01/oradata/SEED/backup.dat';
backup (database); }
in the above example, substitute the string after the word "format" with the location you want the backup to be written. The TRNG database is actually created from the backup file, so after the backup is done, you can open SEED.
3. Set ORACLE_SID=TRNG, then startup the TRNG database in nomount mode. This should be VERY familiar to people that cloned the old fashioned way.
4. determine where you want the destination datafiles & logfiles to be placed based on file id number. Oracle stores each datafile in the database with a file_id, which tells the order the file was created. The first file of the SYSTEM tablespace, for example, is always file #1. You can get the file number from querying DBA_DATA_FILES
SQL> desc dba_data_files
Name Null? Type
------------------------------------- --------- -------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
SQL> select file_id, file_name from dba_data_files;
FILE_ID FILE_NAME
---------- ----------------------------------------
6 /u04/oradata/SEED/devl_SEED_01.dbf
5 /u04/oradata/SEED/users_SEED_01.dbf
4 /u04/oradata/SEED/tools_SEED_01.dbf
3 /u04/oradata/SEED/temp_SEED_01.dbf
2 /u04/oradata/SEED/rbs_SEED_01.dbf
1 /u04/oradata/SEED/system_SEED_01.dbf
now, let suppose our TRNG DB datafiles are to be created under /u05/oradata/TRNG. Then based on the above query...
datafile 1 of the target DB should have a name of /u05/oradata/TRNG/system_TRNG_01.dbf
datafile 2 should be named /u05/oradata/TRNG/rbs_TRNG_01.dbf,
and so forth.
MAKE SURE YOU GET THIS MAPPING RIGHT, OTHERWISE, your tablespace names won't match their filenames!
5. set ORACLE_SID=SEED, and run the following RMAN script after making the appropriate subsitutions for datafile & logfile mappings. Make sure to get your logfile size correct, otherwise this won't work.
connect target 'internal/kernel'
connect rcvcat 'rman/rman@RCAT'
connect auxiliary 'internal/kernel@TRNG'
set auxname for datafile 1 to
'/u05/oradata/TRNG/system_TRNG_01.dbf';
set auxname for datafile 3 to
'/u05/oradata/TRNG/temp_TRNG_01.dbf';
set auxname for datafile 2 to
'/u05/oradata/TRNG/rbs_TRNG_01.dbf';
set auxname for datafile 4 to
'/u05/oradata/TRNG/users_TRNG_01.dbf';
set auxname for datafile 5 to
'/u05/oradata/TRNG/devl_TRNG_01.dbf';
run {
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate target database to TRNG
logfile
group 1 ('/u05/oradata/TRNG/log_TRNG_01.rdo') size 5M,
group 2 ('/u05/oradata/TRNG/log_TRNG_02.rdo') size 5M,
group 3 ('/u05/oradata/TRNG/log_TRNG_03.rdo') size 5M;
}
Once this procedure is done, you will have a TRNG database with a different DBID from SEED!