What is the optimal size 32G ? 128 G ? or maybe 1020 G ( 1 Tera ) ?

[shlomi_ambar]

Hello

During installation of Oracle 11.2 on Redhat 6.1linux , I have asked to define the maxsize of the data file .

is there any best practice regards for size of oracle datafille ?

curently I have max datafile size 32G.

I want the biggest size with the optimization from this size concerning to backup and restore.

what is the optimal size 32G ? 128 G ? or maybe 1020 G ( 1 Tera ) ?

Thanks

Shlomi AmbarI

[volker_borowski2]

Hi,

new note: 1644762 - Oracle BigFile Tablespaces

If you like big files -> there you go.

I like small files and I like having a couple of these.

- Easier multiplexing in backups.

- Better possibility to fillup broken backup or restore runs.

If you are on the edge as far the limits are concerned, you need to get bigger files.

Volker

[sunny_pahuja2]

Hi,

You can check SAP Note 129439 - Maximum file sizes with Oracle.

Thanks

Sunny

[shlomi_ambar]

Hi

The note 129439 was last updated on 07.05.2007 . So it is not reflect the changes with hardware / DB .

Shlomi

[Former Member]

Hello,

Note is 'valid since' 07.05.2007 (Released on 07.05.2007), not last updated on 07.05.2007.

It should be up to date. See the version 23.

Thanks

[former_member188883]

Hi,

There is no thumb rule to define max size of the datafile. It depends upon your OS limits.

Ideally I would recommend to have data file size not more that 32GB. This is because many a times during restoration or during backup file sizes more than this are not readable by the backup copy commands like dd and cp.

Regards,

Deepak Kori

[shlomi_ambar]

Thanks

The OS level permit data file size of about 250 Tera !

As of Oracle 9, the file size limit of 4194302*DB_BLOCK_SIZE applies (8k in the SAP environment), that is, 32GB.

I know that for MS-SQL there is a recomendatiion to use 64k block size.

If I use DB_BLOCK_SIZE = 64k then the hardware able to get more data using I/O bus.

So if the hardware is able to manage big data file whta is the optimal DB_BLOCK_SIZE and from this size what is the optimal DATA size ?

Kr

Shlomi

[former_member188883]

Hi,

As per SAP recommeded paramters Refer SAP note 1431798 for ORacle 11.2

DB_BLOCK_SIZE = 8192.

This should give you optimal performance for SAP application.

Regards,

Deepak Kori