The Art of Being a Successful DBA – Naming Conventions

Ever fumble around at 2 AM looking for that SQL statement you wrote a while back? You know, that one special script that will give you just the information you need to solve the problem and go back to bed? I must admit, I have done my fair share of moonlight script hunting. This blog will provide you with a few recommendations on naming convention best practices.

Following proper naming conventions is absolutely critical to us as a remote DBA services provider. We have dozens of DBAs on staff now that are responsible for supporting hundreds (and hundreds) of different database ecosystems. To ensure DBAs can seamlessly tranfer from one environment to another, we must establish standard naming conventions and document everything in our customer’s environments.

Each one of our DBAs can go to any platform and quickly navigate to the script and monitoring tool 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 we have selected a group of technicians that are responsible for creating, adjusting and auditing naming conventions here at RDBAE. These folks, led by Doug Russel, our Proactive Monitoring Center Team Lead, are responsible for laying the groundwork to ensure that our naming conventions and best practices are being followed.

I’ll continue our discussion on naming conventions by providing you with a few sample 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, do a Google search on the key words “Optimal Flexible Architecture”.  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 administration/monitoring, hot and cold backups (RMAN or OS), loads, exports/DataPump 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 keeping track of all script modifications and ensuring a rigorous review and test plan is executed on the new scripts before they are installed on the supported servers.   Monitoring scripts are even more crtical. They are the foundation of the DBA’s support activities. If your monitoring scripts aren’t working, you are failing in your role as “protector of the organization’s key data assets”.  Here at RDBAE, we have a virtual team that is assigned the responsibility of keeping track of literally dozens, and dozens, of Groundwork monitoring plugins. These technicians use an off-the-shelf code library with full check in/check out features and review capabilities (for approval before release).

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_012609.out would be the output from the DDL statement execution that created the MCFINEMP1 table on January 26, 2009.

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.  Indexes can also be used as the physical mechansim to ensure key value uniqueness.
Naming Conventions:
Format #1   -   tb_idx_uu
tb – table name (abbreviated if required)
idx – constant identifying this object as an index
uu – ascending numeric or character index identifier (used to identify multiple indexes per base table)
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
Director Of Service Delivery