Duplicate the database using Target database RMAN backup with ASM Migration

Oracle11gR2 comes with new features where we can duplicate the database from the Target Database to the Auxiliary Database  using RMAN backup without connecting to the Target database and rman catalog. Only thing what is required is,  full backup of the Target database.

High Level steps :

1. Have recent RMAN L0 backup of target database.
2. FTP the RMAN backup files from target server to auxiliary server
3. Create all the directories in auxiliary server
4. Start the instance(with nomount) in auxiliary instance
5. Duplicate the database in auxiliary instance.
6. create spfile in ASM disk
7. Database validation and verification

Target  DB info :

I am restoring the above database to the new host called ractest1. The RBDMS is already installed in ractest1 host and it is same as target database version.

Step1  Take RMAN L0 backup & Archive log backup in target server. I am using the below script to backup the database in target server. We can also use the recent full backup from tape if it is already available in tape. I am using disk backup for my convenience.

usben_L0_backup.sh

Here is the log file for full backup.

RMAN> connected to target database: USBEN (DBID=2379284599) RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> using target database control file instead of recovery catalog allocated channel: T01 channel T01: SID=61 device type=DISK executing command: SET COMMAND ID Starting backup at 16-FEB-16 channel T01: starting full datafile backup set channel T01: specifying datafile(s) in backup set including current SPFILE in backup set channel T01: starting piece 1 at 16-FEB-16 channel T01: finished piece 1 at 16-FEB-16 piece handle=/backup/usben/USBEN_SPFILE_20160216_0927_133_45qu2cfv_1_1_903950847 tag=USBEN_L0_20160216_0927 comment=NONE channel T01: backup set complete, elapsed time: 00:00:01 Finished backup at 16-FEB-16 Starting backup at 16-FEB-16 channel T01: starting full datafile backup set channel T01: specifying datafile(s) in backup set including current control file in backup set channel T01: starting piece 1 at 16-FEB-16 channel T01: finished piece 1 at 16-FEB-16 piece handle=/backup/usben/USBEN_CTRL_20160216_0927_134_46qu2cg0_1_1_903950848 tag=USBEN_L0_20160216_0927 comment=NONE channel T01: backup set complete, elapsed time: 00:00:01 Finished backup at 16-FEB-16 Starting backup at 16-FEB-16 channel T01: starting compressed incremental level 0 datafile backup set channel T01: specifying datafile(s) in backup set input datafile file number=00001 name=/data01/oradata/usben/system01.dbf -- trimming the info here for brevity input datafile file number=00003 name=/data01/oradata/usben/sysaux01.dbf channel T01: starting piece 1 at 16-FEB-16 channel T01: finished piece 1 at 16-FEB-16 piece handle=/backup/usben/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851 tag=USBEN_L0_20160216_0927 comment=NONE channel T01: backup set complete, elapsed time: 00:02:15 Finished backup at 16-FEB-16 Starting Control File and SPFILE Autobackup at 16-FEB-16 piece handle=/ora/app/oracle/fast_recovery_area/USBEN/ autobackup/2016_02_16/o1_mf_s_903950986_cd6dltf7_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 16-FEB-16 released channel: T01 RMAN> Recovery Manager complete.

usben_Arch_backup.sh

Here is the log file for archive log backup.

RMAN> connected to target database: USBEN (DBID=2379284599) RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> using target database control file instead of recovery catalog allocated channel: T01 channel T01: SID=59 device type=DISK executing command: SET COMMAND ID sql statement: alter system archive log current Starting backup at 16-FEB-16 current log archived channel T01: starting archived log backup set channel T01: specifying archived log(s) in backup set input archived log thread=1 sequence=267 RECID=261 STAMP=903951084 -- trimming the info here for brevity input archived log thread=1 sequence=301 RECID=295 STAMP=903951315 channel T01: starting piece 1 at 16-FEB-16 channel T01: finished piece 1 at 16-FEB-16 piece handle=/backup/usben/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315 tag=TAG20160216T093515 comment=NONE channel T01: backup set complete, elapsed time: 00:01:05 channel T01: deleting archived log(s) archived log file name=/data02/oradata/arch/1_267_902782329.dbf RECID=261 STAMP=903951084 -- trimming the info here for brevity archived log file name=/data02/oradata/arch/1_301_902782329.dbf RECID=295 STAMP=903951315 Finished backup at 16-FEB-16 Starting Control File and SPFILE Autobackup at 16-FEB-16 piece handle=/ora/app/oracle/fast_recovery_area/USBEN/ autobackup/2016_02_16/o1_mf_s_903951381_cd6dz606_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 16-FEB-16 released channel: T01 RMAN> Recovery Manager complete.

