Re: Recover Help Please - needs more recovery

Ouch.

You say that the database has been 'read-only' for about two years? I'd hope that at the point it was decided to make the db read only - whether actually by making it read only or by changing working practice a backup (with real luck a cold backup) was taken. If that is the case *and* you still have the backup you should be able to recover using that backup. Your point 3 may of course exclude this possibility.

alternatively if you have an export of the database (or the relevant schema) then you can create a new database and import the data using the imp utility. it would then be safe to drop the original and now dead database.

failing either of the above my reading of your situation is that without outside data recovery help (read really big bucks) your database is now deceased without a valid backup.

good luck with either recovery or recreation.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************
"Paul Kohlmiller" <pkohlmil_at_best.com> wrote in message
news:H9Rq8.3799$Yb1.17276_at_sea-read.news.verio.net...


> I am reading your book and it may help me. But I wonder if any of the

> following are possible:

> 1. If I say recover database using backup controlfile until cancel, then

> type Cancel, it just says media recovery cancelled. If I leave out the

> "using backup controlfile" it simply tells me that I have to use it and

> quits immediately. Anyway to get it to stop insisting on the backup

> controlfile?

> 2. Is it possible take the system tablespace file offline, recover the

> datafile and bring it back online?

> 3. I don't have a set of backup files for the entire database at the point

> before things got screwy (that part in your book where you say "sack the

dba


> trainee" - well that's kind of me only I never exactly volunteered to be

dba


> trainee). Is it possible to create a new datafile(6.4.4 in your book) for

> the system tablespace that would cause me to lose all users but still be

> able to get at the user's data that is in another tablespace? Or is it

true


> that losing the system tablespace means all hope is gone?

> Thanks,

> Paul K

>

> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message

> news:a8g5cu$i0i$1_at_lust.ihug.co.nz...

> > Paul Kohlmiller wrote:

> >

> > > To prevent excessive laughter I won't tell you what I did to the

system


> > > tablespace datafile. Let's just say it was missing for a while and

then


> > > replaced and Oracle did not like that one little bit. I'm surprised it

> let

> > > me do that while the database was running but "root" can indeed do

> > > anything. I have tried various recover options including "backup

> > > controlfile". Now it insists that I say that all the time. I do have

> have

> > > redo logs and recover says it must use on of those file to get change

X.


> I

> > > do this but then it says it needs more recovery. So I do a recover

until


> > > change X+y. Again it says it needs a certain file which I give it but

it


> > > still needs more recovery. I can do this up to some number Z. When I

try


> > > to recover to change Z+1 or I say recover without specifying a change,

> > > Oracle gives error 283 - some internal error.

> >

> >

> > Your problem is I think that you have done a recover until a particular

> > SCN. That's an Incomplete Recovery. The first rule about incomplete

> > recoveries you need to learn is this: EVERY single data file gets

restored


> > from backup, not just the one you think is a bit dodgy. The entire set

of


> > data files has to be restored from a prior backup and then rolled

forward


> > by applying redo. At some point you stop applying redo. You are left at

> > that point with control files and redo logs at time X, and all datafiles

> > beautifully synchronised to each other at time X-something. You force

the


> > controlfiles and the log files back into synch with the datafiles by

then


> > performing an open database resetlogs.

> >

> > >

> > > Any advice? Here are some more details.

> > > 1. I don't care if it only recovers to a point 2 years ago. This was

an


> > > "abandoned" database and has been treating as read only for about two

> > > years. 2. No, I don't have any actual backups.

> > > 3. This is version 8.0.3 and we just want to get a few users off of

this


> > > database and unto a new system running a much more recent version.

> > > 4. The actual data that I want to get out of this database is probably

> not

> > > in the system tablespace.

> > > 5. Performing a recover until a particular time, say a year ago,

doesn't


> > > help because it claims it needs a recent change.

> >

> > Well it will do if you've bodged up the incomplete recovery procedure.

> > Restore all data files (but NOT the control files or redo logs) from

> > backup, get it to the mount stage. Issue the 'recover database until

> > cancel' command. Type cancel.Type alter database open resetlogs.

> >

> > (Incidentally, there's a Backup and Recovery book at my web site you

might


> > find useful in getting hang of the principles involved).

> >

> > Regards

> > HJR

> > --------------------------------------------

> > Resources for Oracle : http://www.hjrdba.com

> > ============================================

> >

> >

> >

> >

> >

> >

> >

> > > Thanks,

> > > Paul K

> >

>

>