OracleI have posted about the Oracle OFA (Optimal Flexible Architecture) and recent changes to OFA for Oracle Database 10g, but I wanted to go into a more practical application of these rules to act as a quick reference.

This is not intended to be a complete explanation of the OFA standard. For more complete information refer to the OFA whitepaper. The OFA Standard Recommendations below are taken directly from the OFA whitepaper.

OFA recommendations 9-11 pertain to very specific installation types I have chosen to exclude them. The examples below are based on a UNIX environment.

OFA Standard Recommendation 1: Name all mount points that will hold site specific data to match the pattern /pm where p is a string constant chosen not to misrepresent the contents of any mount point, and m is a unique fixed-length key that distinguishes one mount point from another.

Typical Application: Name operating system mount points with the convention /u01, /u02, /u03 etc.

Options: Anything can be used instead of the leading ‘u’, however be careful not to use something which could eventually misrepresent the future contents of the directory.

OFA Standard Recommendation 2:Name home directories matching the pattern /pm/h/u, where pm is a mount point name, h is selected from a small set of standard directory names, and u is the name of the owner of the directory.

Typical Application: The original OFA whitepaper states the oracle user home directory be placed in a directory like /u01/app/oracle.

Options: This rule is typically overlooked. The oracle user is usually given a home directory which matches other UNIX users, such as /export/home/oracle/ or /home/oracle, however the directory /u01/app/oracle (or similar) should be created for software installation (see recommendation 4). The recommended home directory (/u01/app/oracle) is typically referred to as $ORACLE_BASE.

OFA Standard Recommendation 3:Refer to explicit path names only in files designed specifically to store them, such as the UNIX /etc/passwd file and the Oracle oratab file; refer to group memberships only in /etc/group.

Typical Application: Whenever possible, refer to the oracle user’s home directory as ~oracle and use other environment variables such as $ORACLE_HOME and $ORACLE_BASE instead of full paths. As an example, you would typically use $ORACLE_BASE/admin instead of /u01/app/oracle/admin.

Options: ~oracle, $ORACLE_BASE and $ORACLE_HOME are typically used as environment variables. Other variables such as $ORACLE_ADMIN can be set and used in a similar fashion.

OFA Standard Recommendation 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.

Typical Application: Oracle server software should be stored in a directory below the home ($ORACLE_BASE) in the format $ORACLE_BASE/product/9.2. 9.2 should be replaced with the version of Oracle software installed.

Options: This could be on any of the /u01 sequence of partitions.

NOTE: For 10g Oracle has added another level to allow multiple installs of the same version of software. The new recommendation is to install software in a directory in the format of $ORACLE_BASE/product/10.1/db_1.

OFA Standard Recommendation 5:For each database with db_name=d, store database administration files in the following
subdirectories of h/admin/d:
• adhoc — ad hoc SQL scripts for a given database
• adump — audit trail trace files
• arch — archived redo log files
• bdump — background process trace files
• cdump — core dump files
• create — programs used to create the database
• exp – database export files
• logbook — files recording the status and history of the database
• pfile — instance parameter files
• udump — user SQL trace files
where h is the Oracle software owner’s login home directory.

Typical Application: Again the $ORACLE_BASE directory should be used for the home directory, resulting in a path of $ORACLE_BASE/admin/adump

Options: Not all these directories are always used; however, if there are multiple DBAs I highly recommend the adhoc and logbook directories.

OFA Standard Recommendation 6:Name Oracle database files using the following patterns:
• /pm/q/d/control.ctl — control files
• /pm/q/d/redon.log — redo log files
• /pm/q/d/tn.dbf — data files
where pm is a mount point name, q is a string denoting the separation of Oracle data from all other files, d is the db_name OFA Standard • 21 Oracle System Performance Group Technical Paper, September 24, 1995 of the database, n is a distinguishing key that is fixed-length for a given file type, and t is an Oracle tablespace name. Never store any file other than a control, redo log, or data file associated with database d in /pm/q/d.

Typical Application: Control, redo, and data files are typically stored in one or more of the /u01 series of partitions in a subdirectory called oradata then in a folder matching the database name. The resulting path should resemble /u01/oradata/orcl.

Only control, redo, and data files should reside in this path and they should have the appropriate extensions (.ctl, .log or .dbf respectively).

Control and redo files should have a fixed length number to identify them, such as control01.ctl or redo04.log.

Data files should contain the tablespace name and a fixed length number resulting in the format system01.dbf.

Options: Many place the database name in these files. This is redundant since the database name is already in the path to these files and should be avoided as it complicates changing the database name.

OFA Standard Recommendation 7: Separate groups of segments with different lifespans, I/O request demands, and backup frequencies among different tablespaces. For each Oracle database, create the following special tablespaces in addition to those needed for applications segments:
• SYSTEM — data dictionary segments only
• TEMP — temporary segments only
• RBS — rollback segments only
• TOOLS — general-purpose tools only
• USERS — miscellaneous user segments

Typical Application: Here a segment is a piece of data in the database associated with a table, index, or other database object. Basically you should represent the tablespaces listed above and any needed for the appropriate application. Do not put application data in any of these tablespaces, but instead create one or more tablespaces for each application.

Options: TOOLS and USERS tablespaces may be omitted, however it is likely you will want them in the long run.

NOTE: For database 10g Oracle has added a new tablespace called SYSAUX which contains all non-essential system components.

OFA Standard Recommendation 8: Name tablespaces connotatively with eight or fewer characters.

Typical Application: The eight character limit is not necessary; however, tablespace names should be kept fairly short. More importantly tablespace names should be indicative of their contents. WEBCT_DATA or BANNER_INDEX_SMALL are acceptable tablespace names, but DATA or INDEX would not be.

Options: There is a lot of flexibility here. Using a consistent convention at your site is key.

oracle, sql, dba, database administration, database development, database security, database, oracle security