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

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Sat, 29 Jun 2002 12:53:43 +1000
Message-ID: <afj7hl$epa$1@lust.ihug.co.nz>

"Joe Salmeri" <JoeSalmeri_at_comcast.net> wrote in message news:Fg_S8.173467$_j6.8951031_at_bin3.nnrp.aus1.giganews.com...
> In 7+ years of managing Oracle databases

The past 7 years just seem to have flown by, for you, don't they?

Here's a tip: go away and learn something about Oracle backup and Recovery procedures and techniques.

>I have only had one incident where
> the database became corrupt and that was because of a hardware failure,
not
> because of a bug in the operating system or in Oracle. I guess I have
just
> been EXTREMELY lucky.
>

No, Oracle is extremely robust. Operating systems are generally robust enough. You've just discovered the truth that backups are a waste of time ...until you need them. That's all.

> In the scenerio you describe I would not have a warm comfort level
restoring
> that one datafile.

Yup. I always recommend my students shouldn't do anything unless they have the warm and fuzzies about them. New versions? Pah! Not until you have the warm and fuzzies. CLOBS instead of LONGS? Hah! No warm and fuzzies there. And bugger all this block level recovery nonsense in 9i R2 - my friend Joe doesn't feel comfortable about them, so they can't be any good.

>What would happen if there were tables in that bad
> tablespace that had referential integrity to other tables in another
> tablespace?

Oh, OK, I'll bite.

SQL> archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\oracle\ora92\RDBMS
Oldest online log sequence     36
Next log sequence to archive   38
Current log sequence           38

<...You'll note we are in archivelog mode...>

SQL> create tablespace parent datafile 'c:\parent1.dbf' size 1m; Tablespace created.
SQL> create tablespace child datafile 'c:\child.dbf' size 1m; Tablespace created.

SQL> create table p1 (col1 number primary key, col2 char(5)) tablespace parent;
Table created.
SQL> create table c1 (col1 number primary key, col2 number) tablespace child;
Table created.
SQL> alter table c1 add (constraint blah_child_fk foreign key(col2) references p1(col1));
Table altered.

<.....So we have C1, in one tablespace, referencing P1 in another tablespace.....>

SQL> insert into p1 values (42342,'Row 1' ); 1 row created.
SQL> commit;
Commit complete.

SQL> insert into c1 values (2376487,42342); 1 row created.
SQL> commit;
Commit complete.

<...So we have one child row referencing one parent row...>

SQL> alter tablespace parent begin backup; Tablespace altered.

<...Copy the file using O/S commands. Which means that the backup of the parent table only contains a reference to the one record, 42342...>

SQL> alter tablespace parent end backup; Tablespace altered.

SQL> insert into p1 values (276234,'row2'); 1 row created.
SQL> commit;
Commit complete.

SQL> insert into c1 values (3746,276234); 1 row created.
SQL> commit;
Commit complete.

SQL> alter tablespace child begin backup; Tablespace altered.
SQL> alter tablespace child end backup;
Tablespace altered.

<...Note that after other transactions have happened, we backup the child table. So the backup for this table includes 2 rows. The backup for the parent table only includes one row...>

SQL> shutdown abort
ORACLE instance shut down.
C:\del *.dbf

<....ooops.....>

SQL> connect / as sysddba
SQL> startup
ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

Database mounted.
ORA-01157: cannot identify/lock data file 16 - see DBWR trace file ORA-01110: data file 16: 'C:\PARENT1.DBF'

<...use O/S commands to retrieve the backed-up copy, then...>

SQL> recover automatic datafile 16;
Media recovery complete.

<...have another go....>

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: 'C:\CHILD.DBF'

<....bummer. OK, use more O/S commands to restore the backup for file 17. Then....>

SQL> recover automatic datafile 17;
Media recovery complete.
SQL> alter database open;
Database altered.

SQL> select * from p1;

      COL1 COL2
---------- -----

     42342 Row 1
    276234 row2

SQL> select * from c1;

      COL1 COL2
---------- ----------

   2376487      42342
      3746     276234

You will therefore note that although the two datafiles were backed up at different times, both were successfully recovered. I leave it to you as an exercise to work out what happens when you delete first one, then the other, in either order (hint: it makes no difference, because I've just tested it in case I might have been wrong).

This is *really* backup and recovery 101. It's very basic. It's been the same since about version 6. There is nothing intellectually strenously taxing about it.

>
> Not sure what the 4gb boundary you are referring to is, but I have had NT
> 4.0 filesystems that were 40 gb in size that never experienced any
problems.
>

If you are telling me that you created files on an NT system which were 40Gb in size, I'd sack you, were you my DBA. I'd sack you anyway for exhibiting precisely zero knowledge or understanding about Oracle backup and recovery procedures. I'd sack you, too, because you can't seem to control for all the variables in a performance tuning test (see other posts).

Just as well I'm not your boss. And God help me if you're ever mine, because I can see long evenings of 'please explain' yawning before me were it ever so.

HJR
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3d1c6b90$0$231$ed9e5944_at_reading.news.pipex.net...
> > "Joe Salmeri" <JoeSalmeri_at_comcast.net> wrote in message
> > news:BNXS8.424576$Gs.31492095_at_bin5.nnrp.aus1.giganews.com...
> > > And how is physically backing up 5 2000mb datafiles any different than
> > > backing up 1 10000mb datafile?
> > > Either way the same amount of data is backed up.
> >
> > Its way different if one data file becomes corrupt. Then you have a
> restore
> > of 2gb not 10. Not to mention that a say 32gb datafile has 7 chances to
> > exactly extend to a 4gb boundary
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > *****************************************
> > Please include version and platform
> > and SQL where applicable
> > It makes life easier and increases the
> > likelihood of a good answer
> >
> > ******************************************
> >
> >
>
>
Received on Fri Jun 28 2002 - 21:53:43 CDT