Oracle Database Cloning Using User Managed Backup.

Assumption:

1) Hot backups of the production server are there. 

Production Database Name: orcl
Cloned Database Name: pub

--Create pfile from the production servers spfile.

SQL> create pfile='/u01/initpub.ora' from spfile;

-- Make appropriate folder structures.

[oracle@canada ~]$ mkdir -p /u01/app/oracle/admin/pub/adump
[oracle@canada ~]$ mkdir -p /u01/app/oracle/oradata/pub
[oracle@canada ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/pub
[oracle@canada ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/PUB/archivelog


-- Edit the initpub.ora file and change the location of all the files for
-- the "pub" database.

Also add the following parameters to the pfile:

DB_UNIQUE_NAME=pub

-- above parameter is necessary if database is created on the same machine.

DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)

-- above parameter is necessary if database is created on the same machine,
-- or if the directory structure is different.

LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)

-- above parameter is mandatory.

LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/app/oracle/flash_recovery_area/PUB/archivelog/'

Sample Pfile


pub.__db_cache_size=62914560
pub.__java_pool_size=4194304
pub.__large_pool_size=4194304
pub.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
pub.__pga_aggregate_target=113246208
pub.__sga_target=150994944
pub.__shared_io_pool_size=0
pub.__shared_pool_size=75497472
pub.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/pub/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/pub/control01.ctl',
'/u01/app/oracle/flash_recovery_area/pub/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=pubXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=pub
DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)
LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/orcl/,/u01/app/oracle/oradata/pub/)
LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/app/oracle/flash_recovery_area/PUB/archivelog/'

--Copy the backup datafiles, archived log files, and control files to folders created
--earlier.


[oracle@canada ~]$ export ORACLE_SID=pub
[oracle@canada ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 16 19:02:27 2012

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='/u01/initpub.ora';
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1335892 bytes
Variable Size             197135788 bytes
Database Buffers           62914560 bytes
Redo Buffers                2252800 bytes

SQL> alter database mount clone database;

Database altered.

SQL> set pagesize 100
SQL> set linesize 130
SQL> select name,file#,status from v$datafile;

NAME                                FILE# STATUS
------------------------------ ---------- -------
/u01/app/oracle/oradata/pub/sy          1 SYSOFF
stem01.dbf

/u01/app/oracle/oradata/pub/sy          2 OFFLINE
saux01.dbf

/u01/app/oracle/oradata/pub/un          3 OFFLINE
dotbs01.dbf

/u01/app/oracle/oradata/pub/us          4 OFFLINE
ers01.dbf
                                                                                                

SQL> alter database datafile 1 online;

Database altered.

SQL> alter database datafile 2 online;

Database altered.

SQL> alter database datafile 3 online;

Database altered.

SQL> alter database datafile 4 online;

Database altered.


SQL> select name,file#,status from v$datafile;

NAME                                                    FILE# STATUS
-------------------------------------------------- ---------- -------
/u01/app/oracle/oradata/pub/system01.dbf                    1 SYSTEM
/u01/app/oracle/oradata/pub/sysaux01.dbf                    2 ONLINE
/u01/app/oracle/oradata/pub/undotbs01.dbf                   3 ONLINE
/u01/app/oracle/oradata/pub/users01.dbf                     4 ONLINE


SQL>recover database until cancel using backup controlfile;

--manually apply all the suggested archived log files and finish
--the incomplete recovery.

SQL>alter database open resetlogs;