Purpose
This document describes known root-causes for the error :RMAN-06023: no backup or copy of datafile %s found to restore
RMAN-6023: no backup or copy of datafile %s found to restore
Troubleshooting Steps
1) General description
RMAN-06023 "no backup or copy of datafile %d 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.
The error RMAN-6023 means that RMAN cannot find a backup for that datafile in its repository. The RMAN-repository is ALWAYS in the controlfile, but might be in an RMAN-catalog database aswell. So a good starting point for diagnosing the issue is a LIST BACKUP output.
Example :
RMAN> list backup of datafile 1;
--OR--
RMAN> list backup of archivelog sequence;
The backup needs to be marked as AVAILABLE and there needs to be a channel allocated for the 'Device Type' reported in the 'LIST BACKUP'
Example :
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
4 Full 537.90M DISK 00:00:25 17-JUN-2011 17:12:42
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20110617T171216
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/05mf4h3h_1_1
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 Full 975048 17-JUN-2011 17:11:39 /u02/nfs_share/ora_data/B202/datafile/o1_mf_system_6xymz39s_.dbf
However if they above is matching, than it might be one of the issues below which is causing the problems.
2) Backups available on disk / tape but not in the RMAN repository
It might be that RMAN can not find any backup to restore from and they are not shown in the 'LIST BACKUP'-output,
but the backups available on disk or tape.
So in your setup the backups are removed from the RMAN-repository (controlfile and/or catalog), but are still available on disk or tape.
There are configurations possible where this is intended behaviour.
Than the backups need to be cataloged again using the CATALOG command
Backups can only cataloged from 10g onwards.
Example :
RMAN> catalog start with '<directory where the backups are>';
Afterwards the backups should be shown again in the 'LIST BACKUP' output
3) UNTIL TIME conversion
When an SET UNTIL TIME is being used, RMAN will convert it to an UNTIL SCN. This is an estimate as there is NO hard relation between a timestamp and an SCN. RMAN is making an estimate. Especially when a timestamp is used which is close to the end-time of the backup, than this might be an issue. If the conversion to an SCN is generating an SCN which is BEFORE the end fuzziness of the datafiles in the backup, than the backup can NOT be used.
Example :
Backup start on T1 (SCN=1000) and ends on T2 (SCN=1050), than the backup can ONLY be used if the UNTIL SCN is 1050 or higher.
So if the 'UNTIL TIME T2' is converted to SCN 1045, than this backup will NOT be used.
V$BACKUP_DATAFILE / RC_BACKUP_DATAFILE is giving more info on this.
CHECKPOINT_CHANGE# corresponds with T1
ABSOLUTE_FUZZY_CHANGE# corresponds with T2. When ABSOLUTE_FUZZY_CHANGE# is NULL, than it is the same as the CHECKPOINT_CHANGE#
Note 414384.1 RMAN-06023 DURING RMAN DUPLICATE
There is a known RMAN issue with an incorrect UNTIL TIME conversion due to skipping the TIME-part.
Bug 9128954 RMAN IS SELECTING WRONG BACKUP WITH 'SET UNTIL'
4) Inactive thread
A RAC-database but also a Single instance database, can have multiple threads enabled. Each thread will have its own set of redologs files and will archive them.
In a thread (instance) is idle, or not started for some time, than an RMAN DUPLICATE could fail on it as it is looking for datafile or archived redologs from the inactive instance which will not be there anymore.
In addition, RAC becomes the different behavior from 11g. If a user execute 'ALTER SYSTEM ARCHIVE LOG CURRENT' on node#1 and,
even if node#2 stops, the archivelog of thread#2 is NOT archived.
it is archived on 10g even if node#2 stops.
There is an known issue related to inactive/disabled threads as handled in
Bug 9044053 RMAN DUPLICATE CAUSES RMAN-06457 WHEN USING 'UNTIL SCN' UNTIL STARTUP NODE#2.
Best practice is to drop the threads which are not used at all anymore.
SQL> select thread#, status, enabled, instance
from v$thread;
select group#, thread# from v$log;
alter database disable instance '<name>';
alter database drop logfile group <group#>;
5) Incarnation issues
5a) New incarnation added due to implicit resync
This is isssue is only relevant if a Flash | Fast Recovery Area (FRA) is being used.
If 1 or more restore and recovery attemps have been done for this database and the database has been opened with RESETLOGS,
than there might be archived redologs generated for this new incarnation of the database.
During the RMAN recovery-phase, RMAN will do an catalog of all the files in the FRA, and will catalog the new archived redologs aswell.
As they belong to another incarnation, the incarnation will be added (if not there) and will be marked as CURRENT.
The recovery will than look for archived redologs of a different incarnation than intended, as the CURRENT incarnation belongs to a prior RESETLOGS-operation
The best option is to remove all the archived redologs from the FRA belonging to an incarnation of a prior attempt.
Note 965122.1 RMAN RESTORE FAILS WITH RMAN-06023 BUT THERE ARE BACKUPS AVAILABLE
5b) Incarnations have the same RESETLOG_CHANGE#
There is a RMAN issue, which is causing different incarnations having the same RESETLOGS_CHANGE#. So there are multiple records in V$DATABASE_INCARNATION / RC_DATABASE_INCARNATION,
having the same RESETLOGS_CHANGE#. RMAN will loose track of which incarnation to use and might use an incorrect incarnation resulting in unexpected errors
Bug 5844752 RESTORE FAILS - CURRENT INCARNATION RESETLOGS SCN SAME AS PARENT INCARNATION
Note 793401.1 Rman Restore Fails With 'RMAN-06023: no backup ...of datafile .. to restore' Although Backup is Available
5c) Restoring from an none-current incarnation
The symptoms of this issue are closely related to the above issue (5b), but this time it is because the backups really belong to a different incarantion, than the current one.
Possible errors are :
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 1 found to restore
OR
RMAN-06026: some targets not found - aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile 1
OR
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA/pzrsa/datafile/system.288.585590761'
Check for more details :
RMAN RESTORE fails with RMAN-06023 or ORA-19505 or RMAN-06100 inspite of proper backups
Note 112248.1 RMAN-6026 RMAN-6023 During RESTORE Operation
6) Archivelog backup is missing
This issue is likely to occure during an RMAN-duplicate.
An RMAN DUPLICATE will use an UNTIL SCN recovery on the Auxiliary instance, to recover the database.
The end point of the recovery is specified by the UNTIL SCN, which is derived from the last archived redolog on the TARGET.
If this archive is NOT backed up, than the recovery on the AUXILIARY, and therefor the DUPLICATE, will fail on it.
There are 2 solutions for this.
- Make an explicit archivelog backup, before you start the RMAN DUPLICATE. NOTE : this will only help if there are no additional archives created after the BACKUP and before the DUPLICATE starts
- Specify an explicit UNTIL-clause, like UNTIL SEQUENCE. The following query might be usefull in that case:
SQL> select thread#, max(sequence#) + 1 seq#, to_char(max(first_time), 'dd-mon-yyyy hh24:mi:ss') first_time
from v$archived_log
where backup_count >= 1
group by thread#;
RMAN RESTORE fails with RMAN-06023 or ORA-19505 or RMAN-06100 inspite of proper backups
7) Corrupted or missing backups on disk
RMAN is automatically failing over to another backup, if there is an issue with the backuppiece during the restore.
Related errors might be :
ORA-19870: error reading backup piece
ORA-19587 error occurred reading %s bytes at block
ORA-19505: failed to identify file
If an older backup is found in the repository, than RMAN will continue the restore, but most likely will require more (older) archived redologs during the recovery.
Especially when the restore is done on another host and not ALL the backups are accessible on that host, than it may endup in a situation that RMAN will try to CREATE the datafile(s).
This is really an issue when this involves datafile 1, as that can NEVER be created, as it is done during a CREATE DATABASE
Example :
creating datafile fno=1 name=+DATA/v112/datafile/system.288.585590761
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/28/2010 20:12:34
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA/v112/datafile/system.288.585590761'
So double check why the initial restore failed and resolve that issue as the errors ORA-1180 or RMAN-6023 are just a result of the initial errors.
Note 429689.1 RMAN Restore Fails: ORA-19870 ORA-19587 ORA-27091 ORA-27067 RMAN-06026 RMAN-06023
Note 1271551.1 RMAN duplicate failing with ora-19870 ora-19612 then RMAN-06023
Note 562780.1 RMAN Duplicate fails with ORA-19870, ORA-19507, ORA-27029 and ORA-19511
Note 1300586.1 RMAN-6026 RMAN-6023 when restoring to new host
No comments:
Post a Comment