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

Joe Salmeri wrote:
>
> The backup you suggest is worthless to me.

It shouldn't be.

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

No you do not. A valid datafile backup is a valid datafile backup. Hot or cold, from 3 days ago or 3 weeks, as long as the backup is valid and you have all necessary archives, it matters not a bit whether other datafiles in the same tablespace were taken 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.

You're missing the point of Oracle backup and recovery. You're assuming that you'd need to restore datafile_2 from Tuesday night's backup, which is AFTER the problematic DML. Of course, that won't work. You have to restore a backup of datafile_2 from BEFORE the DML. But that does *not* mean you had to have backed up datafile_2 at the same time as datafile_1. You could have taken the backup of datafile_2 from A WEEK AGO TUESDAY, and rolled forward to the point just before the problematic DML.
> 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'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.

As I said already, a good datafile backup is a good datafile backup. There is no dependency between individual datafile backpus within a tablespace.

Regards,
Sean M Received on Fri Jun 28 2002 - 16:25:13 CDT