Optimal Flexible Architecture While Preparing to Create a Database

Optimal Flexible Architecture is a set of guidelines and naming conventions, nothing more. You don't have to follow it, and plenty of sites deviate from it to some degree. Someone could, without too much trouble, dream up a different but equally workable set of conventions for placing and naming the directories and files in an Oracle installation. One of OFA's biggest advantages is simply how widely it's been adopted. Once you're used to it, you can move from one client site to the next and be productive almost immediately, because everything is laid out roughly the same way. That consistency is worth a lot on its own.
OFA defines where each component installs its files. In many respects it resembles the Filesystem Hierarchy Standard (FHS) on UNIX systems, where, for example, /bin always holds the essential system binaries, so administrators moving between systems already know where to look. OFA takes that same FHS-style thinking and applies it to Oracle products on both UNIX and Windows. Database administrators working on any OFA-compliant system will find familiar structures and locations for applications and data. OFA covers where to install each part of each product, addressing both application storage and data storage. Like the FHS, it imposes no hard constraints, it only makes recommendations. Oracle structured OFA so administrators can spread work across multiple disks, applications on one, databases on another, and so multiple versions of the same product can coexist on one host without conflict.

The Oracle base directory is the topmost directory for Oracle software installation. You can install one or more versions of Oracle software beneath it. The OFA standard for the Oracle base directory is:

/<mount_point>/app/<software_owner>

Typical mount point names include /u01, /ora01, and similar short, Oracle-specific names. A short mount-point name is worth the small effort to set up: it makes the mount points immediately recognizable when you're scanning a server, it's less typing when navigating directories, and it stays legible when it shows up in data dictionary queries reporting on your database's physical layout. The software owner is typically named oracle, the OS user you use to install the Oracle software itself. Here's a fully formed Oracle base directory path:

/u01/app/oracle

The Oracle home directory defines the installation location for a particular product and release. Different products, and different releases of the same product, always go in separate Oracle homes, never mixed together. The recommended OFA-compliant Oracle home directory syntax is:

ORACLE_BASE/product/<version>/<install_name>

A typical current version string looks like 23.0.0, and the install name follows the dbhome_1, dbhome_2 convention when more than one installation of the same product shares an Oracle base. Here's an example Oracle home path:

/u01/app/oracle/product/23.0.0/dbhome_1

Note: the dbhome_1 suffix exists specifically to support multiple separate binary installations under the same base, for example a development install alongside a test install. If your environment only ever needs one installation per Oracle base, you can drop the counter and simplify the name, though keeping it costs nothing and leaves room to add a second installation later without renaming anything.

Oracle offers specific recommendations on naming and placing database files to help separate different file types in an installation. This eases administration and spreads I/O across as many devices as possible. The Oracle database software, the control files and datafiles, and the administrative files all live in separate directory trees and can be moved independently of one another. Oracle calls these recommendations the Optimal Flexible Architecture (OFA). Following OFA produces a directory structure like the one below.

The Oracle Managed Files (OMF) feature automates much of tablespace management: file placement, naming, and sizing. You control it through three initialization parameters:

1. DB_CREATE_FILE_DEST
2. DB_CREATE_ONLINE_LOG_DEST_n
3. DB_RECOVERY_FILE_DEST

Set these before creating the database and Oracle uses them for placing data files, control files, and online redo logs automatically. You can also enable OMF after the database already exists; Oracle then uses the parameter values for any newly added files, generating both the location and the file name for you. This is what simplifies tablespace creation, a CREATE TABLESPACE statement under OMF needs nothing beyond the tablespace name itself. First, enable OMF by setting DB_CREATE_FILE_DEST:

SQL> alter system set db_create_file_dest='/u01';

Then issue the CREATE TABLESPACE statement:

SQL> create tablespace inv1;

This creates a tablespace named INV1 with a default data file size of 100MB. You can override that default by specifying a size directly:

SQL> create tablespace inv2 datafile size 20m;

Querying V$DATAFILE shows that Oracle created subdirectories beneath /u01 and named the files using the OMF format:

SQL> select name from v$datafile where name like '%inv%';

NAME
------------------------------------------------------------
/u01/ORCL/datafile/o1_mf_inv1_8b5l63q6_.dbf
/u01/ORCL/datafile/o1_mf_inv2_8b5lflfc_.dbf

One real limitation: OMF only points at one directory for new data files at a time. To add files somewhere else, you change the parameter dynamically:

SQL> alter system set db_create_file_dest='/u02';

That's not a huge burden, but in practice, most environments have many mount points assigned for database use, and constantly re-pointing an initialization parameter to add a file in a directory outside the current DB_CREATE_FILE_DEST definition gets old fast. Many DBAs find it simpler to just issue a CREATE TABLESPACE or ALTER TABLESPACE statement with the file location and storage parameters spelled out directly in the script, rather than lean on OMF at all. Providing directory and file names explicitly to tablespace-management statements isn't particularly cumbersome once you're used to it.

Every Oracle component on the installation media is OFA-compliant, meaning Oracle Universal Installer places components in directory locations that follow OFA guidelines by default. Using OFA isn't a requirement, but Oracle recommends it whenever a database is expected to grow substantially or whenever you plan to run multiple databases. This section covers the naming strategy behind the OFA standard:

• Number of File Systems: for a database stored on file systems that are neither striped nor mirrored, fully implementing OFA generally calls for multiple file systems on separate physical devices, commonly cited as a minimum of three, so that software, administrative files, and data files can each get their own I/O path. Treat this as a starting guideline to confirm against your specific platform's current installation documentation rather than a fixed rule, since exact guidance here has varied across releases.
• Naming Conventions: name file system mount points using the syntax /pm, where p is a string constant and m is a unique, fixed-length key, typically a two-digit number, distinguishing each mount point. For example: /u01 and /u02, or /disk01 and /disk02.

Oracle Base Directory Naming Convention