Re: Loss of One of Many System Datafiles

First off best luck. I’m sure there is a life lesson in there somewhere, there always is.

At first glance I think your approach sounds solid. Oracle wouldn’t be able to use that datafile that got restored since, for one it’s scn needs to be rolled forward and two that datafile is probably already marked as offline. Shutting the instance down, then startup mount and recover datafile on that file seems like the logical solution. If that doesn’t work for some reason I’d try to restore just that one file again. Ideally the recover datafile will roll the file forward to current and you’re able to open the database. Of course if you have backed up and deleted archive log files since the backup you will need to restore them as well.

Kenny

> On Dec 2, 2015, at 8:37 PM, David Barbour <david.barbour1_at_gmail.com> wrote:
>
> Oracle 10.2.0.5 HPUX 11.23 - And This is not Going to Upgraded - stuck with it due to the SAP version and resistance to change.
>
> This is an old SAP database that has been around for years. The system tablespace now has 6 different datafiles in various filesystems/directories. Today about 11:30 or so as far as I can tell, one of these was accidentally deleted. Our backup admin restored the deleted file and I became aware of a problem about 3PM when OEM started sending me alerts about ORA-0600 errors.
>
> The application and database are currently operating, the controlfiles are good, and no datafiles were deleted so I'm not going into panic mode or anything, I think it's a relatively standard recovery scenario, but just want some affirmation.
>
> We've got a good backup from last night as well as all the archivelogs from before the backup through now.
>
> Here's what I'm thinking:
>
> First, stop and restart the database at which point, since we’ve restored a piece of the system tablespace, I’d expect to get the ‘needs media recovery’ error and then we’d apply the archivelogs.
>
>
> If that didn’t work, make copies of all the datafiles, ask the backup admin to restore all 6 files, then proceed with startup and recovery.
>
>
> If that doesn’t work, when the hangover wears off, restore the database from last night’s backup and roll forward using the archive logs.
>
>
>
> Feedback appreciated.
>
>

--
http://www.freelists.org/webpage/oracle-l