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

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).

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).
3. 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.
4. 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 Sun Jun 30 2002 - 19:24:04 CDT