The Application DBA  

Database File Layout

Overview

The file layout of the database and the Oracle database software should follow Oracle’s Optimal Flexible Architecture (OFA) with a few exceptions. The OFA was originally published/presented in 1991, and remains the most definitive word on Oracle file system layout and configuration. Oracle has built the basic OFA into it’s installation tools as well. However, technology has changed considerably since the early 1990s and some of the mount point considerations are not as relevant in current systems as they were back then. In addition, the OFA makes mention of special VLDB considerations, which today would cover most servers being created as Oracle database servers.

The following describes a recommended Oracle dedicated database server file layout for UNIX systems. Based on technology changes and reduced hardware costs, this document adds two additional recommendations to the original OFA:

1) Oracle databases should run on dedicated hardware. This translates into creating Oracle database servers, not just installing Oracle databases on UNIX servers.

2) All Oracle database file mount points should be dedicated in usage (e.g. not shared by other applications) and should encompass entire volumes (e.g. not carved up into disk slices).

With these two new requirements in mind, the following recommendations combine OFA recommendations 1 and 11 into the following:

The Oracle software tree should have a dedicated /oracle mount point.

All Oracle database files should have /ora[xx] mount point(s), where [xx] is an increasing number of sufficient fixed digits to encompass all volume additions.

Note: If the installation does not meet the previous two requirements then a standard OFA mount point configuration should be used.

Oracle Software Tree

Within the Oracle software tree there are several basic directory structures used. The root directory is referred to by the ORACLE_BASE environment variable. The first sub-tree is the Oracle executables software tree or the product directory.  This is generally referred to as the ORACLE_HOME directory tree when combined with the version numbers of the Oracle version installed, and is of the format ORACLE_BASE/product/x.y.z, where x.y.z are the first three version numbers (e.g. 8.1.7, 9.2.0, 10.0.1). Note that Oracle has extended this in 10g to include an additional sub-directory based on type (e.g. db for database, asm for automatic storage management, or client for Oracle client only) and an optional counter. The second sub-tree is the admin or administrative directory tree, and the third is the local directory for site specific utilities and files.

In addition, several additional directory structures may be used. Typically a source directory will be created for Oracle installation source and patches, and a backup directory or orabkup may also be created if performing disk based backups.

If the /oracle mount point must be extended, for example, the arch or backup directory is too large, then a directory should be created on another mount point of the form: /oraxx/admin/SID/arch and then linked back to the original directory point (e.g. /oracle/admin/SID/arch). This preserves the original directory height (e.g. an ls */admin/SID reveals all admin directories for the specified SID) and does not hide the intent of the directory (see OFA 1).

ORACLE_HOME Naming

The OFA standard specifies the following for ORACLE_HOME naming:

OFA 4 Store each version of Oracle Server distribution
software in a directory matching the pattern
h/product/v, where h is the login home directory of
the Oracle software owner, and v represents the version
of the software.

Most OFA for UNIX implementations use values
of vlike 7.0.16. Oracle Server patches
involving changes only to version numbers
right of the third decimal point (e.g., from
6.0.36.3 to 6.0.36.5) usually take place without
elaborate staging, and thus most sites do not
use values of vsignificant beyond the third
decimal point.

 Changes for 10g:

For previous releases of Oracle Database, the OFA recommended Oracle home path was similar to the following: /u01/app/oracle/product/9.2.0

For Oracle Database 10g, the OFA recommended Oracle home path has changed. The OFA recommended path is now similar to the following: /u01/app/oracle/product/10.1.0/type[_n]

In this example, type is the type of Oracle home, for example Oracle Database (db) or Oracle Client (client), and n is an optional counter.

As Oracle has added additional products, the following are being used to differentiate ORACLE_HOMEs:

Standalone OC4J:

/oracle/product/10.1.0/oc4j_1

10g Grid Control:

/u01/app/oracle/product/10.2em/agent10g
/u01/app/oracle/product/10.2em/db10g
/u01/app/oracle/product/10.2em/oms10g


In general most DBAs will create a 9.2.0 directory and then keep that directory across patchset installations. So, in the case of the latest 9i installation you would have version 9.2.0.6.0 installed into a 9.2.0 directory that originally had the base release of 9.2.0.1.0 installed into it. With that said it is possible, for instance in the case of a high availability environment, to use a fully versioned path. This will allow the "staging" of the new software so that the only downtime required is to perform the actual upgrade on the database(s). The other thing to keep in mind is that you should NOT rename an ORACLE_HOME directory once you've installed it. There are links buried within directory structures and I recommend that you don't change them by hand.

Database File Directories

Database file directories should be created on dedicated volumes that are entirely allocated to a mount point. Whether part of a stripe group or a single disk volume, the disks should not be carved up into disk slices. Volumes should be mounted with the format:

/ora[xx]/DBSID

 or

/u[xx]/oradata/DBSID

where [xx] is of the form 00, 01, 02, …, and DBSID is the name of the database SID. If it is expected that there will be more than 100 volumes then the number of digits chosen should meet the requirement in OFA 1 for a unique fixed-length key.

If using a single, or very limited, disk sub-system and placing database files on the same volume as the Oracle software and using the /oracle format then the database file directory should be at the same "level" as the other directories (e.g. /oracle/DBSID or /oracle/oradata/DBSID).

Directory Permissions

All Oracle database file directories should have UNIX permissions of 750 (rwx r-x ---) with owner:group of oracle:dba.

Directory Structure

The following shows the basic directory tree:

/u01/app/oracle 

<OR>

/oracle - [ORACLE_BASE]

(Oracle software)

$ORACLE_BASE/product/9.2.0 – [ORACLE_HOME]

(Administrative & local utility files)

$ORACLE_BASE/admin/DBSID/adump
                        /arch
                        /bdump
                        /cdump
                        /create
                               /logs
                        /exp
                        /pfile
                        /scripts
                        /udump
                        /utlfile
 
                  /snet/admin
                       /log
                       /trace
 
$ORACLE_BASE/local/analyze
                  /bkup
                  /log
                  /script
                  /sql
 
$ORACLE_BASE/source
 
$ORACLE_BASE/orabkup
 
(Database file directories)
 
/uXX/oradata/DBSID  <OR>
 
/oraXX/DBSID
 
Where XX is 01,02,…
and DBSID is the database system identifier (e.g. ORCL)

 

Copyright 1998-2013 AppsDBA Consulting. All Rights Reserved.