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:YN3T8.176384$_j6.9145808_at_bin3.nnrp.aus1.giganews.com...
> The backup you suggest is worthless to me.
>
> If I have 5 2gb files associated with my tablespace and I backup the files
> at different times (on different days as you suggest) and a table has
> extents in more than one of the datafiles, the backup is worthless because
> it has lost it's integrity and consistency. Once you put the tablespace
in
> backup mode you need to backup ALL of the datafiles associated with it at
> the same time.
>

I can't believe that you, with your 7+ years of experience, actually believe this tosh. Provided you keep all archives from the time the tablespace first went into backup mode, you can back up what you like, assured that you can recover anything. This is pretty basic stuff.

> Consider the following example:
>
> The employee table has a supervisor_id column (FK) which points back to an
> employee_id. The employee table has extents in datafile_1 and datafile_2.
> employee_id 1 has a supervisor of 5287. The row for employee_id 1 is in
the
> extent in the datafile_1 and you back it up on Monday. employee_id 5287
> (employee_id 1's supervisor) is in the extent in datafile_2. After
Monday's
> backup you change employee_id 1's supervisor to employee_id 6386 and you
> DELETE employee_id 5287 because he no longer works for the company.
Tuesday
> night you backup datafile_2.
>
> The backup created is invalid because it does not have an complete and
> accurate copy of the employee table data. Since physical backups do not
> allow you to recover individual database objects you would not be able to
> fix this without manual intervention.
>

You are quite correct that you don't use them to recover individual objects. Only export/import can do that. But the backup is perfectly valid and useable, and the entire tablespace can be made consistent again, by the restoration of one or other data file (which ever is the problem) and applying all necessary redo. Strangely enough, "redo" means re-perform the original transactions. So you can throw me any DML you like, and it remains the case that recovery can re-perform it and hence get you a useable database.

Oracle doesn't *guarantee* no data loss for nothing, you know.

> If you attempted to restore datafile_1 and datafile_2 you would have the
> following problem (among many others I am sure):
>
> datafile_1 has a row for employee_id 1 with supervisor 5287 (that no
longer
> exists) and
> datafile_2 has a row for employee_id 6386 (employee_id 1's NEW supervisor)
> but no employee_id of 5287 (employee_id 1's OLD supervisor)
>

You just don't understand Oracle backup and recovery procedures very well, do you?

HJR
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:afif43$ndi$1_at_lust.ihug.co.nz...
> > You've rather missed the point, haven't you.
> >
> > If you have 5 2Gb files, you can back 1 of them up on Monday, one on
> > Tuesday, one on Wednesday, one on Thursday and one on Friday.
> >
> > Of 2 of them on Monday, have an evening off on Tuesday, two on
Wednesday,
> > watch the Kumars on Thursday night, and finish the cycle off on Friday.
> >
> > Or 4 of them on Monday. Have the rest of the week in Marbella. Finish
the
> > job on Friday.
> >
> > If you have 1 10Gb file, er, you have to back it all up on Monday.
> >
> > So yes, same amount of data. But infinitely more options with multiple
> files
> > than with just one.
> >
> > HJR
>
>
>
Received on Fri Jun 28 2002 - 17:06:51 CDT