Re: So, did I do good? (Did my first Oracle recovery, seems good... thoughts?)

Thomas T wrote:
> Wow, am I glad I implemented archivelog mode on our old Oracle 7.3 server!
> Here's what happened- did I do good?
>
> Our Novell server was frozen this morning... had to hit the reset key
> (yikes). When Oracle tried to come up, got message similar to:
>
> ORA-1113: file 11 needs media recovery
> (listed file name)
>
> So first, I copied the bad file out to another directory, deleted it from
> the database directory- and promptly wondered if I deleted the right file.
> (Didn't write the filename down.) I did a startup mount, queried
> v$datafile, and yes, I had the right filename for file 11.
> Dismounted/shutdown Oracle, and started restoring the file from tape. Then
> I realized I should've done a cold backup of the bad database first. I
> deleted the "restored" file, moved the original bad file Back to the
> database directory, and did a backup. Only problem- now the file I had
> moved out and moved back in had a different time stamped on it then the rest
> of the data files.
>
> After the "get a backup before I do any worse damage" job was done, I
> realized that the Novell server was still out of whack... some digging
> uncovered a corrupt license file. No wonder that non-Oracle users couldn't
> get to their files! I couldn't even get to Oracle files! So I fixed the
> license file, and was able to log in and see all my directories again.
>
> I figured I'd leave the "original" file 11, the one that needed "media
> recovery", in the database directory, and see what I could do with it.
>
> I went to the database, and did a startup mount. Checked v$datafile to make
> sure that none of last night's tablespaces were still in backup mode. They
> weren't... that'd be too easy.
>
> So I said alter database open; again, hoping for success... of course,
> none... same ora-1113 error.
>
> So I did a recover database, and hit enter on the suggested redo logs.
> Oracle applied 5 of them, and finished.
>
> I did a shutdown immediate, and then a startup- no errors. Did a query of
> v$datafile, and everything came up okay. I did a query on one of our tables
> with a timestamp field, and had current entries from the morning.
>
> I just did another shutdown immediate, and I'm taking a full cold "after
> recovery" backup as I type this.
>
> I'm hesitant to let anybody back on yet... everybody's gone home so I can do
> a few more queries to see if the data's okay. Did I do good or bad by not
> bringing out an old file #11 from tape? Even though Oracle wanted media
> recovery on the file, I bet it probably just had an SCN that wasn't in sync
> with everything else; and by doing a recover database, Oracle was able to
> sync the SCN with everything else.
>
> But that's just my guess. Did I guess right? Or should I restore
> everything from my "get a backup of the bad database" set, then go to an old
> tape to restore my last "good" copy of file #11, and then do a recover
> database?
>
> Hopefully I didn't make that confusing... basically, I'm wondering if I
> should've brought file 11 from it's last good hot-backup, or if it was okay
> to have performed recovery on the "needs media recovery" file.
>
> Thanks!
>
> -Thomas
>
>

you should be okay but if i were your, i would have done it this way

i'll start from "had to hit the reset key button..."

• startup fails with ORA-1113: file 11 needs media recovery
• startup mount
• since this had crashed early morning, i'd double check its not in backup mode. if yes, then alter database datafile '..' end backup;
  • try to open the database
• if none were reported 'ACTIVE' in v$backup, then
  • recover datafile '..' or rather recover database;
  • provide necessary arch files to bring up the datafile 11 up to speed with control file scn.
  • alter database open.
• still same error, then shutdown abort
• backup stuffed db, just in case you might need it later
• restore all datafiles, archive logs from latest backup
• startup mount
• recover database, then open it.
• Just to be safe, i would backup at this point.

hth,

-- 
-Stan

To reply remove the obvious ones from my email address