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

"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 Fri Jun 28 2002 - 22:34:39 CDT