RMAN Restore Fails -- RMAN-06023: no backup or copy of datafile found to restore

Problem

When you try to restore a database or a datafile you get error:

RMAN> restore datafile 189;

Starting restore at 07-NOV-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=363 devtype=DISK
 

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/07/2013 14:10:20
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 189 found to restore

But you have a valid backup of the datafile:

RMAN> list backup of datafile 189;


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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
168     Full    531.52M    DISK        00:00:00     06-NOV-13
        BP Key: 168   Status: AVAILABLE  Compressed: YES  Tag: TAGXXXXXX
        Piece Name: G:\ORACLE\BACKUP\DATAFILE189.BKP
  List of Datafiles in backup set 168
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  189     Full 965812094  06-NOV-13 F:\ORACLE\ORADATA\DATAFILE189.DBF

Solution

Check the incarnations of the database. Most probably your database belongs to an ORPHAN incarnation. 

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2       2       DBNAME      893832326        ORPHAN  165955462  09-APR-03
1       1       DBNAME      893832326        CURRENT 907602989  30-MAY-13

As a solution restore a controlfile from a previous incarnation or reset the database incarnation in the current controlfile to the previous incarnation.

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN>

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2       2       BKP      893832326        CURRENT 165955462  09-APR-03
1       1       BKP      893832326        ORPHAN  907602989  30-MAY-13

Now you should be able to preform a restore.