Re: Recover Help Please - needs more recovery

"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?

Yup. Easy. Make sure that no data files exist from a time more recent than the control file. The only time I can think you need a 'using backup controlfile' is if you are restoring a binary version of the control file, and if it is from a time before the data files (or the online redo logs, I'll concede!). If it demands that you use it, you must use it: it doesn't ask for it unless it's unhappy with the lack of consistency between the control file and everything else.

> 2. Is it possible take the system tablespace file offline, recover the
> datafile and bring it back online?

Nope. You can never, ever, take the system tablespace offline. That's what a shutdown is for.

> 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?

No. The rule about creating a new data file is that you must possess all archives since the time that the file was originally created. Since SYSTEM is created at database creation, the chances of you having to hand all archives since then are pretty slim, I'd say.

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

Pretty close, I'm afraid. There's data in there which describes exactly what tables are made up of which columns, where they are stored physically, and so on. Lose that, and you're stuffed. You can't just unplug a datafile and expect to be able to get at its contents without something to tell you where to look and what to look for (which is precisely what 8i's transportable tablespaces is all about, of course). Oracle will have tools that can poke inside the datafile and extract raw data. Third party products, I'm sure, exist that do the same job (but don't ask me what they are -perhaps someone else here will know). It won't be pretty. And it will cost an arm and a leg. How deep are your pockets?

Sorry.

You mentioned the backup was taken years ago? Any idea how it was taken? Hot, cold, lukewarm?

Regards
HJR
> 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
> >
>
>
Received on Thu Apr 04 2002 - 00:19:11 CST