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

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?

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:12:12 CDT