Step2  FTP the RMAN backup files from target server to auxiliary server. 

The RMAN backup files are coped to auxiliary server as below. Also copy the password file from target server to auxiliary database server. Password file is placed under $ORACLE_HOME/dbs directory.

The below backup list has full backup, Archive log bakcup & Archive logs.

Step3  Create the necessary folders in auxiliary database.  Here i  am migrating the database from file system to ASM disk. This case, we don't need to create directory in the file system. I created the directory in ASM disk.

[oracle@RACTEST1 ~]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> mkdir USBEN

Add entry in /etc/oratab for usben database.

Edit the parameter file in auxiliary database according to the environment. Here is the modified pfile in auxiliary database.

initusben.ora

usben.__data_transfer_cache_size=0 usben.__db_cache_size=536870912 usben.__java_pool_size=16777216 usben.__large_pool_size=150994944 usben.__oracle_base='/ora/app/oracle' usben.__pga_aggregate_target=671088640 usben.__sga_target=989855744 usben.__shared_io_pool_size=50331648 usben.__shared_pool_size=218103808 usben.__streams_pool_size=0 *.compatible='12.1.0.2.0' *.control_files='+DATA','+DATA' *.db_block_size=8192 *.db_domain='localdomain' *.db_name='usben' *.db_create_file_dest='+DATA' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4560m *.diagnostic_dest='/ora/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=usbenXDB)' *.log_archive_dest_1='location=/backup/usben/arch' *.log_archive_dest_state_1='enable' *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'

Step4  Start the instance using the above parameter with nomount mode:

Step5  Duplicate the database

restore.sh

Start running the above script in background mode.

[oracle@RACTEST1 usben]$ nohup ./restore.sh &

[1] 14120

[oracle@RACTEST1 usben]$

Tailing the log file while restoring the database.

tail -f  restore.log

