Get Oracle Client Versions

PowerShell Help
1

As part of an Oracle DB server upgrade, I am trying to query all PCs that may not have a particular version of the Oracle client installed (Oracle makes this process as hard as possible to automate, unless you script SQL+ queries, which is not an option). I have fuddled around for two weeks trying to put something together but I just can’t seem to get the output that I want.

What I really need the script to do is:

  1. Query HKLM\Software\Oracle and recurse through the hive and find all instances of the “ORACLE_HOME” reg key, and then pull the path variable from that key
  2. Test the path/s found in step one for the oci.dll file
  3. If oci.dll exists, pull the file version
  4. Export to a txt or csv that includes host name and oci.dll version info

I have used/tested variations of the following reg query to pull path info:

reg query \$HOST_NAME\HKLM\Software\Oracle /s | Select-String “ORACLE_HOME” | %{$_.Line.Split(" ")} | Select-String -Pattern c:

I also have used variations of get-itemproperty to pull ‘FileVersion’ from the ‘VersionInfo’ script property on oci.dll, but can’t get the two script chunks to work together.

One of the main issues I have run into is parsing out the path from the reg keys; I can only seem to dump the entire line which I end up having to split out the path using ‘-pattern’.

End game, this is going to be executed on ~2000 PCs and I wanted to try to avoid skipping the reg query piece and doing a recursive search against c:\oracle, c:\ora, C:\app & c:\orant which are the 4 most common paths where the oci.dll file resides (this has recently become an option since I can’t seem to get the reg query piece to work how I want it).

Any help would be greatly appreciated!

2

Because the Reg command isn’t a PowerShell command, you’re indeed stuck parsing its string-based output. You will definitely have to use a regular expression to do that, and the -split or -match operators would do that. Using -match with a “capturing” regular expression might be easier; it’ll populate $matches (a built-in variable) with the captured portion of your regex.

But it’d perhaps help if we narrowed things down a bit. You’ve mentioned the challenge with the Reg command, and you’ve mentioned checking the file. You’ve mentioned a couple of chunks of script, which you haven’t yet shared :). Can we maybe focus on ONE thing, and try and help you with that? If there’s more afterwards, happy to tackle it, but in online forums it’s a little tough to try and handle a bunch of different directions at once.

Also indicate if Remoting is an option. It could speed things up and simplify massively. Or, if deploying the solution as a login script, wherein each client reports back to some central location, is an option. Again, that’d help distribute the load and open up different options for actually accomplishing this. Or, let me know if perhaps WMI is an option, or if you have network access to each client’s hard drive (e.g., via a C$ share).

3

Don, thanks for the reply. I figured as much, as far as using reg query and extracting the string data.

I agree 100% on breaking it down in pieces. I have attached my latest script iteration (link to gist) which was only used for me to get the path info from the initial wave of PCs, just to see how many path variations I am dealing with. I also attached a sample output of what it produced. The oci.dll file that I am looking to get the file version from, is located in a ‘bin’ directory within each of the paths in the output sample.

As of today, unfortunately, remoting is not an option on PCs (currently fighting that battle with the desktop team). Logon script modification is possible, but long story short, it would dilute results for this exact exercise. WMI is available and is what I have historically used to execute remote queries for other needs. I also maintain DA privs so network access via C$ is also an option.

Logically, I think the first step would be to find the most efficient way to search for oci.dll and test to see if it exists. That said, I am completely open if there is a better place to start.

Thanks again.

Script Link >> https://gist.github.com/vN3rd/6921643

4

I prefer to use WMI’s StdRegProv class to query remote registry values in PowerShell, though there are implementations based on the Remote Registry service as well. (Start with reading about the RegistryKey.OpenRemoteBaseKey method if you’re interested in that approach.)

I’m just typing up this code quickly in the forum editor; haven’t tested it. Sorry if I flub it a bit, but hopefully it’ll help you get started:

