David Woodard, 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.