Here is the complete restore log file.

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Feb 17 13:23:26 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. RMAN> connected to auxiliary database: USBEN (not mounted) RMAN> 2> 3> 4> 5> 6> 7> 8> 9> allocated channel: a1 channel a1: SID=34 device type=DISK Starting Duplicate Db at 17-FEB-16 contents of Memory Script: {    sql clone "create spfile from memory"; } executing Memory Script sql statement: create spfile from memory contents of Memory Script: {    shutdown clone immediate;    startup clone nomount; } executing Memory Script Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area     281018368 bytes Fixed Size                     2923440 bytes Variable Size                222299216 bytes Database Buffers              50331648 bytes Redo Buffers                   5464064 bytes allocated channel: a1 channel a1: SID=33 device type=DISK contents of Memory Script: {    sql clone "alter system set  control_files =   ''+DATA/USBEN/CONTROLFILE/current.316.904051433'', ''+DATA/USBEN/CONTROLFILE/current.295.904051435'' comment=  ''Set by RMAN'' scope=spfile";    sql clone "alter system set  db_name =  ''USBEN'' comment=  ''Modified by RMAN duplicate'' scope=spfile";    sql clone "alter system set  db_unique_name =  ''USBEN'' comment=  ''Modified by RMAN duplicate'' scope=spfile";    shutdown clone immediate;    startup clone force nomount    restore clone primary controlfile from  '/backup/usben/rman/USBEN_CTRL_20160216_0927_134_46qu2cg0_1_1_903950848';    alter clone database mount; } executing Memory Script sql statement: alter system set  control_files =   ''+DATA/USBEN/CONTROLFILE/current.316.904051433'', ''+DATA/USBEN/CONTROLFILE/current.295.904051435'' comment= ''Set by RMAN'' scope=spfile sql statement: alter system set  db_name =  ''USBEN'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set  db_unique_name =  ''USBEN'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area     281018368 bytes Fixed Size                     2923440 bytes Variable Size                222299216 bytes Database Buffers              50331648 bytes Redo Buffers                   5464064 bytes allocated channel: a1 channel a1: SID=33 device type=DISK Starting restore at 17-FEB-16 channel a1: restoring control file channel a1: restore complete, elapsed time: 00:00:02 output file name=+DATA/USBEN/CONTROLFILE/current.316.904051433 output file name=+DATA/USBEN/CONTROLFILE/current.295.904051435 Finished restore at 17-FEB-16 database mounted contents of Memory Script: {    set until scn  2534513;    set newname for clone datafile  1 to new;    set newname for clone datafile  2 to new;    set newname for clone datafile  3 to new;    set newname for clone datafile  4 to new;    set newname for clone datafile  5 to new;    set newname for clone datafile  6 to new;    set newname for clone datafile  7 to new;    restore    clone database    ; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 17-FEB-16 channel a1: starting datafile backup set restore channel a1: specifying datafile(s) to restore from backup set channel a1: restoring datafile 00001 to +DATA channel a1: restoring datafile 00002 to +DATA -- trimming the info here for brevity channel a1: restoring datafile 00006 to +DATA channel a1: restoring datafile 00007 to +DATA channel a1: reading from backup piece /backup/usben/rman/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851 channel a1: piece handle=/backup/usben/rman/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851 tag=USBEN_L0_20160216_0927 channel a1: restored backup piece 1 channel a1: restore complete, elapsed time: 00:07:45 Finished restore at 17-FEB-16 contents of Memory Script: {    switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=8 STAMP=904051938 file name=+DATA/USBEN/DATAFILE/system.309.904051473 datafile 2 switched to datafile copy -- trimming the info here for brevity input datafile copy RECID=14 STAMP=904051938 file name=+DATA/USBEN/DATAFILE/usben_indx.310.904051473 contents of Memory Script: {    set until scn  2534513;    recover    clone database     delete archivelog    ; } executing Memory Script executing command: SET until clause Starting recover at 17-FEB-16 starting media recovery archived log for thread 1 with sequence 302 is already on disk as file /backup/usben/rman/1_302_902782329.dbf archived log for thread 1 with sequence 303 is already on disk as file /backup/usben/rman/1_303_902782329.dbf -- trimming the info here for brevity archived log for thread 1 with sequence 331 is already on disk as file /backup/usben/rman/1_331_902782329.dbf archived log for thread 1 with sequence 332 is already on disk as file /backup/usben/rman/1_332_902782329.dbf channel a1: starting archived log restore to default destination channel a1: restoring archived log archived log thread=1 sequence=267 channel a1: restoring archived log archived log thread=1 sequence=268 channel a1: restoring archived log archived log thread=1 sequence=269 -- trimming the info here for brevity archived log thread=1 sequence=300 channel a1: restoring archived log archived log thread=1 sequence=301 channel a1: reading from backup piece /backup/usben/rman/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315 channel a1: piece handle=/backup/usben/rman/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315 tag=TAG20160216T093515 channel a1: restored backup piece 1 channel a1: restore complete, elapsed time: 00:00:55 archived log file name=/backup/usben/arch/1_267_902782329.dbf thread=1 sequence=267 channel clone_default: deleting archived log(s) archived log file name=/backup/usben/arch/1_267_902782329.dbf RECID=66 STAMP=904051990 archived log file name=/backup/usben/arch/1_268_902782329.dbf thread=1 sequence=268 channel clone_default: deleting archived log(s) archived log file name=/backup/usben/arch/1_268_902782329.dbf RECID=44 STAMP=904051963 -- trimming the info here for brevity archived log file name=/backup/usben/arch/1_300_902782329.dbf thread=1 sequence=300 channel clone_default: deleting archived log(s) archived log file name=/backup/usben/arch/1_300_902782329.dbf RECID=65 STAMP=904051989 archived log file name=/backup/usben/arch/1_301_902782329.dbf thread=1 sequence=301 channel clone_default: deleting archived log(s) archived log file name=/backup/usben/arch/1_301_902782329.dbf RECID=64 STAMP=904051989 archived log file name=/backup/usben/rman/1_302_902782329.dbf thread=1 sequence=302 archived log file name=/backup/usben/rman/1_303_902782329.dbf thread=1 sequence=303 -- trimming the info here for brevity archived log file name=/backup/usben/rman/1_331_902782329.dbf thread=1 sequence=331 archived log file name=/backup/usben/rman/1_332_902782329.dbf thread=1 sequence=332 media recovery complete, elapsed time: 00:05:50 Finished recover at 17-FEB-16 Oracle instance started Total System Global Area     281018368 bytes Fixed Size                     2923440 bytes Variable Size                222299216 bytes Database Buffers              50331648 bytes Redo Buffers                   5464064 bytes contents of Memory Script: {    sql clone "alter system set  db_name =  ''USBEN'' comment=  ''Reset to original value by RMAN'' scope=spfile";    sql clone "alter system reset  db_unique_name scope=spfile"; } executing Memory Script sql statement: alter system set  db_name =  ''USBEN'' comment= ''Reset to original value by RMAN'' scope=spfile sql statement: alter system reset  db_unique_name scope=spfile Executing: create pfile='/ora/app/oracle/product/12.1.0.1/db_1/dbs/initusben.ora' from spfile Oracle instance started Total System Global Area     281018368 bytes Fixed Size                     2923440 bytes Variable Size                222299216 bytes Database Buffers              50331648 bytes Redo Buffers                   5464064 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "USBEN" RESETLOGS ARCHIVELOG   MAXLOGFILES     16   MAXLOGMEMBERS      3   MAXDATAFILES      100   MAXINSTANCES     8   MAXLOGHISTORY      292  LOGFILE   GROUP   1 ( '+DATA', '+DATA' ) SIZE 200 M ,   GROUP   2 ( '+DATA', '+DATA' ) SIZE 200 M  DATAFILE   '+DATA/USBEN/DATAFILE/system.309.904051473'  CHARACTER SET WE8MSWIN1252 contents of Memory Script: {    set newname for clone tempfile  2 to new;    switch clone tempfile all;    catalog clone datafilecopy  "+DATA/USBEN/DATAFILE/usben_data.299.904051473",  "+DATA/USBEN/DATAFILE/sysaux.314.904051473",  "+DATA/USBEN/DATAFILE/undotbs1.302.904051473",  "+DATA/USBEN/DATAFILE/example.306.904051473",  "+DATA/USBEN/DATAFILE/users.296.904051473",  "+DATA/USBEN/DATAFILE/usben_indx.310.904051473";    switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 2 to +DATA in control file cataloged datafile copy datafile copy file name=+DATA/USBEN/DATAFILE/usben_data.299.904051473 RECID=1 STAMP=904052369 cataloged datafile copy datafile copy file name=+DATA/USBEN/DATAFILE/sysaux.314.904051473 RECID=2 STAMP=904052369 cataloged datafile copy datafile copy file name=+DATA/USBEN/DATAFILE/undotbs1.302.904051473 RECID=3 STAMP=904052369 cataloged datafile copy datafile copy file name=+DATA/USBEN/DATAFILE/example.306.904051473 RECID=4 STAMP=904052369 cataloged datafile copy datafile copy file name=+DATA/USBEN/DATAFILE/users.296.904051473 RECID=5 STAMP=904052369 cataloged datafile copy datafile copy file name=+DATA/USBEN/DATAFILE/usben_indx.310.904051473 RECID=6 STAMP=904052369 datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/usben_data.299.904051473 datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/sysaux.314.904051473 datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/undotbs1.302.904051473 datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/example.306.904051473 datafile 6 switched to datafile copy input datafile copy RECID=5 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/users.296.904051473 datafile 7 switched to datafile copy input datafile copy RECID=6 STAMP=904052369 file name=+DATA/USBEN/DATAFILE/usben_indx.310.904051473 contents of Memory Script: {    Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 17-FEB-16 RMAN> Recovery Manager complete.

Step 6   Create the spfile in ASM disk.  We could do this step at the beginning too.  However, i do this step at the end for my convenience. 

sys@usben> show parameter spfile; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ spfile                               string -- Creating spfile in ASM disk sys@usben> create spfile='+DATA/USBEN/PARAMETERFILE/spfileusben.ora' from PFILE; File created. sys@usben> ! -- Here i updated the parameter file to point to SPFILE in ASM disk. [oracle@RACTEST1 dbs]$ cat initusben.ora SPFILE='+DATA/USBEN/PARAMETERFILE/spfileusben.ora' [oracle@RACTEST1 dbs]$ -- Restart the database with spfile. sys@usben> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. sys@usben> startup ORACLE instance started. Total System Global Area  281018368 bytes Fixed Size                  2923440 bytes Variable Size             222299216 bytes Database Buffers           50331648 bytes Redo Buffers                5464064 bytes Database mounted. Database opened. sys@usben> set echo on sys@usben> set feedback on sys@usben> show parameter spfile NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ spfile                               string      +DATA/USBEN/PARAMETERFILE/spfileusben.ora sys@usben>

Step7  Verify all the files and make sure all files are moved to ASM disk. Also watch out the alert log for a while. Create the listener and make sure able to connect outside the database server.

Now the database is successfully restored in different host. The database is also migrated to ASM disk as part of restore.

Enjoy reading my blog!!

Cloning Database through RMAN

A wonderful feature in RMAN to clone or duplicate the database on the same server or remote server. This feature is useful when someone truncated or dropped the table and they wanted to recover the table. In that case, we can clone the database in different location and recover the database until point of time. This feature is very useful to recover the selected object from the RMAN backup rather than roll back a whole database. It is possible to create a duplicate database on a remote server with the same file structure or remote server with different file structure or the local server with a different file structure.

This article is tested in windows environment. Now I am going to clone the ORCL database and restore in different location on the same server. The new database name is called CLONEDB. Please remember, the directory, file names, Database names may varry between environment to environment. So you might need to change the scripts based on your database name, file structure when you follow the below steps.

Prerequisite: You should have target database and RMAN should be implemented to backup the target database. How do we implement RMAN backup? Please see this link.

Here is the Target DB and catalog DB info in my server.Target DB = ORCL
CatalogDB = CATDB

Data file, redo log, control file directory location for target and catalog DB:
C:\oracle\product\10.2.0\oradata\orcl
C:\oracle\product\10.2.0\oradata\catdb

Tnsentry file location
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

Password file location
C:\oracle\product\10.2.0\db_1\database

SPFILE location:
C:\oracle\product\10.2.0\db_1\dbs

Here are the nine easy steps to clone the database. I have followed this steps in Oracle10gR2 in windows environment. So please test the below steps in your dev/test environment before proceed to production. The steps are pretty much same for unix environment except directory names and step5

Step1. First create the directory structures for the CLONEDB.

Create the folder clonedb in the following locations.
c:\oracle\product\10.2.0\oradata
C:\oracle\product\10.2.0\admin

Create folders(adump,bdump,cdump,dpdump,udump) in the following location.
C:\oracle\product\10.2.0\admin\clonedb

Step2. Create the password file for clonedb database. Orapwd is oracle utility and we can run this utility in command prompt for windows server. For unix environment, we need to run this in $ prompt.

orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDclonedb.ora password=password entries=10

Step3. Add the appropriate entries into the tnsnames.ora and listener.ora files.

Add this entry in listener.ora file.

(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = CLONEDB))

After adding the above entry, reload the listener(lsnrctl reload)

Add the below entry in tnsnames.ora file..

CLONEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = EW_Training)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clonedb)
)
)

