Scheduling DBA Tasks by Using UNIX Job Scheduler

For those DBAs managing Oracle on UNIX, TOAD offers the ability to schedule shell scripts that perform true server-side database administrative tasks. Plus, TOAD ships with over 50 very useful shell scripts that work on Linux, Solaris, HPUX, AIX, and Tru64 already provided. Moreover, TOAD provides a simple template for creating additional scripts. TOAD simply utilizes the server's cron daemon to schedule and run the tasks. The TOAD Unix Job Scheduler is shown in Figure 5.33 and is located on the main menu at DBA, OS Utilities, Unix Job Scheduler. Its tree view contains two basic node types at the first level: one entry for All Available Tasks and an entry for each database server.

Figure 5.33. TOAD UNIX Job Scheduler.

The All Available Tasks node contains categorized directories for the 50+ shell scripts provided (as well as any new ones that you create). In Figure 5.33, the General DBA category has been expanded. Here you will see the list of general DBA tasks that have been predefined. If you click on a task's entry, the shell script's file will be displayed on the right-hand side of the screen. So in essence the tree view under All Available Tasks serves as a mini-explorer for viewing the shell scripts. These shell script files are located in the base TOAD directory under unixjobs\Base\Shell .

The individual server nodes contain each of the database SIDs for that server. Initially, TOAD scans the PC's local host file to construct the server list and the TNSNAMES.ORA file to create the SID list. You can, of course, customize the nodes in case the automatically discovered entries are incorrect. You just right-click to add servers and SIDs. To use these servers and their SIDs (regardless of whether manually or automatically created), you must perform a right-click edit operation to provide critical operational information, as described in the following two paragraphs.

For each server, you must provide the UNIX user id, password, and schedule deployment directory as shown in Figure 5.34. TOAD will log in to that UNIX user id when deploying the job schedule. It's also the user id that will be running all the jobs via cron . The UNIX user id does not have to be the Oracle account as shown. It could be any UNIX user id. But note that some DBA tasks, such as a backup, require that the user id be in the UNIX DBA group and thus have read access to the Oracle data files. The UNIX user id should also have access to sufficient disk space for the log files created by each job execution. Although TOAD keeps log files for just the past 10 days or 10 most recent executions, a server with lots of SIDs and jobs could still consume lots of space.

Figure 5.34. Editing Job Scheduler server data.

For each database SID, you must provide the correct Oracle home directory and various Oracle user ids and passwords as shown in Figure 5.35. The Oracle home directory is used to set an environment variable utilized by all the shell scripts to reference Oracle binary files (for example, $ORACLE_HOME/bin/sqlplus). If you set the wrong value here, the deployed schedule will not work. There are three types of Oracle user ids and passwords that must be provided. The first is a DBA user id for performing typical tasks requiring DBA privileges (for example, coalesce a tablespace). The second is the user id owning the TOAD objects for performing TOAD tasks (for example, gathering TOAD history for tablespace growth). And the third is a user id with SYSDBA rights for database startup and shutdowns (if used).

Figure 5.35. Editing Job Scheduler SID data.

After the server and SID information are correct, it's time to schedule jobs. It's really very easy; you just drag and drop a job from the Available Tasks. If you drop it on a server, it adds that task to all the SIDs. If you drop it on a SID, it's only added to that SID. You can also drag and drop a task from one SID to another. That's all there is to it. Look back again at Figure 5.33. The ORLI81 SID has three scheduled jobs.

There is still one more critical step though, as all tasks have properties. When you select a scheduled task on the tree view, the right-hand side displays that job's properties. Look back once again at Figure 5.33. You must define how often you want each job to run. They come with reasonable defaults, but should be reviewed and set for your needs. Plus many jobs have optional parameters that control their behavior. Again sensible defaults are provided that should likewise be reviewed and set to your specific needs.

After all the server, SID and task information has been set, you are ready to deploy the job schedule. First you should click the Check Required Elements toolbar icon (the one with the green check mark) in order to verify that all your data entry is correct. Then you merely click the Deploy/FTP icon to copy the jobs and schedule to the server. The Deploy/FTP icon also activates the schedule with cron . It's all up and running at this point.

To monitor and control deployed jobs, you merely select a particular server node back on the job scheduler tree view and reference the Log tab as shown in Figure 5.36. Note that there are four options under the Refresh Log List button: All Log Files, Last Execution, Failures Only, and Failures Only/Last Execution. You can view the log files using either the View Log File toolbar button or by double-clicking on a log file entry (it FTPs the file to your machine and then opens it in Notepad). Plus you can delete log files on the server to control disk space usage. Finally, you view the crontab file and start or stop the cron schedule. In short, you should never have to log in to a UNIX box to work with anything related to the TOAD job scheduler. The GUI provides for every conceivable need.

Scheduling DBA Tasks by Using UNIX Job Scheduler