August 6, 2010
An ERP mailing list email asked if there was a way to determine which Oracle clients had not been upgraded to the Oracle 11.2.0.1 client – I also recently encountered a request to determine similar information about the Oracle client versions of potential sessions, so I thought it might be fun to develop a solution. I might be overlooking something, but it does not appear that the Oracle Database, by default, maintains the connected client’s release version. Is there a solution? Assume that the following is true:
- The client computers are running Windows 2000 (client or server) or above
- The client computers are members of ActiveDirectory
- WMI (Windows Management Instrumentation) has not been disabled on the client computers
- VBS (wscript or cscript) may be run from a computer that is logged in with domain administrator permissions
First of all, I would prefer not to visit each computer to determine which Oracle client is installed. I also do not want to type all of the computer names – so we need a way to retrieve a list of the computers that are members of the ActiveDirectory domain. A partial code fragment might look something like this (replace oracle and com as necessary for your domain):
Dim dbActiveDirectory Dim comData Dim snpData Dim strDomain Set dbActiveDirectory = CreateObject("ADODB.Connection") Set comData = CreateObject("ADODB.Command") Set snpData = CreateObject("ADODB.Recordset") dbActiveDirectory.Provider = "ADsDSOObject" dbActiveDirectory.Open "Active Directory Provider" comData.ActiveConnection = dbActiveDirectory strDomain = "DC=oracle,DC=com" 'Equivalent to oracle.com, change as needed With comData strSQL = "SELECT" & vbCrLf strSQL = strSQL & " NAME" & vbCrLf strSQL = strSQL & "FROM" & vbCrLf strSQL = strSQL & " 'LDAP://" & strDomain & "'" & vbCrLf strSQL = strSQL & "WHERE" & vbCrLf strSQL = strSQL & " OBJECTCLASS='computer'" & vbCrLf strSQL = strSQL & "ORDER BY" & vbCrLf strSQL = strSQL & " NAME" .CommandText = strSQL .Properties("Page Size") = 1000 .Properties("Searchscope") = ADS_SCOPE_SUBTREE End With Set snpData = comData.Execute If snpData.State = 1 Then Do While Not (snpData.EOF) 'Do something snpData.MoveNext Loop End If
Now that we have a method for retrieving the computer names in the domain (note that the above code might only work for the domain administrators), we need an easy method to determine which Oracle client is installed – or at least which client is listed first in the system path. Something like this might work:
C:\> tnsping /? TNS Ping Utility for 32-bit Windows: Version 11.1.0.7.0 - Production on 05-AUG-2010 20:54:43 Copyright (c) 1997, 2008, Oracle. All rights reserved. Used parameter files: C:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora TNS-03505: Failed to resolve name
There is the Oracle client release version on the second line of the output, along with the client’s operating system (just ignore the error message). Now we just need a way to record the output of that command – let’s redirect the output to a text file:
C:\> tnsping /? >C:\OracleClientversion.txt
Next, we need to find a way to tell the client computers on the network to execute the above command. WMI will help, as long as our network user account is a member of the Administrators group on the remote computer (or our network user account is a member of the Domain Adminitrators group). If we are somehow able to create a batch file named C:\OracleClientVersion.bat on the remote client computer, we could execute a command like this in our script:
lngResult = GetObject("winmgmts:\\" & snpData.Fields("Name").Value & "\root\cimv2:Win32_Process").Create("C:\OracleClientVersion.bat", Null, Null, intProcessID)
The next task is to read the resulting text files that are created on each of the client computers, looking for the line that contains “TNS Ping Utility”. Obviously, we need to write the results to another text file. We end up with something like this:
Set objFile = objFSO.OpenTextFile("\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF, ForReading) Do While Not (objFile.AtEndOfStream) strLine = objFile.ReadLine If InStr(UCase(strLine), "TNS PING UTILITY") > 0 Then objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & strLine & vbCrLf Exit Do Else 'Do nothing End If Loop objFile.Close
One problem remains. If we attempt to access the WMI objects on a computer that is offline our script will lock up for a period of time. To work around that problem we should probably try to ping the remote computer first. The ping routine looks like this:
Function PingTest(strComputer) Dim intPosition Dim objShell Dim objExec Dim strLine Dim strCommand On Error Resume Next PingTest = False Set objShell = CreateObject("wscript.shell") 'command to execute strCommand = "PING -i 10 -w 10 -n 1 " & strComputer 'Create Exec object Set objExec = objShell.Exec(strCommand) 'skip lines that contain information about our DNS 'server Do While objExec.StdOut.AtEndOfStream <> True strLine = objExec.StdOut.ReadLine intPosition = InStr(UCase(strLine), "RECEIVED =") If intPosition > 0 Then If InStr(strLines, "TTL expired in transit") = 0 Then If Trim(Mid(strLine, intPosition + 10, 2)) = "1" Then PingTest = True Else PingTest = False End If Else PingTest = False End If Exit Do End If Loop Set objShell = Nothing Set objExec = Nothing End Function
A simple, straight-forward solution, right? Because the columns in the combined log file are tab delimited, we are able to easily open the file using Microsoft Excel. Partial output might look something like this:
Obviously, the client computers need to be turned on for the script to work correctly. 🙂 COMP19 certainly is running an old version of the Oracle client.
Notes:
* For every computer that responds to a ping, the script pauses for two seconds to allow enough time for the tnsping command on the remote computers to execute.
* The script execution speed may be improved by modifying the script to remember each computer name that responded to a ping, and remotely launch the tnsping command on those computers. Once the tnsping command has been started on each computer, make a loop through the list of computers that previously reponded to a ping and collect the OracleClientversion.txt files from those computers.
The full script may be downloaded here: DetOrclClient.vbs (save as DetermineOracleClientReleaseVersion.vbs). The full script is displayed below (the script download from WordPress seems to fail):
'Revision 1.0 Created by Charles Hooper August 6, 2010 ProbeAllComputers Sub ProbeAllComputers() 'For modifying the INI file Const ForReading = 1 Const ForWriting = 2 Dim objFSO Dim objFile Dim objFileLog Dim objFileBatch Dim strLine Dim strOracleClientFile Dim strOracleClientFileF Dim lngResult 'For querying active directory Const ADS_SCOPE_SUBTREE = 2 Dim dbActiveDirectory Dim comData Dim snpData Dim strSQL Dim strDomain Dim strCommand On Error Resume Next strDomain = "DC=oracle,DC=com" 'Equivalent to oracle.com, change as needed strOracleClientFile = "C:\OracleClientVersion.txt" 'The location of the output file on the computers strCommand = "tnsping /? >C:\OracleClientVersion.txt" 'Make the file location compatible with a UNC name strOracleClientFileF = Replace(strOracleClientFile, ":", "$") strOracleClientFileF = "\" & strOracleClientFileF Set dbActiveDirectory = CreateObject("ADODB.Connection") Set comData = CreateObject("ADODB.Command") Set snpData = CreateObject("ADODB.Recordset") dbActiveDirectory.Provider = "ADsDSOObject" dbActiveDirectory.Open "Active Directory Provider" comData.ActiveConnection = dbActiveDirectory If Err <> 0 Then lngResult = MsgBox("An error happened while connecting to Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!") Exit Sub End If With comData strSQL = "SELECT" & vbCrLf strSQL = strSQL & " NAME" & vbCrLf strSQL = strSQL & "FROM" & vbCrLf strSQL = strSQL & " 'LDAP://" & strDomain & "'" & vbCrLf strSQL = strSQL & "WHERE" & vbCrLf strSQL = strSQL & " OBJECTCLASS='computer'" & vbCrLf strSQL = strSQL & "ORDER BY" & vbCrLf strSQL = strSQL & " NAME" .CommandText = strSQL .Properties("Page Size") = 1000 .Properties("Searchscope") = ADS_SCOPE_SUBTREE End With Set snpData = comData.Execute If Err <> 0 Then lngResult = MsgBox("An error happened while reading the computer list from Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!") Exit Sub End If If snpData.State = 1 Then Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFileLog = objFSO.CreateTextFile("C:\Oracle Remote Client Log.txt", True) Do While Not (snpData.EOF) If PingTest(CStr(snpData.Fields("Name").Value)) = True Then 'Write the command file to the remote computer Set objFileBatch = objFSO.CreateTextFile("\\" & CStr(snpData.Fields("Name").Value) & "\C$\OracleClientVersion.bat", True) objFileBatch.Write "tnsping /? >C:\OracleClientversion.txt" & vbCrLf objFileBatch.Close Set objFileBatch = Nothing lngResult = GetObject("winmgmts:\\" & snpData.Fields("Name").Value & "\root\cimv2:Win32_Process").Create("C:\OracleClientVersion.bat", Null, Null, intProcessID) If lngResult = 0 Then 'Wait 2 seconds for the command to execute WScript.Sleep 2 * 1000 End If 'Erase the batch file from the remote computer objFSO.DeleteFile "\\" & CStr(snpData.Fields("Name").Value) & "\C$\OracleClientVersion.bat" Err = 0 'Reset the error indicator Set objFile = objFSO.OpenTextFile("\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF, ForReading) If Err = 0 Then Do While Not (objFile.AtEndOfStream) strLine = objFile.ReadLine If InStr(UCase(strLine), "TNS PING UTILITY") > 0 Then objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & strLine & vbCrLf Exit Do Else 'Do nothing End If Loop objFile.Close If Err = 0 Then 'lngResult = MsgBox("No Errors accessing the file on " & CStr(snpData.Fields("Name").Value), 64, "Good") Else 'lngResult = MsgBox("An error happened while accessing the output file on the computer " & CStr(snpData.Fields("Name").Value) & vbCrLf & Err.Description & vbCrLf & "Continue?", 16 + 4, "Oh NO!") Err = 0 objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "Error " & Err.Description & vbCrLf End If 'Erase the log file from the remote computer objFSO.DeleteFile "\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF Err = 0 Else If Err <> 0 Then 'lngResult = MsgBox("An error happened while accessing the INI file on the computer " & CStr(snpData.Fields("Name").Value) & vbCrLf & Err.Description & vbCrLf & "Continue?", 16 + 4, "Oh NO!") objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "Error " & Err.Description & vbCrLf If lngResult = 7 Then dbActiveDirectory.Close Set snpData = Nothing Set dbActiveDirectory = Nothing Exit Sub End If Err = 0 End If End If Else 'lngResult = MsgBox("The computer " & CStr(snpData.Fields("Name").Value) & " could not be pinged" & vbCrLf & "Continue?", 16 + 4, "Oh NO!") objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "No ping response" & vbCrLf If lngResult = 7 Then dbActiveDirectory.Close Set snpData = Nothing Set dbActiveDirectory = Nothing Exit Sub End If End If snpData.MoveNext Set objFile = Nothing Loop snpData.Close objFileLog.Close Else If Err <> 0 Then lngResult = MsgBox("An error happened while connecting to Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!") End If End If dbActiveDirectory.Close Set snpData = Nothing Set dbActiveDirectory = Nothing End Sub Function PingTest(strComputer) Dim intPosition Dim objShell Dim objExec Dim strLine Dim strCommand On Error Resume Next PingTest = False Set objShell = CreateObject("wscript.shell") 'command to execute strCommand = "PING -i 10 -w 10 -n 1 " & strComputer 'Create Exec object Set objExec = objShell.Exec(strCommand) 'skip lines that contain information about our DNS 'server Do While objExec.StdOut.AtEndOfStream <> True strLine = objExec.StdOut.ReadLine intPosition = InStr(UCase(strLine), "RECEIVED =") If intPosition > 0 Then If InStr(strLines, "TTL expired in transit") = 0 Then If Trim(Mid(strLine, intPosition + 10, 2)) = "1" Then PingTest = True Else PingTest = False End If Else PingTest = False End If Exit Do End If Loop Set objShell = Nothing Set objExec = Nothing End Function
.
The V$SESSION_CONNECT_INFO view contains information about clients programs
connected to the database.
See http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_3017.htm#REFRN30224
Client programs can set arbitrary text in OCI_ATTR_DRIVER_NAME to have their name & version
shown in the CLIENT_DRIVER column.
@Chris
Yep, but the disadvantage is that its only on 11g… The view isn’t that “extensive” on 10g
Chris,
Thank you for sharing the information about GV$SESSION_CONNECT_INFO/V$SESSION_CONNECT_INFO. It appears that this view is intended to allow an application program to identify itself as being version w.x.y.z, rather than the view showing the Oracle Client version that is in use (maybe there is a setting to enable the Oracle client to fill in this information?).
To Marco’s point, this is the output from Oracle Database 10.2.0.x:
And from 11.1.0.7:
The above is a test database with a single connected user session (SQL*Plus).
Anyone have additional ideas? It seems like this should be an easy task to accomplish. It seems like SQL*Net logging might indicate the version, but logging cannot be enabled and disabled without the client disconnecting and reconnecting.
not sure if it is what you are looking for but you can try to use genezi (from 10g on) but it does not work on windows
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14294/install.htm#sthref105
Fidelinho,
Thank you for the suggestion. Here is what the output looks like:
The above may be compared with the output of the tnsping command executed from the same server:
I am not sure why the parameter file was not listed, other than the fact that the file /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora does not exist.
Now the question is how would a person execute this command and capture the output on each Unix/Linux computer (or server)?
Article by a primarily Windows “power user” magazine:
http://www.maximumpc.com/article/features/linux_remote_system_access_guide
From RedHat:
http://www.redhat.com/mirrors/LDP/LDP/nag2/x-087-2-appl.remote.html
Others:
http://tldp.org/HOWTO/Networking-Overview-HOWTO-7.html
http://www.faqs.org/docs/linux_intro/sect_10_03.html
—-
The ideal solution should provide a way for the DBA to obtain the information, without having to involve the Windows and/or Unix and/or Linux administrators (or the SOX auditors). Maybe a review of the built-in PL/SQL packages?
Any auditing capabilities that might help?