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

[monto1]

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

[engiri]

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

[engiri]

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

[engiri]

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>

[engiri]

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

[hrishy]

Hi

You can do both.

Try

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

[engiri]

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

[hrishy]

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

[crimpe]

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.