The best Oracle database structure

[Deanna Tran]

Is there a recommendation of designing a database in terms of datafiles, rollback ..etc...?
what is the advantage of having the control files and redologs on a shared storage vs the root filesystem... ?

[Andrew S Babb]

The operating system should be stored on the internal storage and should not contain any application software or database files.

After this, the application software (oracle dbms) should be installed on to another disk.

Finally the database objects should be allocated to the remaining available disks. There are thoughts that allocating objects to specific disks is better than SAME, but you must know your application to go the former. The SAME paper is available on Oracle's website at http://otn.oracle.com/deploy/availability/pdf/oow2000_same.pdf

The storage does not need to be shared unless you are wanting to run RAC in which case the data files must also be on LV's as opposed to File Systems

[Raynald Boucher]

One performance principle always applies: try to keep concurrent i/o from hitting the same device.
In any database, keep indexes on different disks than the tables they refer to, separate the redo and archive logs on different disks.
As for control files, put them on different disks in case of hardware failure.

[Tim D Fulford]

Do not put ANY database data in vg00 (redo logs, roll back segments...). You risk killing your root disks.

Oracle reccommend a SAME structure (well HP say so...I'll dig out link). Put simply spread your database load over as many disks as possible. By way of an example

Disks typically have a service time of 5ms (or up to 200 IO/s), this is about 1.5MB/s, not too high for most apps. Consequentially an individual disk will bottle neck fairly quickley on IO. However, if you have the data striped across 10 disks you can deal with 2000 IO/s (15 MB/s). If you use "smart" disks with cache, or use cooked chunks (which should utilise buffercache) you can get even better performance from your disks (say 2-3 ms).

Personally, I think redologs & rollback segments should be on their own disks & the data & indexes use the SAME structure. Re-dologs & rollback segments do large sequential writes and so could get service times of 1-2 ms (4MB) which should be enough for xaction logging... The rolls royce solution is to use solid state disks for these..... But the SAME paper advises against this & I've not been able to convience anyone else that it is a great idea...Bottom line SAME works well and is easy(er) to manage, the paper explains & gives some advice on how to architect a solution.

SAME paper http://forums.itrc.hp.com/cm/components/FileAttachment/0,,0x4a8a8cc5e03fd6118fff0090279cd0f9,00.pdf

Tim

[Tim Krego_2]

We are running Oracle 8.1.6 and WebDB 2.2 on a K460 with an AutoRAID. The AutoRAID has twelve 9GB disks installed.

The vg00 volume is on an internal 4GB disk.

We have volume groups for /u01, /u02, /u03 for the Oracle software and datafiles.

Since we are using an AutoRAID I assume we are OK as far as mirroring and striping are concerned.

I'm new to the HP hardware so I'm working with a setup that was done by a DBA that has left our company.

[Duncan Edmonstone]

You should also endeavour to follow Oracle's OFA (Optimal Flexible Architecture) standard as much as pos;sible.

For 8i See Appendix A of this doc:

http://otn.oracle.com/docs/products/oracle8i/pdf/hp-ux_adminguide_817.pdf

For 9i see this doc:

http://technet.oracle.com/docs/products/oracle9i/doc_library/901_doc/A90347-02/html/appf_ofa.htm#632541

HTH

Duncan

[Tim D Fulford]

Tim Krengo

The AutoRAID sounds OK. The SAME paper would reccomend that you use extent striping across the mirrored AutoRAID in RAID 1 (i.e RAID 1+0).

I hope your root disk is mirrored & not just on one 4GB disk.

As you have an AutoRAID it would be difficult for you to assign a single LUN to redologs & rolback segments etc, so don't bother just stripe it along with your data.

Tim