The Non-Technical Art of Being a Successful DBA - Naming Conventions

Following proper naming conventions is absolutely critical to us as a remote database services provider. We have a couple of dozen DBAs on staff now that are responsible for supporting hundreds (and hundreds) of different database ecosystems. We have a standard set of naming conventions for our administrative directories. Although we sometimes didn't follow them in the past - we do now.

Each one of our DBAs can go to any platform and quickly navigate to the script directories, find the script they are looking for and execute it. I feel that this is so important to the quality of support we provide that I have selected a few DBAs to be members of a team that are responsible for creating and auditing naming conventions here at CTi. These folks, led by Ace DBA Jeff Kondas, review environments to ensure that our naming conventions and best practices are being followed. They find few deviations because this best practice has become integrated into our organization's psyche. Very good news.

I'll continue our discussion on naming conventions by providing you with a few recommendations. As always, these recommendations are not intended to be all-inclusive. They are a few examples to help start you in the right direction.

Follow OFA Naming Conventions
OFA stands for Optimal Flexible Architecture. The OFA standard is a set of naming conventions and configuration guidelines that:

• Distributes I/O among different disk drives.
• Facilitates ease of administration by creating naming conventions for mountpoints, directories, file suffixes, database binary directories and database output (i.e, background dump, core dump).
• Improves the DBA's ability to manage and administer database growth.

These standard naming conventions and placement guidelines are intended to improve database performance by spreading I/O, protect against drive failures and also allow administrators to more easily assume the responsibility of supporting new database environments. In addition, because OFA standards are well documented by the Oracle Corporation, newly hired DBA and consultants are able to more quickly assume administration responsibilities.

To learn more about OFA, please visit Oracle's Technet website. Go to the top right hand corner of the page and type "OFA" in the search box. You will find all of the documentation you need!

Create and Standardize Monitoring and Administration Scripts
All DBAs have their own set of favorite Oracle administration and monitoring scripts. They find them on the web, get them from third-party books and trade them like bubble-gum cards. They then tune, tweak and tailor them until they fit their own unique style of administration.

It is highly recommended that database administration units create a set of scripts for daily monitoring, hot and cold backups, exports and common administrative activities. This library of scripts can then be installed on each server administered by the team. Personalizing the scripts should be highly discouraged. Any modifications to the library can be reviewed during team meetings. Assign a team member the responsibility of being the script library owner. The script library owner will be responsible for making all script modifications and installing the new scripts on the supported servers. Here at CTi, we have a virtual team that is assigned this responsibility.

All scripts and directories should have names that allow them to be easily recognized. It irks me to no end when I log in to a server, navigate to a script directory and find files named SQL1.SQL, SQL2.SQL, FIXIT.SQL, TUNEIT.SQL… Fix what? Tune what? What the? Personally, I would prefer to have a SQL file name that is long, but descriptive, as opposed to a name that doesn't provide me with any clue on its contents.

It is also important to follow a strict naming convention for your output files. Whether the output is from a SQL, DDL or operating system command, the output file should be placed in the appropriate directory and have a descriptive name. The file name should also contain the date the output was created.

For example:

create_mcfinemp1_table_012606.out would be the output from the DDL statement execution that created the MCFINEMP1 table on January 26, 2006.

Because many of our customers have a preference where we store our scripts and internal monitors, we have created our own directory naming conventions:

Object Naming Conventions
This is the area where having a Data Administrator available is certainly beneficial. These are the folks that are trained in naming convention best practices. If you don't have the good fortune of having a DA available, it will be up to you and your application developer counterparts to create a set of naming convention guidelines. It is important to create a naming convention that contains abbreviation guidelines that are used to shorten object names.

Here's an example of an index naming convention I have used in the past:

INDEX
Definition: An index is an object that ensures efficient access to the data by using an ordered set of pointers to the table's data rows.

Naming Conventions:

Example: MCFINEMP_IDX_01 would be the first index on the MCFINEMP table.

There are dozens of guidelines available on the web. In addition, many of the books on Data Administration provide suggested naming conventions. We also have our own personal preferences. For example, many DBAs like to include column name abbreviations in their index names.

What the standard naming conventions are is not as important as creating them and adhering to them religiously. That is the key to success.

These recommendations are not intended to coerce readers into using the provided naming convention examples verbatim but to emphasize the importance of a creating a set of standardized naming conventions and then strictly adhering to them.

Thanks for Reading,

Chris Foot
Oracle Ace