In this method following tablespaces can't be skipped.
======================================================
select tablespcae_name,count(*) from dba_extents where owner in ('SYS','SYSTEM') group by tablespace_name ;
Tablespace pointin time recovey :
===================================
08:58:01 SQL> select count(*) from bill;
COUNT(*)
----------
9
==========
Parameter file :
================
*.audit_file_dest='/usr/app/oracle/product/10.2.0/admin/dummy/adump'
*.background_dump_dest='/usr/app/oracle/product/10.2.0/admin/dummy/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA/dummy/controlfile/dummy.ctl'
*.core_dump_dest='/usr/app/oracle/product/10.2.0/admin/dummy/cdump'
*.db_block_size=8192
*.db_name='dummy'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sourceXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/usr/app/oracle/product/10.2.0/admin/dummy/udump'
db_file_name_convert='+DATA/source','+DATA/dummy'
log_file_name_convert='+DATA/source,'+DATA/dummy'
Source database datafiles list :
====================================
FILE_NAME FILE_ID
--------------------------------------------- ----------
+DATA/source/datafile/users.269.714904761 4
+DATA/source/datafile/sysaux.267.714904759 3
+DATA/source/datafile/undotbs1.268.714904761 2
+DATA/source/datafile/system.266.714904757 1
+DATA/source/datafile/ranji.276.714905559 5
+DATA/source/datafile/tools.277.714905603 6
Archive log files generation history on source databse :
=======================================================
SQL> select name,stamp,completion_time,sequence# from v$archived_log;
NAME STAMP COMPLETION_TIME SEQUENCE#
--------------------------------------------- ---------- -------------------- ----------
+DATA/source/1_1_714904885.dbf 714906459 MAR 29 2010 09:07:39 1
+DATA/source/1_2_714904885.dbf 714906674 MAR 29 2010 09:11:14 2
+DATA/source/1_3_714904885.dbf 714911401 MAR 29 2010 10:30:01 3
+DATA/source/1_4_714904885.dbf 714911868 MAR 29 2010 10:37:48 4
+DATA/source/1_5_714904885.dbf 714912166 MAR 29 2010 10:42:46 5
+DATA/source/1_6_714904885.dbf 714913375 MAR 29 2010 11:02:55 6
+DATA/source/1_7_714904885.dbf 714913652 MAR 29 2010 11:07:32 7
7 rows selected.
RMAN Script:
============
run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
set until time "to_date('Mar 29 2010 09:11:00','MON DD YYYY HH24:MI:SS')";
set newname for datafile 1 to '+DATA/dummy/datafile/system.dbf';
set newname for datafile 2 to '+DATA/dummy/datafile/undotbs1.dbf';
set newname for datafile 3 to '+DATA/dummy/datafile/sysaux.dbf';
set newname for datafile 5 to '+DATA/dummy/datafile/ranji.dbf';
duplicate target database to dummy skip tablespace TOOLS
logfile
group 1('+DATA/dummy/onlinelog/group_1.log') size 10m reuse,
group 2('+DATA/dummy/onlinelog/group_2.log') size 10m reuse,
group 3('+DATA/dummy/onlinelog/group_3.log') size 10m reuse;
}
Output :
=========
RMAN> run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
set until time "to_date('Mar 29 2010 09:11:00','MON DD YYYY HH24:MI:SS')";
set newname for datafile 1 to '+DATA/dummy/datafile/system.dbf';
set newname for datafile 2 to '+DATA/dummy/datafile/undotbs1.dbf';
set newname for datafile 3 to '+DATA/dummy/datafile/sysaux.dbf';
set newname for datafile 5 to '+DATA/dummy/datafile/ranji.dbf';
duplicate target database to dummy skip tablespace TOOLS
logfile
group 1('+DATA/dummy/onlinelog/group_1.log') size 10m reuse,
group 2('+DATA/dummy/onlinelog/group_2.log') size 10m reuse,
group 3('+DATA/dummy/onlinelog/group_3.log') size 10m reuse;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>
allocated channel: c1
channel c1: sid=156 devtype=DISK
allocated channel: c2
channel c2: sid=155 devtype=DISK
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 29-MAR-10
Datafile 6 skipped by request
contents of Memory Script:
{
set until scn 454075;
set newname for datafile 1 to
"+DATA/dummy/datafile/system.dbf";
set newname for datafile 2 to
"+DATA/dummy/datafile/undotbs1.dbf";
set newname for datafile 3 to
"+DATA/dummy/datafile/sysaux.dbf";
set newname for datafile 4 to
"+DATA/dummy/datafile/users.269.714904761";
set newname for datafile 5 to
"+DATA/dummy/datafile/ranji.dbf";
restore
check readonly
clone database
skip tablespace TOOLS ;
}
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
Starting restore at 29-MAR-10
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/dummy/datafile/system.dbf
restoring datafile 00002 to +DATA/dummy/datafile/undotbs1.dbf
restoring datafile 00003 to +DATA/dummy/datafile/sysaux.dbf
restoring datafile 00004 to +DATA/dummy/datafile/users.269.714904761
restoring datafile 00005 to +DATA/dummy/datafile/ranji.dbf
channel c1: reading from backup piece /crs/backups/02l9p6rs.bkp
channel c1: restored backup piece 1
piece handle=/crs/backups/02l9p6rs.bkp tag=TAG20100329T090811
channel c1: restore complete, elapsed time: 00:01:59
Finished restore at 29-MAR-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUMMY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA/dummy/onlinelog/group_1.log' ) SIZE 10 M REUSE,
GROUP 2 ( '+DATA/dummy/onlinelog/group_2.log' ) SIZE 10 M REUSE,
GROUP 3 ( '+DATA/dummy/onlinelog/group_3.log' ) SIZE 10 M REUSE
DATAFILE
'+DATA/dummy/datafile/system.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=714917673 filename=+DATA/dummy/datafile/undotbs1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=714917673 filename=+DATA/dummy/datafile/sysaux.dbf
datafile 5 switched to datafile copy
input datafile copy recid=3 stamp=714917673 filename=+DATA/dummy/datafile/ranji.dbf
datafile 4 switched to datafile copy
input datafile copy recid=4 stamp=714917673 filename=+DATA/dummy/datafile/users.285.714917561
contents of Memory Script:
{
set until time "to_date('Mar 29 2010 09:11:00','MON DD YYYY HH24:MI:SS')";
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 29-MAR-10
datafile 6 not processed because file is offline
starting media recovery
archive log thread 1 sequence 2 is already on disk as file +DATA/source/1_2_714904885.dbf
archive log filename=+DATA/source/1_2_714904885.dbf thread=1 sequence=2
media recovery complete, elapsed time: 00:00:01
Finished recover at 29-MAR-10
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUMMY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA/dummy/onlinelog/group_1.log' ) SIZE 10 M REUSE,
GROUP 2 ( '+DATA/dummy/onlinelog/group_2.log' ) SIZE 10 M REUSE,
GROUP 3 ( '+DATA/dummy/onlinelog/group_3.log' ) SIZE 10 M REUSE
DATAFILE
'+DATA/dummy/datafile/system.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA/dummy/tempfile/temp.274.714904919";
switch clone tempfile all;
catalog clone datafilecopy "+DATA/dummy/datafile/undotbs1.dbf";
catalog clone datafilecopy "+DATA/dummy/datafile/sysaux.dbf";
catalog clone datafilecopy "+DATA/dummy/datafile/users.285.714917561";
catalog clone datafilecopy "+DATA/dummy/datafile/ranji.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to +DATA/dummy/tempfile/temp.274.714904919 in control file
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/undotbs1.dbf recid=1 stamp=714917764
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/sysaux.dbf recid=2 stamp=714917765
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/users.285.714917561 recid=3 stamp=714917766
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/ranji.dbf recid=4 stamp=714917766
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=714917764 filename=+DATA/dummy/datafile/undotbs1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=714917765 filename=+DATA/dummy/datafile/sysaux.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=714917766 filename=+DATA/dummy/datafile/ranji.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=714917766 filename=+DATA/dummy/datafile/users.285.714917561
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
contents of Memory Script:
{
# drop offline and skipped tablespaces
sql clone "drop tablespace TOOLS including contents cascade constraints";
}
executing Memory Script
sql statement: drop tablespace TOOLS including contents cascade constraints
Finished Duplicate Db at 29-MAR-10
RMAN> exit
Recovery Manager complete.
single tablespace recovery :
=============================
run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
set newname for datafile 1 to '+DATA/dummy/datafile/system.dbf';
set newname for datafile 2 to '+DATA/dummy/datafile/undotbs1.dbf';
set newname for datafile 3 to '+DATA/dummy/datafile/sysaux.dbf';
set newname for datafile 5 to '+DATA/dummy/datafile/ranji.dbf';
set newname for datafile 4 to '+DATA/dummy/datafile/user.dbf';
duplicate target database to dummy skip tablespace TOOLS
logfile
group 1('+DATA/dummy/onlinelog/group_1.log') size 10m reuse,
group 2('+DATA/dummy/onlinelog/group_2.log') size 10m reuse,
group 3('+DATA/dummy/onlinelog/group_3.log') size 10m reuse;
}
Output :
==========
[oracle@localhost dbs]$ rman target sys/source@source auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Mar 29 11:34:01 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: SOURCE (DBID=2833265521)
connected to auxiliary database: DUMMY (not mounted)
RMAN> run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
set newname for datafile 1 to '+DATA/dummy/datafile/system.dbf';
set newname for datafile 2 to '+DATA/dummy/datafile/undotbs1.dbf';
set newname for datafile 3 to '+DATA/dummy/datafile/sysaux.dbf';
set newname for datafile 5 to '+DATA/dummy/datafile/ranji.dbf';
set newname for datafile 4 to '+DATA/dummy/datafile/user.dbf';
duplicate target database to dummy skip tablespace TOOLS
logfile
group 1('+DATA/dummy/onlinelog/group_1.log') size 10m reuse,
group 2('+DATA/dummy/onlinelog/group_2.log') size 10m reuse,
group 3('+DATA/dummy/onlinelog/group_3.log') size 10m reuse;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=156 devtype=DISK
allocated channel: c2
channel c2: sid=155 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 29-MAR-10
Datafile 6 skipped by request
contents of Memory Script:
{
set until scn 463518;
set newname for datafile 1 to
"+DATA/dummy/datafile/system.dbf";
set newname for datafile 2 to
"+DATA/dummy/datafile/undotbs1.dbf";
set newname for datafile 3 to
"+DATA/dummy/datafile/sysaux.dbf";
set newname for datafile 4 to
"+DATA/dummy/datafile/user.dbf";
set newname for datafile 5 to
"+DATA/dummy/datafile/ranji.dbf";
restore
check readonly
clone database
skip tablespace TOOLS ;
}
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
Starting restore at 29-MAR-10
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA/dummy/datafile/undotbs1.dbf
restoring datafile 00003 to +DATA/dummy/datafile/sysaux.dbf
restoring datafile 00005 to +DATA/dummy/datafile/ranji.dbf
channel c1: reading from backup piece /crs/backups/0fl9pdjq.bkp
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/dummy/datafile/system.dbf
restoring datafile 00004 to +DATA/dummy/datafile/user.dbf
channel c2: reading from backup piece /crs/backups/0el9pdjq.bkp
channel c1: restored backup piece 1
piece handle=/crs/backups/0fl9pdjq.bkp tag=TAG20100329T110321
channel c1: restore complete, elapsed time: 00:02:05
channel c2: restored backup piece 1
piece handle=/crs/backups/0el9pdjq.bkp tag=TAG20100329T110321
channel c2: restore complete, elapsed time: 00:02:30
Finished restore at 29-MAR-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUMMY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA/dummy/onlinelog/group_1.log' ) SIZE 10 M REUSE,
GROUP 2 ( '+DATA/dummy/onlinelog/group_2.log' ) SIZE 10 M REUSE,
GROUP 3 ( '+DATA/dummy/onlinelog/group_3.log' ) SIZE 10 M REUSE
DATAFILE
'+DATA/dummy/datafile/system.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=714915496 filename=+DATA/dummy/datafile/undotbs1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=714915497 filename=+DATA/dummy/datafile/sysaux.dbf
datafile 5 switched to datafile copy
input datafile copy recid=3 stamp=714915497 filename=+DATA/dummy/datafile/ranji.dbf
datafile 4 switched to datafile copy
input datafile copy recid=4 stamp=714915497 filename=+DATA/dummy/datafile/user.dbf
contents of Memory Script:
{
set until scn 463518;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 29-MAR-10
datafile 6 not processed because file is offline
starting media recovery
archive log thread 1 sequence 7 is already on disk as file +DATA/source/1_7_714904885.dbf
archive log filename=+DATA/source/1_7_714904885.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:10
Finished recover at 29-MAR-10
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUMMY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA/dummy/onlinelog/group_1.log' ) SIZE 10 M REUSE,
GROUP 2 ( '+DATA/dummy/onlinelog/group_2.log' ) SIZE 10 M REUSE,
GROUP 3 ( '+DATA/dummy/onlinelog/group_3.log' ) SIZE 10 M REUSE
DATAFILE
'+DATA/dummy/datafile/system.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA/dummy/tempfile/temp.274.714904919";
switch clone tempfile all;
catalog clone datafilecopy "+DATA/dummy/datafile/undotbs1.dbf";
catalog clone datafilecopy "+DATA/dummy/datafile/sysaux.dbf";
catalog clone datafilecopy "+DATA/dummy/datafile/user.dbf";
catalog clone datafilecopy "+DATA/dummy/datafile/ranji.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to +DATA/dummy/tempfile/temp.274.714904919 in control file
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/undotbs1.dbf recid=1 stamp=714915644
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/sysaux.dbf recid=2 stamp=714915647
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/user.dbf recid=3 stamp=714915648
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/ranji.dbf recid=4 stamp=714915649
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=714915644 filename=+DATA/dummy/datafile/undotbs1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=714915647 filename=+DATA/dummy/datafile/sysaux.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=714915649 filename=+DATA/dummy/datafile/ranji.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=714915648 filename=+DATA/dummy/datafile/user.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
contents of Memory Script:
{
# drop offline and skipped tablespaces
sql clone "drop tablespace TOOLS including contents cascade constraints";
}
executing Memory Script
sql statement: drop tablespace TOOLS including contents cascade constraints
Finished Duplicate Db at 29-MAR-10
RMAN>
RMAN>
RMAN>
======================================================
select tablespcae_name,count(*) from dba_extents where owner in ('SYS','SYSTEM') group by tablespace_name ;
Tablespace pointin time recovey :
===================================
08:58:01 SQL> select count(*) from bill;
COUNT(*)
----------
9
==========
Parameter file :
================
*.audit_file_dest='/usr/app/oracle/product/10.2.0/admin/dummy/adump'
*.background_dump_dest='/usr/app/oracle/product/10.2.0/admin/dummy/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA/dummy/controlfile/dummy.ctl'
*.core_dump_dest='/usr/app/oracle/product/10.2.0/admin/dummy/cdump'
*.db_block_size=8192
*.db_name='dummy'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sourceXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/usr/app/oracle/product/10.2.0/admin/dummy/udump'
db_file_name_convert='+DATA/source','+DATA/dummy'
log_file_name_convert='+DATA/source,'+DATA/dummy'
Source database datafiles list :
====================================
FILE_NAME FILE_ID
--------------------------------------------- ----------
+DATA/source/datafile/users.269.714904761 4
+DATA/source/datafile/sysaux.267.714904759 3
+DATA/source/datafile/undotbs1.268.714904761 2
+DATA/source/datafile/system.266.714904757 1
+DATA/source/datafile/ranji.276.714905559 5
+DATA/source/datafile/tools.277.714905603 6
Archive log files generation history on source databse :
=======================================================
SQL> select name,stamp,completion_time,sequence# from v$archived_log;
NAME STAMP COMPLETION_TIME SEQUENCE#
--------------------------------------------- ---------- -------------------- ----------
+DATA/source/1_1_714904885.dbf 714906459 MAR 29 2010 09:07:39 1
+DATA/source/1_2_714904885.dbf 714906674 MAR 29 2010 09:11:14 2
+DATA/source/1_3_714904885.dbf 714911401 MAR 29 2010 10:30:01 3
+DATA/source/1_4_714904885.dbf 714911868 MAR 29 2010 10:37:48 4
+DATA/source/1_5_714904885.dbf 714912166 MAR 29 2010 10:42:46 5
+DATA/source/1_6_714904885.dbf 714913375 MAR 29 2010 11:02:55 6
+DATA/source/1_7_714904885.dbf 714913652 MAR 29 2010 11:07:32 7
7 rows selected.
RMAN Script:
============
run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
set until time "to_date('Mar 29 2010 09:11:00','MON DD YYYY HH24:MI:SS')";
set newname for datafile 1 to '+DATA/dummy/datafile/system.dbf';
set newname for datafile 2 to '+DATA/dummy/datafile/undotbs1.dbf';
set newname for datafile 3 to '+DATA/dummy/datafile/sysaux.dbf';
set newname for datafile 5 to '+DATA/dummy/datafile/ranji.dbf';
duplicate target database to dummy skip tablespace TOOLS
logfile
group 1('+DATA/dummy/onlinelog/group_1.log') size 10m reuse,
group 2('+DATA/dummy/onlinelog/group_2.log') size 10m reuse,
group 3('+DATA/dummy/onlinelog/group_3.log') size 10m reuse;
}
Output :
=========
RMAN> run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
set until time "to_date('Mar 29 2010 09:11:00','MON DD YYYY HH24:MI:SS')";
set newname for datafile 1 to '+DATA/dummy/datafile/system.dbf';
set newname for datafile 2 to '+DATA/dummy/datafile/undotbs1.dbf';
set newname for datafile 3 to '+DATA/dummy/datafile/sysaux.dbf';
set newname for datafile 5 to '+DATA/dummy/datafile/ranji.dbf';
duplicate target database to dummy skip tablespace TOOLS
logfile
group 1('+DATA/dummy/onlinelog/group_1.log') size 10m reuse,
group 2('+DATA/dummy/onlinelog/group_2.log') size 10m reuse,
group 3('+DATA/dummy/onlinelog/group_3.log') size 10m reuse;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>
allocated channel: c1
channel c1: sid=156 devtype=DISK
allocated channel: c2
channel c2: sid=155 devtype=DISK
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 29-MAR-10
Datafile 6 skipped by request
contents of Memory Script:
{
set until scn 454075;
set newname for datafile 1 to
"+DATA/dummy/datafile/system.dbf";
set newname for datafile 2 to
"+DATA/dummy/datafile/undotbs1.dbf";
set newname for datafile 3 to
"+DATA/dummy/datafile/sysaux.dbf";
set newname for datafile 4 to
"+DATA/dummy/datafile/users.269.714904761";
set newname for datafile 5 to
"+DATA/dummy/datafile/ranji.dbf";
restore
check readonly
clone database
skip tablespace TOOLS ;
}
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
Starting restore at 29-MAR-10
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/dummy/datafile/system.dbf
restoring datafile 00002 to +DATA/dummy/datafile/undotbs1.dbf
restoring datafile 00003 to +DATA/dummy/datafile/sysaux.dbf
restoring datafile 00004 to +DATA/dummy/datafile/users.269.714904761
restoring datafile 00005 to +DATA/dummy/datafile/ranji.dbf
channel c1: reading from backup piece /crs/backups/02l9p6rs.bkp
channel c1: restored backup piece 1
piece handle=/crs/backups/02l9p6rs.bkp tag=TAG20100329T090811
channel c1: restore complete, elapsed time: 00:01:59
Finished restore at 29-MAR-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUMMY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA/dummy/onlinelog/group_1.log' ) SIZE 10 M REUSE,
GROUP 2 ( '+DATA/dummy/onlinelog/group_2.log' ) SIZE 10 M REUSE,
GROUP 3 ( '+DATA/dummy/onlinelog/group_3.log' ) SIZE 10 M REUSE
DATAFILE
'+DATA/dummy/datafile/system.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=714917673 filename=+DATA/dummy/datafile/undotbs1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=714917673 filename=+DATA/dummy/datafile/sysaux.dbf
datafile 5 switched to datafile copy
input datafile copy recid=3 stamp=714917673 filename=+DATA/dummy/datafile/ranji.dbf
datafile 4 switched to datafile copy
input datafile copy recid=4 stamp=714917673 filename=+DATA/dummy/datafile/users.285.714917561
contents of Memory Script:
{
set until time "to_date('Mar 29 2010 09:11:00','MON DD YYYY HH24:MI:SS')";
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 29-MAR-10
datafile 6 not processed because file is offline
starting media recovery
archive log thread 1 sequence 2 is already on disk as file +DATA/source/1_2_714904885.dbf
archive log filename=+DATA/source/1_2_714904885.dbf thread=1 sequence=2
media recovery complete, elapsed time: 00:00:01
Finished recover at 29-MAR-10
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUMMY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA/dummy/onlinelog/group_1.log' ) SIZE 10 M REUSE,
GROUP 2 ( '+DATA/dummy/onlinelog/group_2.log' ) SIZE 10 M REUSE,
GROUP 3 ( '+DATA/dummy/onlinelog/group_3.log' ) SIZE 10 M REUSE
DATAFILE
'+DATA/dummy/datafile/system.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA/dummy/tempfile/temp.274.714904919";
switch clone tempfile all;
catalog clone datafilecopy "+DATA/dummy/datafile/undotbs1.dbf";
catalog clone datafilecopy "+DATA/dummy/datafile/sysaux.dbf";
catalog clone datafilecopy "+DATA/dummy/datafile/users.285.714917561";
catalog clone datafilecopy "+DATA/dummy/datafile/ranji.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to +DATA/dummy/tempfile/temp.274.714904919 in control file
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/undotbs1.dbf recid=1 stamp=714917764
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/sysaux.dbf recid=2 stamp=714917765
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/users.285.714917561 recid=3 stamp=714917766
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/ranji.dbf recid=4 stamp=714917766
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=714917764 filename=+DATA/dummy/datafile/undotbs1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=714917765 filename=+DATA/dummy/datafile/sysaux.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=714917766 filename=+DATA/dummy/datafile/ranji.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=714917766 filename=+DATA/dummy/datafile/users.285.714917561
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
contents of Memory Script:
{
# drop offline and skipped tablespaces
sql clone "drop tablespace TOOLS including contents cascade constraints";
}
executing Memory Script
sql statement: drop tablespace TOOLS including contents cascade constraints
Finished Duplicate Db at 29-MAR-10
RMAN> exit
Recovery Manager complete.
single tablespace recovery :
=============================
run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
set newname for datafile 1 to '+DATA/dummy/datafile/system.dbf';
set newname for datafile 2 to '+DATA/dummy/datafile/undotbs1.dbf';
set newname for datafile 3 to '+DATA/dummy/datafile/sysaux.dbf';
set newname for datafile 5 to '+DATA/dummy/datafile/ranji.dbf';
set newname for datafile 4 to '+DATA/dummy/datafile/user.dbf';
duplicate target database to dummy skip tablespace TOOLS
logfile
group 1('+DATA/dummy/onlinelog/group_1.log') size 10m reuse,
group 2('+DATA/dummy/onlinelog/group_2.log') size 10m reuse,
group 3('+DATA/dummy/onlinelog/group_3.log') size 10m reuse;
}
Output :
==========
[oracle@localhost dbs]$ rman target sys/source@source auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Mar 29 11:34:01 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: SOURCE (DBID=2833265521)
connected to auxiliary database: DUMMY (not mounted)
RMAN> run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
set newname for datafile 1 to '+DATA/dummy/datafile/system.dbf';
set newname for datafile 2 to '+DATA/dummy/datafile/undotbs1.dbf';
set newname for datafile 3 to '+DATA/dummy/datafile/sysaux.dbf';
set newname for datafile 5 to '+DATA/dummy/datafile/ranji.dbf';
set newname for datafile 4 to '+DATA/dummy/datafile/user.dbf';
duplicate target database to dummy skip tablespace TOOLS
logfile
group 1('+DATA/dummy/onlinelog/group_1.log') size 10m reuse,
group 2('+DATA/dummy/onlinelog/group_2.log') size 10m reuse,
group 3('+DATA/dummy/onlinelog/group_3.log') size 10m reuse;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=156 devtype=DISK
allocated channel: c2
channel c2: sid=155 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 29-MAR-10
Datafile 6 skipped by request
contents of Memory Script:
{
set until scn 463518;
set newname for datafile 1 to
"+DATA/dummy/datafile/system.dbf";
set newname for datafile 2 to
"+DATA/dummy/datafile/undotbs1.dbf";
set newname for datafile 3 to
"+DATA/dummy/datafile/sysaux.dbf";
set newname for datafile 4 to
"+DATA/dummy/datafile/user.dbf";
set newname for datafile 5 to
"+DATA/dummy/datafile/ranji.dbf";
restore
check readonly
clone database
skip tablespace TOOLS ;
}
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
Starting restore at 29-MAR-10
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA/dummy/datafile/undotbs1.dbf
restoring datafile 00003 to +DATA/dummy/datafile/sysaux.dbf
restoring datafile 00005 to +DATA/dummy/datafile/ranji.dbf
channel c1: reading from backup piece /crs/backups/0fl9pdjq.bkp
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/dummy/datafile/system.dbf
restoring datafile 00004 to +DATA/dummy/datafile/user.dbf
channel c2: reading from backup piece /crs/backups/0el9pdjq.bkp
channel c1: restored backup piece 1
piece handle=/crs/backups/0fl9pdjq.bkp tag=TAG20100329T110321
channel c1: restore complete, elapsed time: 00:02:05
channel c2: restored backup piece 1
piece handle=/crs/backups/0el9pdjq.bkp tag=TAG20100329T110321
channel c2: restore complete, elapsed time: 00:02:30
Finished restore at 29-MAR-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUMMY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA/dummy/onlinelog/group_1.log' ) SIZE 10 M REUSE,
GROUP 2 ( '+DATA/dummy/onlinelog/group_2.log' ) SIZE 10 M REUSE,
GROUP 3 ( '+DATA/dummy/onlinelog/group_3.log' ) SIZE 10 M REUSE
DATAFILE
'+DATA/dummy/datafile/system.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=714915496 filename=+DATA/dummy/datafile/undotbs1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=714915497 filename=+DATA/dummy/datafile/sysaux.dbf
datafile 5 switched to datafile copy
input datafile copy recid=3 stamp=714915497 filename=+DATA/dummy/datafile/ranji.dbf
datafile 4 switched to datafile copy
input datafile copy recid=4 stamp=714915497 filename=+DATA/dummy/datafile/user.dbf
contents of Memory Script:
{
set until scn 463518;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 29-MAR-10
datafile 6 not processed because file is offline
starting media recovery
archive log thread 1 sequence 7 is already on disk as file +DATA/source/1_7_714904885.dbf
archive log filename=+DATA/source/1_7_714904885.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:10
Finished recover at 29-MAR-10
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUMMY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA/dummy/onlinelog/group_1.log' ) SIZE 10 M REUSE,
GROUP 2 ( '+DATA/dummy/onlinelog/group_2.log' ) SIZE 10 M REUSE,
GROUP 3 ( '+DATA/dummy/onlinelog/group_3.log' ) SIZE 10 M REUSE
DATAFILE
'+DATA/dummy/datafile/system.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA/dummy/tempfile/temp.274.714904919";
switch clone tempfile all;
catalog clone datafilecopy "+DATA/dummy/datafile/undotbs1.dbf";
catalog clone datafilecopy "+DATA/dummy/datafile/sysaux.dbf";
catalog clone datafilecopy "+DATA/dummy/datafile/user.dbf";
catalog clone datafilecopy "+DATA/dummy/datafile/ranji.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to +DATA/dummy/tempfile/temp.274.714904919 in control file
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/undotbs1.dbf recid=1 stamp=714915644
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/sysaux.dbf recid=2 stamp=714915647
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/user.dbf recid=3 stamp=714915648
cataloged datafile copy
datafile copy filename=+DATA/dummy/datafile/ranji.dbf recid=4 stamp=714915649
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=714915644 filename=+DATA/dummy/datafile/undotbs1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=714915647 filename=+DATA/dummy/datafile/sysaux.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=714915649 filename=+DATA/dummy/datafile/ranji.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=714915648 filename=+DATA/dummy/datafile/user.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
contents of Memory Script:
{
# drop offline and skipped tablespaces
sql clone "drop tablespace TOOLS including contents cascade constraints";
}
executing Memory Script
sql statement: drop tablespace TOOLS including contents cascade constraints
Finished Duplicate Db at 29-MAR-10
RMAN>
RMAN>
RMAN>
