There (still) is much speak about OFA, Oracle Flexible Architecture. The reason for OFA is obvious (OFA is designed to organize large amounts of complicated software and data on disk, simplify administration tasks, maximise performance and assist switching between Oracle databases.)
I work in a team which administers +200 databases and we thought out a OFA strategy, for all the reasons stated above. (this document applies to unix/linux filesystems, but can be adjusted to windows too)
First of all: use a single place for all “oracle stuff”. This means everything we use for the oracle database. We use “/oracle”. This way, all the oracle database stuff is concentrated on one place on our system, and it’s easy for the linux/unix system administrator to see if something is belonging to oracle.
Before you ask: please remember it is not mandatory to mount a filesystem directly under root.
Next, there are more oracle products than the database. So the second stage should be “db” (/oracle/db)
At this level, we have a place where all the stuff for an oracle database should be. At this place we can make a distinction of what we could encounter at this point. That can be any of the two following things:
-the database software of a certain version
So, to install the database software, it should be in “/oracle/db/” (eg, /oracle/db/10.2.0.1)
There are a few more things which are very important for the database software.
-We use a seperate mountpoint for each version (meaning /oracle/db/10.2.0.1 is a mountpoint). This way the space can be given back when we stop using the software of a certain version.
-No instance specific files are allowed in the software filesystem. Some files (like network files) can be moved using a environment variable (TNS_ADMIN), some are needed at a certain point, so we use symbolic links to ‘link them back’ to an instance specific place (like password file and pfile/spfile). This helps the instance directory (eg, /oracle/db/myinstance) in being perfectly movable to another machine, because it’s self contained)
And, for all the files concerning a database instance, it should be in “/oracle/db/” (eg, /oracle/db/test). Underneath this directory, we have a large number of instance specific things:
-udump, bdump, adump, cdump (the dump dirs)
-pfile (for the pfile/spfile and password file)
-utlfile (if UTL_FILE_DIR is used)
-exp (exports directory/mountpoint)
-arch (archive directory/mountpoint)
-scripts (instance specific scripts, creation scripts)
-data (for database files and redologfiles)
this is the directory where the important stuff is going. There is a little structure here:
->we need at least two online redolog directories. These need to be mountpoints (to spread IO), and can be even more, depending on your needs. Minimum is two (which should at least contain two redolog groups each, so redo-IO is switched back and forth between the two mountpoints).
->this is the mountpoint for the datafiles. Of course, ‘fs’ means filesystem, and we could add as many of this mountpoints as we need. Also, it’s easy to monitor IO’s with the Oracle disk manager to see of the IO load is spread well enough for any given number of mountpoints and databases.
There are a few more things which we need to specify:
-oracle scripts and config files
Oracle net is global to the databases. Even if we got more databases, we use one listener most of the time (so the listener configuration should not be at the database level). The networking files (tnsnames.ora, sqlnet.ora, listener.ora, ldap.ora, etc.) should be in /oracle/net/admin. Update the .profile of the oracle database user to set TNS_ADMIN to this directory.
The oracle scripts are the ones which root.sh puts in /usr/local/bin by default. Because we want to have access to these scripts as dba’s, and we do not have root rights most of the time, we move these files to /oracle/admin/bin (after root.sh has put them there, we have root rights at that time), and put a symlink in /usr/local/bin. This way we have full access to these scripts, without the need for root.
There are two files (if emtab is needed, it follows oratab) left which we want inside /oracle, but all the oracle software expects them at another place:
these files are needed for some scripts and tools, so it’s wise to put them in the /oracle structure as well, and put a symbolic link at the place oracle expects them to be. Put the files in /oracle/admin/etc.
This way we achieved the following:
-all oracle files are in /oracle
-all files concerning an instance are grouped in /oracle/db/
-if a new version or a new patchlevel of the software is installed, it’s easy to move an instance to this software
-to move a database to a new machine, it’s already grouped and simple to move
-the distribution of filesystems is easy to see
-it’s easy to add filesystems to a database for both database files and online redologfiles