max size dbf files on 32 bit windows xp 4

[raygg]

I have a graduate research star datawarehouse with more than 25 years of historical data and have been given a windows xp pc to upgrade the dw from 10 to 11g. Don't ask.

The existing dw is about 300GB and many of the dbf files are maxed out to 32GB which I am told is the limit for 32 bit windows xp NTFS. This dw is very static now but you never know if it will get more active and changes will be made to it. I doubt the dw will grow much larger than 500 or 600 gb before Oracl3e 12g and 13g once it is enhanced with a new schema and many indexes and keys.

What is an optimum max dbf filesize in this environment when I convert it to 11g?

What is an optimum db blocksize for 11g for this environment?.

It is running on a 1 TB drive.

Although I am an Oracle DBA I have no 11g or dw experience, but much experience on versions 7-10gR2.

 

[karluk]

I'm fairly certain that the 32gb limit on Oracle data files is not a windows xp limit. Rather, it is a limit imposed by Oracle traditional (smallfile) tablespaces. Oracle can only access 2^22-1 = 4194303 blocks per data file, so a database with an 8k block size has a not quite 32gb data file size limit.

This same size limitation applies to Oracle 11g, so you will need to use multiple data files for your larger tablespaces, unless you decide to create a bigfile tablespace, which has a much higher limit on the number of blocks it can contain. Personally I would advise against trying this. Backup and recovery on such large datafiles can take an extremely long time, and you would lose the advantage of being able to run certain backup and recovery tasks in parallel. Your database, although quite large, is simply not large enough to require a bigfile tablespace.

 

[raygg]

That's very helpful.

We are using a portable 1.3TB IOMEGA HD to build the new db warehouse as the desktop HD does not have enough room for another database. In formatting the portable HD when the format window in XP came up I selected 2k but it would not accept it. I dropped down to 1K and it formatted the drive ok. I presume that means I have 1k ntfs blocksize on the portable HD.

Would it be prudent to size the dbf at a max of 30gb to allow for future changes?

Should I increase the blocksize to 16k in the new db warehouse on 11g on the IOMEGA?

 

[SantaMufasa]

Raygg,

Would it be prudent to size the dbf at a max of 30gb to allow for future changes?

The size of your database datafiles represents no limitation on the number or size of future changes you make. I, personally, use the following code to add new datafiles to our 2TB tablespace:

ALTER TABLESPACE xyz
  ADD DATAFILE '+DATA01_A001' SIZE 500M
      AUTOEXTEND ON NEXT 500M MAXISIZE 20G;

Therefore, I have 100 of such files, representing the theoretical 2TB maximum for the tablespace. Our RMAN backups seem to function better with 20gb files than with 32GB files (which is the maximum if you specify UNLIMITED for the maxsize).

Should I increase the blocksize to 16k in the new db warehouse on 11g on the IOMEGA?

The relationship of the Oracle blocksize for your database and the blocksize of your o/s-device is that the Oracle blocksize should be a multiple of your O/S blocksize. I don't run my databases (even on my PC) with less than 16K for my Oracle blocksize.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[karluk]

It shouldn't be automatic to go with a 16k block size - there are trade-offs involved. Basically, the higher the block size, the more you can expect good disk i/o at the cost of either needing to allocate more memory to your instance or having to accept a lower buffer cache hit ratio. See Oracle support document 65984.1 for details. However, in your case you are building a data warehouse database, so good disk performance figures to be the most important consideration. So I would go with the highest block size that your platform supports.

You could also experiment with using non-standard block sizes for your bigger application tablespaces. We use that method for one of our largest databases. It has a default block size of 16k, but also contains one tablespace with a 32k block size.

 

[raygg]

SantaMafusa - great tip about RMAN 20g filesize. I like the cmd too.

THanks to karluk on the blocksize thoughts.

I want to give stars to both of you but the webpage is not respoonding when I click on the stars link.

 

[raygg]

Star to karlik -