works great on your mobile device too!

Most Popular

Database Best Practice

Use Modern OFA (Optimal Flexible Architecture)

Ensure the database file system layout you use is standardized on all your database systems. In Oracle this is done via OFA.

Optimally at this point you should use Oracle Managed Files (OMF). Among all its other benefits it enforces OFA. If you cannot use OMF a good example of manually implementing OFA is below:

-- SAN\RAID Example

c:\oradata\DB01\arc    Archived Redo Logs 1 
o:\oradata\DB01\arc    Archived Redo Logs 2 

c:\oradata\DB01\ctl    Control File 1
o:\oradata\DB01\ctl    Control File 2

c:\oradata\DB01\fra    Fast recovery area.

c:\oradata\DB01\rdo    Redo Log Files 1
o:\oradata\DB01\rdo    Redo Log Files 2

c:\oradata\DB01\dbf    Core system tablespace datafiles (SYSTEM, SYSAUX, TEMP, UNDO ...).
o:\oradata\DB01\dbf    User data tablespace datafiles.

r:\exports\DB01        DataPump export files.
r:\rman\DB01           RMAN backup files.

-- JBOD (just a bunch of disks) Example

c:\oradata\DB01\arc    Archived Redo Logs 1
c:\oradata\DB01\ctl    Control File 1
c:\oradata\DB01\dbf    Core System Tablespace Datafiles (SYSTEM, SYSAUX, TEMP, UNDO ...)
c:\oradata\DB01\fra    Fast recovery area.
c:\oradata\DB01\rdo    Redo Log Files 1

d:\oradata\DB01\arc    Archived Redo Logs 2
d:\oradata\DB01\ctl    Control File 2
d:\oradata\DB01\rdo    Redo Log Files 2

f:\oradata\DB01\dbf    User data tablespace datafiles.
g:\oradata\DB01\dbf    User data tablespace datafiles.
h:\oradata\DB01\dbf    User data tablespace datafiles.

r:\rman\DB01           RMAN backup files.
x:\exports\DB01        DataPump export files.

The JBOD method is becoming less and less common all the time.

ASM Sidebar

For non-RAC databases on most modern SAN systems there is little to be gained in using ASM except added complexity. However, ASM implemented in a JBOD environment can be very effective.

According to Donald K. Burleson, "The advent of RAID 0+1 has made Oracle-level striping obsolete since RAID 0+1 stripes at the block level, dealing out the table blocks, one block per disk, across each disk device. RAID 0+1 is also a far better striping alternative since it distributes the load evenly across all of the disk devices, and the load will rise and fall evenly across all of the disks. This relieves the Oracle administrator of the burden of manually striping Oracle tables across disks."

Exceptions would be if your RAID/SAN consistently experiences writes that take longer than 5ms and reads that take longer than 1ms. Some SAN vendors advise the use of ASM with Oracle to get around their hardware short comings. These tend to be SAN/NAS systems designed for historical data storage, i.e. not designed for exhaustive real-time file access (like an Oracle database).

<- Articles For more Oracle articles and best practices go here.