Step 4. Build init.ora file for the duplicate database. Since we are duplicating the database on the same server as the original, we must convert the file names, so there is no conflict. These entries needs to be modified in pfile for cloning database.

db_name='clonedb'
dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
audit_file_dest='C:\oracle\product\10.2.0\admin\clonedb\adump'
background_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\bdump'
control_files='C:\oracle\product\10.2.0\oradata\clonedb\control01.ctl',
'C:\oracle\product\10.2.0\oradata\clonedb\control02.ctl',
'C:\oracle\product\10.2.0\oradata\clonedb\control03.ctl'
core_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\cdump'
user_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\udump'
db_file_name_convert=('C:\oracle\product\10.2.0\oradata\orcl','C:\oracle\product\10.2.0\oradata\clonedb')
log_file_name_convert=('C:\oracle\product\10.2.0\oradata\orcl','C:\oracle\product\10.2.0\oradata\clonedb')

The modified PFILE is located in C:\oracle\product\10.2.0\db_1\dbs\PFILE_CLONE.ORA

Step 5. Create the service for CLONEDB instance. Use ORDIM utility for windows environment. This command should run on OS Level.

ORADIM -NEW -SID clonedb

Creating service is only applicable for windows environment. We do not need to create any service in unix environment. In unix, starting the database spawns a services. We must ignore this step for unix environment.

