Oracle - Clone Database without RMAN

If you need to do a clone of a database, say from production to test, then you can do this by backing up the production control file to trace, copying the datafiles and pfile across to the new test environment, then running the script created by the control file backup.

NB. if you are copying to a database of the same name then once you have copied the pfile and control files you just need to startup the database after setting the $ORACLE_SID and $ORACLE_HOME:

SQL> startup
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 2072056 bytes
Variable Size 88080904 bytes
Database Buffers 167772160 bytes
Redo Buffers 6316032 bytes
Database mounted.
Database opened.
SQL>

Clone to new database

On primary database:

SQL> alter database backup controlfile to trace;
SQL> shutdown immediate;

On clone database:

• copy all datafiles across to the clone database directories (maybe necessary to create these directories, but is best to scp -Rp the entire directory instead

• copy the pfile across to the clone database directory ( $ORACLE_HOME/dbs)

• rename clone pfile to new db name and change directory names if necessary

• make create script from the backup controlfile such as below:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "TST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 (
'/tst/redologs1/redo01_1.log',
'/tst/redologs2/redo01_2.log'
) SIZE 25M,
GROUP 2 (
'/tst/redologs1/redo02_1.log',
'/tst/redologs2/redo02_2.2og'
) SIZE 25M,
GROUP 3 (
'/tst/redologs1/redo03_1.log',
'/tst/redologs2/redo03_2.log'
) SIZE 25M
DATAFILE
'/tst/system/system_01.dbf',
'/tst/undo/undotbs_01.dbf',
'/tst/system/sysaux_01.dbf',
'/tst/oradata/users_01.dbf',
'/tst/oradata/data_01.dbf',
'/tst/oradata/data_02.dbf',
'/tst/oradata/data_03.dbf',
'/tst/oradata/indexes_01.dbf',
'/tst/oradata/indexes_02.dbf',
'/tst/oradata/csmig_01.dbf',
'/tst/oradata/cm_rem_ts_01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER TABLESPACE TEMP ADD TEMPFILE '/tst/temp/temp_01.dbf'
SIZE 1048576000 REUSE AUTOEXTEND OFF;

• create udump, adump, bdump, cdump directories

• run create script

• check database is open
• change DBID if required using NID