How to perform a time-based incomplete recovery

Every incomplete recovery is followed by a resetlogs command before opening the database: each time you use a resetlogs command, a new incarnation of the database is created.
While performing incomplete recovery it should be known the target point at which the recovery process needs to terminate: there are several ways to establish and set this recovery point.

Today I'm going to use a time based incomplete recovery, generally used when it's known the time to which you want to stop the recovery process: in the next posts I will describe scenarios on log-sequence, SCN and cancel-based incomplete recovery.

Let's first take a backup of the database.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Apr 22 21:32:05 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup database plus archivelog delete all input;

Starting backup at 22-04-2013 21:33:34
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=47 RECID=265 STAMP=813447215
channel ORA_DISK_1: starting piece 1 at 22-04-2013 21:33:35
channel ORA_DISK_1: finished piece 1 at 22-04-2013 21:33:38
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_annnn_TAG20130422T213335_8qd3rzng_.bkp tag=TAG20130422T213335 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_47_8qd3ryt3_.arc RECID=265 STAMP=813447215
Finished backup at 22-04-2013 21:33:38

Starting backup at 22-04-2013 21:33:39
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/APEX.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/read_only01.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/example02.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/APEX02.dbf
input datafile file number=00012 name=/home/oracle/app/oracle/oradata/orcl/test01.dbf
channel ORA_DISK_1: starting piece 1 at 22-04-2013 21:33:39
channel ORA_DISK_1: finished piece 1 at 22-04-2013 21:45:36
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_nnndf_TAG20130422T213339_8qd3s6fx_.bkp tag=TAG20130422T213339 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:11:57
Finished backup at 22-04-2013 21:45:36

Starting backup at 22-04-2013 21:45:37
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=48 RECID=266 STAMP=813447937
channel ORA_DISK_1: starting piece 1 at 22-04-2013 21:45:38
channel ORA_DISK_1: finished piece 1 at 22-04-2013 21:45:39
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_annnn_TAG20130422T214537_8qd4hl7n_.bkp tag=TAG20130422T214537 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_48_8qd4hkl5_.arc RECID=266 STAMP=813447937
Finished backup at 22-04-2013 21:45:39

Starting Control File and SPFILE Autobackup at 22-04-2013 21:45:39
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_04_22/o1_mf_s_813447939_8qd4hndz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-04-2013 21:45:42

The backup finished at 21:45:39. Now I'm going to simulate some activities on the database.

SQL> desc marcov.t1;
 Name                Null?    Type
 ------------------- -------- ----------------------------
 A                            NUMBER

SQL> insert into marcov.t1 select level from dual connect by level<1001;

1000 rows created.

SQL> create table marcov.t2 (b number);

Table created.

SQL> insert into marcov.t2 select level from dual connect by level<2001;

2000 rows created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into marcov.t2 select level from dual connect by level<2001;

2000 rows created.

SQL> insert into marcov.t1 select level from dual connect by level<1001;

1000 rows created.

SQL> commit;

Commit complete.

At 22:12:53 a new software version is deployed into the database.

SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') TIME from dual;

TIME
-------------------
22/04/2013 22:12:53

SQL> create table marcov.new_deployment_t1 (a number);

Table created.

SQL> create table marcov.new_deployment_t2 (b number);

Table created.

SQL> select count(*) from marcov.t1;     

  COUNT(*)
----------
      2000

SQL> select count(*) from marcov.t2;

  COUNT(*)
----------
      4000

SQL> insert into marcov.new_deployment_t1 select * from marcov.t1;

2000 rows created.

SQL> commit;

Commit complete.

SQL>  insert into marcov.new_deployment_t2 select * from marcov.t2;

4000 rows created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> truncate table marcov.t1;

Table truncated.

SQL> truncate table marcov.t2;

Table truncated.

SQL> alter system switch logfile;

System altered.

SQL> update marcov.new_deployment_t1 set a=0 where a>1000;

1000 rows updated.

SQL> update marcov.new_deployment_t2 set b=0 where b>1000;

5334 rows updated.

SQL> commit;

Commit complete.

No real users are still connected to the database: only few people working in the development department are finishing to conduct few regression tests and only when they confirm every thing is working as expected the database could be open to every real users again.
Unfortunately during tests, after 10 minutes they discover a bug has been introduced and they want to rollback all the committed transactions occured in that short period.
They want to rewind the database just like it was at 22:12:53 time.
To proceed with this operation the database should be in mount mode.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Apr 22 22:24:18 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                360712888 bytes
Database Buffers              88080384 bytes
Redo Buffers                   6008832 bytes

I know exactly when my restore operation has to finish, so I can use the restore ... until time option:

RMAN> restore database until time "to_date('22/04/2013 22:12:53','DD/MM/YYYY HH24:MI:SS')";

Starting restore at 22-04-2013 22:26:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK

skipping datafile 7; already restored to file /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/app/oracle/oradata/orcl/example02.dbf
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/app/oracle/oradata/orcl/APEX02.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /home/oracle/app/oracle/oradata/orcl/test01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_nnndf_TAG20130422T213339_8qd3s6fx_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_nnndf_TAG20130422T213339_8qd3s6fx_.bkp tag=TAG20130422T213339
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:19
Finished restore at 22-04-2013 22:33:43

I have issue the recover command using again the until time option:

RMAN> recover database until time "to_date('22/04/2013 22:12:53','DD/MM/YYYY HH24:MI:SS')";

Starting recover at 22-04-2013 22:35:39
using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 49 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_49_8qd5pd13_.arc
archived log for thread 1 with sequence 50 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_50_8qd6ckq5_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=48
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_annnn_TAG20130422T214537_8qd4hl7n_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_22/o1_mf_annnn_TAG20130422T214537_8qd4hl7n_.bkp tag=TAG20130422T214537
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_48_8qdxzf75_.arc thread=1 sequence=48
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_48_8qdxzf75_.arc RECID=270 STAMP=813474045
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_22/o1_mf_1_49_8qd5pd13_.arc thread=1 sequence=49
media recovery complete, elapsed time: 00:00:18
Finished recover at 22-04-2013 22:36:04

Every incomplete recovery needs to be followed by a resetlogs:

RMAN> alter database open resetlogs;

database opened

So the database is now available again.
Let's see if my data are still there just like before inserting new values for the deployment:

SQL> select count(*) from marcov.t1;     

  COUNT(*)
----------
      2000

