This page includes a summary of OFA with examples.
What is OFA
The OFA standard is a series of recommendations for naming files and folders when installing and implementing an Oracle database.
The official OFA advice can be found in the manuals at docs.oracle.com
(for 9i - Administering Oracle, for 10g - Installing Oracle)
The OFA standard is designed to organize large amounts of complex software and data on disk, simplify administration tasks, maximise performance and assist switching between Oracle databases.
The file system of an OFA-Compliant Database is organized to enable easy administration for issues such as adding hardware, creating databases and adding items to an existing database.
Other benefits are that I/O loads are distributed across enough disk drives to prevent performance bottlenecks. Home directories can be distributed across more than one disk drive: You can add, move, or delete login home directories without having to revise programs that refer to them.
Consistent File Naming
Files are named such that Control files, redo log files, and datafiles are identifiable as such.
Files of one database are easily distinguishable from files of another database.
The association of datafile to tablespace is clearly indicated.
Categories of files are separated into independent UNIX directory subtrees so that files in one category are minimally affected by operations on files in other categories.
You can execute multiple versions of application software simultaneously. This simplifies the process of switching an instance to an upgraded version of oracle.
OFA separates the administrative information for each database.
Tablespace contents are also separated to minimize free space fragmentation and I/O request contention.
OFA under Windows?
The basic principles behind OFA were written with UNIX in mind, some but not all, of these principles can be applied to a windows installation. Arguably OFA is less important under Windows because few organisations will actually take the risk of installing multiple versions of Oracle along with multiple database instances on a single Wintel Box.
1) Mount Points
Naming strategy as recommended by the OFA standard implemented on UNIX.
An Oracle installation requires at least two mount points: one for the software and at least one for the database files. To implement OFA fully, requires at least four mount points: one for the software and at least three for database files.
Mount Point Syntax
where p is a string constant
m is a unique fixed-length key (a two-digit number) used to distinguish each mount point.
/u01 and /u02, or
/disk01 and /disk02.
Mount Points for Very Large Databases (VLDBs)
If each disk drive contains database files for one application only, use the syntax
pm A mount point name
q A string denoting that Oracle data is stored in this directory
dm The value of the initialization parameter DB_NAME (synonymous with the instance sid for single-instance databases)
For example, mount points named
/u02/oradata/test allocate two drives for the Oracle test database.
Multiple drives for the same database help to prevent I/O bottlenecks.
This section describes the OFA naming convention for directories.
To support the execution of multiple versions of Oracle at the same time, store each version of the Oracle Server software in a directory matching the pattern:
/ MountPoint/ StdDirectoryName/ OwnerOfDirectory/ product/ version
Set the environment variable to match:
For Oracle 10g, the OFA recommended path is extended to:
/ MountPoint/ StdDirectoryName/ OwnerOfDirectory/ product/ version/ type[_n]
Where type is the type of Oracle home, e.g. Database (db) or Client (client), and n is an optional counter.
This extended syntax allows you to install different products with the same release number in the same
base directory, or install the same product version more than once.
Note: Under Windows, Multiple Oracle Homes are only available starting with Oracle 8.0.4
Before installing mutiple versions on a windows box read the metalink advice:
Doc ID: Note:73963.1 Subject: WIN: Using multiple ORACLE HOMES on Windows platform
Store data files in directories separate from the software.
In subdirectories matching the pattern
Scroll down for details of the data filenames.
Store administration files in directories separate from the software and data.
This keeps all the files that DBA's need to tinker with in one place.
Subdirectories match the pattern
e.g. The adhoc subdirectory for the database named fas.
The admin subfolders will normally include: SQL scripts, Archive Logs, audit and trace files, export dump files, creation sql scripts, log files and init.ora parameter files.
(sub folders: adhoc, arch, adump, bdump, cdump, create, exp, logbook, pfile, udump.)
Initialization file (init.ora) parameters need to be set to match these locations.
Home Directory Syntax
Name UNIX home directories using the syntax
The Oracle software owner home directory (Oracle base directory, default used by the installer)
/u01/app/applmgr is an Oracle applications software owner home directory.
Placing home directories at the same level in the UNIX file system is advantageous because it allows the collection of applications owner login home directories on different mount points to be referred to using the single pattern matching string, /*/app/*.
Refer to explicit pathnames only in files designed specifically to store them, such as the password file, /etc/passwd, and the Oracle oratab file.
Refer to group memberships only in the /etc/group file.
3) Database Files
The following naming convention for database files ensures that they are easily
/MountPoint/ oradata/ sid/ control.ctl
There must be at least 2 control files, each on a different device.
Redo log files
/MountPoint/ oradata/ sid/ redonn.log
/MountPoint/oradata/ sid / TblSpacenn.dbf
Note: Do not store files other than control files, redo log files, or datafiles under the oradata path.
Create Separate Tablespaces for segments with different lifespans, I/O request demands, or backup frequencies.
As a minimum each database will require Rollback segments (RBS), Data dictionary (SYSTEM) and Temporary segments (TEMP). Additional tablespaces can be created as required.
As an example the additional Tablespaces created by the Database Configuration Assistant.
CWMLITE - OLAP catalog metadata repository (CWMLite)
DEMO - Demo schema
DRSYS - Oracle Text segment
INDX - Index associated with data in the USERS tablespace
OEM_REPOSITORY - Repository for Oracle Enterprise Manager
USERS - Miscellaneous user segments
XDB - Data that is stored in the Oracle XML DB repository.
Data dictionary segments are never dropped, and no other segments that can be dropped are allowed in the SYSTEM tablespace.
Rollback segments should not be stored in tablespaces holding application data.
Tablespace names can be a maximum of 30 characters long.
Tablespace filenames can be a maximum of 10 characters long (plus a 3 digit extension).
Portable UNIX filenames are restricted to 14 characters.
(Windows filenames under NTFS are no longer restricted to 8.3 characters, but there are performance benefits from shorter names and directory paths.)
Name datafiles as tn.dbf
where t is a short description and n is a two-digit string.
The tablespace GENERAL_LEDGER might have the data file GLD01.dbf (for data) and GLX01.dbf (for indexes) The description of GL helps to associate the datafile with the tablespace.
Note: Do not embed reminders of the word "tablespace" in tablespace names. This should be distinguishable by context, i.e where they are stored.
Portable filename character set - The set of characters from which portable filenames are constructed: ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789._-
/u01… /u99 User data directories /*/home/* User home directories /*/app/* User application software directories /*/app/applmgr Oracle applications software subtrees /*/app/oracle/product Oracle software subtrees /*/app/oracle/product/22.214.171.124.0 Oracle Server distribution files /*/app/oracle/admin/fas fas database administrative subtrees /*/app/oracle/admin/fas/arch/* fas database archived log files /*/oradata Oracle data directories /*/oradata/fas/* fas database files /*/oradata/fas/*.log fas database redo log files
Key Filename Extensions
.ctl SQL*Loader control file; Oracle Server control file .dat SQL*Loader datafile .dbf Tablespace data file .dmp Export file .log Installation log files; Oracle Server redo log file .ora Oracle configuration file (initN.ora) .sql SQL script file .tab SQL script file .trc Trace file
Oracle naming conventions