recover from missing datafile that is never backed up

8/4/2019 Recover From Missing Datafile That is Never Backed Up

1/6

Recover from Missing Datafile that is Never Backed Up (RMAN-06026)

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

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.


2/6

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 {




restore datafile '/u02/oradata/target/sys_target01.dbf';

.....

recover database;


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 {




3/6


restore database

until scn 118247 ;

recover database;


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 {




restore datafile '/u02/oradata/target/sys_target01.dbf';

recover database

until scn 338325;


4/6

# 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 {




restore database

until scn 118247 ;



recover database

until scn 338325;



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!


5/6

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 {


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 {


restore database

until logseq 5 thread 1;

restore archivelog;


release channel d1;

}

3. Use svrmgrl or sqlplus (8i only) to recover the database:

SVRMGRL> recover database using backup controlfile


6/6

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.

recover from missing datafile that is never backed up

Documents