Get Oracle Client Information with Powershell

Leave a comment

I spent a couple of hours this morning coming up with a way to see which of a large number of windows hosts have Oracle client installed. In particular, I wanted the path to the Oracle home for each machine. We have some 3rd party agent-based tools that gather data about windows machines, but licensing prohibits installing them everywhere.

Eventually I decided to create a powershell script that iterates through a list of windows hosts and outputs the value of all ORACLE_HOME entries in the registry plus the main PATH variable. A number of machines are likely to have more than one Oracle client. Or they may have multiple values for ORACLE_HOME even though some have been uninstalled. Getting the PATH value will allow me to see which one was installed most recently, since the most recently installed client should be foremost in the PATH.

I’ve posted the powershell script below. It requires an input file and creates an output file that can be opened in Excel. It illustrates using FOREACH loops, invoking the REG QUERY command against remote machines, using IF logic, and reading and writing to a file and some grep style filtering.

# Written with powershell 2.0. Not sure if it works with 1.0.

# File containing list of hosts.
$host_inputfile = "host_list.txt"

# Name of output data file and of log file.
$ofile = "ohome_values.txt"
$olog = "ohome_run.log"

# Initialize counters for hosts and Oracle Homes.
$total_hosts = 0
# Total homes found
$total_homes = 0

# Update log file to say that processing is starting.
echo "Starting new run of get_oracle_client." | out-file $olog -append
$start_time = get-date
echo $start_time | out-file $olog -append

# Start output data file by putting a header row in.
echo "HOSTNAME, KEY_NAME, KEY_TYPE, KEY_VALUE" | out-file $ofile

# Read into a variable the file containing the list of hosts.
$host_list = get-content $host_inputfile

