Windows Scripting for Oracle DBAs

Scripting

Database Administrators and Systems Administrators both know that they have a large number of routine, repetitive tasks to perform that are subject to error, either simple typographical error or by omitting or repeating steps inadvertently. These categories of tasks are eminently suitable for scripting.
On Unix platforms such scripting is often performed by means of a shell script indeed job descriptions for database administrators often list shell scripting as a required skill. The windows platform shell (cmd.exe) is a relatively poor environment for shell scripting, largely due to its backwards compatibility with the MS-DOS PC environment common in the 1980s and 1990s.This is often leads Unix administrators to consider the Windows environment as lacking in facilities to script repetitive administration tasks. There are however a number of technologies available to the administrator on windows platforms that readily allow scripting of regular maintenance tasks.

Most windows scripting should probably be done, not using the command line but using an environment designed specifically for the job. Microsoft Windows provides a generic environment and a number of technologies that are useful for administration scripts of this nature. Specifically these are the windows scripting host, a choice of scripting languages and the windows management instrumentation interface.
In addition there are a number of viable alternative approaches that will be briefly discussed at the end of the article. In general these alternative approaches require the installation or purchase of additional software on the database server itself. For now therefore we will examine using the windows script technologies to automate a number of simple example administration tasks, namely;

Whilst some or maybe most of these specific tasks could be accomplished by use of the Oracle 10g Enterprise Manager or Grid Control products, the development will illustrate most of the key techniques of Windows scripting as well as being applicable to environments which choose not to manage the database via EM.

Introduction to Windows Scripting Host (WSH)

The Windows scripting host is an environment that hosts scripts to automate routine tasks These scripts are written in a programming language that windows script can host. The 3 most popular are VBScript, Jscript or Perl. The host itself comes in the form of two executables wscript.exe which runs programs in the windows GUI and cscript.exe which is a command line interface. For the purposes of automation I almost always use cscript.
Wsh itself can run programs, access network drives, manipulate registry settings and environment variables and so on. In addition it allows access to COM objects. Of particular interest are the FileSystem Object which allows file operations – for example reading the alert.log, and the sets of objects exposed by the management technology Windows Management Instrumentation (wmi) which is provided with current Windows releases. WMI is a framework for interacting programmatically with systems management information. For our purposes it is the source of management information about database services, and service state that will be of interest to us.

Typical Script Structure

A typical windows script has the following structure.

In addition if the script is a fully fledged wsh script, then the script can contain a preamble describing the script itself, detailing usage information and so on. This feature greatly aids the maintainability and clarity of the script – the alert.log monitoring script illustrates this technique.

Stopping and Starting an Oracle Database

One task that a DBA might commonly wish to automate is the starting and stopping of the windows services, on UNIX platforms Oracle supply a couple of scripts (dbstart and dbshut) that provides equivalent functionality. Here we present a couple of small (less than 30 line) scripts that achieve the same result.
This script illustrates the pattern we described before, initially we define the object variables which we will be using – here we need the wmi interface itself and a collection object which will contain references to the services we are interested in, finally we will also include a third variable for the computer name for the database server which we wish to manage. In this case I have just used the “.” character to denote the local machine, but with appropriate security this script could manage database services in any database server on your network. In keeping with my preference the script then calls a start service or stop service subroutine to start or stop the necessary services in the appropriate order. This subroutine follows a standard pattern common to all wmi scripts
The first step is to establish a connection to the management service on the target computer. This is achieved by calling VBScript's Getobject function and passing the name of the WMI Scripting Library's moniker, which is "winmgmts:" followed by the name of the target computer, in our case this is contained in the strComputer variable.
The second step consists of determining which services we need to start. This is achieved by issuing a SQL-like query against the management service. This query is actually in a language called WQL (WMI Query Language) which is a subset of SQL. In our case we are querying for services that have a display name that matches the string variable we pass to the subroutine. This query returns a collection of wmi service objects that reference the actual services that we are interested in. Those of you familiar with SQL may wonder why we use the equality operator repeatedly rather than the like operator. This is because LIKE is not supported in WQL until Windows 2003/XP.
The last and final step is to iterate through the collection we received as a result of our query and start or stop each service. This is achieved by calling the StartService or StopService method exposed by the wmi service object

The code for these scripts are available in the download as dbstart.vbs and dbshut.vbs. The code for dbstart is shown in the listing below.

dbstart.txt


