oracledba.help

Scripting Best Practices

<- Articles

Overview

The following material is an executive overview of how and why enterprise environments write and maintain internal maintenance scripts. It will enable you to write more effective and easily maintainable scripts.

Maybe this material is not for you. Let's see: Do your maintenance scripts work 99.99% of the time? When a script crashes can you determine why in less than 15 seconds? Do you know exactly how long a script takes to run in the past, present and future? If you answered no to any of these this topic may be for you!

Standardization

I frequently attend Oracle University training classes and similar events. Often, fellow attendees are implementing large and diverse IT systems. I always take the opportunity to ask them:

Q:
What is the most important factor you can attribute to the success of your environment?

A:
Standardization.

Standardizing to one language for maintenance scripts guarantees better stability and support. New employees ramp-up more quickly, and making changes to your script code base becomes a snap.

Standardizing the location of your scripts goes hand-in-hand with standardizing the script language. In an enterprise environment you must move quickly from system to system. Because of this you want your scripts to be in the same place on every system.

When I show up in an IT shop I always ask: What is your standard language for writing maintenance scripts? If I get weird looks and fuzzy answers I know the shop has issues. This is because an environment can only produce what it has been architected for (see my guide on environment best practices).

It does not matter which script language you use. Let me say that a different way: it does not matter which script language you use! All script languages have their strong areas and weak areas. Religious scripting wars aside, most native OS script languages will do fine. For UNIX, some include Bourne, C-Shell and BASH. For Windows there is batch, VBScript and Power Shell. They are all functionally adequate. There is also Perl and Lua. I have used all of the script languages mentioned here, and have never had an instance with any of them in which I could not write a script to perform a required maintenance task.

This can end forever the issue of requiring a "specialist" in each script language, as everyone becomes the expert. I'm sure you are familiar with scenarios like this: "We cannot change that until Sheryl gets back. She is the only one who knows how to program in WackyScript."

It should be noted that it is essential to have a test environment where technicians can freely test any and all scripting languages to their hearts' content. They need a place without any practical limitations, to learn their craft and test new technologies without affecting production systems. This can actually improve morale.

But Production is the domain of System DBAs and System Administrators. The rules are different here. For production systems you want to standardize to one scripting language.

Separate the Script Configuration From the Process

Breaking your scripts into two sections as outlined here will make them easier to maintain and more reliable. The first section is for your documentation preamble and user variables (configuration). The second section has the guts of the script, your process routines. In this way you can make changes quickly and easily. You can also immediately determine what is unique about this script as opposed to another of the same name and version.

You want to avoid "hack-like" changes, i.e. global string replace operations, as they invalidate version control. Also, these types of changes tend to produce greater instances of failure.

The optimal way to handle separating your configuration from processing routines is to put all your configuration in an include file. In this way the code versions (logic and processing area) can be easily updated without effecting the code configuration.

Example Section 1 - Documentation Header and Configuration (traditional approach):

:: ================================================================================
:: Purpose: Run windows disk defrag.
:: Version: 2.1
:: OS:      Windows 2000/2003/2008
:: --------------------------------------------------------------------------------
:: DRIVES   Space delimited list of drive letters.
::          Example 1: C
::          Example 2: C D R
:: --------------------------------------------------------------------------------
:: Comments:   Run this batch file from the Windows scheduler.
:: ================================================================================

:::::::::::::::::::::::::::::::::::::::::::
:: Configuration
:::::::::::::::::::::::::::::::::::::::::::
set DRIVES=C D X

Example Section 2 - Process Routines

:::::::::::::::::::::::::::::::::::::::::::
:: Process
:::::::::::::::::::::::::::::::::::::::::::
FOR %%D IN (%DRIVES%) DO (
   defrag %%D: -v
)

:::::::::::::::::::::::::::::::::::::::::::
:: End
:::::::::::::::::::::::::::::::::::::::::::

This is pretty straight forward yet very effective. One environment I worked in mandated that a banner be placed after the configuration section with the verbiage:

===== DO NOT CHANGE ANYTHING BELOW THIS LINE =====

Output Session Information and Historical Run Times

In most enterprise environments it is vital to capture both script session information and the script historical execution times. You want to create a log for each, for ease of interpretation. The session output is useful in determining if a script ran as expected, and to quickly determine why any error occurred. The historical output allows you to quickly determine how long a script takes to execute and also its trends. Many in smaller shops do not see the need for a separate log for history but with huge systems and databases with limited job execution time windows this information is critical in decision making.

It is common to have a light API that you use from script-to-script for this purpose.
Here is an example:

set SCRIPTNAME=%~n0
set DATE_TIME_START=%date% %time%
set SLOG=c:\oracle\scripts\logs\%SCRIPTNAME%.session.log
set HLOG=c:\oracle\scripts\logs\%SCRIPTNAME%.hist.log

The SLOG variable is for tracking your script execution session, while HLOG is for tracking historical run information. Review the usage below:

:::::::::::::::::::::::::::::::::::::::::::
:: Process Start (Init Logs)
:::::::::::::::::::::::::::::::::::::::::::
echo %SCRIPTNAME% Started: %DATE_TIME_START% >> %HLOG%
echo %DATE_TIME_START% > %SLOG%

