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
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Do you have any technical questions about Oracle administration or development? Post them--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature! Our Oracle gurus are waiting to answer your toughest Oracle questions.