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:c05T8.35460$Ca2.2011571_at_bin2.nnrp.aus1.giganews.com...
> > You're talking about needing point in time recovery, which must apply to
> > the ENTIRE database. Even if you had taken backups of datafile_1 and
> > datafile_2 at the same time, you can't simply restore them and move on.
> > They would have to be make consistent with the rest of the files in the
> > tablespace. You can't roll only a portion of a database back in time.
> > It's all or nothing.
>
> I understand that I would not be able to put both datafiles back and just
> move on, I was trying to simplify my example.
>
> How would you ever use a backup of an individual datafile? How would you
> ever use backups of individual datafiles that were not taken at the same
> time?
>

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.

> Since it's all or nothing as you put it (and I AGREE) I can not see a
single
> situation where your individual datafile backups could be used.
>

Unfortunately, perhaps, for your argument, there are people all round the world doing it on a nightly basis. And not losing sleep over it.

It's NOT an all or nothing matter if you are attempting to recover the database COMPLETELY (Sean carefully made the point that it's all or nothing for an INCOMPLETE recovery -where, for example, you want the recovery process above to stop at SCN 18300, and not go further, because the transaction at 18301 was a stupid one that did damage).

You know, I hope, that in 9i, it's possible to restore individual BLOCKS from within a datafile? (OK, you have to use RMAN to do the deed, but it's nevertheless true that you now don't even need to restore a complete datafile, still less a complete tablespace). And whilst you aren't running 9i, the point is that the application of redo can recover anything, be it a block, a file, a tablespace or a database. The principle hasn't changed since way back; only the granularity has.

HJR
> > As I said already, a good datafile backup is a good datafile backup.
> > There is no dependency between individual datafile backpus within a
> > tablespace.
>
> It's a good backup, but how would it ever be used???
>

See above.

>
>
Received on Fri Jun 28 2002 - 17:26:18 CDT