oracle datafile size recommendations

[Former Member]

Hi

Can anone tell me what can be the maximum datafile size in oracle10 database. i am using HP-UX on ECC6.

If there is no limit then what is the best practice to follow regarding the datafile size while adding and what are the pros and cons of a bigger and a smaller datafile size.

[Former Member · Accepted solution]

Taken from Oracle 10gr2 documentation

*File Type Platform *

Data files Any 4,194,303 multiplied by the value of the DB_BLOCK_SIZE parameter

I'll let you do the computation for your database

Looking at the size of most modern SAP databases, I don't really see the point in allocating lots of small datafiles. It's cumbersome to manage not talking about maxdatafiles limit that you can reach quite quickly if you do not pay attention.

Some pure Oracle DBA colleagues working on AIX work with 32 GB datafiles. I limit to 10GB on SAP databases because if you do not backup your database with RMAN, it can take quite a long time to restore a 32 GB file to recover from a corrupt db block !

[Former Member]

Hi Muhammad,

there is one additional thing to mention from my side. HP-UX has the problem, that writers blocks writers, if you don't use one of those Veritas file system products with enhanced features for Oracle Databases (I think it's called Quick I/O).

That means on a standard HP-UX system you should have enough files to achieve good write performance. Which number of files is enough depends on your database size. For an average SAP database (up to 1TB) I would also use files not bigger than 8-10 GB. On bigger Databases I would increase the size to may be 16GB.

Regards

Ralph

[Former Member]

There is no SAP recommendation on the maximum size of datafile. The datafile size can be greater than 2GB and the maximum file size depends on the OS limit and the DB_BLOCK_SIZE.

As mentioned previously, there is a generic limit on the maximum Oracle datafile size of 4 million

Oracle data blocks. The exact limit is 4194303 * DB_BLOCK_SIZE.

Please see SAP note 129439 (Maximum file sizes with Oracle) for further information.