Re: recovery stops due to missing datafile

"Kerry Scott" <kerrysco_at_ameritech.net> wrote in message news:<oBzT6.35$O36.31905_at_nntp0.chicago.il.ameritech.net>...
> Actually, most of the following is incorrect, as long as you were in
> archivelog mode, datafile doesn't belong to the system tablespace, and you
> have the most recent controlfile. You need to "alter database create
> datafile...", and then recover. The recovery should continue by asking for
> the archivelog that was being used in your previous recovery (where the
> error occurred). You will not lose data, as the data is contained in your
> most recent archive log files, and will be recovered.
>
> However, always backup at least the controlfile and tablespace after adding
> a datafile.
>
> Kerry
>
> "Mark D Powell" <mark.powell_at_eds.com> wrote in message
> news:178d2795.0106060631.4f49a03c_at_posting.google.com...
> > "Rolf Sapler" <bud_socks_at_gmx.net> wrote in message
 news:<9fl18b$77f$01$1_at_news.t-online.com>...
> > > Hi,
> > >
> > > we have started a recovery and after a few minutes the recovery process
> > > stopped due to
> > > a missing datafile.
> > >
> > > Unfortunately i do not have this datafile on a backup not even a
 controlfile
> > > when this datafile
> > > was created.
> > >
> > > (Thank God its just a test scenario)
> > >
> > > The database is in mount status - so no way to create a tablespace or
 add
> > > datafiles.
> > > What can i do ?
> > >
> > > Thanks
> > >
> > > Rolf
> >
> > You are now in a position where you can not recover your database
> > without data loss, but you can continue the recovery process providing
> > the missing datafile is not the system data file.
> >
> > You can use the alter database command while in mount to remove the
> > missing datafile from the database then perform recovery. Oracle will
> > skip this file and the tablespace to which it belongs will not open
> > and will have to be dropped including contents. Hence, the data loss
> > but the rest of the database will be OK.
> >
> > If the missing file belongs to an index tablespace you can query the
> > dictionary once recovery is complete to generate a list of indexes and
> > source that need to be recreated, then drop the tablespace including
> > contents, recreate the tablespace, and build the indexes. You will
> > probably have to disable most of your database constaints while you do
> > this.
> >
> > Better yet the missing file belongs to your temp (sort segment)
> > tablespace so all you have to do is recreate the tablespace, but if
> > the file is part of an rbs tablespace you will have some missing rbs
> > segments which complicates things as the rollback phase of recovery
> > will fail. You will need the underbar parameter to stop Oracle from
> > using affected rbs segments. Before using an underbar parameter you
> > should probably contact support to make sure you have the right
> > parameter for the version you are running and to make sure you are
> > using the right parameter for your situation. In this case you should
> > still be able to recover but you could end up with some data
> > inconsistencies.
> >
> > Most important of all is that you look at your backup and recovery
> > procedures to make sure every needed file is available.
> >
> > -- Mark D Powell --

Kerry, If the data file that is lost existed before the oldest archive log being applied and had objects that were not created and/or updated during the period of time covered by the reapplied redo logs then you have a data loss situation. Data can not be recreatd from the redo logs if it is not there to begin with, and only change data is in the archived logs so what happens to the pre-existing object data? However, if the tablespace was created during the time held in the archived logs you have managed to fully recover so recreating missing datafiles is a better technique, but it was not available to us when had to follow the above plan and I did not think of it when I posted.

• Mark D Powell --