Re: Oracle 8i (8.1.7.0.1) + Redhat Linux 7.2 = Cannot create tablespace file > 2 gb

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:dcNT8.24722$Hj3.76831_at_newsfeeds.bigpond.com...
> Hi Howard,
>
> Just want to clarify a statement you made at this point in the thread
> regarding the fact that "*all* prior backups and archives are totally and
> utterly worthless" after you open a database with resetlogs. This is not
> entirely correct (since I think Oracle 7.3ish).
>

I know, I know. If you search my contributions on this topic in the past at Google, you will see that I usually add a "without a great deal of effort' qualifier. 'Recovery Through Resetlogs': "Recovery Despite Resetlogs" is more accurate. Possible but nasty, as you say.

Regards
HJR
> Although I would agree with you that you really should take a fresh backup
> ASAP after opening the database with resetlogs, there is a process by
which
> you can still fully recover the database whilst using the previous backup.
> Although somewhat messy, it can be done.
>
> 1) Shutdown the db (not nice but you're got no choice at this point)
> 2) Fully backup your existing database (at least the control files but
> probably the rest of the db as well. Lets face it, you're having a bad day
> here. And don't overwrite your previous backup !)
> 3). Restore all datafiles and the controlfile from backup (note the back
up
> prior to the resetlogs).
> 4) recover database until change = scn at the time of the resetlogs (this
> is documented in the alert log) using backup controlfile. You now have
> recovered the database to the point when the resetlogs command was issued.
> 5) Restore just the controlfile from the current state of the database (as
> backup in step 1)
> 6).now fully recover the database. The current controlfile documents the
> fact that archive log starting at number 1 (and not say 1001) now needs to
> be applied.
>
> You have just fully recovered the database using a backup and redo logs
> prior to the resetlogs.
>
> Now this all very messy as I said and requires an incomplete recovery
> (meaning the db is down) followed by a complete recovery so definitely a
> fresh backup would have meant a far simpler recovery scenario. However if
a
> fresh complete cold backup means unacceptable availability issues or the
> database problem occurred during your attempted hot backup (ie. you are
> having one hell of a bad day), then this is at least a way out.
>
> Keep up the good work.
>
> Regards
>
> Richard
>
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:afj9ug$guj$1_at_lust.ihug.co.nz...
> >
> > "Joe Salmeri" <JoeSalmeri_at_comcast.net> wrote in message
> > news:gA9T8.40403$Ca2.2223995_at_bin2.nnrp.aus1.giganews.com...
> > > Howard,
> > >
> > > Thank you! That is the level of detail that I needed to better
> understand
> > > your position.
> > >
> > > Of the 3 backup options available (cold, hot, export) I have always
been
> > > fortunate in being able to do a cold backup as the primary backup and
an
> > > export as the fallback option, therefore I have not really had the
need
> or
> > > opportunity to work with the hot backup options.
> > >
> > > One question regarding your example on Wednesday when data file 1
blows
> > up:
> > >
> > > After restoring data file 1 is at SCN 17000 and data file 2 (and the
> rest
> > of
> > > the database) is at SCN 18634.
> > >
> > > Before you recover datafile 1 you realize that a change was made to
the
> > > database after the data file 1 backup (SCN 17000) and before the
> database
> > > was at SCN 18634. Let's say that you determine that you want to
restore
> > to
> > > 9am on Wednesday and the SCN at that time was 17985.
> > >
> > > From your example I can see that data file 1 can be recovered from
> 17000
> > up
> > > to 17985 (9am on Wednesday) but how is data file 2 handled since it
has
> > an
> > > SCN greater than the point in time that you want to recover too? Do
you
> > > need to find a backup of datafile 2 that is BEFORE the Tuesday backup
> (SCN
> > > 18000) or will the recovery process back out those additional changes?
> >
> > No, you can only ever roll a datafile FORWARD. Whenever you feel the
> > temptation to roll a file backwards, that's a cue for an 'INCOMPLETE
> > RECOVERY'. That means you restore every single data file (the 'all or
> > nothing' option Sean was talking about') from the last available
backups,
> > and roll them forward until time 17985.
> >
> > That gets every datafile consistent with every other.
> >
> > But, unfortunately, that doesn't get the data files consistent with the
> > Control File (which is still there, happily thinking the time is 18634).
> >
> > Therefore, you issue the magic command 'alter database open resetlogs'
at
> > the end of the recovery phase. That gets all the datafiles consistent
with
> > the controlfile by the simple expedient of setting the clock back to 0.
> > (Actually, and just in case Sean M is watching, the SCN is itself not
> reset,
> > the log sequence number is reset. But that's a technical truth: the
> > essential point is, the database now thinks time has just started).
> >
> > And since it thinks time has just begun, it's oblivious to the existence
> of
> > prior backups, or prior archives. Therefore, *all* prior backups and
> > archives are totally and utterly worthless. They cannot be used to
recover
> > what is, in effect, a new database. Therefore, you are supposed to
> > immediately shut down the database and start a new cold backup.
> >
> > However, that's a pain, and lots of people do new *hot* backups. Which
is
> > fine, so long as the backup cycle completes successfully. Until it does,
> you
> > have no protection for your database at all. (And again, just in case,
it
> is
> > true that you *can* use prior backups and archives in extremis, but it
> isn't
> > pleasant, and you don't want to go there if you can avoid it).
> >
> > So resetlogs are EXCEEDINGLY expensive. To the point where the only time
> > you'd ever want to use an incomplete recovery is when everything else
has
> > failed.
> >
> > Incompletes are very rare events. They are awkward, difficult to
perform,
> > and at the mercy of Sod's Law ("If anything can go wrong, it will"). You
> > don''t want to go there unless absolutely necessary.
> >
> > But if you do, it's not a sweat. Oracle copes. There's not a recovery
> > situation it *can't* cope with, one way or another. Which sounds like
> > marketing bullshit but (and here's the true majesty of the product)
isn't.
> >
> > HJR
> >
> >
> >
> >
> > >
> > > Since the other data files would also be at SCN 18634 at the point
that
> > you
> > > recovered data file 1 I would expect that they would be in the same
> > > situation as data file 2? (either it backs out the changes or I need
to
> > > find a older backup of those data files too).
> > >
> > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > > news:afins3$uqh$1_at_lust.ihug.co.nz...
> > > > OK, here goes. One tablespace, two backups:
> > > >
> > > > I backup a file on Monday night. It's SCN is 17000. During Tuesday
> day,
> > > > transactions are performed, pushing the SCN onwards. All these
> > > transactions
> > > > are logged, of course, so the archives produced that day contain
SCNs
> > > 17001,
> > > > 17002, 17003 and so on.
> > > >
> > > > I backup a file on Tuesday. It's SCN is 18000. During Wednesday day,
> > > > transactions are performed. The archives contain the transactions
with
> > > SCNs
> > > > 18001, 18002 and so on.
> > > >
> > > > On Wednesday afternoon, with the database (and hence data file 2)
now
> at
> > > SCN
> > > > 18634, data file 1 blows up. When you restore it from backup, it is
at
> > > time
> > > > 17000. That's not consistent with the rest of the database of
course,
> so
> > > you
> > > > can't work with that tablespace. So you issue the command 'recover
> > > datafile
> > > > 1' (or 'recover tablespace DATA'). That causes Oracle to retrieve
all
> > > > transactions from the archives, starting with 17001. As it applies
> > > whichever
> > > > transactions actually affected that datafile, datafile 1 becomes
more
> > and
> > > > more up to date. Eventually, it stops applying transactions from the
> > > > archives, and starts lifting them from the online logs. File 1 is
> still
> > > > getting transactions re-applied to it. When you reach the end of log
> > > marker
> > > > in the current online redo log, the last possible transaction
> affecting
> > > any
> > > > datafile has been applied. Data file 1 is now at SCN 18634. It's
> > > consistent
> > > > with the rest of the database, and is fully functional. Not a single
> > > > committed transaction has been lost.
> > > >
> > > > Had it been file 2 that blew up, the same procedure would have been
> > > > followed, with the only difference being that Oracle would only have
> had
> > > to
> > > > roll the file forward from SCN 18000 -so less redo would have been
> > needed
> > > to
> > > > be applied. Recovery would have been quicker, it's true, but
otherwise
> > the
> > > > process is the same.
> > > >
> > > > Therefore, backups taken at different times, but either one is fully
> > > usable,
> > > > *provided* every single piece of redo (archives and online) is
> available
> > > > from the time that the *first* file went into backup mode.
> > >
> > >
> > >
> >
> >
>
>
Received on Mon Jul 01 2002 - 04:00:58 CDT