RMAN-06023 Recover from Missing Datafile that is Never Backed Up
***Checked for relevance on 02-Oct-2014***
Problem Description
-------------------
Full restore via RMAN of a database when a datafile is missing and never
backed up results in the following errors:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: restore
RMAN-03002: failure during compilation of command
RMAN-03013: command type: IRESTORE
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 2 found to restore
Apparently file# 2 is never backed up.
Solution Description
--------------------
The following examples are still valid upto RMAN 11g. However, starting
from RMAN 10g, RMAN will create the datafile automaticly if no backup is
available.
In the following examples database recovery is done before the database is
opened. Of course you can adjust the scripts in order to open the database
as soon as possible and then recover the datafile(s) as needed.
A. The controlfiles do not have to be restored
-----------------------------------------------
Make sure the database is mounted.
In all cases you need the name of the missing datafile. Because the
controlfile is up-to-date you can retrieve this information with the
following query:
select name from v$datafile where file#=2;
=> /u02/oradata/target/users_target01.dbf
1. The missing datafile is the only datafile that needs recovery.
In this case you do not have to restore anything.
Archivelogs are restored automatically by RMAN as they are needed for the
recover command.
run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
recover database;
sql "alter database open";
release channel d1;
}
2. If other datafiles need recovery too, you have two choices:
- Specify each datafile that must be restored:
run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore datafile '/u02/oradata/target/sys_target01.dbf';
.....
recover database;
sql "alter database open";
release channel d1;
}
You can get a list of datafiles by querying v$datafile.
Instead of specifying the datafile by name, you can also specify it by
number:
restore datafile 1;
- Restore the complete database until just before the missing datafile
was created.
run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore database
until scn 118247 ;
recover database;
sql "alter database open";
release channel d1;
}
An apropriate SCN can be found by querying v$datafile:
select CREATION_CHANGE# from v$datafile where file#=2;
=> 118248
Lower this value by 1 or more.
Instead of 'until scn 118247' you can use one of the following:
until logseq 662 thread 1;
until time "to_date('Dec 15 2000 10:10:00','Mon DD YYYY HH24:MI:SS')";
You can find the the logseq or time by querying the alert.log.
Choose a time which lies BEFORE the creation time of the missing datafile,
or supply a logseq which was completed before the creation time.
3. If you want to recover the database to a time in the past, but after the
creation of the missing datafile (incomplete recovery / PITR=point in time
recovery), again you have two choices:
- Specify which datafiles must be restored.
Add an until clause to the recover command.
run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore datafile '/u02/oradata/target/sys_target01.dbf';
recover database
until scn 338325;
# until logseq 684 thread 1;
# until time "to_date('Dec 15 2000 15:12:00','Mon DD YYYY HH24:MI:SS')";
sql "alter database open" resetlogs;
release channel d1;
}
- Restore all datafiles until just before the missing datafile was created.
Add an until clause to the recover command as well. Note that that the
until clause for the restore command is different from the until clause
for the recover command!
Open the database with resetlogs.
run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore database
until scn 118247 ;
# until logseq 662 thread 1;
# until time "to_date('Dec 15 2000 10:10:00','Mon DD YYYY HH24:MI:SS')";
recover database
until scn 338325;
# until logseq 684 thread 1;
# until time "to_date('Dec 15 2000 15:12:00','Mon DD YYYY HH24:MI:SS')";
sql "alter database open" resetlogs;
release channel d1;
}
When you opened the database successfully with resetlogs, you must create
a new database incarnation record in the recovery catalog:
RMAN> reset database;
And of course it is necessary to make a full (cold) backup immediately!
B. The controlfiles must be restored too
-----------------------------------------
Recovering a database using a backup controlfile and having a missing
datafile that is never backed up, breaks down into several steps. RMAN is
not a suitable tool for every step.
Because you use an old controlfile the name of the missing datafile cannot
be queried from v$datafile. Because the datafile was never backed up
RMAN's repository has no knowledge of this datafile either.
1. Restore the controlfile - the database must be started NOMOUNT:
run {
allocate channel d1 type disk;
restore
controlfile to '/u02/oradata/target/control01.ctl';
replicate
controlfile from '/u02/oradata/target/control01.ctl';
release channel d1;
}
2. Restore datafiles to the moment BEFORE the missing datafile was created.
Restore archivelogs from some time before the oldest datafile up to the
moment to which you want to recover the database.
The possibilities are discussed above, for instance:
run {
allocate channel d1 type disk;
restore database
until logseq 5 thread 1;
restore archivelog;
# until logseq 9 thread 1;
release channel d1;
}
3. Use svrmgrl or sqlplus (8i only) to recover the database:
SVRMGRL> recover database using backup controlfile
Supply the names of the archives until you get the following error:
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 2: '/u02/oradata/target/users_target01.dbf'
Retrieve the filename that is added to the controlfile from v$datafile:
SVRMGR> select name from v$datafile where file#=5;
=> UNNAMED0002
4. Now you have all the information to recreate the missing datafile.
SVRMGR> alter database create datafile 'UNNAMED0002'
2> as '/u02/oradata/target/users_target01.dbf';
5. Resume recovering the database:
SVRMGRL> recover database using backup controlfile
Supply the names of the archives up to the moment you want to stop or
until you recovery is finished.
Open the database (noresetlogs/resetlogs)
When you opened the database successfully with resetlogs you must create
a new database incarnation record in the recovery catalog:
RMAN> reset database;
And of course it is necessary to make a full (cold) backup immediately!
Explanation
-----------
Automatic full restore is not possible when a datafile is missing and never
backed up. The controlfile contains all the information needed to recreate
the missing datafile. RMAN does not automatically recreate a missing datafile.
You must either recreate it manually before invoking RMAN, or add some
sql-statements to the rman script.
References
----------
Note:175768.1 "RMAN-6038 and RMAN-20003 After Alter Database Open Resetlogs"
Note:29430.1 "How to Recover a Database Having Added a Datafile Since Last
Backup"
===============================================================
Comments
Post a Comment
Oracle DBA Information