An administrator came to me today. Recently they had migrated from Oracle 10 to Oracle 11 and from HP UX to ODA (Oracle Database Appliance). Due to the fact that in this organization there are over 2000 databases, some applications wheren’t tested. In some cases application server look at the wrong TNSNames.ora. The request was to replace the text after host for the approriate host. I had the ‘luck’ that this request only included servers.

I created a that resolve his problem. The script contacts the ActiveDirectory for the list of “servers”. Then the script test if that server has an Oracle directory under C:\. Change this if you have Oracle installed under a different folder. Next there’s a check under the Oracle Directory for any folder that match Ora. These folders are included in an array. The next thing that will be checked is, if the TNSNames.ora exists in the usual folder. If that is the case the script continues. If it doesn’t exist the script will continue with the next folder or server.

Then the content of the TNSNames.ora will be loaded into the memory. The script renames the original TNSNames.ora to TNSNames.old. Or if that file is already in use in some other extension. Then all lines in the TNSNames will be check on the existance of few specific words and those words will be replaced with something else. At last the array NewTNSNames will be written to the location.

Import-Module ActiveDirectory
$ADComputers = Get-ADComputer -Filter * -Properties * | Where {$_.OperatingSystem -match "Server"}
$Computers =@()
ForEach ($ADComputer in $ADComputers){$ADComputer = $ADComputer.DNSHostName
If((Test-Path "\\$ADComputer\C$\Oracle") -eq $True){$Computers += $ADComputer}}
ForEach ($Computer in $Computers){$Folders =@( gci "\\$Computer\c$\Oracle\Ora*")
ForEach ($Folder in $Folders){If ((Test-Path "$Folder\Network\Admin\TNSNames.ora") -eq $True){$TNSNames = get-content "$Folder\Network\Admin\TNSNames.ora"
If ((Test-Path "$Folder\Network\Admin\TNSNames.old") -eq $False){Rename-item -Path "$Folder\Network\Admin\TNSNames.ora" -NewName "$Folder\Network\Admin\TNSNames.old"}
If ((Test-Path "$Folder\Network\Admin\TNSNames.NaChangeAugustus2014") -eq $False){Rename-item -Path "$Folder\Network\Admin\TNSNames.ora" -NewName "$Folder\Network\Admin\TNSNames.NaChangeAugustus2014"}
$NewTNSNames =@()
ForEach ($Item in $TNSNames){
If ($Item -match "odatst101a"){$NewTNSNames += $Item.replace("odatst101a","RobIsDeBarbecueKoning.local")}
If ($Item -match "odatst101b"){$NewTNSNames += $Item.replace("odatst101b","RobIsDeBarbecueKeizer.local")}
If ($Item -notmatch "odatst101a" -and $Item -notmatch "odatst101b"){$NewTNSNames +=  $Item}}
ForEach ($Item in $NewTNSNames){Add-Content -Value $Item -Path "$Folder\Network\Admin\TNSNames.ora" }}}}