Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Planing Oracle database & application

SOLVED
Go to solution
Inesa Clinko
Advisor

Planing Oracle database & application

Hi!
Could anybody help me to plan VGs and FSs
for an application, based on Oracle9 dbase,
OS HPUX11,mashine rp8400 and storage HP StorageWorks.
The initial parameters are:
-70 GB for software,system
tablespaces,rollback,temp..);
-1.3-1.4 TB for data, based on RAID5;

How I have to design my VGs in order to
phisically separate software, system, data,indexes,rollback,segments,log files..?
What is the usual solution?
Is there any special remarks about kernel parameters,connected with Oracle?

10 REPLIES
Pete Randall
Outstanding Contributor

Re: Planing Oracle database & application

I'm not an Oracle expert but I can offer some general guidelines.

Keep your root volume group separate. Usually the root vg is placed on internal disk and mirrored using MirrorDisk/UX. Keep all your data in separate volume groups, probably on external storage. We use vg01 for non-db data and vg02 for the database itself.


Pete


Pete
James Specht
Trusted Contributor

Re: Planing Oracle database & application

Here we have three volume groups. vg00 root, vg01 DB data, vg02 DB applications. vg01 is in a VA74000 all by itself.
When we migrated from oracle 8 to oracle 9 there were about 10 kernel parameters we had to change. There is documentation on which parameters that comes with oracle or can be downloaded from oracle's site.

--Jim
"Everyone can be taught to sculpt: Michelangelo would have had to be taught how not to. So it is with the great programmers."
Steven E. Protter
Exalted Contributor

Re: Planing Oracle database & application

Petes advice is excellent.

Let me add that Oracle reccomends Raid 1 or Raid 10 for data and rollback.

On a database that large, its costly but if you don't do it, expect performance issues at high load factors.

shmmax should be maxed out which means 25% of system memory, which is defined as swap plus memory. shmseg should be set based on anticipated user load.

Having all the oracle stuff in the same volume group will not hurt performance, even with mixed Raid 10 and Raid 5 Luns. Just put them in different logical volumes, don't make one massive filesystem for everything.

The HP-UX OS will fit in less than 20 GB of space even with a ton of add in OS software.

Here is some good kmtune output. Attached.



SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
A. Clay Stephenson
Acclaimed Contributor

Re: Planing Oracle database & application

I would urge you to upgrade to HP-UX 11.11 - the filesystem performance is better. If at all possible, choose RAID 1/0 over RAID 5. For a database of this size I would split the I/O into at least datafiles, indices, and archive/redo logs each with it's own VG.
Don't get too hung up on which disks to do what because the array is going to split it anyway. Surprisingly, under 11.11, Oracle actually performs better (and I have measured it) using fully cooked files for everything. If you do choose to use raw/io, dont use devices like /dev/vg10/rlvol1 but instead use /u01/oradata/datafile01.dbf and then symbolically link /u01/oradata/datafile01.dbf to /dev/vg10/rlvol1. Using tyhis method of indirection will allow you to easily move the i/o around (or go to fully cooked files) with no Oracle changes.

My best advice is however much memory you think you need, double it. Oracle 9 needs huge SGA's to really perform well.
If it ain't broke, I can fix that.
Alexander M. Ermes
Honored Contributor

Re: Planing Oracle database & application

Hi there.
Clay's advice is good as always.
You cannot have enough memory for Oracle, because Oracle eats resources like popcorn.
Try to spread the io-load through several channels ( alternate paths / devices ).
Mirror your control and logfiles on different disks and controlers if possible.
Go for two locations of offline log files
( running the database in archivelog mode ).

Did you take a look at the documentation ?
Install guide / release notes etc

http://otn.oracle.com/documentation/oracle9i.html


Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
twang
Honored Contributor

Re: Planing Oracle database & application

In terms of administration, RAID is far simple than using Oracle
techniques for data placement and striping.


Recommendations:

In general, RAID usually impacts write operations more than read operation.
This is specially true where parity need to be calculated (RAID 3, RAID 5, etc).
Online or archived redo log files can be put on RAID 1 devices.
You should not use RAID 5. 'TEMP' tablespace data files should also go on
RAID1 instead of RAID5 as well. The reason for this is that streamed
write performance of distributed parity (RAID5) isn't as good as that of
simple mirroring (RAID1).

Swap space can be used on RAID devices without affecting Oracle.


====================================================================================
RAID Type of RAID Control Database Redo Log Archive Log
File File File File
====================================================================================
0 Striping Avoid* OK* Avoid* Avoid*
------------------------------------------------------------------------------------
1 Shadowing OK OK Recommended Recommended
------------------------------------------------------------------------------------
0+1 Striping + OK Recommended Avoid Avoid
Shadowing (1)
------------------------------------------------------------------------------------
3 Striping with OK Avoid Avoid Avoid
Static Parity (2)
------------------------------------------------------------------------------------
5 Striping with OK Avoid Avoid Avoid
Rotating Parity (2)
------------------------------------------------------------------------------------

* RAID 0 does not provide any protection against failures. It requires a strong backup
strategy.
(1) RAID 0+1 is recommended for database files because this avoids hot spots and gives
the best possible performance during a disk failure. The disadvantage of RAID 0+1
is that it is a costly configuration.
(2) When heavy write operation involves this datafile

Steven E. Protter
Exalted Contributor

Re: Planing Oracle database & application

I didn't have this doc at home.

I can't overemphasize the RAID issue.

A. Clay makes excellent points with regards to the OS, I/O and RAID.

This tuning doc is maintainted by HP's top oracle tuning guru.

http://www2.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-1335382922+1065626708921+28353475&searchCrit=allwords

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Steven E. Protter
Exalted Contributor

Re: Planing Oracle database & application

The doc I submitted is temporarily offline.

Attaching a text copy.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Yogeeraj_1
Honored Contributor

Re: Planing Oracle database & application

hi,
-
To add to above replies, generally here is what I like (raid 0 = stripes, raid 1 = mirrors, raid 5 = striping+parity):
-
o no raid, raid 0 or raid 0+1 for online redo logs AND control files.
You should still let us multiplex them ourselves even if you mirror them. We have more opportunities for failure if the raid subsystem reports a "warning" back to us -- if we have multiplexed them -- we are OK with that.
-
o no raid or raid 0 for temporary datafiles (used with temporary tablespaces). no raid/raid 0 is sufficient. If you lose these, who cares? You want speed on these, not reliability. If a disk fails, drop and recreate temp elsewhere.
-
o no raid, raid 0 or raid 0+1 for archive. Again, let us multiplex if you use no raid or raid 0, let the OS do it (different from online redo log here) if you use 0+1.
-
o raid 0+1 for rollback. It get written to lots. It is important to have protected. We cannot multiplex them so let the OS do it. Use this for datafiles you believe will be HEAVILY written. Bear in mind, we buffer writes to datafiles, they happen in the background so the poor write performance of raid 5 is usually OK except for the heavily written files (such as rollback).
-
o raid 5 (unless you can do raid 0+1 for all of course) for datafiles that experience what you determine to be "medium" or "moderate" write activity. Since this happens in the background typcially (not with direct path loads and such) -- raid 5 can typically be safely used with these. As these files represent the BULK of your database and the above represent the smaller part -- you achieve most of the cost saving without impacting performance too much.
-
Try to dedicate specific devices to
-
o online redo
o archive
o temp
-
they should not have to share their devices with others in a "perfect" world (even with eachother).
-
Hope this helps too!
-
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)