Rman-06023 no backup of datafile -- when restoring from full backup

Hi All

We have rman full backup (Level 0) of a 7 TB database taken before 3 months and we are in the need of restoring it in a different locaition.

During the backup, we have created one datafile and it is not allowing us to proceed further with below error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/09/2008 22:17:14
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 2141 found to restore


We are using until scn option -- the scn of the full backup completion time.

We tried adding the datafile with alter database create datafile option too, then also the same is failing.

We have also the following incremental backup available during that backup also we created some more files due to the database growth.

Please suggest the available option to complete the recovery.

We have already restored the controlfile and mounted the database.

Thanks
Giri

the file doesnt exist, cant restore what isnt there

Is there an option to skip that file?

If I select the until scn option prior to the creation of the file, the restore asks for the prior full backup which is not available with us.

Please Let me know how to proceed to restore.

Thanks
Venkatagiri

Hi

Whats the output of
list backup of datafile 2141 summary

regards
Hrishy

Hi

RMAN> list backup of datafile 2141 summary;

RMAN>


No output. Because this file was created after 75% of the completion of the database. I also restored the controlfile which is the scn of the backup completion!.

Thanks
Venkatagiri

restore the controlfile used when the backup was taken

I have restored the latest available control file and now the backup is listed.

RMAN> list backup of datafile 2141 summary;



List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
2406874 B 2 A SBT_TAPE 06-NOV-07 1 1 INCREMENTAL_ORACLE_BACKUP

Can I start the restore/recovery with until scn (Upto the backup of this piece) ?

THanks
Venkatagiri

Hi

Its not the latest controlfile rather the control file which was backed up as part of the full backup when you took it.

see daveys reply

regards
Hrishy

Do you have all archived logs since datafiles were added?
If this is the case I think you can still recover.

Another possibility is to restore only tablespaces which were not effected by adding datafiles and rebuilding control file.

Quote:

---

Originally Posted by BV1963

Do you have all archived logs since datafiles were added?
If this is the case I think you can still recover.

Another possibility is to restore only tablespaces which were not effected by adding datafiles and rebuilding control file.

---

or do the correct thing of restoring the correct control file and doing the restore normally

create an empty datafile and if archivelogs are available from the time the datafile has been created , recover the datafile.

I have restored the correct control file which displays the backup of this datafile.

Now trying to restore the database with recovery with until scn clause.

I will update if I am facing further problems.

Thanks

I found the problem. But not solution!

The backups are available as below.
RMAN> list backup of datafile 2141;


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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
55820 Incr 2 172M SBT_TAPE 00:23:06 06-NOV-07
BP Key: 55820 Status: AVAILABLE Tag: INCREMENTAL_ORACLE_BACKUP
Piece Name: bk_56419_1_637942006
List of Datafiles in backup set 55820
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2141 0 Incr 1765344875060 06-NOV-07 /usr/local/pkg/oracle/admin/cpg/links/cpg_cf_tpts_002_07.dbf



RMAN> list backup of datafile 2142;

using target database controlfile instead of recovery catalog

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
55820 Incr 2 166M SBT_TAPE 00:23:06 06-NOV-07
BP Key: 55820 Status: AVAILABLE Tag: INCREMENTAL_ORACLE_BACKUP
Piece Name: bk_56419_1_637942006
List of Datafiles in backup set 55820
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2142 0 Incr 1765344875060 06-NOV-07 /usr/local/pkg/oracle/admin/cpg/links/cpg_ca_ipts_005_37.dbf


But before the scn of the backup of that datafile(1765344875060 ) two more datafiles were added. (datafile addition was a ever happening one in this datamart database).

SO Until scn of restore to 1765344875060 will definetely complain that other two datafiles added were not available until that scn.

I cannot offline that tablespace since that tablespace contains many datafiles.

Please let me know how to proceed.

Thanks
Venkatagiri

Just to update the status:

I have generated the below command and its running now. Hope it will resolve the issue. Please update if any one has any comment!

RMAN> run {
2> allocate channel c1 type 'sbt_tape' ;
3> allocate channel c2 type 'sbt_tape';
4> allocate channel c3 type 'sbt_tape' ;
5> sql "alter database create datafile
6> ''/usr/local/pkg/oracle/admin/cpg/links/cpg_cf_tpts_002_07.dbf'' " ;
7> restore
8> datafile
9>'/usr/local/pkg/oracle/admin/cpg/links/cpg_cf_tpts_002_07.dbf';
10> restore database until scn 1765181042107;
11> release channel c1;
12> release channel c2;
13> release channel c3;
14> }
15>

Misinterpreted Hrishy's input.

Now restoring the correct controlfile taken at the time of full backup.

After this -- what kind of restore I have to apply? is it a
restore database or restore database until scn?

Thanks

Hi

You can do both.

Try

Code:

---

RMAN>connect target /
RMAN>startup mount ;

 
Your control file is now available
open another sqlplus session and
determine the SCN using query below

select sequence#,first_change,first_time
from v$archived_log
order by first_time
/

then back in rman do

RMAN>run{
set until SCN  #first_change;
restore database;
recover database;
}

RMAN>alter database open resetlogs;

---

regards
Hrishy

Do you mean, I have to take the last (Max) scn number of the above query?
32133 1765157586325 04-nov-2007-13-36
32134 1765157684954 04-nov-2007-13-44
32135 1765157713564 04-nov-2007-13-47
32136 1765157775287 04-nov-2007-13-56

So in my case( i pasted few of the end lines for the query), I will be taking until scn 1765157775287 . Please confirm
THanks

Hi

After mounting the controlfile do

SELECT archivelog_change#-1 SCN
FROM v$database

that would be SCN to which you can do your incomplete recovery

regards
Hrishy

I believe what you need to do is to recreate the datafile and perform a database/tablespace recovery-provided you have all your archived logs intact.