Scripting
By default, Windows NT provides support for scripting through the use of DOS command files, named with an extension of BAT or CMD. Although this functionality provides the means to script certain types of tasks, it leaves much to be desired in terms of conditional logic and error checking capabilities. With ORACLE on NT, an additional option is provided by PL/SQL scripts that can be run by SVRMGR or SQL*Plus as non-interactive batch jobs. Additionally, the NT Resource Kit provides a variety of scripting engines such as KIX32, Perl, REXX, and a POSIX shell. These scripting engines extend scripting capabilities considerably, but have a very proprietary flavor and may not always work as consistently as the already slim documentation suggests. Luckily, Microsoft has recently made available a host-level scripting engine called "Windows Scripting Host". Like it's counterparts the IIS ASP scripting engine and the IE browser script interpreter, the WSH scripting engine interprets and executes VBscripts and Jscripts, only it does so with a focus on running batch jobs, administrative tasks, login scripts, etc., on the host machine in a reasonably small footprint. The WSH is supported for installations using Windows NT 4.0, and is available for download from Microsoft's web-site. (Please see the resources section for the URL.)
Most Microsoft-oriented organizations running ORACLE on NT are likely to have some expertise in Visual Basic, VBA, or VBscript, and PL/SQL, so this article will focus on automating tasks using VBscript, WSH, and PL/SQL. VBscript is capable of fairly sophisticated logic, has a rich set of built-in functions, and has a reasonably simple syntax. VBscripts can dynamically construct and execute DOS commands from literals and variables. The WSH scripting engine is installed once, then can run any collection of VBscripts without complicated installation procedures for each individual script (as compared to writing an actual Visual Basic executable application for each task). Once the WSH is installed, "installing" a new script merely requires copying it to a local folder on the server, and adding it to the scheduler or a command file, all of which can be done remotely without a visit to the server console. WSH includes a WSCRIPT.EXE for running scripts as an interactive GUI-enabled job, and a CSCRIPT.EXE that directs any output to STDOUT, allowing scripts that produce interactive output to be run from a non-graphical TELNET session or Remote Command session. In practice, it is very likely to have combinations of CMD files, VBscripts, and PL/SQL scripts to automate certain types of tasks.
The links below provide the script examples for automating a variety of tasks. Please feel free to copy, modify, and use the provided examples. DOS command files are named with the CMD extension, VBscripts are named with the VBS extension, and PL/SQL scripts are named with the SQL extension. As with any sample source code, good practices dictate that jobs and processes be thoroughly evaluated in a test environment before use in a production environment. (Please see the disclaimer in the legal notices section.)
1) Rename/move file with date appended between filename and file extension (VBS)2) Find and Email ORACLE Alert Log errors (CMD & VBS)
3) ANALYZE tables and Indexes (CMD & SQL)
4) Running UtlBSTAT.sql and UtlESTAT.sql scripts (CMD, SQL, & VBS)
5) Manage MONITOR Service performance logs (CMD & VBS)
6) Run and Email disk space report (CMD)
7) Running hot backups to disk via PL/SQL (CMD & SQL)
8) Run full export (CMD)
9) Daily archiving of ORACLE alert logs (CMD & VBS)