Problem solve Get help with specific problems with your technologies, process and projects.

Oracle flexible architecture layout

Base your Oracle directories on the Oracle Flexible Architecture layout (OFA).

In order to support a robust environment for Oracle, the best basis for your Oracle directories should be based on the Oracle Flexible Architecture layout (OFA).

In addition to the OFA guidelines, there are some additions that I like to make:

Create an environmental variable ORACLE_ADMIN and point it to ORACLE_BASE/admin. Use this directory to house all your database directories, and their subdirectories, dump directories (adump, bdump, cdump, udump), your parameter files (pfile), the scripts used to create the database (create), and a directory for all the addition SQL used for this database (admin).

I like to use the admin directory to store all security scripts (creation of roles, grants to users), plus the scripts for the creation of database links, and any special synonym creations. Make sure you use prompts in any SQL that uses passwords (like database link creations). The use of the admin directory makes the creation of copies of the database much easier, and makes life easier for other DBAs who are trying to fill in for you.

Under the ORACLE_ADMIN directory you should also create a sql and bin directory to store all common SQL scripts, and shell scripts that you use to administer Oracle. Make sure the you add ORACLE_ADMIN/sql to your ORACLE_PATH (this is the search order use by sqlplus to locate SQL scripts) and add ORACLE_ADMIN/bin to your PATH. For Oracle 7.x users, change your SQLPATH variable instead on ORACLE_PATH, and upgrade your database.

When it comes to the file structure for Net8, I like to place the tnsnames.ora and sqlnet.ora files in the /etc directory. The biggest advantage to this is that these files are in a single location regardless of how many versions of Oracle you are running.

About the Author

James Giordano is an Oracle database administrator. He has been working with Oracle for about seven years, and also has experience with UNIX and PeopleSoft/Oracle financials.

For More Information

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.