Step 6. Create the spfile by using pfile which is build in step 4. Login as sys user in ORCL database and run this script.

create spfile = 'C:\oracle\product\10.2.0\db_1\dbs\SPFILECLONEDB.ORA'
from pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE_CLONE.ORA'

Step 7. Create file name called initclonedb.ORA in C:\oracle\product\10.2.0\db_1\database location. The file should have the below content.
SPFILE='C:\oracle\product\10.2.0\db_1/dbs/spfileclonedb.ora'
Step 8. Login as sys user in clonedb database. Start the clonedb instance without nomount.

C:\>set oracle_sid=clonedb

C:\>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 28 15:22:37 2009

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

Connected to an idle instance.

SQL> startup force nomount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
SQL>

Step 9. Connect as RMAN and clone the database. If you use any reserve word(like CLONE, TEST etc) for cloning database name, then you should use in double quotes. This case, i am using database name as clonedb. But in case, if we use name CLONE, then it should be quoted like this... (RMAN> duplicate target database to "CLONE";)

C:\>rman target=sys/password@orcl catalog=rman/rman@catdb AUXILIARY /

RMAN> duplicate target database to clonedb;

Now the ORCL database is successfully cloned and cloned database name is CLONEDB. In case if we want to clone the database to specific point of time, then we can use the below command to clone.

