10g RMAN Point-in-time recovery

[jhmartin]

I'm attempting to practice a RMAN based DB PITR using Oracle XE and am not getting the expected results. The scenario is such:

1. Create an instance and set ARCHIVELOGS and enable archiving.
2. Create a table.
3. Back up the database with RMAN (NOCATALOG mode).
4. Note the SCN from V$DATABASE.
5. Drop the table.
6. Execute RMAN restore/recover with a SET UNTIL SCN = the scn from step 4.

The problem is that RMAN throws an error saying it can't find any datafiles to restore, which seens wrong -- it should be able to restore the datafiles and roll forward to the appropriate SCN.

Create table BAR.

Run backup (list backup output below):

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    505.83M    DISK        00:02:05     11-OCT-06
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20061011T130322
        Piece Name: C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\BACKUPSET\2006
_10_11\O1_MF_NNNDF_TAG20061011T130322_2LTMJD7W_.BKP
  List of Datafiles in backup set 14
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 532588     11-OCT-06 C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF
  2       Full 532588     11-OCT-06 C:\ORACLEXE\ORADATA\XE\UNDO.DBF
  3       Full 532588     11-OCT-06 C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF
  4       Full 532588     11-OCT-06 C:\ORACLEXE\ORADATA\XE\USERS.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15      Full    7.08M      DISK        00:00:03     11-OCT-06
        BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: TAG20061011T130531
        Piece Name: C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\AUTOBACKUP\200
6_10_11\O1_MF_S_603551131_2LTMNDJX_.BKP
  Control File Included: Ckp SCN: 532633       Ckp time: 11-OCT-06
  SPFILE Included: Modification time: 11-OCT-06

Get the SCN:

checkpoint_change#|archive_change#|controlfile_change#|current_scn
532675|532672|532677|532719

Drop the BAR table:

Get the SCN:

 
checkpoint_change#|archive_change#|controlfile_change#|current_scn
532675|532672|533316|533362

Attempt the restore:

RMAN> run {
2> SET UNTIL SCN=532719;
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> ALTER DATABASE OPEN RESETLOGS;
6> }

executing command: SET until clause
using target database control file instead of recovery catalog

Starting restore at 11-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/11/2006 13:25:08
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN>

The backup is performed with the standard 'backup database' script provided with XE, which ultimately does a 'backup database' command in RMAN.

Any ideas why this might be failing?

[Thomasps]

Backup archive logs as well with DELETE INPUT and try restore again.

[jhmartin]

I ended up recreating the instance and trying again; it worked this time. I must have messed something up in my previous attempts such that RMAN couldn't identify the backup files.