Windows Operating Systems Utilities for the DBA

posted July 25, 2014, 2:12 AM by

David Woodward, COO and Principal Architect

In a prior post, I discussed some Unix tools that are available that can help the DBA with managing an Oracle database. This time around I am going to look at some tools for the Microsoft Windows platform that can help with the management of Oracle within this operating system.

Within this article, I will discuss native Windows tools where possible, but to get all the same functionality I described under Unix in my last article, we have to look at some additional free tools to get there.

There are two different sets of tools that we like to refer our customers to: the Cygwin toolkit from http://www.cygwin/.com and the Syslnternals freeware tools at http://www.sysinternalscom.

Cygwin is now managed by Red Hat, and Sysinternals was purchased by Microsoft from it’s founders, Mark Russinovich and Bryce Cogswell. Both sets of tools are still free to use, but as always, you should read the licensing agreements to make sure your particular use is covered.

Cygwin is mainly built around the Cygwin DLL that provides an interface layer from Unix system calls to the underlying Windows system calls. This allows Unix utilities to be easily ported to Windows because they still make Unix calls, which the Cygwin DLL translates to Windows calls. There are quite a number of Unix utilities that come with Cygwin, including “ps”, “df”, and “du”. You interact with Cygwin via a shell prompt, typically the Bash shell. You reference all path names with forward slashes, just as you would in Unix.

Sysinternals.com utilities are a collection of separately downloadable programs, some of which are GUI based and some of which are command line based. The command line utilities you can run from a command line window or in a Windows script.

Tracking processes

Under Windows you can always see processes using the Task Manager, but at times you want to write scripts to monitor certain processes. Cygwin includes the “ps” utility and this is the easiest way to script checking for processes. The “-e”option lists all processes running on the system, and “-f” give a “full” listing, which lists a few more columns of information, such as parent process ID and start time. The Cygwin version of “ps” is much more limited in its output than its Unix counterpart. It normally only list processes started under Cygwin, but if you add the flag, it will show all Windows processes as well as their Windows PIDS:

8 0 O 8 ? 0 14:24:48 *** unknown ***

140 O O 140 ? 0 16:34:41 \SystemRoot\System32\smss.exe

188 O O 188 ? 0 16:34:41 \??\C:\WINNT\system32\winlogon.exe

220 O 0 220 ? 0 16:34:42 C:\WINNT\system32\services.exe

232 0 0 232 ? 0 16:34:42 C:\WINNT\system32\lsass.exe

404 O 0 404 ? 0 16:34:42 C:\WINNT\system32\svchost.exe

428 O O 428 ? 0 16:34:42 C:\WINNT\system32\spoolsv.exe

488 O O 488 P 0 16:34:42 C:\WINNT\System32\svchost.exe

528 O 0 528 2 0 16:34:43 C:\WINNT\system32\regsvc.exe

556 O O 556 ? 0 16:34:43 C:\WINNT\system32\MSTask.exe

652 0 O 652 ? 0 16:34:43 C:\WINNT\System32\WBEM\WinMgmt.exe

676 0 O 676 ? 0 16:34:43 C:\WINNT\system32\svchost.exe

204 0 0 204 ? 0 16:35:57 C:\WINNT\Explorer.EXE

316 0 0 316 ? 0 16:35:58 C:\WINNT\system32\wuauclt.exe

216 O O 216 ? 0 16:43:25 C:\WINNT\system32\msiexec.exe

1136 O O 1136 ? 0 16:46:18 C:\oraclexe\app\oracle\product\10.2.0\server\BIN\tnslsnr.exe

1216 O 0 1216 ? 0 16:46:27 c:\oraclexe\app\oracle\product\10.2.0\server\bin\ORACLE.EXE

964 O O 964 ? 0 16:49:11 C:\Program Files\Internet Explorer\iexplore.exe

1300 0 O 1300 ? 0 16:51:12 C:\WINNT\system32\cmd.exe

1092 1 1092 1092 con 1000 16:51:12 /usr/bin/bash

1100 1092 1100 792 con 1000 16:51:21 /usr/bin/ps

 

Cygwin does not have the “pgrep” utility, so you will need to use “ps” with “grep” to find certain processes. Remember our trick from the last post of putting the first character of the process to match in brackets so that we do not match our own grep:

$ ps -W|grep -i [o]racle.exe
  1216  0 0  1216  ?  0 16:46:27 c:\oraclexe\app\oracle\product\10.2.0\server\bin\ORACLE.EXE

 

Remember that the columns are slightly different than Unix, as they include the Windows PID.

du

Since this is Windows, you can just right click on a folder and select “Properties” to see how much space a folder takes. Sometimes it is useful to see the space taken up by a whole list of subdirectories, and Cygwin contains the “du” utility that works just like Unix. Just as a note, Cygwin shows the Windows drive letters such as “C:\” and “D:\” as “/cygdrive/c” and “/cygdrive/d”.

$ cd /cygdrive/c/oraclexe
$ du -ks *|sort -n

341319 app

800536 oradata

 

Using “sort -n” sorts the output numerically, putting the largest disk space consumer at the bottom. If “oradata” was a file, we’d be done, having found the biggest space hog. Since its a directory, you would “cd” into it and do “du -ks *|sort -n” again, and continue looking for where your space it used.

Sysinternals also has a nice command line “du” utility displays slightly different output. You give it the name of a directory and it gives you summary information by default:

c:\> cd \oraclexe

c:\> du -q *

(“-q” is quiet mode that prints no banner)

Files: 2374

Directories: 140

Size: l,l64,7l4,699.00 bytes

Size on disk: 1,164,714,699.00 bytes

 

This version of “du” also has a “-v” flag that will show the size of all subdirectories under the one given on the command line, and a “-l” flag that allows you to specify how many levels to go down. Looking at the default Oracle XE directory and those one level below it we get:

C:\oraclexe> du -l l

336,906.00 C:\oraclexe\app

810,776.00 C:\oraclexe\oradata

1,147,682.00 C:\oraclexe

Files: 2383

Directories: 140

Size: l,l75,226,928.00 bytes

Size on disk: l,175,226,928.00 bytes

 

You can pipe the output of the Sysinternals “du -v” through sort to find the largest space hog, but keep in mind this is the DOS/ Windows sort utility instead of the Unix sort under Cygwin. Use “sort /?” to get help on how to use it.

lsof

As I mentioned in my previous post,”lsof” is one of my favorite Unix tools. It shows me open files and network port for a process. There is nothing like it in the Cygwin toolkit, but the Sysinternals guys have several utilities that fill the void.

For looking at open files, the “Handle” utility by Sysinternals is a command line program to show all open files for processes on your system. The really nice thing is the “-p” command line parameter, which allows you to look at one process by either PID or partial executable name.

Here is the output for the Oracle listener:

C:\> handle -p tnslsnr

Handle v3.2

Copyright (C) 1997—2006 Mark Russinovich

Sysinternals — www.5ysinternals.com

-------------------------------------------------------------------------------------------------------------

TNSLSNR.EXE pid: 632 NT AUTHORITY\SYSTEM
   18: File (RW-) C:\WINNT\system32
   C8: File (RW-) C:\oraclexe\app\oracle
product\10.2.0\server\NETWORK\log\listenerlog
   CC: File (R--) C:\oraclexe\app\oracle\
product\10.2.0\server\NETWORK\mesg\nlus.msb
   D4: File (R--) C:\oraclexe\app\oracle
product\10.2.0\server\NETWORK\mesg\tnsus.msb
I recently was installing Oracle patches on Windows via “opatch”, the Oracle patch installation utility. Opatch was reporting that a DLL was still in use even though I had shut down all Oracle processes. Using “handle” together with the DOS “find” utility (similar to grep), I was able to discover a Java process that still had the DLL open. For example, to locate all open DLL’s:
C:\> handle |find /i “dll”
  19C: File (RW-) C:\WINNT\system32\dllcache
  3F8: File (RW-) C:\WINNT\system32\os2\dll
  1A8: File (RW-) C:\WINNT\system32\sens.dll
  1BC: File (R--) C:\Program Files\Adobe\Acrobat\ActiveX\AcroIEHelper.dll

Sysinternals also has a GUI tool called “Process Explorer”, which show open files and many, many other types of information, including per process and per thread CPU usage and TCP/IP connections.

If you want to see network port connections, similar to what “lsof -i” can do on Linux, you also have several choices. As mentioned above, Process Explorer can show connections per process. The “tcpvcon” utility (packaged with the GUI tool tcpview) will show them from the command line for all processes, or certain processes by process id or name:

C:\> tcpvcon tnslsnr
[TCP] C:\oraclexe\app\oracle\
product\10.2.0\server\BIN\tnslsnr.exe
        PID:    588
        State:  ESTABLISHED
        Local:  hobvm5:1521
        Remote: hobvm5:1034

 

The “tcpview” GUI utility from Systinternals shows you all outstanding network connections and updates them every second by default. Be sure to enable the “Show Unconnected Endpoints” under the Options menu to show processes, which are listening as well as those that are connected.

System call tracing

System call tracing under Windows can be a little more difficult than under Unix. With Cygwin, we have the “strace” utility available, and it works just as we discussed in the last article. However, it only traces programs that were compiled under Cygwin, so that leaves out tracing any Oracle supplied executables with it.

The best way to trace systems calls in Windows is usually by using a debugger. But typically, we are interested in file reads and writes, and with Windows, registry access. Sysinternals formerly had two utilities, filemon and regmon, which have been combined into the Process Monitor utility. This utility allows you to monitor file operations and registry operations. You can supply a filter to restrict the tracing to just a particular process, otherwise it shows all system-wide file and registry activity, which can be a bit overwhelming.

Network packet capture

In the last post, I discussed the Wireshark network capture utility. This is my preferred utility for Windows as well and can be found at www.wireshark.com.

Wireshark has both a GUI mode and a command line mode and it can read capture files from other utilities such as snoop and tcpdump. Under Windows it installs WinPCAP, which is a packet capture library. So, you can use Wireshark to capture data from the Windows network adapters. The GUI is fairly intuitive, and the command line-capture utility, tshark, that works much like tcpdump.

Analyzing network traffic with Wireshark is beyond the scope of this article, but it is the best utility out there for doing network packet capture and analysis.

Conclusion

There are quite a few shops running Oracle on the Windows platform, and there’s no shortage of troubleshooting tools for this platform. Hopefully I have given you some ideas to help you on your day-to-day work with Oracle on Windows.

Share with your networkTweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
Share on Facebook
Facebook
Digg this
Digg
Email this to someone
email

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

WANT TO LEARN MORE?