Configure Servers to use TNSNAMES.ORA on a Network Share

A Windows Script to Set an Environment Variable on Servers Across the Network:  Here's a script that configures servers to use a TNSNAMES.ORA file on a network share.

Environments that use Oracle often have Windows servers running front/middle layer applications that access the Oracle back-end. These Windows servers have the Oracle client installed, and use a TNSNAMES.ORA file (and SQLNET.ORA) which is similar to a hosts file. The database names are listed in the file and resolves them to the host name and port address where the database listener resides.

The TNSNAMES.ORA file is typically stored locally on every client and server that has an Oracle client installed. As databases are added, changed, and moved over time, the TNSNAMES.ORA file must be updated, and distributed to all of the clients and servers that need it. This can be challenging. The alternative is to place the file on a network share, and point everyone's Oracle client to that share to reference the file.

There a a few ways to do this, but one way (the one covered here) is to set an environment variable, TNS_ADMIN, on the client, whose value is the UNC path to the folder that contains the files. The script reads a list of servers from a text file that you provide, reaches out and sets the environment variable on each server.

System environment variables are all stored in the same location in the Windows registry, HKey_Local_Machine/System/CurrentControlSet/Control/Session Manager/Environment. So, if you want to use this script to set other variables for other purposes, just change the variable name after the // near the end of the registry line.

use Win32::TieRegistry(Delimiter=>"/",ArrayValues=>0);

open SERVERS,"servers.txt";
open OUT,">report.txt";

$myShare = "\\\\myServer\\myShare";

while(<SERVERS>){
 chomp($server=$_);
 $server=~s/\s+//g;
 print "$server\t";
 print OUT "$server\t";
 if($Registry->{"//$server/LMachine/System/CurrentControlSet/Control/Session Manager/Environment//TNS_ADMIN"} = $myShare){
  print "OK\n";
  print OUT "OK\n";
 }else{
  print "Failed\n";
  print OUT "Failed\n";
 }
}

close OUT;