SQL> select count(*) from marcov.t2;

  COUNT(*)
----------
      4000

Of course also the new tables are no more available.

SQL> select count(*) from marcov.new_deployment_t1;
select count(*) from marcov.new_deployment_t1
                            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select count(*) from marcov.new_deployment_t2;
select count(*) from marcov.new_deployment_t2
                            *
ERROR at line 1:
ORA-00942: table or view does not exist

That's all.

How to create an archival backup and maintain it forever

Sometimes it could happen to retain a backup for a very long time, even forever that is it should be maintained until you manually remove it.

This post is related on how to keep a backup forever.

In particular the syntax to have a backup retained forever is using the keep forever option: these kind of backups are also called archival backups, that is a self-contained backups, including all the necessary files (datafiles and archived redo logs) to recreate a consistent backup.

At the same time to successfully complete and maintain a backup forever fewer requisites are required, but let's begin issuing the backup command.

[oracle@vsi08 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 19 12:25:00 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=223010867)

RMAN> backup database keep forever;

Starting backup at 19-04-2013 12:25:27
current log archived

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/19/2013 12:25:28
RMAN-06522: KEEP FOREVER option is not supported without the recovery catalog

As you can deduct from the error message you cannot issue a keep forever option using the target database control file: when using keep forever option you must specify a recovery catalog schema on a dedicated database.
So I'm going to use my new recovery catalog schema on RCAT database.

[oracle@vsi08 ~]$ rman target / catalog rman/rman@RCAT

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 19 12:26:07 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=223010867)
connected to recovery catalog database

I can issue the same backup command...

RMAN> backup database keep forever;

Starting backup at 19-04-2013 12:26:16
current log archived

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/app/oracle/oradata/PROD/system01.dbf
input datafile file number=00002 name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
input datafile file number=00003 name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
input datafile file number=00004 name=/opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-04-2013 12:26:18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/19/2013 12:26:18
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

... but it fails once again.
The second restriction while using the keep forever option is related on the flash recovery area: you cannot create those archival backups into the FRA and if you try to do so, the backup fails and an error is thrown.
As you can see I'm currently using a flash recovery area.

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest       string  /opt/app/oracle/flash_recovery
       _area
db_recovery_file_dest_size      big integer 4977M

To complete my backup I need to use also the format option specifying a directory where creating the archival backups:

RMAN> backup database format '/home/oracle/backup/%U' keep forever;

Starting backup at 19-04-2013 12:35:12
current log archived

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/app/oracle/oradata/PROD/system01.dbf
input datafile file number=00002 name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
input datafile file number=00003 name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
input datafile file number=00004 name=/opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-04-2013 12:35:14
channel ORA_DISK_1: finished piece 1 at 19-04-2013 12:35:59
piece handle=/home/oracle/backup/11o7fhc2_1_1 tag=TAG20130419T123513 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 19-04-2013 12:35:59
channel ORA_DISK_1: finished piece 1 at 19-04-2013 12:36:00
piece handle=/home/oracle/backup/12o7fhdf_1_1 tag=TAG20130419T123513 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

current log archived
using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=94 RECID=47 STAMP=813155760
channel ORA_DISK_1: starting piece 1 at 19-04-2013 12:36:01
channel ORA_DISK_1: finished piece 1 at 19-04-2013 12:36:02
piece handle=/home/oracle/backup/13o7fhdh_1_1 tag=TAG20130419T123513 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 19-04-2013 12:36:03
channel ORA_DISK_1: finished piece 1 at 19-04-2013 12:36:05
piece handle=/home/oracle/backup/14o7fhdi_1_1 tag=TAG20130419T123513 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 19-04-2013 12:36:05

Listing the archival backups you can notice a new line after the Piece Name: it says that backup is an archival backup created using the KEEP option and it will be maintained FOREVER. All those archival backups are AVAILABLE, that is RMAN is able to find them on the file system.

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
403     Full    835.42M    DISK        00:00:44     19-04-2013 12:35:58
        BP Key: 406   Status: AVAILABLE  Compressed: NO  Tag: TAG20130419T123513
        Piece Name: /home/oracle/backup/11o7fhc2_1_1
        Keep: BACKUP_LOGS        Until: FOREVER        
  List of Datafiles in backup set 403
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 1410646    19-04-2013 12:35:14 /opt/app/oracle/oradata/PROD/system01.dbf
  2       Full 1410646    19-04-2013 12:35:14 /opt/app/oracle/oradata/PROD/sysaux01.dbf
  3       Full 1410646    19-04-2013 12:35:14 /opt/app/oracle/oradata/PROD/undotbs01.dbf
  4       Full 1410646    19-04-2013 12:35:14 /opt/app/oracle/oradata/PROD/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
404     Full    80.00K     DISK        00:00:00     19-04-2013 12:35:59
        BP Key: 407   Status: AVAILABLE  Compressed: NO  Tag: TAG20130419T123513
        Piece Name: /home/oracle/backup/12o7fhdf_1_1
        Keep: BACKUP_LOGS        Until: FOREVER        
  SPFILE Included: Modification time: 19-04-2013 11:18:50
  SPFILE db_unique_name: PROD

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
426     2.50K      DISK        00:00:00     19-04-2013 12:36:01
        BP Key: 429   Status: AVAILABLE  Compressed: NO  Tag: TAG20130419T123513
        Piece Name: /home/oracle/backup/13o7fhdh_1_1
        Keep: BACKUP_LOGS        Until: FOREVER        

  List of Archived Logs in backup set 426
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    94      1410634    19-04-2013 12:35:13 1410670    19-04-2013 12:36:00

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
427     Full    9.39M      DISK        00:00:02     19-04-2013 12:36:04
        BP Key: 430   Status: AVAILABLE  Compressed: NO  Tag: TAG20130419T123513
        Piece Name: /home/oracle/backup/14o7fhdi_1_1
        Keep: BACKUP_LOGS        Until: FOREVER        
  Control File Included: Ckp SCN: 1410684      Ckp time: 19-04-2013 12:36:02

So I have on file system the archival backups, but I want to copy them into a tape library.

[oracle@vsi08 backup]$ pwd
/home/oracle/backup
[oracle@vsi08 backup]$ ls -lhrt
total 846M
-rw-r----- 1 oracle oinstall 836M Apr 19 12:35 11o7fhc2_1_1
-rw-r----- 1 oracle oinstall  96K Apr 19 12:35 12o7fhdf_1_1
-rw-r----- 1 oracle oinstall 3.0K Apr 19 12:36 13o7fhdh_1_1
-rw-r----- 1 oracle oinstall 9.5M Apr 19 12:36 14o7fhdi_1_1

