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

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.

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.

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)

"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 - 15:37:12 CDT