' Name: dbstart.vbs
'
' Date: 24 Feb 2006
'
' Author: Niall Litchfield
'
' Purpose: Start Oracle Database Services
'
' Usage: Cscript.exe dbstart.vbs

sub StartService(strServiceName)
' Build the wql query
strQuery = "Select * from Win32_Service where DisplayName = '" & strServiceName & "' and status <> 'Running'"
' execute the query to return a collection of service references
Set objOraServices = objWMIService.ExecQuery(strQuery)
' iterate through collection starting stopped services
For each objService in objOraServices
intStart = objService.StartService()
next
end sub


Implementing an alert script

The next part of our utility is a script that will alert the dba via email whenever the database service stops and the database is therefore unavailable. This script illustrates the ability of windows scripts to respond to operating system events. This is done by registering a notification event with Windows and then runs in a loop to detect when the event fires. When the event does occur we trigger an alert email to be sent to a system administrator and resume listening.
Step 1 is identical and just consists of establishing a wmi connection in the standard manner
Step 2 however instead of issuing a straightforward query issues a Notification Query, this still uses WQL and is fairly straightforward to read, the effect however is different, instead of returning information from the wmi datastore, it creates a notification request with wmi, and allows the script itself to handle the notification.
Step 3 therefore is also different and consists of looping indefinitely until the notification is received by the script, at this point we create a simple mail transport protocol message with appropriate information and mail it via our domains smtp server to the designated administrator. This step relies upon the CDOSYS mail libraries to be installed, which they will be on windows 2000 and later. They are a free download for earlier systems.
The script is shown below

monitor_services.vbs


' Name: monitor_services.vbs
'
' Date: 24 Feb 2006
'
' Author: Niall Litchfield
'
' Purpose: Monitor Oracle Database Service State
'
' Usage: Cscript.exe monitor_services.vbs

sub SendAppMail(strSubject,strText)
Set objMessage = CreateObject("CDO.Message")
with objMessage
.Subject = strSubject
.From = strSender
.Sender = strSender
.To = strTo
.TextBody = strText
.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strMailHost
.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = NONE
.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
end with
objMessage.Configuration.Fields.Update
objMessage.Send
set objMessage = Nothing
end sub


Management of Archived Redo Logs

One task with which all DBAs will be familiar with is the need to ensure both that sufficient archived redo is available for recovery purposes and that the archived redo destination does not become full. It is common practice to manage this by determining how much redo is generated in a typical day, allocating several times this amount of storage for the archive destination and keeping a rolling window of a number of days worth of archived redo on disk. The script manage_archives.vbs in the download is designed to fulfill the regular purging of old archives that this strategy requires. This script demonstrates the use of the FileSystem object for scripting file operations.
The script first defines a couple of constants – in practice they could be parameters passed to the script, a technique we will see with the alert.log example – for the number of days worth of redo to keep and the location of the archived logs.
The routine that does the work, called DeleteArchives takes two parameters; the directory name for the archive destination and a date that is calculated as the current date less the retention period specified by the iDays constant. The routine then creates an instance of the FileSystemObject which is supplied as part of the scripting runtime libraries. This object acts as a reference to the server filesystem. The code then obtains a handle on the filesystem folder that contains our archived redo by calling the GetFolder method exposed by the filesystem object. The folder object itself exposes a collection of files that are stored in the folder and the code iterates through these and deletes any that are older than the required retention period. Finally the script cleans up in the usual way.

manage_archives.vbs


' Name: manage_archives.vbs
'
' Date: 24 Feb 2006
'
' Author: Niall Litchfield
'
' Purpose: Maintain log_archive_dest folder
'
' Usage: Cscript.exe manage_archives.vbs
'
const iDays = 7
const arch_dest = "location of files to delete"

Sub DeleteArchives(strFolder,modDate)
Dim fileSystem ' Filesystem Object
Dim folder ' Folder
Dim file ' File Object
set filesystem = createobject("Scripting.FileSystemObject")
set folder = fileSystem.GetFolder( strFolder)
For each filename in folder.files
set file = fileSystem.GetFile( fileName)
dateLastModified = file.DateLastModified
' if file is older than iDays
if dateLastModified < modDate then
fileSystem.DeleteFile (fileName)
end if
next
' cleanup
set folder = Nothing
set filesystem = Nothing
End Sub


Monitoring the Alert.log

