Re: Oralce disk setup

Hello there;

This discussion touched right in the middle of the discussion I had this morning with my DBAs (I am a UNIX SysAdmin). I hope you might help me figure out the following problem:

I am about to configure a new system (IBM RS/6000 B80, AIX 4.3.3) with two SSA cards and two SSA drawers. To get the best performance I decided to follow Oracle recomendation: striping and mirroring across two adapters and two drawers. Now comes the tricky part: I found an IBM redbook
(http://www.redbooks.ibm.com/redbooks/SG245251.html, appendix F) and it suggests that mirror-write-consistency (MWC) should be turned on only for redo logs and archive logs. Database files do NOT need MWC turned on.

Could somebody assure me that this is really true? I've seen Oracle crashes and whenever it recovered it applied redo logs and it was all fine. Is there any possibility that Oracle will come up after the crash without playing the redo logs? What would cause it to do so? And when it goes through the logs how far does it go? All available logs, or just the last few transactions?

Any insight will be appreciated.
mj

Frank van Bortel wrote:

> Hmmm - looking at your requirements, any disk layout will do,
> it's the CPU's that bother me.
>
> Rule(s) of thumb:
> - Raw performs better than cooked
> - more spindles perform better
> - Murphy will hit
>
> Here are some remarks:
>
> Definitions:
> RAID0: Striping
> RAID1: Mirroring
> RAID5:Striping with parity
> RAID10: mirroring + striping
>
> Types of files, used by Oracle:
> 1) Redo logs.
> During update activity, redo logs are written to in a continuous and
> sequential manner,
> and are not read under normal circumstances. RAID5 would be the worst
> choice for performance.
> Oracle Corporation recommends placing redo logs on single non-RAIDed disk
> drives, under the
> assumption that this configuration provides the best overall performance
> for simple sequential writes.
> Redo logs should always be multiplexed by ORACLE, so RAID1 provides few
> additional benefits.
> Since non-RAID and RAID0 configurations can vary with hardware from
> different vendors,
> you should contact the hardware vendor to determine whether non-RAIDed
> disks or RAID0 arrays
> will yield the best performance for continuous sequential writes.
> Note that even if redo logs are placed on RAID1 arrays, the redo logs should
> still be mirrored at the ORACLE
> level. When the log writer process determines that it does not know whether
> the contents of a particular
> redo log are valid, it will mark that redo log as "STALE" in the V$LOG
> table. If this redo log is the only
> copy, then it cannot be archived, and will cause a database halt (assuming
> the database is running in
> archivelog mode). If redo logs are multiplexed as recommended, then the
> archiver process will choose a
> copy of the redo log that is not marked as "STALE", thus generating no
> interruptions. If the redo logs are
> mirrored only at the hardware level, then both copies of the redo log are
> "STALE".
>
> 2) Archive logs.
> As redo logs are filled, archive logs are written to disk one whole file at
> a time (assuming, of course, that
> the database is running in archivelog mode), and are not read under normal
> circumstances.
> Any RAID or non-RAID configuration could be used, depending upon the
> performance
> requirements and size of the redo logs. For instance, if the redo logs are
> large, then they will become full
> and be archived less often. If an archive log is likely to be written no
> more than once per minute, then
> RAID5 may provide acceptable performance. If RAID5 proves too slow, then a
> different RAID
> configuration can be chosen, or the redo logs can simply be made larger.
> Numerous early ORACLE
> installations wrote redo logs directly to tape rather than disk, so
> reasonable sizing of redo logs can
> unquestionably minimize the write requirements enough to make RAID5
> performance acceptable in
> small to medium volume installations. Note that a fault-tolerant
> configuration is advisable: if the archive
> log destination becomes unavailable, the database will halt.
>
> 3) Rollback Segments.
> As modifications are made to the database tables, undo information is
> written to the buffer cache in memory.
> These rollback segments are used to to maintain commitment control and read
> consistency. Rollback
> segment data is periodically flushed to disk by checkpoints.
> Consequently, the changes to the rollback segments are also recorded in the
> redo logs. However, a
> smaller amount of information is typically written to the rollback segments
> than to the redo logs, so the
> write rate is less stringent.
> A fault-tolerant configuration is advisable, since the database cannot
> operate without rollback
> segments, and recovery of common rollback segments will typically require
> an instance shutdown.
> If the transaction rate is reasonably small, RAID5 may provide adequate
> performance. If it does not, then
> RAID1 (or RAID10) should be considered.
>
> 4) User tables and indexes.
> As updates are performed, these changes are stored in memory.
> Periodically, a checkpoint will flush the changes to disk. Checkpoints
> occur under two normal
> circumstances: a redo log switch occurred, or the time interval for a
> checkpoint expired. (There are a
> variety of other situations that trigger a checkpoint. Please check the
> ORACLE documentation for more
> detail.) Like redo log switches and generation of archive logs, checkpoints
> can normally be configured
> so that they occur approximately once per minute. Recovery can be performed
> up to the most recent
> checkpoint, so the interval should not be too large for an OLTP system. If
> the volume of updated data
> written to disk at each checkpoint is reasonably small (ie. the transaction
> rate is not extremely large),
> then RAID5 may provide acceptable performance. Additionally, analysis
> should be performed to
> determine the ratio of reads to writes. Recalling that RAID5 offers
> reasonably good read performance,
> if the percentage of reads is much larger than the percentage of writes
> (for instance, 80% to 20%), then
> RAID5 may offer acceptable performance for small, medium, and even some
> large installations. A
> fault-tolerant configuration is preferable to maximize availability
> (assuming availability is an objective of
> the organization), although only failures damaging datafiles for the SYSTEM
> tablespace (and active
> rollback segments) require the instance to be shutdown. Disk failures
> damaging datafiles for
> non-SYSTEM tablespaces can be recovered with the instance on-line, meaning
> that only the
> applications using data in tablespaces impacted by the failure will be
> unavailable. With this in mind,
> RAID0 could be considered if RAID5 does not provide the necessary
> performance. If high availability
> and high performance on a medium to large system are explicit requirements,
> then RAID1 or RAID10
> should be considered.
>
> 5) Temp segments.
> Sorts too large to be performed in memory are performed on disk. Sort data
> is written to disk in
> a block-oriented manner. Sorts do not normally occur with
> INSERT/UPDATE/DELETE
> activity. Rather, SELECTS with ORDER BY or GROUP BY clauses and aggregate
> functions (ie.
> operational reports) , index rebuilds, etc., will use TEMP segments only if
> the sort is too large to
> perform in memory. Temp segments are good candidates for non-RAIDed drives
> or RAID0 arrays.
> Fault-tolerance is not critical: if a drive failure occurs and datafiles
> for a temp segment are lost, then the
> temp segment can either be recovered in the normal means (restore from tape
> and perform a tablespace
> recovery), or the temp segment can simply be dropped and re-created since
> there is no permanent data
> stored in the temp segment. Note that while a temp segment is unavailable,
> certain reports or index
> creations may not execute without errors, but update activity will
> typically not be impacted. With this in
> mind, RAID1 arrays are a bit unnecessary for temp segments, and should be
> used for more critical
> database files. RAID5 will provide adequate performance if the sort area
> hit ratios are such that very
> few sorts are performed on disk rather than in memory.
>
> 6) Control files.
> Control files are critical to the instance operation, as they contain the
> structural information for the
> database. Control files are updated periodically (at a checkpoint and at
> structural changes), but the
> data written to the control files is a very small quantity compared to
> other database
> files. Control files, like redo logs, should be multiplexed by ORACLE
> software on different drives or
> arrays. Non-RAIDed drives or or any RAID configuration would be acceptable
> for control
> files, although most organizations will typically distribute the multiple
> copies of the control files with the
> other database files, given that the read and write requirements are so
> minimal. For control files,
> maintaining multiple copies in different locations should be favored over
> any other concern.
>
> 7) Software and static files.
> The ORACLE software, configuration files, etc. are very good candidates for
> RAID5 arrays. This information
> is not constantly updated, so the RAID5 write penalty is of little concern.
>
> Fault-tolerance is advisable: if the database software (or O/S software)
> becomes unavailable
> due to a disk failure, then the database instance will abort. Also,
> recovery will include restore or
> re-installation of ORACLE software (and possible operating system software)
> as well as restore and
> recovery of the database files. RAID5 provides the necessary
> fault-tolerance to prevent this all-inclusive
> recovery, and good read performance for dynamic loading and unloading of
> executable components at
> the operating system level.
>
> Having copied all the above, consider:
>
> Redolog members on: RAID-0 or non-RAID; preferably 4 disks in 2 sets
> Archived redolog files: RAID-1 or 5
> Rollback: RAID-5, RAID-1 or RAID-10
> User table and index tablespaces: RAID-5, RAID-1 or 10
> Temp: RAID-0 or non-RAID
> Control files: RAID-5
> Oracle s/w: RAID-5
> Remeber to set Cache settings on the RAID controller to Write-thru
>
> If you cannot mix different RAID types, I would guess RAID-1 (Mirroring) would
> be
> the best offset between speed and security.
> Hth,
>
> edipko01 wrote:
>
> > Hi all,
> >
> > I'm looking for some advice on disk setup for Oracle 8.1.6.1 on Solaris.
> > This is my first go-around with oracle, so any and all advice will be
> > greatly appreciated.
> >
> > I will be working with 3, 40Mbit/sec scsi channels, and approx 16 -
> > 18.2Gb Hard Drives on a Sun Enterprise 450 with 4x400Mhz CPUs.
> > Database overview: Data gets written only once per hour, users will only
> > read from the database but large calculations are involved with almost
> > every read.
> >
> > Initial thought for config (raw disks):
> > Controler 1:
> > 1 disk (Operating System)
> > 3 stripes across 2 disks(u01, u02, u06 - Application, RedoLogs, ArchiveLogs)
> > 2 stripes across 2 disks(u03, u05 - Rollback, Temp)
> >
> > Controller 2:
> > 1 disk (Mirror Operating System)
> > 4 disk stripe (/u04 - data)
> >
> > Controller 3:
> > 4 disks stripe (mirror /u04)
> > 3 stripes across 2 disks (mirror /u01, u02, u06)
> >
> > Questions:
> > 1.) Would I be better off putting 5 stripes on 4 disks for u01,2,3,5,6?
> > 2.) Should I use raw disks everywhere?
> > 3.) With only a couple dollars difference between 18.2Gb and 9.6Gb
> > drives, is there any reason not to use the 18.2Gb?
> > 4.) Would it be better to partition each disk as needed for u01...u06,
> > and create 6 stripes across say 9 drives (3 per controller), and do the
> > same for the mirrors?
> >
> > Again, this is my first experience with Oracle, so please be gentle.
> >
> > Thanks in advance
> > Ernie
>
> --
> Gtrz,
>
> Frank van Bortel
  Received on Tue Feb 20 2001 - 03:23:16 CET