Resolving Oracle RMAN-06023 or RMAN-06025

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

Cause: A datafile, tablespace, or database restore could not proceed because no backup or copy of the indicated file was found. It may be the case that a backup or copy of this file exists but does not satisfy the criteria specified in the user's restore operands.

Action: None - this is an informational message. See message 6026 for further details.

RMAN-06025: no backup of archived log for thread number with sequence number and starting SCN of string found to restore Cause: An archived log restore restore could not proceed because no backup of the indicated archived log was found. It may be the case that a backup of this file exists but does not satisfy the criteria specified in the user's restore operands. Action: None - this is an informational message. See message 6026 for further details.

SYMPTOMS

You are receiving error

RMAN-06025: no backup of archived log for thread 1 with sequence 19684 and starting SCN of 1483914453 found to restore

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

CAUSE

These errors are returned by RMAN when it is unable to identify a backup of either an archivelog file (RMAN-06025) or datafile (RMAN-06023)

SOLUTION

1. Connected with RMAN to the instance in which you are running the restore (or the target when running an RMAN duplicate) and issue:

For RMAN-06025: no backup of archived log.......

RMAN> list backup of archivelog sequence <sequence# returned by error>;

For example

RMAN> list backup of archivelog sequence 19684;

For RMAN-06023: no backup or copy of datafile ......

RMAN> list backup of datafile <number returned by the error>;

For example

RMAN> list backup of datafile 4;

2. If no backups are returned

A. Check if the you should be connecting to a catalog.

B. If no catalog is used, you may need to catalog the backups contining the file (archivelog or datafile). To do this, execute:

RMAN> catalog start with '/location of backup/' noprompt;

If backups are on tape, see

Note 550082.1 How to Catalog Tape Backup Pieces

C. Consider restoring a controlfile which contains the backup information.

3. If RMAN returns backup information

A. Check that the backup's status is available.

For example

RMAN> list backup of datafile 4;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

291 Full 1.06G DISK 00:00:15 27-JUL-15

BP Key: 292 Status: AVAILABLE Compressed: NO Tag: TAG20150727T144532

Piece Name: <backuppiece path</<backuppiece name>

B. for datafiles, check that the datafile's Ckp SCN/Ckp Time in the backup is before the until specified by the restore/duplicate:

For example

RMAN> list backup of datafile 4;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ --------------------

291 Full 1.06G DISK 00:00:15 27-JUL-2015 14:45:47

BP Key: 292 Status: AVAILABLE Compressed: NO Tag: TAG20150727T144532

Piece Name: <backuppiece path</<backuppiece name>

List of Datafiles in backup set 291

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- -------------------- ----

4 Full 59195530 27-JUL-2015 14:45:32 <db file path>/<db file name>

C. check that the backup you expect to be used is within the CURRENT incarnation.

For example

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

------- ------- -------- ---------------- --- ---------- ----------

1 1 <db name> 82280094 PARENT 1 19-JUN-2015 10:31:58

2 2 <db name> 82280094 CURRENT 59448598 29-JUL-2015 12:45:27

In this case, we clearly see that the backup from 27-JUL-2015 was not taken in the CURRENT incarnation, Reset Time=29-JUL-2015. To restore the backup of datafile 4, the incarnation must be reset.

For example

RMAN> reset database to incarnation 1;

Database reset to incarnation 1

RMAN> list incarnation;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1 1 <db name> 82280094 CURRENT 1 19-JUN-2015 10:31:58

2 2 <db name> 82280094 ORPHAN 59448598 29-JUL-2015 12:45:27

NOTE: If you are getting 'specification does not match any archive log..' for archivelog files, this is a bit different. This error indicates that the controlfile doesn't know of an existing archivelog file vs backup. See Note 869671.1 RMAN List / Delete error with "specification does not match any archive log..."

DBRECOVER Recovery Options

For Oracle incidents, start with the DBRECOVER for Oracle trial to verify table visibility, row previews, and export readiness on copied datafiles. For MySQL and InnoDB incidents, DBRECOVER for MySQL is free software and can inspect.ibd files, ibdata1, and database directories locally.

When the case is urgent, preserve the original files first, work from copies, and contact paid emergency support with the database version, platform, error messages, file list, and recovery objective.