DB Creation   «Prev  Next»

Lesson 3Optimal Flexible Architecture
ObjectiveUnderstand Oracle's recommendations for placing Oracle software and database files.

Optimal Flexible Architecture while preparing to create Database

Optimal Flexible Architecture is a set of guidelines and naming conventions, nothing more. You do not have to follow it, and many sites do deviate to some degree or another. I suppose that without too much trouble, someone could dream up a different but equally flexible set of conventions for placing and naming the directories and files used in an Oracle installation. I think one of the biggest benefits of OFA is that it has been so widely adopted. Once you get used to it, you will find that you can move from one client to the next, and immediately be productive because everything is set up in pretty much the same way. There is a lot to be said for that. OFA defines where each component will install its files. In many respects it resembles the Filesystem Hierarchy Standard (FHS) on UNIX systems in which, for example, the directory "/bin" always holds the essential system binaries, so when administrators and users use other systems they will already know where to find the standard system binaries. OFA takes FHS standard-style concepts and uses them for Oracle products on UNIX and on Windows. In this way Oracle Database administrators will find familiar structures and locations of the various applications and data installed on any OFA-compliant system. OFA covers where to install each part of each product; it addresses the storage of both applications and data. Much like the FHS, OFA imposes no constraints on the locations: it merely makes recommendations. Oracle Corporation has structured the OFA system so that system administrators can use multiple disks (for example: applications on one disk and databases on another). OFA also allows for installing multiple versions of the same product on the same host: for example Oracle Database 9 and Oracle Database 10.

Oracle Base Directory

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

Typical names for the mount point include /u01, /ora01, /oracle, and /oracle01. You can name the mount point according to whatever your standard is for your environment. I prefer to use a mount-point name such as /ora01. It is short, and when I look at the mount points on a database server, I can immediately tell which are used for the Oracle database. Also, a short mount-point name is easier to use when you are querying the data dictionary to report on the physical aspects of your database. Additionally, a shorter mount-point name makes for less typing when you are navigating through directories via OS commands. The software owner is typically named oracle. This is the OS user you use to install the Oracle software (binaries). Listed next is an example of a fully formed Oracle base directory path:

Oracle Home Directory
The Oracle home directory defines the installation location of software for a particular product, such as Oracle Database 12c or Oracle Database 11g. You must install different products or different releases of a product in separate Oracle homes. The recommended OFA-compliant Oracle home directory is as follows:

In the previous line of code, possible versions include and Possible install_name values include db_1, devdb1, test2, and prod1. Here is an example of an Oracle home name for a 12.1 database:

Note: Some DBAs dislike the db_1 string on the end of the ORACLE_HOME directory and see no need for it. The reason for the db_1 is that you may have two separate installations of binaries: a development installation and a test installation. If you do not require that configuration in your environment, feel free to drop the extra string (db_1).

Oracle Corporation offers specific recommendations on the naming and placement of database files to help you separate the different types of files in an Oracle installation. This eases administration and spreads I/O across as many devices as possible. The Oracle database software, the control files and the datafiles, and the administrative files are all stored in separate directory trees and can be moved around independently of each other. Oracle refers to their recommendations as the Optimal Flexible Architecture (OFA). Following OFA creates a directory structure like the one below. Move your mouse over the following diagram for more explanations.
Optimal Flexible Architecture
/oracle --> product
  1. oracle: The top-level directory is called the Oracle Base directory. On UNIX systems, the ORACLE_BASE environment variable will point to this. Windows uses a registry entry named ORACLE_BASE to point to this directory.
  2. product: Software for Oracle products is installed under this directory.
  3. 815: Multiple releases of Oracle may be installed on the same machine. The first three digits of the release number form the directory name. This directory contains the Oraclei 8.1.5 release. Using separate directories for each release enables you to install a new release, or delete an old one, without affecting the others. These directories are referred to as Oracle Homes.
  4. 734: This directory would contain the 7.3.4 release of Oracle, and would also contain subdirectories named bin, rdbms, orainst, and so forth.
  5. bin, rdbms, orainst, etc.: A number of different directories are used to contain various elements of the Oracle database software.
  6. admin: This directory tree contains administrative files such as scripts, parameter files, and database alert logs.
  7. prod, devl, test: Each Oracle database gets its own subdirectory underneath the admin directory.
  8. pfile: Contains parameter files for the prod database.
  9. create: Contains database creation scripts for the prod database.
  10. bdump: Contains the alert log and trace files for the prod database.
  11. data: Under UNIX, you can place all the mount points used for data files under the data directory. Not all sites do this however. Under Windows NT you can't do it at all, because that would force you to put all database files on the same physical disk.
  12. ora01, ora02: Mount points used for database files are usually numbered, and are also named to make it obvious that they contain Oracle database files.
  13. prod: This directory contains files for the production database.
  14. devl: This directory contains files for the development database.
  15. test: This directory contains files for the test database.