DUPLICATE TARGET DATABASE TO CLONEDB UNTIL TIME 'SYSDATE-4';

Questions and Answers....

Question1
I wanted to change the file names while cloning the database. How do we do that? Let us say, the file names in the target database are...

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF

While cloning the database, i wanted to change the file name as below. How can i achieve this?

C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSAUXCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\UNDOTBSCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSTEMCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE02.DBF

The directory structures are taken care by db_file_name_convert and log_file_name_covert parameters. The file name changes are not taken care by this parameters. Here is the below sample script to take care of this....

RMAN>run
{
SET NEWNAME FOR DATAFILE 1 TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSTEMCLONE01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE01.DBF';
duplicate target database to clonedb;
}

Question2 One of my programmer truncated the table accidently. I wanted to clone that database and recover that table. But the database size is 2TB. I do not want to clone the entire 2 TB data. Since i wanted to clone only the tablespace which blongs to my table. How do we exclude unwanted tablespaces? Please remember, we can not skip SYSTEM, SYSAUX, UNDO, TEMP and default permanent tablespace. The below script ingore the specific tablespaces.

Run
{
DUPLICATE TARGET DATABASE TO clonedb
SKIP TABLESPACE USERS, INVENTORY;
}

Question3 How do i skip the read only tablespace while cloning the database? The below script skip the read only tablespaces.

Run
{
DUPLICATE TARGET DATABASE TO clonedb
SKIP READONLY ;
}

Question4 Let us say, my target database is running in Host1. My catalog database is running in Host2. I wanted to clone the target database in different server which is called Host3. Where should i run the RMAN?

RMAN is client program that connects to the database. So it does not matter where you run the RMAN. We could actually run in Host4 if we want to... We should be good as long as we connect right database. Where the RMAN client runs is irrelevant.

Note : As per oracle10gR2, during the cloning, if any tablespaces are OFFLINE in target database, will not be cloned into new database.

Temp tablespace never be backed up through RMAN. Hence, it will not be cloned. At the end, cloning process add temp files for temporary tablespace automatically. This is pretty much same for restoring the database from RMAN backup. RMAN creates the temporary tablespace when we restore the database.

I have done more Restore scenarios in Oracle12c. Please refer this post if you want to..

1.  Duplicate the database using target database backup. Click  here  for details.
2.  Duplicate the database using Active database duplication. Click   here  for details.
3.  Regular RMAN restore approach. Click   here  for details.
4.  Setup the Standby Database. Click  here  for details.