Re: Oracle Data Warehousing, UNIX and large file-enabled file systems

> I understand with large file-enabled JFSs, I could have 64GB files
> (one posting says 32GB is max for Oracle) and up to 8TB JFSs.

One quick point to make here :
The maximum size of an oracle datafile depends on the DB block size. A database block address (dba) is 4 bytes - the 10 upper bits make up the file number (platform/version specific) - that leaves 22 bits for the offset
within a file ... so 2^22 = 4194304 times 16K (max on AIX) gives 64GB .... So in short
2K db blocksize -> 8GB max filesize
4K -> 16GB
8K -> 32GB
16K -> 64GB
32K -> 128GB (not possible on AIX)

"Don Gillespie" <don.gillespie_at_mts.mb.ca> wrote in message news:6ffd83a6.0203251137.50307a5f_at_posting.google.com...
> I am the DBA for a data warehouse environment that is expected to get
> to about 3TB. That would mean about 1500 or so data files with the
> 2GB file limit. Besides being a nightmare to manage that many files,
> I anticipate the overhead on checkpoints would be tremendous. The
> environment is 32-bit Oracle (possibility of 64 bit in the future) on
> AIX with disk storage on an IBM Shark SAN (RAID5, 32K stripe; no
> choice here), with a 16K Oracle block size (the max allowed). We are
> using Journaled File Systems, not raw partitions. I am contemplating
> the use of large file-enabled JFSs for all JFSs that would contain
> oracle data files, log files and control files. But I don't know much
> about them, and I am wondering if there are serious performance, space
> consumption or administration issues in doing so.
>
> I understand with large file-enabled JFSs, I could have 64GB files
> (one posting says 32GB is max for Oracle) and up to 8TB JFSs. But I
> have heard conflicting comments on implications for INODEs, allocation
> units and fragmentation sizes(?). Am I going to suffer an I/O penalty
> every time the database attempts to write blocks out to the files,
> especially if the blocks are non-contiguous? What about disk space
> usage? Any other issues (backup/recovery, RMAN, EXP/IMP, etc.)?
>
> I would be particularly interested in what others have experienced.
> What file sizes did you allow? What size of JFSs were allowed? What
> issues were encountered? If you had the chance to start from scratch
> again, would you go large file-enabled again, and if so, what would
> you set as your maximum file size and JFS size?
>
> These issues have come to the forefront as I prepare to create the
> tablespaces, so unfortunately this is a time-sensitive issue. Any
> input would be GREATLY appreciated!
Received on Mon Mar 25 2002 - 14:04:40 CST