Re: Problem after restore and recovery...HELP!

You are probably facing the Punishment that Oracle has to offer for adding Datafiles during the HotBackup.
Did the 'another DBA' verify the Status of the Datafile after he added when Online Backup was going on ?

You may be correct in guessing that it's Status would have been 'Offline' all along.

On the 2nd Database , Query it's Status in both DBA_DATA_FILES and V$DATAFILE . If they are Offline or 'Needs Recovery' and if you are sure that all your Database Objects are fine(Do an Export Backup to verify all the Objects in that Tablespace and also You can query like

select segment_name from dba_extents where file_id=198; ..I'm not sure if this will show anything becos the File is offline..but you can try it anyway) then
you can drop the Datafile using
>alter database datafile '<file_name>' offline drop;

But you cannot remove this File from the Data Dictionary(v$datafile will still show this file as being ' Needs Recovery' unless you REcreate the Tablespace which is upto you to decide..

Hope this helps

-Thiru

chiuljnk_at_my-deja.com wrote:

> I backed up DB1 in online mode.
> During the online backup, another DBA added a datafile. This datafile
> was not backed up.
> At the end of the online backup, I backed up the archive logs generated
> during the online backup. Also, I backed up controlfile to trace
>
> I then restored the online backup of DB1 to another server, renaming it
> DB2. Edited trace to reflect new name.
> Because new datafile was not part of the backup set, creating the new
> controlfiles using trace sql script failed.
> Edited trace again to remove line referencing new datafile.
> Created new controlfiles using trace successfully.
>
> Rolled forward a bunch of archivelogs.
> Error encountered.
> File #198 added to control file as 'UNNAMED0198'. Originally created as:
> '/oracle/DB1/data1/data37'
> Media Recovery failed with error 1244
>
> Entered command: alter database create datafile 'UNNAMED0198'. This
> created a 10M file in /oracle/DB1/dbs directory called UNNAMED0198.
>
> Continue rolling forward until all archivelogs (generated during backup)
> are applied. No more errors.
>
> Entered command: alter database archivelog and alter database open
> resetlogs. Success.
>
> PROBLEM:
> For some reason, added datafile added is somehow corrupted.
> It has status OFFLINE. But I can't bring it online because I get the
> error:
> alter database datafile '/oracle/DB2/data1/data37' online
> *
> ORA-01190: control file or data file 198 is from before the last
> RESETLOGS
> ORA-01110: data file 198: '/oracle/DB2/data1/data37'
>
> I can't drop the datafile.
> Database is functioning without error, but I can't bring tablespace DATA
> into backup mode for online backups.
> I am guessing that after creating the datafile, it was offline the
> entire time, even through the roll-forward. That means that there is no
> data in the datafile. How can I get rid of this datafile without
> dropping the entire tablespace?
>
> Thank you very much for your assistance.
>
> Regards,
> Larry Chiu
> chiuljnk_at_my-deja.com
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Wed Aug 25 1999 - 14:22:13 CDT