$regProv = Get-WmiObject -ComputerName $HOST_NAME -Namespace 'root\default' -Class 'StdRegProv' -List
$HKLM = [System.UInt32]0x80000002L

$result = $regProv.GetStringValue($HKLM, 'Software\Oracle', 'ORACLE_HOME')
if ($result.ReturnValue -ne 0)
{
    throw New-Object System.ComponentModel.Win32Exception([System.Int32]$result.ReturnValue)
}

$oracleHome = $result.sValue

# Do stuff with the $oracleHome variable.
5

Dave, great info. I will test a bit with that and see what I can come up with. Thanks.

[quote=10646]I prefer to use WMI’s StdRegProv class to query remote registry values in PowerShell, though there are implementations based on the Remote Registry service as well. (Start with reading about the RegistryKey.OpenRemoteBaseKey method if you’re interested in that approach.)
I’m just typing up this code quickly in the forum editor; haven’t tested it. Sorry if I flub it a bit, but hopefully it’ll help you get started:

$regProv = Get-WmiObject -ComputerName $HOST_NAME -Namespace 'root\default' -Class 'StdRegProv' -List
$HKLM = [System.UInt32]0x80000002L

$result = $regProv.GetStringValue($HKLM, 'Software\Oracle', 'ORACLE_HOME')
if ($result.ReturnValue -ne 0)
{
    throw New-Object System.ComponentModel.Win32Exception([System.Int32]$result.ReturnValue)
}

$oracleHome = $result.sValue

# Do stuff with the $oracleHome variable.
[/quote]
6

A very common issue I see in my environment is that I see both workstations and (app) servers with multiple Oracle clients, sometimes as many as four, and possibly with different versions and architectures. If you are relying on the PATH and running a utility like SQLPLUS or TNSPING you’ll have one of two unacceptable results:

either your PATH successfully resolves the executable and you get ONE version result
or, the PATH didn’t resolve the executable, and you get no results.
Either way, you are blind to possibly multiple client installations.

Issue #2: Instant Client doesn’t have TNSPING, and sometimes doesn’t include SQL*Plus.

If a computer has the Oracle Instant Client (not the full client), then TNSPING is not included, and SQLPLUS is an optional-addon. So can’t rely on those tools being there. Furthermore, the Instant Client is sometimes installed as an unzip-and-go solution, so there’s no Oracle Inventory and nothing in HKLM.

Issue #3: Client was installed using “Custom”, and ODBC, OLEDB, ODP.Net, and JDBC were not installed.

Obvious case, there will be no ODBC or JDBC readme’s to scrape version info from.

Solution:

One thing that the Instant client and the full client have in common is a DLL file called oraclient10.dll, oraclient11.dll, generally: oraclient*.dll. So let’s traverse the hard disk to find them and extract their version info. PowerShell is amazing at this and can do it in one line, reminds me of home sweet Unix. So you could do this programatically or even remotely.

Here’s the one-liner (sorry about the right scroll, but that’s the nature of one-liners, eh?). Supposing you’re already in a PowerShell:

gci C:,D:\ -recurse -filter ‘oraclient*.dll’ -ErrorAction SilentlyContinue | %{ $_.VersionInfo } | ft -Property FileVersion, FileName -AutoSize
And if you’re not in PowerShell, i.e. you’re simply in a CMD shell, then no problem, just call powershell " … ", as follows:

powershell “gci C:,D:\ -recurse -filter ‘oraclient*.dll’ -ErrorAction SilentlyContinue | %{ $_.VersionInfo } | ft -Property FileVersion, FileName -AutoSize”
Example Outputs

Here’s some outputs from some of my systems. This bad citizen has 3 Oracle 11.2.0.3 clients. You can see that some of them are 32-bit and others are 64-bit:

FileVersion FileName

11.2.0.3.0 Production C:\NoSync\app\oracle\product\11.2\client_1\bin\oraclient…
11.2.0.3.0 Production C:\oracle\product\11.2.0\client_1\bin\oraclient11.dll
11.2.0.3.0 Production C:\oracle64\product\11.2.0\client_1\bin\oraclient11.dll
Another system, this one has 10g client on the D:\

