Re: Help: restore accidental deleted data from cold backup

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 :

1. Restore the 50 Gb datafiles. System tablespace must be within these files.
2. Mount the database.
3. query the views v$datafile and v$datafile_header to identify the datafiles which are missing (the Error column).
4. 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.
5. The database can now be opened and you can retrive your data.

Regards,

• Kenneth Koenraadt