Recover Oracle Database until cancel and noresetlogs

N.B. This has been editied since its original posting.

You may want to look at duplicating the database to a new name provided you have all the required backups and archivelogs to restore to the required point in time.

I am assuming that ASM is involved here, if not you might need to look at the duplicate command to see how to rename the files as it's done automatically by ASM.

If your using standard os files rather than ASM then you might need to include this in your duplicate statement once you've connected to the atarget and auxiliary databases (see below);

run 
{
  SET NEWNAME FOR DATAFILE 1 TO '/oradata/instance/file1.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/oradata/instance/file1.dbf';
  <repeat for all the files in your Database>
  DUPLICATE TARGET DATABASE to newdb;
}

Now the theory should be;

Create a new pfile (from the existing database and rename the various references to the database), making sure you change any log destinations and the directories exist

Start the new instance in a no mount state

Then start

rman
Connect target sys@instance
Connect auxiliary /

Once the two connections are up.

The duplicate command to restore to a point in time should be as follows

DUPLICATE TARGET DATABASE TO newdb UNTIL TIME TO_DATE('20/07/2011 19:45:00','DD/MM/YYYY HH24:MI:SS')

-- This should be prior to when you created the Test table so the database will be restored upto that point in time. It theory at least.

another option i do find useful is to resize the logfile and groups

DUPLICATE TARGET DATABASE TO newdb
LOGFILE
      GROUP 1 ('/oradata/instance/redo01a.log', 
               '/oradata/instance/redo01b.log') SIZE 200K REUSE, 
      GROUP 2 ('/oradata/instance/redo02a.log', 
               '/oradata/instance/redo02b.log') SIZE 200K REUSE;

or with ASM something like

DUPLICATE TARGET DATABASE TO newdb
LOGFILE
      GROUP 1 ('+DATA', 
               '+FRA') SIZE 200K REUSE, 
      GROUP 2 ('+DATA', 
               '+FRA') SIZE 200K REUSE;

There are several options you can also specify, I believe one is to an scn, sequence or until a date time. You might need to do a to_date convertion for the last option. putting these together should help you get what you're after.

Each redo log file (and archived redo log file) contains starting SCN and ending SCN. In case it is a last redo, the ending SCN is 0xffffffffffff.

nap01:~/oradata/jt10g$ strings redo01.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000004, SCN 0x0000000b05b5-0x0000000bd34f

nap01:~/oradata/jt10g$ strings redo02.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000005, SCN 0x0000000bd34f-0x0000000bf612

nap01:~/oradata/jt10g$ strings redo03.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000006, SCN 0x0000000bf612-0xffffffffffff

Until database sees 0xffffffffffff it keeps asking for further logs. But it is not a problem, you can specify UNTIL SCN or UNTIL CANCEL for your recovery (this means you don't want a complete recovery, in other words you want to lose some recent data).