FileVersion FileName

10.2.0.4.0 Production D:\oracle\product\10.2\BIN\oraclient10.dll
Caveats/Issues

This obviously requires PowerShell, which is standard in Windows 7+ and Server 2008 R2+. If you have XP (which you shouldn’t any more) you can easily install PowerShell.
I haven’t tried this on 8i/9i or 12c. If you are running 8i/9i, then there’s a good chance you are on an old OS as well and don’t have PowerShell and Heaven help you. It should work with 12c, since I see there is such a file oraclient12.dll that gets installed. I just don’t have a Windows 12c client to play with yet.
This solution is taken from here.

For more solutions and updates regarding oracle errors and solutiondo visit here.

7

A very common issue I see in my environment is that I see both workstations and (app) servers with multiple Oracle clients, sometimes as many as four, and possibly with different versions and architectures. If you are relying on the PATH and running a utility like SQLPLUS or TNSPING you’ll have one of two unacceptable results:

either your PATH successfully resolves the executable and you get ONE version result
or, the PATH didn’t resolve the executable, and you get no results.
Either way, you are blind to possibly multiple client installations.

Issue #2: Instant Client doesn’t have TNSPING, and sometimes doesn’t include SQL*Plus.

If a computer has the Oracle Instant Client (not the full client), then TNSPING is not included, and SQLPLUS is an optional-addon. So can’t rely on those tools being there. Furthermore, the Instant Client is sometimes installed as an unzip-and-go solution, so there’s no Oracle Inventory and nothing in HKLM.

Issue #3: Client was installed using “Custom”, and ODBC, OLEDB, ODP.Net, and JDBC were not installed.

Obvious case, there will be no ODBC or JDBC readme’s to scrape version info from.

Solution:

One thing that the Instant client and the full client have in common is a DLL file called oraclient10.dll, oraclient11.dll, generally: oraclient*.dll. So let’s traverse the hard disk to find them and extract their version info. PowerShell is amazing at this and can do it in one line, reminds me of home sweet Unix. So you could do this programatically or even remotely.

Here’s the one-liner (sorry about the right scroll, but that’s the nature of one-liners, eh?). Supposing you’re already in a PowerShell:

gci C:,D:\ -recurse -filter ‘oraclient*.dll’ -ErrorAction SilentlyContinue | %{ $_.VersionInfo } | ft -Property FileVersion, FileName -AutoSize
And if you’re not in PowerShell, i.e. you’re simply in a CMD shell, then no problem, just call powershell " … ", as follows:

powershell “gci C:,D:\ -recurse -filter ‘oraclient*.dll’ -ErrorAction SilentlyContinue | %{ $_.VersionInfo } | ft -Property FileVersion, FileName -AutoSize”
Example Outputs

Here’s some outputs from some of my systems. This bad citizen has 3 Oracle 11.2.0.3 clients. You can see that some of them are 32-bit and others are 64-bit:

FileVersion FileName

11.2.0.3.0 Production C:\NoSync\app\oracle\product\11.2\client_1\bin\oraclient…
11.2.0.3.0 Production C:\oracle\product\11.2.0\client_1\bin\oraclient11.dll
11.2.0.3.0 Production C:\oracle64\product\11.2.0\client_1\bin\oraclient11.dll
Another system, this one has 10g client on the D:\

FileVersion FileName

10.2.0.4.0 Production D:\oracle\product\10.2\BIN\oraclient10.dll
Caveats/Issues

This obviously requires PowerShell, which is standard in Windows 7+ and Server 2008 R2+. If you have XP (which you shouldn’t any more) you can easily install PowerShell.
I haven’t tried this on 8i/9i or 12c. If you are running 8i/9i, then there’s a good chance you are on an old OS as well and don’t have PowerShell and Heaven help you. It should work with 12c, since I see there is such a file oraclient12.dll that gets installed. I just don’t have a Windows 12c client to play with yet.