Using Oracle Managed Files

The Oracle Managed File (OMF) feature automates many aspects of tablespace management, such as file placement, naming, and sizing. You control OMF by setting the following initialization parameters:
If you set these parameters before you create the database, Oracle uses them for the placement of the data files, control files, and online redo logs. You can also enable OMF after your database has been created. Oracle uses the values of the initialization parameters for the locations of any newly added files. Oracle also determines the name of the newly added file. The advantage of using OMF is that creating tablespaces is simplified. For example, the CREATE TABLESPACE statement does not need to specify anything other than the tablespace name. First, enable the OMF feature by setting the DB_CREATE_FILE_DEST parameter:
SQL> alter system set db_create_file_dest='/u01';
Now, issue the CREATE TABLESPACE statement:
SQL> create tablespace inv1;

This statement creates a tablespace named INV1, with a default data file size of 100MB. Keep in mind that you can override the default size of 100MB by specifying a size:
SQL> create tablespace inv2 datafile size 20m;

To view the details of the associated data files, query the V$DATAFILE view, and note that Oracle has created subdirectories beneath the /u01 directory and named the file with the OMF format:
SQL> select name from v$datafile where name like '%inv%';

One limitation of OMF is that you are limited to one directory for the placement of data files. If you want to add data files to a different directory, you can alter the location dynamically:
SQL> alter system set db_create_file_dest='/u02';

Although this procedure is not a huge deal, I find it easier not to use OMF. Most of the environments I have worked in have many mount points assigned for database use. You do not want to have to modify an initialization parameter every time you need a data file added to a directory that is not in the current definition of DB_CREATE_FILE_DEST. It is easier to issue a CREATE TABLESPACE statement or ALTER TABLESPACE statement that has the file location and storage parameters in the script. It is not cumbersome to provide directory names and file names to the tablespace-management statements.

Overview of the Optimal Flexible Architecture Standard

All Oracle components on the installation media are compliant with Optimal Flexible Architecture, which means that Oracle Universal Installer places Oracle Database components in directory locations that follow Optimal Flexible Architecture guidelines. Although using Optimal Flexible Architecture is not a requirement, Oracle recommends that you use it if the database will grow in size, or if you plan to have multiple databases. This section describes the naming strategy recommended by the Optimal Flexible Architecture standard. It contains the following sections:
  • Number of File Systems: To fully implement the Optimal Flexible Architecture recommendations for a database stored on file systems that are not striped or mirrored, you require at least three file systems located on separate physical devices.
  • Naming Conventions: Name all 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) used to distinguish each mount point. For example: /u01 and /u02, or /disk01 and /disk02.

Oracle Base Directory Naming Convention

The Oracle Base directory is the top level directory that you can use to install the various oracle software products. You can use the same Oracle base directory for more than one installation. If different operating system users install Oracle software on the same system, then each user must create a separate Oracle base directory. Name Oracle base directories using the syntax /pm/s/u. Table 6-3 describes the variables used in this syntax.
Table 6-3: Syntax for Naming Oracle Base Directories
Variable Description
pm A mount point name
s A standard directory name
u The name of the owner of the directory (the user running Oracle Universal Installer)

There is more to the Optimal Flexible Architecture than just the directory structure shown here. We will be following the OFA guidelines as we create the course database, and I'll be pointing out other features of the OFA as we go along.

SEMrush Software