To complete this task I need to change the backup status to UNAVAILABLE, that is RMAN cannot consider it during a restore and recovery operation: it doesn't exist because I'm going to move it on tape and delete from file system.

RMAN> change backup tag 'TAG20130419T123513' unavailable;

changed backup piece unavailable
backup piece handle=/home/oracle/backup/11o7fhc2_1_1 RECID=31 STAMP=813155714
changed backup piece unavailable
backup piece handle=/home/oracle/backup/12o7fhdf_1_1 RECID=32 STAMP=813155759
changed backup piece unavailable
backup piece handle=/home/oracle/backup/13o7fhdh_1_1 RECID=33 STAMP=813155761
changed backup piece unavailable
backup piece handle=/home/oracle/backup/14o7fhdi_1_1 RECID=34 STAMP=813155763
Changed 4 objects to UNAVAILABLE status

The status is now UNAVAILABLE and I can copy the archival backups on tape.

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
403     Full    835.42M    DISK        00:00:44     19-04-2013 12:35:58
        BP Key: 406   Status: UNAVAILABLE  Compressed: NO  Tag: TAG20130419T123513
        Piece Name: /home/oracle/backup/11o7fhc2_1_1
        Keep: BACKUP_LOGS        Until: FOREVER        
  List of Datafiles in backup set 403
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 1410646    19-04-2013 12:35:14 /opt/app/oracle/oradata/PROD/system01.dbf
  2       Full 1410646    19-04-2013 12:35:14 /opt/app/oracle/oradata/PROD/sysaux01.dbf
  3       Full 1410646    19-04-2013 12:35:14 /opt/app/oracle/oradata/PROD/undotbs01.dbf
  4       Full 1410646    19-04-2013 12:35:14 /opt/app/oracle/oradata/PROD/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
404     Full    80.00K     DISK        00:00:00     19-04-2013 12:35:59
        BP Key: 407   Status: UNAVAILABLE  Compressed: NO  Tag: TAG20130419T123513
        Piece Name: /home/oracle/backup/12o7fhdf_1_1
        Keep: BACKUP_LOGS        Until: FOREVER        
  SPFILE Included: Modification time: 19-04-2013 11:18:50
  SPFILE db_unique_name: PROD

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
426     2.50K      DISK        00:00:00     19-04-2013 12:36:01
        BP Key: 429   Status: UNAVAILABLE  Compressed: NO  Tag: TAG20130419T123513
        Piece Name: /home/oracle/backup/13o7fhdh_1_1
        Keep: BACKUP_LOGS        Until: FOREVER        

  List of Archived Logs in backup set 426
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    94      1410634    19-04-2013 12:35:13 1410670    19-04-2013 12:36:00

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
427     Full    9.39M      DISK        00:00:02     19-04-2013 12:36:04
        BP Key: 430   Status: UNAVAILABLE  Compressed: NO  Tag: TAG20130419T123513
        Piece Name: /home/oracle/backup/14o7fhdi_1_1
        Keep: BACKUP_LOGS        Until: FOREVER        
  Control File Included: Ckp SCN: 1410684      Ckp time: 19-04-2013 12:36:02

Now it could be possible to copy on a tape and remove from file system these archival backups.

That's all.

How to create the recovery catalog

To simulate other RMAN scenarios I need to create a recovery catalog.
This post so will be related on the basic steps to create the user account managing the recovery catalog and the creation of the recovery catalog itself.

The first step is to create the recovery catalog user account.
I prefer to assign a default tablespace to that user account. The database containig the recovery catalog is RCAT.

[oracle@vsi08devpom ~]$ export ORACLE_SID=RCAT
[oracle@vsi08devpom ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 18 15:13:54 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace RECOVERY_CATALOG datafile '/opt/app/oracle/oradata/RCAT/recovery_catalog01.dbf' size 15M autoextend on next 15M MAXSIZE 300M;

Tablespace created.

Now it's possible to create the user:

SQL> create user rman identified by rman
    temporary tablespace TEMP
    default tablespace RECOVERY_CATALOG;

User created.

I gave to rman user an unlimited quota to its default tablespace:

SQL> alter user rman quota unlimited on RECOVERY_CATALOG;

User altered.

Only two privileges are required to be granted to rman user:

SQL> grant create session, recovery_catalog_owner to rman;

Grant succeeded.

It's now possible to create the recovery catalog on RCAT database. Use the RMAN client and the catalog connection to specify you want to use a recovery catalog database:

[oracle@vsi08devpom ~]$ export ORACLE_SID=RCAT
[oracle@vsi08devpom ~]$ export NLS_DATE_FORMAT='DD-MM-RRRR HH24:MI:SS'
[oracle@vsi08devpom ~]$ rman catalog rman/rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 18 15:33:07 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

To create the recovery catalog objects inside the rman user schema you simply have to specify the create catalog command:

RMAN> create catalog;

recovery catalog created

The latest step is to register your database (in my case is PROD) in the recovery catalog.

[oracle@vsi08devpom ~]$ export ORACLE_SID=PROD
[oracle@vsi08devpom ~]$ export NLS_DATE_FORMAT='DD-MM-RRRR HH24:MI:SS'
[oracle@vsi08devpom ~]$ rman target / catalog rman/rman@RCAT

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 19 11:33:25 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=223010867)
connected to recovery catalog database

Once you are connected both with the target PROD database and with RCAT recovery catalog database you can issue the register database command.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Use the following command to show the registered databases on the recovery catalog:

RMAN> list db_unique_name all;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
2       PROD     223010867        PRIMARY          PROD

That's all.

How to resynchronize a duplicated remote database using RMAN backups

Today I want to resynchronize a duplicated database: the source database is PROD @vsi08 server and the target database will have the same SID, but located @vsi10 server.
All the steps to set up a duplicated database are already explained in this previous post.
Let's duplicate for the first time our target database PROD located on a different server (vsi10) using a backup taken from the source database PROD located on vsi08 server: the duplicated database have the same SID and directories structure of source database.

So I need to take a backup of PROD database at vsi08 server

[oracle@vsi08 ~]$ export NLS_DATE_FORMAT='DD-MM-RRRR HH24:MI:SS'  
[oracle@vsi08 ~]$ export ORACLE_SID=PROD  
[oracle@vsi08 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 11:46:59 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=223010867)

