Here is a nice clean way to clone an Oracle database.

STEP 1:

Generate a control file trace which will allow you to create a new database. On the current database server, go into SQL*Plus as sysdba: enter the command:

alter database backup controlfile to trace as ‘/filepath/filename.sql’;

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “OLDDB” RESETLOGS NOARCHIVELOG
MAXLOGFILES 8
MAXLOGMEMBERS 4
MAXDATAFILES 1021
MAXINSTANCES 1
MAXLOGHISTORY 584
LOGFILE
GROUP 1 ‘/oracle/db/olddb/redo/redo01.log’ SIZE 100M BLOCKSIZE 512,
GROUP 2 ‘/oracle/db/olddb/redo/redo02.log’ SIZE 100M BLOCKSIZE 512,
GROUP 3 ‘/oracle/db/olddb/redo/redo03.log’ SIZE 100M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/oracle/db/olddb/data/system01.dbf’,
‘/oracle/db/olddb/data/sysaux01.dbf’,
‘/oracle/db/olddb/data/psundots01.dbf’,
‘/oracle/db/olddb/data/psdefault.dbf’,
‘/oracle/db/olddb/data/aaapp.dbf’,
…..All the PeopleSoft Datafiles will be listed here…….
‘/oracle/db/olddb/data/aalarge.dbf’,
‘/oracle/db/olddb/data/psimage2.dbf’
CHARACTER SET WE8ISO8859P15
;

STEP 2:

Shutdown the OLDDB database

STEP 3:

Copy all of the associated files with the database into a new directory or to a new server. Make sure you end up with the same structure as before just with the NEWDB name. So in my case I did: cp -rf /oracle/db/olddb /oracle/db/newdb, make sure you create the bdump, udump and cdump directories

STEP 4:

Using the script from Step 1, change the controlfile creation script:

Original: CREATE CONTROLFILE REUSE DATABASE “OLDDB” NORESETLOGS
Modified: CREATE CONTROLFILE SET DATABASE “NEWDB” RESETLOGS

STEP 4a:

Identify the TEMPFILE’s reuse statements in the script and make sure to keep them as part of the script (Should be at the end of the trace file)

STEP 5:

Remove any other instructions from the trace file and save it.

STEP 6:

Change the olddb references in the datafile and controlfile sections to the NEWDB location.

Original: DATAFILE ‘/oracle/db/olddb/data/system01.dbf’,
Modified: DATAFILE ‘/oracle/db/newdb/data/system01.dbf’,

STEP 6a:

Change the paths for the TEMPFILE to the new database location:

ALTER tablespace {TableSpaceName} add TEMPFILE ‘/oracle/db/{newdb}/data/psgtt01.dbf’ reuse;

STEP 7:

Save as newdb_create.sql.

STEP 8:

Copy the old initOLDDB.ora file to initNEWDB.ora
Modify the initNEWDB.ora and make the changes from the OLDDB to the NEWDB.

STEP 9:

Add entries in the tnsnames.ora, listener.ora and oratab for the NEWDB.

STEP 10:

Change to the NEWDB and STARTUP NOMOUNT and run the @newdb_create.sql

STEP 11:

ALTER DATABASE OPEN RESETLOGS;

STEP 12:

For a PeopleSoft Environment, you will want to update the PS.PSDBOWNER table to reflect the NEWDB.