# Iterate through each host in the list.
foreach ($HOST_NAME in $host_list) {

	# Update the user on progress.
	echo "Working on $HOST_NAME..."

	# Run a a command to get a list of all values from the registry for
	# ORACLE_HOME. Several strings need to be excluded, such as
	# ORACLE_HOME_NAME and agent10g.

	$OH_RAW = reg query \\$HOST_NAME\HKLM\Software\Oracle /s | select-string "ORACLE_HOME" 

	$OH = echo $OH_RAW | sort | get-unique | select-string -notmatch "ORACLE_HOME_KEY", "ORACLE_HOME_NAME", "agent10g"

	# Write Oracle Home values to file. Include a column for the host name.
	# Iterate through each line returned from the remote machine.
	foreach ($line in $OH) {

		# Initialize output var
	  $out_var = "$HOST_NAME"

		$split_line = -split $line

		foreach ($word in $split_line) {

		# If there was nothing to be processed, then don't write to file.
		if ($out_var -eq "$HOST_NAME,") {

		# Increment Oracle Homes counter variable and write values to file.
		$total_homes = $total_homes + 1
		echo $out_var | out-file $ofile -append


	# Key for getting PATH:
	# HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment

	$path_var = reg query "\\$HOST_NAME\HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment"
	$path_var = echo $path_var | select-string "Path"
	$path_var = echo $path_var | select-string -notmatch "PATHEXT", "TRACE_FORMAT_SEARCH_PATH", "PSModulePath", "Os2LibPath"

	# Write path value to file. Include a column for the host name.
	foreach ($line in $path_var) {

		# Initialize output var
	  $out_var = "$HOST_NAME"

		$split_line = -split $line

		foreach ($word in $split_line) {

		# If there was nothing to be processed, then don't write to file.
		if ($out_var -eq "$HOST_NAME,") {

		# Write values to file.
		echo $out_var | out-file $ofile -append

	# Increment hosts counter variable.
	$total_hosts = $total_hosts + 1


$end_time = get-date

# Update screen.
echo "Start time was: $start_time."
echo "End time was: $end_time."
echo "$total_hosts hosts processed."
echo "$total_homes Oracle Homes found." 

# Append to log file.
echo "Ending run of get_oracle_client." | out-file $olog -append
echo "$total_hosts hosts processed." | out-file $olog -append
echo "$total_homes Oracle Homes found." | out-file $olog -append
echo $end_time | out-file $olog -append

Using Powershell for Oracle DBA scripts


I needed to deploy an RMAN backup script on a Windows OS machine and couldn’t to use OEM in the target environment. So I decided to try out powershell rather than use BAT and VBScript. Powershell proved to be a handy, easy to use scripting environment.

While powershell is object based and is tightly plugged into both the Windows OS and .NET, I took the approach of writing a straight-forward script similar to a unix shell script.

To execute a powershell script, you create a text file that ends with the extension PS1, e.g. backup_script.ps1. Here’s how to run the script from the windows command line.

C:\> powershell -file backup_script.ps1

I learned that powershell scripts can have signed security certificates to prevent rogue scripts from causing damage. Powershell can run in different security modes, ranging from restricted (interactive use only, no scripts) to unrestricted, where you get a prompt in order to run a script from the internet. I settled for “RemoteSigned” mode, where scripts downloaded from the internet must be signed, but not ones that you create. Default mode is restricted, so you’ll need to run the following from the powershell prompt to get local scripts to run:

PS C:\> set-executionpolicy remotesigned

Powershell supports two things that help out the unix shell scripter: aliases and pipelines. There are built-in aliases so that you can use familiar commands like ls and echo for the equivalent powershell commands that have much longer names. The powershell commands behave differently with a different set of command switches, but it’s still helpful to have the aliases. The pipeline support allows one to pipe virtually all powershell commands, unlike the meager pipeline support for windows bat commands.

In powershell 2.0 there’s an email command, send-mailmessage, which DBAs will find handy for monitoring and reporting.

The help system is built in to the powershell command line. Typing

PS C:\> help

gets you a complete list of commands and aliases.
And typing

PS C:\> help specific-command

gets you help on that command. There are also switches on the help command for examples, details and full help, e.g. help specific-command -examples.

What’s radically different about powershell from unix style shell languages is that it’s object based. When you pipe a command to another command, you are piping an object, not text. The receiving command can then reference properties of the object that is passed.

One item not supported in powershell: input redirection. This is a mainstay in unix shell scripting. It’s a handy way to keep your sql statements embedded in your shell script and to use variables derived from the shell script directly in the sql without passing them as positional arguments. By input redirection, I mean the following bit of unix scripting:

sqlplus /nolog << EOF
connect $user/$pass@$sid 
select * from table
where f1 = $another_var;

Here’s a pared down sample of the powershell script I created. This sample does not do anything with the .NET or object based attributes of powershell, with the exception of the line that deletes old files.

It illustrates the following:
> Getting command line arguments
> Formating a date
> String concatenation, done a la unix shell without a concatenation operator
> Assigning variables
> Launching an external command (nothing special, just run it)
> Sending email
> Deleting files older than a certain date

# Get script input params

# Assemble other variables
$datetime = get-date -format ""
$RMAN_LOG = "$LOG_DIR\$ORACLE_SID.$RMAN_TYPE.$datetime.rman.log"

# Start logging to file and then run RMAN command. Echo is an alias for Write-Output.
$newline = "Start Execution of $RMAN_TYPE backup on $ORACLE_SID"
echo $newline | out-file $RMAN_LOG
get-date | out-file $RMAN_LOG -append
rman cmdfile=$BIN_DIR\rman_backup_disk_compressed.rman using $ORACLE_SID | out-file $RMAN_LOG –append

# Send email. Note that cat is an alias for Get-Content.
$body = cat $RMAN_LOG | out-string 
send-mailmessage -to "" -from "" -body $body -subject "RMAN log output" -smtpserver mailserver

# Remove files older than 8 days.
# Note that ls is an alias for Get-ChildItem and del is an alias for Remove-Item.
# Also, ? is an alias for Where-Object and $_ is a built-in variable 
# to refer to the collection of objects passed from the previous pipeline.
ls $LOG_DIR\$ORACLE_SID.$RMAN_TYPE.* |? {$_.LastWriteTime -lt (get-date).AddDays(-8)} | del