Re: help with recovery

"daveb" <davebest_at_SuPsAaM.net> wrote in message news:7uSdnewOU5KofP3d4p2dnA_at_speakeasy.net...
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:4060d1f0$0$3956$afc38c87_at_news.optusnet.com.au...
> >
> > "daveb" <davebest_at_SuPsAaM.net> wrote in message
> > news:EtOdnRJrCONcV_3dRVn-tw_at_speakeasy.net...
> > > We have a small test database (9.2.0.1 on Windows 2000) and the
> hard-drive
> > > just fried. Should be no biggie since we have create scripts for the
> > > schema. But now we find we need some of the test data...no db backup
on
> > > that, d'oh. But it turns out that we have a copy of the oracle
> directory.
> >
> > Strangely enough, some degree of precision is required when describing a
> > problem before a solution can usefully be offered. What oracle
directory?
> Do
> > you mean the "oradata" directory? If so, what -exactly- is in that
> > directory? Are there control files? Are there things which look like
redo
> > logs? Are there all the data files of the entire database before the
hard
> > disk failure?
> >
> > You know, just a few minor details would help!
>
> You're quite right! The exact scenario is that we had a test program
> running against the database, doing sporadic transactions. The test
program
> received TNS errors, the server machine was locked up, and reboots failed
> with hard-drive errors as does chkdsk. But I was able to attach the drive
> as a secondary on another machine and copy most of the Oracle directory,
> including the ora92\database (has the spfile), and oradata
sub-directories.
> So, yes there are control files and redo logs..

OK. Even better that you have your spfile, though that's going to cause a problem if it's got the wrong path to the control files in.

> Uh, something I, uh, read somewhere...uh, ah, mumble...yup, pretty
clueless
> of me <hangs head in shame>

Oh, I wasn't having a go! It's just that on several occasions just lately, creating a brand new database is the first thing that's been done, instead of trying to recover the old one! There's a slightly long thread at the Google Archives all about it.... I was there, I remember it well!!!!

Just in case Ron is still looking in <adopts tone of concerned surgeon>: David, can you assure me that there is nothing in the new database that you've created that is of value? If you lost that new database, would you (a) care (b) cry or (c) don't know? </adopt>

If you don't care, then blow it away and get rid of it and try this (done from memory and 'feel', but not in practice, so I might miss things, and you'll have to tell me if you encounter any errors along the way).

You've performed a new install of Oracle onto your new server. Restore the spfile from your backup to the Oracle/database directory on the new server
Restore the control files, datafiles and online redo logs from your backup to wherever you fancy on the server (let's call it \oradata for the sake of simplicity).

Create a new service. At the command line:

oradim -NEW -SID xxxx -STARTMODE auto

The "xxxx" there should be whatever the SID was on the old machine for this database. There's a privileged user authentication issue here which I'm hoping won't be an issue because this is Windows and you're almost bound to be a member of the ORA_DBA group already. But you may experience fireworks trying to get the service to start if you aren't.

Check that the new service is started.

In a DOS window (command line, whatever!), set your ORACLE_SID=xxxx:

set ORACLE_SID=xxxx

Then launch SQL*Plus:

sqlplus "/ as sysdba"

What you do next depends on what message you see at that point. If it says "connected to an idle instance", we've got work to do. If it says "connected", you should already be home and dry.

The concern I have is that your control files might not be in exactly the spot they should be compared to where the spfile thinks they should be. If that's true, then with or without an instance, you can issue this command:

create pfile from spfile;

...and that will give you a text-based init.ora you can edit to get the control_files path correct. With that done, back in SQL*Plus, you can say

create spfile from pfile;

...and you've now got a new spfile that correctly knows where your control files are.

So then you could try startup mount, and hopefully get the message "MOUNTED". Then if you try 'alter database open', you'll discover quick enough whether your *data files* are all in one piece, and where they should be. If it opens without a problem, congratulations. If it complains about being unable to identify datafile X, then you've got to tell the control file where your data files are actually at these days:

alter database rename file 'c:\oracle\ora92\oradata\system01.dbf' to 'd:\oradata\system01.dbf';
alter database rename file 'c:\oracle\ora92\oradata\undotbs01.dbf' to 'd:\oradata\undotbs01.dbf';

...and so on. Use "NAME" from v$datafile to show you where the control file *thinks* the data files are, and correct things by hand until it matches where they really are.

If you are genuinely missing some data files (you mentioned that you had "most" of the original database!) then do an 'alter database datafile X offline' to temporarily persuade Oracle not to mind about it. If it's SYSTEM that's missing, we're in trouble however! UNDO is a pain as well. Hopefully nothing too strenuous is missing.

Well, that's about it I think. I'm bound to have missed something, so give it a whirl, and stop at the first point where things don't go as described.

Regards
HJR Received on Tue Mar 23 2004 - 23:27:59 CST