Clone a database on the same server

How to clone a db on the same server:

1. First to determine the existing db's datafiles, controlfiles location. And create these folders.
SQL> select DESTINATION from V$ARCHIVE_DEST;

SQL> select * from V$CONTROLFILE;

SQL> select MEMBER from V$LOGFILE;

SQL> select name from V$TEMPFILE;

[@more@]2. create these dump destination.
mkdir adump
mkdir bdump
mkdir cdump
mkdir create
mkdir dpdump
mkdir pfle
mkdir udump

3. create password file.
orapwd file=orapwmydb1 password=oracle entries=20;

4. copy init.ora from existing db, and modify it, db_name, controlfile location etc in init.ora
add below in init.ora

db_file_name_convert=('olddb1','mydb1')
log_file_name_convert=('olddb1','mydb1')

Prefer to use above convert.

#DB_FILE_NAME_CONVERT=(/db01/oradata/olddb1/,/db01/oradata/mydb1/)
#LOG_FILE_NAME_CONVERT=(/db01/oradata/olddb1/,/db01/oradata/mydb1/)

5. May have to add new db in listener.ora

listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /db00/app/oracle/product/10.2.0)
(PROGRAM = extproc)
)
)

Add the new db to listener.ora, changed as below --&gt

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /db00/app/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = mydb1)
(ORACLE_HOME = /db00/app/oracle/product/10.2.0)
(SID_NAME = mydb1)
)

)


6. set env. and start nomount the new db.

export ORACLE_SID=mydb1
echo $ORACLE_SID

sqlplus /nolog
conn / as sysdba

startup nomount pfile=$ORACLE_HOME/dbs/initmydb1.ora

7. If we don't know the source database passwd for sys, we export ORACLE_SID=olddb1, so we don't need pwd in rman for source db.

export ORACLE_SID=olddb1

8. Create a full backup for source db .

rman target / nocatalog

run {
backup full database format '/db03/oraback/mydb1/hot/full_%U.bak';
backup archivelog all format '/db03/oraback/mydb1/hot/arch_%U.bak' ;
}

9. Start clone the database: first rman target / auxiliary sys/oracle@mydb1 then user this statement: duplicate target database to mydb1;

rman target / auxiliary sys/oracle@mydb1

Recovery Manager: Release 10.2.0.3.0 - Production on Fri Mar 22 20:59:18 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: olddb1 (DBID=1169852946)
connected to auxiliary database: mydb1 (not mounted)

duplicate target database to mydb1;

.....

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 22-MAR-13


10. This is the clone step on same server, what about clone db on a different server. after source db full backup, transfer the backup to target server. and Needs to configure tnsname so this works: rman target / auxiliary sys/oracle@mydb1