A task common to all DBAs is that of monitoring and managing the alert log for the database instances that they are responsible for. This is only one of a number of logs that the DBA and system administrator monitor on a regular basis. The techniques shown here will be applicable to all such file monitoring tasks.
The script that is available in the download is our first fully fledged Windows Scripting Host script, rather than the vbs scripts we have been using the scripting host to execute. The script is named ReadAlert.vb.wsf and is shown in listing 4. I have used a naming convention of <purpose>.<language>.wsf to help me distinguish between code in different languages, you may wish to use a different convention.
The first thing to notice is that the file is in an XML style format. The script itself is now enclosed in script tags and there is a preamble. The preamble is used for two things here. The first is to define the expected parameters to the script including datatypes, and the second is to provide documentation so that the expected usage can be shown at runtime.
Since this script is a wsf file, the Windows Script environment itself is available to the script. Here we merely enumerate the arguments received and if an incorrect number are supplied echo the correct usage and quit the script This is shown in the screenshot below.
We then call two subroutines, the first to read the alert log and the second to rename it (so we don’t check the same error twice). The arguments are supplied as named, rather than positional arguments due to personal preference more than anything else.
The first routine opens the alert.log using the filesystem object technique that we saw in the archived redo example and reads through a line at a time checking for lines that begin with the string “ORA-“. We then use the split built-in function to create an array that holds the oracle error code and the associated text. In this simplified example we merely echo the error out to the default output device, in production we would likely mail the DBA using the technique shown in the service monitoring example.
The second routine grabs a reference to the alert log using the filesystem object as before and renames the file, appending a datetime to the filename. It is worth noting that the datetime used is in the system long date format. If this format contains the / character the file rename will fail as the OS will interpret the name as a directory path.

ReadAlert.vb.wsf


' Name: ReadAlert.vb.wsf
'
' Date: 08 Feb 2006
'
' Author: Niall Litchfield
'
' Purpose: Read the alert log of an Oracle Instance and renames at the end.
'
' Usage: Cscript.exe ReadAlert.vb.wsf /"path\to\alert.log"
<job>
<runtime>
<description>This script reads the alert log of an Oracle Instance</description>
<named
name = "log"
helpstring = "Alert.log to read"
type = "string"
required = "true"
/>
<example>Example: ReadAlert.vb.wsf /log:"c:\oracle\10.1.0\admin\orcl\bdump\alert_orcl.log"</example>
</runtime>
<script language="VBScript">

Sub RenameAlert(alertlog)
dim fso,alert, newname
Set fso = CreateObject("Scripting.FileSystemObject")
set alert = fso.GetFile(alertlog)
newname = alertlog & "." & FormatDateTime(now(),vbLongDate)
Wscript.Echo newname
alert.Move(newname)
set alert = nothing
set fso = nothing
End Sub
</script>
</job>


Scheduling the work

Now that we have our scripts, and once we have tested it to ensure that it works as we expect, we wish to execute the task on a schedule, in this case we will schedule the dbshut script to run every morning at 2am.
Under previous releases of the Windows OS the utility provided for this task was the somewhat clumsy command line based AT task scheduler. The available options for this utility are shown in the screen shot.

Fig 1: The AT interface

It will be quickly seen that whilst this utility allows the relatively straightforward scheduling of tasks at specific times of day, more complex scheduling tasks(for example every 2 hours between 8pm and 6am) are significantly difficult to achieve, as they will have to be broken down into multiple schedules. What is perhaps not obvious from the above is what is missing entirely from the available options. This includes the ability to schedule tasks at system startup and user logon. Additionally the tasks always execute under the SYSTEM profile

With the arrival of Windows 2000 there is a more suitable task graphical task scheduler.

Fig 2 The Scheduled Tasks Interface

This utility overcomes the restrictions of the AT scheduler as well as making administration far simpler. We will use this utility to schedule our shutdown script to run each day at 2am and our startup script to run at 4am in a security context that we will create specifically for this purpose. First we will create the account, here I am creating a local account on my laptop for this purpose, in a real deployment I suggest you dedicate a domain account for this. Fig 3 shows the properties of the account.

Fig 3 The scheduler user

We then need to lock down this account somewhat. I have chosen to include this account in the local administrators group, but to include the account in the list of users denied interactive logon rights this can be achieved through a group policy. If you are testing this setup I suggest that you leave this step until you are happy that everything works. This allows you to ensure that a permissions issue really is a permissions issue and not some other problem.
Now we need to create our scheduled tasks. To do this we need to open the scheduled tasks applet from Control Panel. We then choose Add Scheduled Task from the available options. This runs the Scheduled Tasks Wizard, which after a welcome screen presents you with a list of registered programs.

