Re: Help: restore accidental deleted data from cold backup

(Kenneth Koenraadt) wrote in message news:<3d07386f.3919886_at_news.capgemini.se>...
> On 11 Jun 2002 17:14:34 -0700, ewong_at_mail.com (Ed Wong) wrote:
>
> >Hi,
> >
> >One of the developers accidentally deleted some data two weeks ago and
> >we need to restore it. We do cold backup(w/archive) once a week. So
> >basically we can restore a two-week old copy to a different server and
> >retreive the data back. We can't restore to the existing database
> >since we will lose 2 weeks data and we don't want any downtime.
> >
> >The database is 300GB and there is one big table 250Gb located in four
> >partition tablespaces. I DON'T NEED to restore data from this big
> >table. My question is: Can I simply restore the remaining 50GB
> >datafiles? I understand that Oracle won't open if some files are
> >missing. But can I have Oracle logically drop the 250GB
> >tablespace/datafiles after "startup mount" and before "database open"?
> > Anyone has experience?
> >
> >Thanks,
> >ewong
>
> Hi Ewong,
>
> Assuming you are running 8i+, do the following :
>
> a) Restore the 50 Gb datafiles. System tablespace must be within these
> files.
> b) Mount the database.
> c) query the views v$datafile and v$datafile_header to identify the
> datafiles which are missing (the Error column).
> d) Take ALL the missing files offline, i.e.
> alter database datafile '/u01/obsolete_file_01.dbf' offline;
> alter database datafile '/u01/obsolete_file_02.dbf' offline;
> etc.

In Oracle 7, you actually have to drop the datafiles you don't need,

   alter database datafile '/u01/obsolete_file_01.dbf' offline drop; Otherwise Oracle will inssist on synchronizing these files in anticipation you might bring them back online at a later time.

I used to do this in a slightly different way: Mount the database (this only loads the old control file). Alter database backup controlfile to trace. Edit the dumped trace file to remove the files you don't need. You can   then use this script to recreate your control file. Open database nomount and recreate control file. Recover database(I believe you can roll forward at this point if you choose to). Open database resetlogs.

Seems a lot of steps but it really won't take very long. Never had to do this in Oracle8i so I don't know things are any different now.

> e) The database can now be opened and you can retrive your data.
>
> Regards,
>
> - Kenneth Koenraadt
Received on Wed Jun 12 2002 - 16:05:38 CDT