Last week, Jeff Smith’s blog on the FTP utility in Toad^® was very good. He even says at the end that “If you like Toad’s FTP, then I recommend you check out the REXEC and TELNET features as well.” That made me think – it probably would be nice just to see a list of all the UNIX and Linux capabilities within Toad in one article. Thus I decided to expand upon the story that Jeff initiated – what else can Toad do with remote servers?

Let’s start with where Jeff left off – REXEC and TELNET. These features, plus several other great capabilities, are located on the Main Menu under “Utilities” as shown below:

The “Network Utilities” selection offers a plethora of very useful capabilities all from a centralized location – including the Oracle TNSPING utility. Here’s a simple example of using the TELNET feature, followed by a screen snapshot of Oracle’s “TNSPING” being used. The latter screen is highly useful when trying to debug strange connection problems to remote database servers. For example if TNSPING cannot open communication with the remote server, it might suggest that the problem lies on the other end (i.e. maybe the database server did not leave the proper ports open on its firewall for SQL Net traffic).

The key to successfully using this screen was covered in Jeff Smith’s blog, the remote server must have been set up to support either REXEC or SSH access. Otherwise this screen simply will NOT work. This is critical because once you know that this screen functions properly, then you can also use Toad’s “UNIX Monitor” – which is accessed off the Main Menu under “Database” and then “Monitor” as shown below:

So in my case, I simply have a VMWare “CentOS 4.5” Linux database server running Oracle 10g on my Windows XP notebook. Since this network is connected via a “host only” virtual network and thus was secure, I was happy to rely upon REXEC even though it’s very insecure. So in most environments, the system administrators should probably use SSH instead. The three things I needed to do to make my setup function was: 

1. On Windows, add LINUX_10G entry to my hosts file
2. On Linux, add BSCALZO entry to my hosts file
3. On Linux, add BSCALZO + entry to my hosts.equiv file

Note: You also could instead use a ”rhosts” file per operating system user account, but that would be more work. Thus I instead went with the globally applied and less secure method of “/etc/hosts.equiv”. I also used the very insecure notation – which again is not recommended. But once again, I have a simple VMWare setup that’s totally isolated from outside network access. Thus I went with the most expedient setup. Yes – I am lazy

For UNIX and Linux remote database server setups, The “UNIX Monitor” compliments Toad’s “Database Monitor” and “Database Probe” screens (both part of the optional DBA module).  Sometimes it’s quite useful to correlate these three screens output. What I mean is that sometimes a reported or suspected database performance problem is really just a symptom of a database server operating system issue – and thus actually not a database problem at all. The “Unix Monitor” works for Linux, Solaris, AIX, HP-UX and Tru64.

Here’ an example of the “Unix Monitor” showing my VMWare Linux operating system activity while executing an industry standard TPC-C Benchmark with Quest Software’s Benchmark Factory^® for 20 concurrent users.

It shows what I expected, my single disk drive notebook is IO bound. Look at the graph on the right, both my IO devices show heavy activity. And look at the process list. Both Oracle DBWR (database writer) and LGWR (log writer) processes are my top resource consumers. Both my CPU Usage and Process Queue are quite reasonable – and thus not an issue. So there’s not too much that I can tune in the database for this miniscule setup.

Now a logical question is why doesn’t Toad offer a Windows Monitor? Because if you have the necessary privileges, you can use your PC’s Windows Performance Monitor to watch a remote Windows database server’s runtime characteristics – as shown here.

The final useful UNIX tool within Toad is the “CRON Job Manager” which is accessed via the Main Menu as “Unix Job Scheduler.”

If you have ever worked with CRON jobs, you know that the syntax is very cryptic and hard to remember. And what about the most common and useful database jobs you might want to schedule – why should you have to reinvent the wheel for dozens of useful shell scripts? You don’t have to worry about any of this anymore as Toad offers a wonderful GUI that totally simplifies CRON scheduling, management and history as shown below:

You simply define your servers and databases, then drag-and-drop the scripts onto them – that’s it. Then Toad will copy the necessary scripts to the targets and then create all the correct CRON job file entries to schedule your jobs. For example, here’s an example of scheduling to coalesce my tablespaces every fifteen minutes. Note that I simply had to choose the timing from the drop-down list, and Toad knew what cryptic CRON syntax to use.

Now you simply press the final two toolbar icons on this screen. The first represents doing a pre-check to verify that everything looks properly constructed for successful deployment. The second then performs the actual deployment (i.e. ftp’s over the shell scripts and appends the new jobs’ entries to your server’s appropriate CRONTAB file). That’s pretty much all there is to do! Toad will create a directory to contain all the necessary files, and the following files and directories under that as shown below:

So now UNIX is running your jobs at the scheduled intervals. But we’re not quite done yet. It would not be too useful if you could not check back on the jobs’ progress and also manage them further. So you simply click on the server node back on the main screen, and you will see a history of all the jobs and their completion status. Plus, if you double click on any job history line – Toad will ftp that log file onto your PC and open it up to view inside notepad (or your defined editor of choice). You can also delete history files and update job schedules using the various toolbar icons on the right hand side of this screen. It’s some pretty cool stuff.

Only one word of caution: be very, very cognizant of what you schedule. For example someone once scheduled all the pre-canned jobs to run once every minute to see what would happen. Imagine their surprise when the CRON job load brought their server to its knees.