:::::::::::::::::::::::::::::::::::::::::::
:: Process
:::::::::::::::::::::::::::::::::::::::::::
FOR %%D IN (%DRIVES%) DO (
   echo %time% - Processing drive: %%D >> %SLOG% 2>&1
   defrag %%D: -v >> %SLOG%
)
echo.&&echo.

:::::::::::::::::::::::::::::::::::::::::::
:: End
:::::::::::::::::::::::::::::::::::::::::::
set DATE_TIME_END=%date% %time%
echo %SCRIPTNAME% Ended
echo =============================================
echo %SCRIPTNAME% Ended:   %DATE_TIME_END% >> %HLOG%
echo %DATE_TIME_END% - %SCRIPTNAME% Ended  >> %SLOG%
echo Started:  %DATE_TIME_START%
echo Ended:    %DATE_TIME_END%
echo.&&echo.

Here is a snippet from a session.log:

Sun 07/13/2008 19:00:00.12
19:00:04.19 - Processing drive: C
Windows Disk Defragmenter
Copyright (c) 2003 Microsoft Corp. and Executive Software International, Inc.

Analysis Report                    

    Volume size                      = 68.20 GB
    Cluster size                     = 512 bytes
    Used space                       = 33.79 GB
    Free space                       = 34.41 GB
    Percent free space               = 50 %

Volume fragmentation               
    Total fragmentation              = 0 %
    File fragmentation               = 0 %
    Free space fragmentation         = 0 %

File fragmentation                 
    Total files                      = 53,121
    Average file size                = 775 KB
    Total fragmented files           = 41
    Total excess fragments           = 1,266
    Average fragments per file       = 1.02

Here is a snippet from a history.log:

defragDisks Started: Sun 06/08/2008 19:00:00.10
defragDisks Ended:   Sun 06/08/2008 19:13:00.10
-----------------------------------------------
defragDisks Started: Sun 06/22/2008 19:00:00.10
defragDisks Ended:   Sun 06/22/2008 19:15:29.65
-----------------------------------------------
defragDisks Started: Sun 07/06/2008 19:00:00.10
defragDisks Ended:   Sun 07/06/2008 19:16:29.48
-----------------------------------------------
defragDisks Started: Sun 07/13/2008 19:00:00.12
defragDisks Ended:   Sun 07/13/2008 19:20:44.66

Proper Testing

Many books have been written on testing. Suffice it to say, scripts need to be treated just as any other mission critical code in enterprise environments. This means version control and testing.

ANY changes to the process section of your script needs to be tested on a unit test system. If the changes prove to be stable, then move the script to larger test systems. Again, after it has proven stable, rollout your new script version, starting with production databases that have the least impact on the business mission. Then migrate it to larger systems until the rollout to all your systems has been completed.

Code for Your Largest Environments

One of the most significant factors of code stability and scalability is the coding target. In other words, you should write your code for your largest and most complex systems and databases. In so doing you will spend less time writing (and re-writing) code. Code written for a large, complex system scales better when utilized on a smaller system, rather than vice-versa. If code has been written for a database with only a few schema and less than 500 megabytes of data, then it should be no surprise that it fails to run on a 5 terabyte database with hundreds of connections!

It is to your advantage to write code scalable to your largest and most complex system. You will have just one script per task. For example, you only need one script to backup all your databases, regardless of their size and complexity. From a 500 megabyte Oracle database to a 5 terabyte Oracle database - same script. This eases the support of systems and databases tremendously.

Yep, this is programming 101, but many shops outside the Fortune 200 fail miserably to invest in this area which, ironically, produces the greatest return! Management must have the vision to see this and communicate this, regardless of the short term plaints voiced by programmers OR bean counters! You can write a feature once, or over-and-over again wasting colossal programming hours/dollars. If you have unlimited resources then this is not a big deal. Do you?

Most of you do not. Your programming resources, personnel and budget, are constrained. Why not do it right the first time?

Have Your Scripting Policies Mandated From Above

The material covered here is useless if management cannot or will not mandate it. If it does not apply to all technologists equally, then these principles cannot work. If you instruct one administrator that he/she must use BASH while all the other administrators use various combinations of Bourne, Perl, C-Shell and Lua, then your systems and corresponding mission objectives will experience failure.

Summary

A paradigm shift, from micro IT environment to enterprise scale environment, comes when code is written in a manner best suited for long-term business mission continuity, rather than what is immediately convenient for individual technologists. Writing maintenance scripts in multiple languages introduces unnecessary risk that a mature business in growth mode with client obligations simply cannot afford. Writing scripts as outlined in this material is nothing more than maturing your environment. Raising the bar. Moving from a carefree "anything goes" type scripting environment to one of enterprise level quality and stability.

In summary, to gain the most benefit from writing maintenance scripts:

  • Standardize to one script language and location.
  • Break scripts into two sections: configuration and process.
  • Output session information and historical run times separately.
  • Properly test and use version control.
  • Code for your largest environment.
  • Have management mandate your scripting policies.

<- Articles