RMAN> backup database plus archivelog delete input;

Starting backup at 04-04-2013 11:47:07
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=65 RECID=18 STAMP=811856828
channel ORA_DISK_1: starting piece 1 at 04-04-2013 11:47:09
channel ORA_DISK_1: finished piece 1 at 04-04-2013 11:47:10
piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114709_8otm0xgs_.bkp tag=TAG20130404T114709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_65_8otm0wbf_.arc RECID=18 STAMP=811856828
Finished backup at 04-04-2013 11:47:10

Starting backup at 04-04-2013 11:47:10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/app/oracle/oradata/PROD/system01.dbf
input datafile file number=00002 name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
input datafile file number=00003 name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
input datafile file number=00004 name=/opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-04-2013 11:47:10
channel ORA_DISK_1: finished piece 1 at 04-04-2013 11:47:35
piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp tag=TAG20130404T114710 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 04-04-2013 11:47:35

Starting backup at 04-04-2013 11:47:35
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=66 RECID=19 STAMP=811856855
channel ORA_DISK_1: starting piece 1 at 04-04-2013 11:47:35
channel ORA_DISK_1: finished piece 1 at 04-04-2013 11:47:36
piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp tag=TAG20130404T114735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otm1qsm_.arc RECID=19 STAMP=811856855
Finished backup at 04-04-2013 11:47:36

Starting Control File and SPFILE Autobackup at 04-04-2013 11:47:37
piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 04-04-2013 11:47:38

The available backups for PROD database @vsi08 server are the following:

RMAN> list backup;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
23      30.68M     DISK        00:00:01     04-04-2013 11:47:10
        BP Key: 23   Status: AVAILABLE  Compressed: NO  Tag: TAG20130404T114709
        Piece Name: /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114709_8otm0xgs_.bkp

  List of Archived Logs in backup set 23
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    65      743889     03-04-2013 15:55:16 777811     04-04-2013 11:47:07

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
24      Full    740.51M    DISK        00:00:23     04-04-2013 11:47:33
        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20130404T114710
        Piece Name: /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp
  List of Datafiles in backup set 24
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 777822     04-04-2013 11:47:10 /opt/app/oracle/oradata/PROD/system01.dbf
  2       Full 777822     04-04-2013 11:47:10 /opt/app/oracle/oradata/PROD/sysaux01.dbf
  3       Full 777822     04-04-2013 11:47:10 /opt/app/oracle/oradata/PROD/undotbs01.dbf
  4       Full 777822     04-04-2013 11:47:10 /opt/app/oracle/oradata/PROD/users01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
25      4.00K      DISK        00:00:01     04-04-2013 11:47:36
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20130404T114735
        Piece Name: /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp

  List of Archived Logs in backup set 25
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    66      777811     04-04-2013 11:47:07 777836     04-04-2013 11:47:35

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
26      Full    9.36M      DISK        00:00:00     04-04-2013 11:47:37
        BP Key: 26   Status: AVAILABLE  Compressed: NO  Tag: TAG20130404T114737
        Piece Name: /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp
  SPFILE Included: Modification time: 03-04-2013 14:53:02
  SPFILE db_unique_name: PROD
  Control File Included: Ckp SCN: 777847       Ckp time: 04-04-2013 11:47:37

I copy all backup sets from vsi08 to vsi10 server, creating the same directories on the remote server:

[oracle@vsi08 PROD]$ scp -r /opt/app/oracle/flash_recovery_area/PROD/*back* vsi10.MYDOMAIN.it:/opt/app/oracle/flash_recovery_area/PRODoracle@vsi10.MYDOMAIN.it's password: 
o1_mf_s_811856857_8otm1s7l_.bkp                        100% 9600KB   9.4MB/s   00:00    
o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp           100% 4608     4.5KB/s   00:00    
o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp           100%  741MB  39.0MB/s   00:19    
o1_mf_annnn_TAG20130404T114709_8otm0xgs_.bkp           100%   31MB  30.7MB/s   00:00

To start the duplicate process I need to start in NOMOUNT mode the instance I want to duplicate:

[oracle@vsi10 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 4 11:51:17 2013

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

Connected to an idle instance.

SQL@vsi10> startup nomount;
ORACLE instance started.

Total System Global Area 1686925312 bytes
Fixed Size      2213976 bytes
Variable Size   1006634920 bytes
Database Buffers   671088640 bytes
Redo Buffers      6987776 bytes

Connecting from vsi10 server to the target (@vsi08 server) database and to the auxiliary (@vsi10 server) I can execute the duplicate target database command.

[oracle@vsi10 ~]$ rman target sys/oracle@PROD_AT_VSI08 auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 11:55:36 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=223010867)
connected to auxiliary database: PROD (not mounted)

RMAN> duplicate target database to PROD nofilenamecheck;

Starting Duplicate Db at 04-04-2013 11:56:52
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes

Starting restore at 04-04-2013 11:57:00
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp tag=TAG20130404T114737
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/PROD/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/PROD/control02.ctl
Finished restore at 04-04-2013 11:57:03

database mounted

contents of Memory Script:
{
   set until scn  777836;
   set newname for datafile  1 to 
 "/opt/app/oracle/oradata/PROD/system01.dbf";
   set newname for datafile  2 to 
 "/opt/app/oracle/oradata/PROD/sysaux01.dbf";
   set newname for datafile  3 to 
 "/opt/app/oracle/oradata/PROD/undotbs01.dbf";
   set newname for datafile  4 to 
 "/opt/app/oracle/oradata/PROD/users01.dbf";
   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

Starting restore at 04-04-2013 11:57:08
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/PROD/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp tag=TAG20130404T114710
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 04-04-2013 11:57:53

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=811857473 file name=/opt/app/oracle/oradata/PROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=811857473 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=811857473 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=811857473 file name=/opt/app/oracle/oradata/PROD/users01.dbf

contents of Memory Script:
{
   set until scn  777836;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 04-04-2013 11:57:53
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=66
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp tag=TAG20130404T114735
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otmo2js_.arc thread=1 sequence=66
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otmo2js_.arc RECID=20 STAMP=811857474
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-04-2013 11:57:55

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   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    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes

sql statement: alter system set  db_name =  ''PROD'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M ,
  GROUP  3  SIZE 50 M 
 DATAFILE
  '/opt/app/oracle/oradata/PROD/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/opt/app/oracle/oradata/PROD/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/opt/app/oracle/oradata/PROD/sysaux01.dbf", 
 "/opt/app/oracle/oradata/PROD/undotbs01.dbf", 
 "/opt/app/oracle/oradata/PROD/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /opt/app/oracle/oradata/PROD/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf RECID=1 STAMP=811857493
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf RECID=2 STAMP=811857493
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/users01.dbf RECID=3 STAMP=811857493

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=811857493 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=811857493 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=811857493 file name=/opt/app/oracle/oradata/PROD/users01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 04-04-2013 11:58:19

The database was duplicated using a backup coming from the target server. Let's see if the two databases have the same data.

[oracle@vsi08 PROD]$ sqlplus / as sysdba
SQL@vsi08> select count(*) from sys.test;

  COUNT(*)
----------
      1000

On vsi10 server I have of course the same amount of data:

[oracle@vsi10 ~]$ sqlplus / as sysdba
SQL@vsi10> select count(*) from sys.test;

  COUNT(*)
----------
      1000

Now on vsi08 server (the target database) I executes some switch of logfile, inserting 2000 more rows on SYS.TEST table:

[oracle@vsi08 PROD]$ sqlplus / as sysdba
SQL@vsi08> alter system switch logfile;

System altered.

SQL@vsi08> insert into sys.test select level from dual connect by level<1001;

1000 rows created.

SQL@vsi08> commit;

Commit complete.

SQL@vsi08> alter system switch logfile;

System altered.

SQL@vsi08> insert into sys.test select level from dual connect by level<1001;

1000 rows created.

SQL@vsi08> commit;

Commit complete.

SQL@vsi08> select count(*) from sys.test;

  COUNT(*)
----------
      3000

I would like to resynchronize the already duplicated database with these 2000 rows so I first have to backup the new archived redo log @vsi08 server:

[oracle@vsi08 PROD]$ rman target /

...

RMAN> backup archivelog all delete input;

Starting backup at 04-04-2013 12:05:03
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=67 RECID=20 STAMP=811857748
input archived log thread=1 sequence=68 RECID=21 STAMP=811857778
input archived log thread=1 sequence=69 RECID=22 STAMP=811857905
channel ORA_DISK_1: starting piece 1 at 04-04-2013 12:05:05
channel ORA_DISK_1: finished piece 1 at 04-04-2013 12:05:06
piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp tag=TAG20130404T120505 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otmxn7m_.arc RECID=20 STAMP=811857748
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_68_8otmylvo_.arc RECID=21 STAMP=811857778
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_69_8otn2kfr_.arc RECID=22 STAMP=811857905
Finished backup at 04-04-2013 12:05:06

Starting Control File and SPFILE Autobackup at 04-04-2013 12:05:06
piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811857906_8otn2lxd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 04-04-2013 12:05:07

The new backup sets need to be copied into the appropriate directories on the remote vsi10 server:

[oracle@vsi08 ~]$ scp /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp vsi10.MYDOMAIN.it:/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04
oracle@vsi10.MYDOMAIN.it's password: 
o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp                    100%  985KB 984.5KB/s   00:00
[oracle@vsi08 ~]$ scp /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811857906_8otn2lxd_.bkp vsi10.MYDOMAIN.it:/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04
oracle@vsi10.MYDOMAIN.it's password: 
o1_mf_s_811857906_8otn2lxd_.bkp                                 100% 9600KB   9.4MB/s   00:00

The PROD database @vsi10 server must run always in NOMOUNT mode to successfully resynchronize it:

SQL@vsi10> shutdown immediate;

...

SQL@vsi10> startup nomount
...

Using the same duplicate target database command as above on vsi10 server I can resynchronize it with the new 2000 rows.

[oracle@vsi10 ~]$ rman target sys/oracle@PROD_AT_VSI08 auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 12:19:09 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=223010867)
connected to auxiliary database: PROD (not mounted)

RMAN> duplicate target database to PROD nofilenamecheck;

Starting Duplicate Db at 04-04-2013 12:19:28
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes

Starting restore at 04-04-2013 12:19:35
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811857906_8otn2lxd_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811857906_8otn2lxd_.bkp tag=TAG20130404T120506
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/PROD/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/PROD/control02.ctl
Finished restore at 04-04-2013 12:19:39

database mounted

contents of Memory Script:
{
   set until scn  778560;
   set newname for datafile  1 to 
 "/opt/app/oracle/oradata/PROD/system01.dbf";
   set newname for datafile  2 to 
 "/opt/app/oracle/oradata/PROD/sysaux01.dbf";
   set newname for datafile  3 to 
 "/opt/app/oracle/oradata/PROD/undotbs01.dbf";
   set newname for datafile  4 to 
 "/opt/app/oracle/oradata/PROD/users01.dbf";
   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

Starting restore at 04-04-2013 12:19:43
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/PROD/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp tag=TAG20130404T114710
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 04-04-2013 12:20:28

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=811858828 file name=/opt/app/oracle/oradata/PROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=811858828 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=811858828 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=811858828 file name=/opt/app/oracle/oradata/PROD/users01.dbf

contents of Memory Script:
{
   set until scn  778560;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 04-04-2013 12:20:28
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=66
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp tag=TAG20130404T114735
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otnzfly_.arc thread=1 sequence=66
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otnzfly_.arc RECID=23 STAMP=811858829
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=67
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=68
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=69
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp tag=TAG20130404T120505
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otnzgvh_.arc thread=1 sequence=67
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otnzgvh_.arc RECID=24 STAMP=811858830
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_68_8otnzgwf_.arc thread=1 sequence=68
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_68_8otnzgwf_.arc RECID=26 STAMP=811858830
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_69_8otnzgw1_.arc thread=1 sequence=69
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_69_8otnzgw1_.arc RECID=25 STAMP=811858830
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-04-2013 12:20:32

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   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    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes

sql statement: alter system set  db_name =  ''PROD'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M ,
  GROUP  3  SIZE 50 M 
 DATAFILE
  '/opt/app/oracle/oradata/PROD/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/opt/app/oracle/oradata/PROD/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/opt/app/oracle/oradata/PROD/sysaux01.dbf", 
 "/opt/app/oracle/oradata/PROD/undotbs01.dbf", 
 "/opt/app/oracle/oradata/PROD/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /opt/app/oracle/oradata/PROD/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf RECID=1 STAMP=811858850
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf RECID=2 STAMP=811858850
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/users01.dbf RECID=3 STAMP=811858850

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=811858850 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=811858850 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=811858850 file name=/opt/app/oracle/oradata/PROD/users01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 04-04-2013 12:20:56

Let's see how many rows are into SYS.TEST table

[oracle@vsi10 ~]$ sqlplus / as sysdba
...
SQL@vsi10> select count(*) from sys.test;

  COUNT(*)
----------
      3000

That's all right.
My duplicated database is really synchronized with PROD database @vsi08 server: I have the same amount of rows into SYS.TEST table.

But now I want to go back in time on my vsi10 server and exactly I want to go back few minutes before I inserted 2000 more rows on vsi08 server.
After I inserted those rows on PROD database @vsi08 server I took a backup of the archived redo log: as you can seen above that backup started at 04-04-2013 12:05:03 (search in this post the following text: Starting backup at 04-04-2013 12:05:03) and, since it includes the new 2000 rows, I need simply to do not apply it during the duplicate process.
Let's see what my current time is:

SQL@vsi10> select sysdate from dual;

SYSDATE
-------------------
04-04-2013 12:38:17

So I need to go back in time for about 40 minutes that is before the backup including the new 2000 rows started (11:58:31 < 12:05:03)

SQL@vsi10> select sysdate-(40/(24*60)) from dual;

SYSDATE-(40/(24*60)
-------------------
04-04-2013 11:58:31

With the already available backups copied previously on vsi10 server I start in NOMOUNT mode the instance...

SQL@vsi10> shutdown immediate;

...

SQL@vsi10> startup nomount;

... 

...and then I simply execute once again the duplicate target database command using also the untill time clause.
In this way I'm able to have a complete copy on vsi10 server of the PROD database that was running on vsi08 server untill '04-04-2013 11:58:31' time:

[oracle@vsi10 ~]$ rman target sys/oracle@PROD_AT_VSI08 auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 12:40:51 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=223010867)
connected to auxiliary database: PROD (not mounted)

RMAN> duplicate target database to PROD until time 'sysdate-(40/(24*60))' nofilenamecheck;

Starting Duplicate Db at 04-04-2013 12:40:59
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:
{
   set until scn  777847;
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set  db_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes

Starting restore at 04-04-2013 12:41:07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811856857_8otm1s7l_.bkp tag=TAG20130404T114737
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/PROD/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/PROD/control02.ctl
Finished restore at 04-04-2013 12:41:10

database mounted

contents of Memory Script:
{
   set until scn  777847;
   set newname for datafile  1 to 
 "/opt/app/oracle/oradata/PROD/system01.dbf";
   set newname for datafile  2 to 
 "/opt/app/oracle/oradata/PROD/sysaux01.dbf";
   set newname for datafile  3 to 
 "/opt/app/oracle/oradata/PROD/undotbs01.dbf";
   set newname for datafile  4 to 
 "/opt/app/oracle/oradata/PROD/users01.dbf";
   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

Starting restore at 04-04-2013 12:41:15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/PROD/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp tag=TAG20130404T114710
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 04-04-2013 12:42:00

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=811860120 file name=/opt/app/oracle/oradata/PROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=811860120 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=811860120 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=811860120 file name=/opt/app/oracle/oradata/PROD/users01.dbf

contents of Memory Script:
{
   set until time  "sysdate-(40/(24*60))";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 04-04-2013 12:42:00
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=66
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp tag=TAG20130404T114735
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otp7sqx_.arc thread=1 sequence=66
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otp7sqx_.arc RECID=20 STAMP=811860121
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=67
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp tag=TAG20130404T120505
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otp7v1l_.arc thread=1 sequence=67
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otp7v1l_.arc RECID=21 STAMP=811860123
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-04-2013 12:42:04

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   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    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes

sql statement: alter system set  db_name =  ''PROD'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M ,
  GROUP  3  SIZE 50 M 
 DATAFILE
  '/opt/app/oracle/oradata/PROD/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/opt/app/oracle/oradata/PROD/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/opt/app/oracle/oradata/PROD/sysaux01.dbf", 
 "/opt/app/oracle/oradata/PROD/undotbs01.dbf", 
 "/opt/app/oracle/oradata/PROD/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /opt/app/oracle/oradata/PROD/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf RECID=1 STAMP=811860141
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf RECID=2 STAMP=811860141
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/users01.dbf RECID=3 STAMP=811860141

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=811860141 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=811860141 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=811860141 file name=/opt/app/oracle/oradata/PROD/users01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 04-04-2013 12:42:29

As you can verify the above RMAN command didn't apply the backup set o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp containing the 2000 rows: indeed when I query the SYS.TEST table I can see only the first 1000 rows.

[oracle@vsi10 ~]$ sqlplus / as sysdba

...

SQL@vsi10> select count(*) from sys.test;

  COUNT(*)
----------
      1000

Let's insert on PROD database @vsi08 server other 2000 rows:

[oracle@vsi08 PROD]$ sqlplus / as sysdba

...

SQL@vsi08> select count(*) from sys.test;

  COUNT(*)
----------
      3000

SQL@vsi08> alter system switch logfile;

System altered.

SQL@vsi08> insert into sys.test select level from dual connect by level<1001;

1000 rows created.

SQL@vsi08> alter system switch logfile;

System altered.

SQL@vsi08> insert into sys.test select level from dual connect by level<1001;

1000 rows created.

SQL@vsi08> select count(*) from sys.test;

  COUNT(*)
----------
      5000

SQL@vsi08> commit;

Commit complete.

Let's take another backup of the new archived redo logs:

[oracle@vsi08 ~]$ rman target /
...
RMAN> backup archivelog all delete input;

Starting backup at 04-04-2013 12:49:06
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=70 RECID=23 STAMP=811860462
input archived log thread=1 sequence=71 RECID=24 STAMP=811860486
input archived log thread=1 sequence=72 RECID=25 STAMP=811860546
channel ORA_DISK_1: starting piece 1 at 04-04-2013 12:49:07
channel ORA_DISK_1: finished piece 1 at 04-04-2013 12:49:08
piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T124907_8otpo3bb_.bkp tag=TAG20130404T124907 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_70_8otplgnq_.arc RECID=23 STAMP=811860462
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_71_8otpm6bs_.arc RECID=24 STAMP=811860486
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_72_8otpo2p9_.arc RECID=25 STAMP=811860546
Finished backup at 04-04-2013 12:49:08

Starting Control File and SPFILE Autobackup at 04-04-2013 12:49:08
piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811860548_8otpo4nr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 04-04-2013 12:49:09

Again I have to copy the backup sets on the same directories from vsi08 to vsi10 server:

[oracle@vsi08 ~]$ scp /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T124907_8otpo3bb_.bkp vsi10.MYDOMAIN.it:/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04
oracle@vsi10.MYDOMAIN.it's password: 
o1_mf_annnn_TAG20130404T124907_8otpo3bb_.bkp                    100%  155KB 154.5KB/s   00:00    
[oracle@vsi08 ~]$ scp /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811860548_8otpo4nr_.bkp vsi10.MYDOMAIN.it:/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04
oracle@vsi10.MYDOMAIN.it's password: 
o1_mf_s_811860548_8otpo4nr_.bkp                                 100% 9664KB   9.4MB/s   00:01

PROD database @vsi10 server must be in NOMOUNT mode:

[oracle@vsi10 ~]$ sqlplus / as sysdba
...

SQL@vsi10> shutdown immediate;
...

SQL@vsi10> startup nomount;

... 

Execute the same command to resynchronize the PROD database @vsi10 server:

[oracle@vsi10 ~]$ rman target sys/oracle@PROD_AT_VSI08 auxiliary / 

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 12:57:25 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=223010867)
connected to auxiliary database: PROD (not mounted)

RMAN> duplicate target database to PROD nofilenamecheck;

Starting Duplicate Db at 04-04-2013 12:57:38
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes

Starting restore at 04-04-2013 12:57:43
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811860548_8otpo4nr_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_04_04/o1_mf_s_811860548_8otpo4nr_.bkp tag=TAG20130404T124908
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/PROD/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/PROD/control02.ctl
Finished restore at 04-04-2013 12:57:46

database mounted

contents of Memory Script:
{
   set until scn  779940;
   set newname for datafile  1 to 
 "/opt/app/oracle/oradata/PROD/system01.dbf";
   set newname for datafile  2 to 
 "/opt/app/oracle/oradata/PROD/sysaux01.dbf";
   set newname for datafile  3 to 
 "/opt/app/oracle/oradata/PROD/undotbs01.dbf";
   set newname for datafile  4 to 
 "/opt/app/oracle/oradata/PROD/users01.dbf";
   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

Starting restore at 04-04-2013 12:57:51
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/PROD/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_nnndf_TAG20130404T114710_8otm0yrs_.bkp tag=TAG20130404T114710
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 04-04-2013 12:58:36

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=811861116 file name=/opt/app/oracle/oradata/PROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=811861116 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=811861116 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=811861116 file name=/opt/app/oracle/oradata/PROD/users01.dbf

contents of Memory Script:
{
   set until scn  779940;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 04-04-2013 12:58:36
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=66
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T114735_8otm1qyk_.bkp tag=TAG20130404T114735
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otq6xfh_.arc thread=1 sequence=66
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_66_8otq6xfh_.arc RECID=26 STAMP=811861117
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=67
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=68
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=69
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T120505_8otn2kln_.bkp tag=TAG20130404T120505
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otq6yqw_.arc thread=1 sequence=67
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_67_8otq6yqw_.arc RECID=27 STAMP=811861118
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_68_8otq6yrv_.arc thread=1 sequence=68
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_68_8otq6yrv_.arc RECID=29 STAMP=811861118
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_69_8otq6yrh_.arc thread=1 sequence=69
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_69_8otq6yrh_.arc RECID=28 STAMP=811861118
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=70
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=71
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=72
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T124907_8otpo3bb_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_04_04/o1_mf_annnn_TAG20130404T124907_8otpo3bb_.bkp tag=TAG20130404T124907
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_70_8otq70q6_.arc thread=1 sequence=70
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_70_8otq70q6_.arc RECID=30 STAMP=811861120
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_71_8otq70s6_.arc thread=1 sequence=71
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_71_8otq70s6_.arc RECID=31 STAMP=811861120
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_72_8otq70sm_.arc thread=1 sequence=72
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_04_04/o1_mf_1_72_8otq70sm_.arc RECID=32 STAMP=811861120
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-04-2013 12:58:42

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   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    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes

sql statement: alter system set  db_name =  ''PROD'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size               1006634920 bytes
Database Buffers             671088640 bytes
Redo Buffers                   6987776 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M ,
  GROUP  3  SIZE 50 M 
 DATAFILE
  '/opt/app/oracle/oradata/PROD/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/opt/app/oracle/oradata/PROD/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/opt/app/oracle/oradata/PROD/sysaux01.dbf", 
 "/opt/app/oracle/oradata/PROD/undotbs01.dbf", 
 "/opt/app/oracle/oradata/PROD/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /opt/app/oracle/oradata/PROD/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf RECID=1 STAMP=811861138
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf RECID=2 STAMP=811861138
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/users01.dbf RECID=3 STAMP=811861138

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=811861138 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=811861138 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=811861138 file name=/opt/app/oracle/oradata/PROD/users01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 04-04-2013 12:59:07

Again on PROD database @vsi10 server there are the same rows existing in PROD database @vsi08 server: the two database are again synchronized.

[oracle@vsi10 ~]$ sqlplus / as sysdba
SQL@vsi10> select count(*) from sys.test;

  COUNT(*)
----------
      5000

That's all.

How to recover corrupted data blocks using 'recover tablespace' RMAN syntax

This post finishes a series of previous posts related to the recovery process of corrupt data blocks:
1) How to recover contiguous corrupted data blocks using 'recover datafile' RMAN syntax;
2) How to recover sparse corrupted data blocks using 'recover datafile' RMAN syntax;
3) How to recover corrupted data blocks using 'recover datafile' RMAN syntax on a system critical datafile;
4) How to perform a recovery when the first block of a datafile containing the datafile header becomes corrupt;

Reading the documentation there was only another syntax method to recover corrupt data blocks: the recover tablespace ... dba ... command.

I don't find it very useful compared with the recover corruption list command but I would like only to describe this possibility.

So let's simulate this scenario backing up MARCOV tablespace first:

RMAN> backup tablespace marcov;

Starting backup at 20-03-2013 02:51:45
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf
channel ORA_DISK_1: starting piece 1 at 20-03-2013 02:51:45
channel ORA_DISK_1: finished piece 1 at 20-03-2013 02:51:47
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_20/o1_mf_nnndf_TAG20130320T025145_8nm1ol0w_.bkp tag=TAG20130320T025145 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 20-03-2013 02:51:47

Starting Control File and SPFILE Autobackup at 20-03-2013 02:51:47
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_03_20/o1_mf_s_810528707_8nm1omhz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-03-2013 02:51:48

First of all let's wipe out some blocks of MARCOV tablespace's datafile, in particular some blocks of the header.

[oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf bs=8k conv=notrunc seek=3 count=3
3+0 records in
3+0 records out
24576 bytes (25 kB) copied, 0.000349459 seconds, 70.3 MB/s

DBVERIFY is able to find the three corrupt blocks on the datafile:

[oracle@localhost trace]$ dbv file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.2.0 - Production on Wed Mar 20 02:53:27 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
Page 3 is marked corrupt
Corrupt block relative dba: 0x02c00003 (file 11, block 3)
Completely zero block found during dbv: 

Page 4 is marked corrupt
Corrupt block relative dba: 0x02c00004 (file 11, block 4)
Completely zero block found during dbv: 

Page 5 is marked corrupt
Corrupt block relative dba: 0x02c00005 (file 11, block 5)
Completely zero block found during dbv: 

DBVERIFY - Verification complete

Total Pages Examined         : 768
Total Pages Processed (Data) : 196
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 16
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 553
Total Pages Marked Corrupt   : 3
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 14877710 (0.14877710)

Even flushing the buffer cache I'm able to successfully execute query like the following if some header blocks are corrupt:

SQL> select count(*) from marcov.t1;

  COUNT(*)
----------
      1000

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from marcov.t1;

  COUNT(*)
----------
      1000

SQL> select header_block, blocks, extents from dba_segments where segment_name='T1';

HEADER_BLOCK     BLOCKS    EXTENTS
------------ ---------- ----------
          10        256         17

Now I'm going to reset other two blocks after the end of the latest header block, two data blocks:

[oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf bs=8k conv=notrunc seek=11 count=2
2+0 records in
2+0 records out
16384 bytes (16 kB) copied, 0.00193982 seconds, 8.4 MB/s

I'm not able to count again the rows in T1 table after I flush the buffer cache:

SQL> select count(*) from marcov.t1;

  COUNT(*)
----------
      1000

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from marcov.t1;
select count(*) from marcov.t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 11, block # 11)
ORA-01110: data file 11: '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'

The database currently doesn't know there are few header and data corrupt blocks on datafile 11:

SQL> select * from v$database_block_corruption;

no rows selected

To check and have a completed list of corrupt blocks you should issue a backup validate command:

RMAN> backup validate tablespace marcov;

Starting backup at 20-03-2013 03:00:12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
11   FAILED 0              553          768             14877710  
  File Name: /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              194             
  Index      0              0               
  Other      5              21              

validate found one or more corrupt blocks
See trace file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9582.trc for details
Finished backup at 20-03-2013 03:00:13

The backup validate command is able to finish its job and identify the corrupt blocks, filling the v$database_block_corruption view.

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        11         11          2                  0 ALL ZERO
        11          3          3                  0 ALL ZERO

Let's try to start the recovery process using the recover tablespace ... dba syntax where dba stands for data block address.
How can you know the data block address of block 3,4 and 5 of datafile 11 ? You can use the Oracle DBMS_UTILITY package and obtain the data block address like in the following example:

SQL> select DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(11,3) dba1, DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(11,3+1) dba2, DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(11,3+2) dba3 from dual;

      DBA1       DBA2       DBA3
---------- ---------- ----------
  46137347   46137348   46137349

You can follow the same procedure to know the data block address of data block 11 and 12:

SQL> select DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(11,11) dba4, DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(11,11+1) dba5 from dual;

      DBA4       DBA5
---------- ----------
  46137355   46137356

Now that you know every data block address you can specify them in one unique RMAN command:

RMAN> recover tablespace marcov dba 46137347,46137348,46137349,46137355,46137356;

Starting recover at 20-03-2013 03:08:49
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00011
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_20/o1_mf_nnndf_TAG20130320T025145_8nm1ol0w_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_20/o1_mf_nnndf_TAG20130320T025145_8nm1ol0w_.bkp tag=TAG20130320T025145
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 20-03-2013 03:08:52

The v$database_block_corruption view doesn't contain any information:

SQL> select * from v$database_block_corruption;

no rows selected

It's again possible to query T1 table

SQL> select count(*) from marcov.t1;

  COUNT(*)
----------
      1000

If you try to dump the three header blocks...

SQL> alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf' block min 3 block max 5;

System altered.

... you can find that beside rdba there is an hexadecimal number:

...
buffer tsn: 40 rdba: 0x02c00003 (11/3)
scn: 0x0000.00e2a62a seq: 0x01 flg: 0x04 tail: 0xa62a1e01
frmt: 0x02 chkval: 0x4dcc type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...
buffer tsn: 40 rdba: 0x02c00004 (11/4)
scn: 0x0000.00e2a047 seq: 0x01 flg: 0x04 tail: 0xa0471e01
frmt: 0x02 chkval: 0x823c type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...
buffer tsn: 40 rdba: 0x02c00005 (11/5)
scn: 0x0000.00e2a049 seq: 0x01 flg: 0x04 tail: 0xa0491e01
frmt: 0x02 chkval: 0xc235 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...

If I try to convert those hexadecimal numbers to decimal I find the same number returned by the previous DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS procedures:

[oracle@localhost trace]$ printf '%d %d %d\n' 0x02c00003 0x02c00004 0x02c00005
46137347 46137348 46137349

The same thing happens for the two data blocks:

SQL> alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf' block min 11 block max 12;

System altered.

The two hexadecimal numbers...

...
buffer tsn: 40 rdba: 0x02c0000b (11/11)
scn: 0x0000.00e3040e seq: 0x07 flg: 0x04 tail: 0x040e0607
frmt: 0x02 chkval: 0x0691 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
...
buffer tsn: 40 rdba: 0x02c0000c (11/12)
scn: 0x0000.00e3040e seq: 0x07 flg: 0x04 tail: 0x040e0607
frmt: 0x02 chkval: 0x6c94 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
...

are converted and equal to the data block address returned by the DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS procedure.

[oracle@localhost trace]$ printf '%d %d\n' 0x02c0000b 0x02c0000c
46137355 46137356

That's all.