Fig 4 The Scheduled Task Wizard

We want to ignore these so we select browse and get the standard File Open dialog box, with ‘programs pre-selected’. On my test laptop the programs setting is ignored and all files are shown. We then select the appropriate script

Fig 5 Choosing the Script

After clicking next we can use the Wizard to schedule the task to execute each day at 2am under the security context we created for the job using the screens below.

Fig 6 Scheduling the script - a workflow

You can choose to further edit the task in the final screen, you might do this if for example you have a more complicated schedule to set (or multiple schedules to set). One adjustment we will make is to the default period of time that the operating system will allow the task to run for. By default this is 72 hours which is far too long. This can be adjusted by opening the scheduled tasks control panel applet, secondary clicking on the task and choosing properties. This will give the screen shown below where we can control the ending of the task. I am going to select 15 minutes as the maximum amount of time I want to wait.

Fig 7 Setting a Script timeout

The shutdown script will now run every morning at 2am, repeat these steps for the start up script.

Scripting Job Schedules

It is naturally also possible to script the scheduling of Operating System commands using the windows scripting host, though there are a couple of important limitations. The first limitation is that it is only the windows AT scheduling interface that is scriptable, this means that the same limitations about complex schedules discussed above apply. The second limitation is that jobs created in this way always run under the AT Service Account. This can be configured from control panel by selecting AT Service Account from the advanced menu as shown below

Fig 8. Changing the AT Service Account

In addition the default behaviour is subtly different depending on the exact operating system which you are running. On Windows 2000, tasks started in this way run under the LocalSystem account by default. However, in Windows Server 2003 these tasks run under the NetworkHost Account.
The script itself illustrates nearly all of the characteristics we have seen so far, the only part likely to confuse is the datetime formatting needed to specify the start time and the formatting used to specify either days of the month or days of the week on which the task should run.
As we saw with the alert.log example, this script AddScheduledTask.vb.wsf in the download is a fully fledged windows scripting host file, complete with usage information and documentation in the header. This script takes 3 named parameters, the server on which to schedule the task, the path to the script to be scheduled and the time at which to run the script in the format HH24MM. If these parameters are not supplied the usage will be shown in a dialog box.
Having called the subroutine with these arguments the script does the following; first it obtains a handle on the wmi service on the supplied computer the string “{impersonationLevel=impersonate}!” used in the call allows us, provided we have the appropriate security rights (usually being an administrator on the server), to obtain this connection remotely if necessary.
The next call obtains a handle on the scheduled task interface exposed to wmi. This will return a reference to a scheduled task, we could equally use WQL here to query the existing list of scheduled tasks on the server, there is an example of this in the Windows Scripting Centre. Here however we wish to create a new job, this is done by using the Create method exposed by the win32_scheduledjob object. This method takes the following parameters

AddScheduledTask.vb.wsf


<job id="AddScheduledTask">
<runtime>
<comment>"This script schedules a regular task"</comment>
<named
name = "server"
helpstring ="Server to schedule script execution on"
required="true"
/>
<named
name = "scriptname"
helpstring ="Script to schedule"
required="true"
/>
<named
name = "when"
helpstring ="time to start execution"
required="true"
/>
<usage>AddScheduledTask.vb.wsf /server:"." /scriptname:"c:\scripts\otn\dbshut.vbs" /when:"0200"</usage>
</runtime>

sub AddTask(server,script,when)
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & server & "\root\cimv2")
Set objNewJob = objWMIService.Get("Win32_ScheduledJob")
objNewJob.Create script, "********"& when &"00.000000+000", True , 127, , , JobId
WScript.Echo "Job Created with ID: " & JobId
end sub
</script>
</job>


Summary

In this article you have been exposed to the various scripting technologies available on the Microsoft platform and an example set of scripts has been developed that demonstrates some core techniques useful for DBA and system administration of Oracle Systems on Windows Servers, in particular we have seen scripts that respond to events, manipulate and monitor core files and communicate with administrators via email. Contrary to popular belief Microsoft Platforms do have a rich set of scripting utilities available for use by the systems administrator or DBA, but because these differ from traditional shell scripts they are often overlooked.

Further Resources

Script resources on the Microsoft Developer Network (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnancho...)
Windows Management Instrumentation
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/...