Parsing Out Oracle TNSNames.ora Using PowerShell

TNSNames.ora

If you work with Oracle, you are no stranger to TNSNames.ora whether or not you currently use it. In short, it is a local/network configuration file used by the Oracle client(s) to point and connect to Oracle databases. One could use other methods like LDAP or EZConnect as alternatives but TNSNames.ora is more common and I also mention this for a reason.

How do you enumerate through the Oracle data sources?

First off, before you start parsing TSNNames.ora, you have to understand that there are better ways to get at the information than parsing the TNSNames.ora file.

The method that I would recommend and the one that I wrote this small post about is a good way to do so:

Enumerate entries in TNSNames.ora using Oracle data provider class and DataSourceEnumerator

The reason that the above is the best method to enumerate data sources is because, it does not matter what naming method you used in SQLnet.ora, the above method would work nicely, assuming, you have a working structure already in place.

Why am I re-inventing the wheel?

The keyword above is  “working structure in place”. If you don’t have one or would like to move to a better working structure, you most likely have to to parse TNSNames.ora.

Assuming you are currently using TNSNames.ora, here are some of the scenarios that I can think of:

  1. You need to merge multiple tnsnames.ora files to come-up with a master tnsnames.ora
  2. You want to programmatically export tnsnames.ora entries to LDAP (much better and centralized)
  3. You want to get the entries and store them in a repository containing the list of databases you manage
  4. You want to programmatically get at the information for your automation needs

The problem is that you don’t have a working structure in place yet to use the recommended method!

Do I mean no one has done this before?

Yes and No. There are variations. The one that I liked the most for my use in PowerShell is this one:

Add-TnsAdminEntry by anti121

It mostly works except in some edge-case situations like the name containing hyphens and a few other minor things. Why would I not alter it to make it work for my edge-case situations? Good question. “Regular Expressions” – is what I would retort. It is one of those things that is great and is a curse at the same time. It is powerful but so hard to reacquaint oneself time and time again (as I have done) and still fail for most but the most simplest of use-cases. I spent the whole morning trying to adjust the regular expression and failed miserably. It took me less than an hour to write the one below and you, my friend, you can maintain this easily!

Alright, show me the code!

I hear you.

I am positive that this does not cover a lot of edge-cases but you are free to modify it to cover additional scenarios.

I based this version on the “Add-TnsAdminEntry” referenced above but did not go the Regular Expressions route.

#Inspiration source: http://poshcode.org/1602
#20150820 Jana - Variation of the function Get-TnsAdminEntries since the original noted above
#               does not work well when the "Name" or "Host" has hyphens + "Service_Name" could be aliased as "SID"
#         This version is easier to enhance/maintain since it does not use regular expressions (which most people are not familiar with)

#Usage:
<# Get-OraTnsAdminEntries -File "C:\oracle\product\11.2.0\client_1\network\admin\tnsnames.ora" #>
function Get-OraTnsAdminEntries
{
    param
    (
        [System.IO.FileInfo] $File
    )
    
    begin {}
    
    process
    {
        [object[]] $tnsEntries = @()        

        if ($_)
        {
            $File = [System.IO.FileInfo] $_
        }
        if (!$File)
        {
            Write-Error "Parameter -File  is required."
            break
        }
        if (!$File.Exists)
        {
            Write-Error "'$File.FullName' does not exist."
            break
        }
        
        [string] $data = gc $File.FullName | ? {!$_.StartsWith('#')}



        #Sample TNS entry
        <# $Name = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = $Protocol)(HOST = $Hostname)(PORT = $Port)) ) (CONNECT_DATA = (SERVICE_NAME = $Service) ) ) #>
        
        #Here is what we have to do to identify individual TNS entries..
        #----------------------------------------------------------------
        #1) Replace all "`n" with ""...so that we flatten out the file
        #2) Replace all " " with ""...so that there are no spaces
        #3) Replace all "`t" with ""...so that there are no tabs
        #4) Replace all ")))(" with "))("...remove false positive ))) as in the case of 8iQA01 for example which has ))) in the middle. for ADDRESS_LIST vs ADDERESS
        #5) Replace all ")))" with ")))`n"...so that we separate out individual entries into their own line
        #6) Replace all "=(" with "=;"...to separate by and identify individual entries
        #7) Replace all "(" with ""...to remove open brackets
        #8) Replace all ")" with ";"...to replace close brackets with ";"
        #9) Replace all ";;" with ";"...to not have multiple ;
        #10) Replace all ";;" with ";" again...to not have multiple ;
        
        $lines = $data.Replace("`n","").Replace(" ","").Replace("`t","").Replace(")))(","))(").Replace(")))",")))`n").Replace("=(","=;").Replace("(","").Replace(")",";").Replace(";;",";").Replace(";;",";").Split("`n")


        #At this point each line should look like this
        #----------------------------------------------------------------
        #$Service,$Service.WORLD=;DESCRIPTION=;ADDRESS=;PROTOCOL=$Protocol;Host=$Hostname;Port=$Port;CONNECT_DATA=;SERVICE_NAME=$Service;

        foreach ($line in $lines)
        {
            if ($line.Trim().Length -gt 0)
            {
                #Replace ";" with "`n" so that each can become a name=value pair in a hash-table
                $lineBreakup = ConvertFrom-StringData -StringData $line.Replace(";","`n")

                #At this point $linebreakup would look like this
                #----------------------------------------------------------------
                <# Name Value ---- ----- ADDRESS Port $Port CONNECT_DATA $Service,$Service.WORLD PROTOCOL $Protocol DESCRIPTION Host $Hostname SERVICE_NAME $Service #>

                $entryName = $line.Split("=")[0]       #Everything to the left of the first "=" in "$Service,$Service.WORLD=;DESCRIPTION=;ADDRESS=;PROTOCOL=$Protocol;Host=$Hostname;Port=$Port;CONNECT_DATA=;SERVICE_NAME=$Service;"

                $tnsEntry = New-Object System.Object 
                $tnsEntry | Add-Member -type NoteProperty -name Name     -value $entryName
                $tnsEntry | Add-Member -type NoteProperty -name SimpleName -value ($entryName.Split(",")[0].Trim().Split(".")[0].Trim())  #Pick "MyDB" from "MyDB, MyDB.World" or "MyDB.World, MyDB"
                $tnsEntry | Add-Member -type NoteProperty -name Protocol -value $lineBreakup["PROTOCOL"]
                $tnsEntry | Add-Member -type NoteProperty -name Host     -value $lineBreakup["Host"]
                $tnsEntry | Add-Member -type NoteProperty -name Port     -value $lineBreakup["Port"]
                $tnsEntry | Add-Member -type NoteProperty -name Service  -value $(if ($lineBreakup["SERVICE_NAME"] -eq $null) {$lineBreakup["SID"]} else {$lineBreakup["SERVICE_NAME"]})  #One of the two will have the value. Pick the one that does!

                #Make sure we ignore entries created due to empty lines or mal-formed structure
                if ($tnsEntry.Service.Trim().Length -gt 0)
                {
                    $tnsEntries += $tnsEntry
                }
                else
                {
                    #Make sure people notice problem entries!
                    if ($line.Trim().Length -gt 0)
                    {
                        Write-Warning "Ignoring empty/mal-formed entry: [{0}]" -f $line
                    }
                }
            }
        }

        $tnsEntries
    }
    
    end {}
}

Sample output (obfuscated of course)


Name                        SimpleName  Protocol  Host                 Port Service
----                        ----------  --------  ----                 ---- -----
MYPRODDB,MYPRODDB.WORLD     MYPRODDB    TCP       PROD-ODS.tare.local  1521 MYPRODDB
PRODUCTION,PRODUCTION.WORLD PRODUCTION  TCP       PROD-ODS.tare.local  1521 MYPRODDB
PROD-ODS,PROD-ODS.WORLD     PROD-ODS    TCP       PROD-ODS.tare.local  1521 MYPRODDB
PROD-OLTP.WORLD,PROD-OLTP   PROD-OLTP   TCP       prod-oltp.tare.local 1522 PROD-OLTP 
Advertisements

12 thoughts on “Parsing Out Oracle TNSNames.ora Using PowerShell

  1. Parsing out tnsnames.ora file contents …. That’s a great script, and it comes so very close to meeting my needs. However, I have a few entries that have duplicate ADDRESS= references. Our Oracle DBA states that this is accepted and is used as some degree of load balancing? Anyway, any tnsnames entry having more than 1 ADDRESS= entry causes the script to fail with errors like “ConvertFrom-StringData : Data item ‘ADDRESS’ in line ‘ADDRESS=’ is already defined.
    At D:\My Documents\PowerShell\Get-OraTnsAdminEntries.ps1:68 char:32
    + $lineBreakup = ConvertFrom-StringData -StringData $line.Replace( …”

    I know that hash tables require unique keys; can I ask how you’d go about a workaround for this edge case? Thank you in advance,

    Steve

      1. Sure, thanks! Here is a snippet of our current tnsnames.ora file.

        OFNTST=
        (DESCRIPTION=
        (ADDRESS=(PROTOCOL=TCP)(HOST=wxydb01-vip)(PORT=1524))
        (ADDRESS=(PROTOCOL=TCP)(HOST=wxydb02-vip)(PORT=1524))
        (CONNECT_DATA=(SERVICE_NAME=ofntst.egh.intregacommu.com))
        )

        OFNDEV=
        (DESCRIPTION=
        (ADDRESS=(PROTOCOL=TCP)(HOST=wxydb01-vip)(PORT=1525))
        (ADDRESS=(PROTOCOL=TCP)(HOST=wxydb02-vip)(PORT=1525))
        (CONNECT_DATA=(SERVICE_NAME=ofndev.egh.intregacommu.com))
        )

        OFNUAT=
        (DESCRIPTION=
        (ADDRESS=(PROTOCOL=TCP)(HOST=wxydb01-vip)(PORT=1527))
        (ADDRESS=(PROTOCOL=TCP)(HOST=wxydb02-vip)(PORT=1527))
        (CONNECT_DATA=(SERVICE_NAME=ofnuat.egh.intregacommu.com))
        )

        OFNPSS=
        (DESCRIPTION=
        (ADDRESS=(PROTOCOL=TCP)(HOST=wxydb01-vip)(PORT=1528))
        (ADDRESS=(PROTOCOL=TCP)(HOST=wxydb02-vip)(PORT=1528))
        (CONNECT_DATA=(SERVICE_NAME=ofnpss.egh.intregacommu.com))
        )

  2. I have made necessary changes to the loop to accommodate your condition. It is not great code but it should work. Search for “SteveA” to locate the changes

    foreach ($line in $lines)
    {
    if ($line.Trim().Length -gt 0)
    {
    #If there are more than one ADDRESS’s, make the second one ADDRESS2
    #Begin Addition (for SteveA)
    $re = [regex]’ADDRESS’
    $line = $re.Replace($line, ‘ADDRESS2′, 1)
    $re = [regex]’PROTOCOL’
    $line = $re.Replace($line, ‘PROTOCOL2′, 1)
    $re = [regex]’HOST’
    $line = $re.Replace($line, ‘HOST2′, 1)
    $re = [regex]’PORT’
    $line = $re.Replace($line, ‘PORT2’, 1)
    #End Addition (for SteveA)

    #Replace “;” with “`n” so that each can become a name=value pair in a hash-table
    $lineBreakup = ConvertFrom-StringData -StringData $line.Replace(“;”,”`n”)

    #At this point $linebreakup would look like this
    #—————————————————————-

    $entryName = $line.Split(“=”)[0] #Everything to the left of the first “=” in “$Service,$Service.WORLD=;DESCRIPTION=;ADDRESS=;PROTOCOL=$Protocol;Host=$Hostname;Port=$Port;CONNECT_DATA=;SERVICE_NAME=$Service;”

    $tnsEntry = New-Object System.Object
    $tnsEntry | Add-Member -type NoteProperty -name Name -value $entryName
    $tnsEntry | Add-Member -type NoteProperty -name SimpleName -value ($entryName.Split(“,”)[0].Trim().Split(“.”)[0].Trim()) #Pick “MyDB” from “MyDB, MyDB.World” or “MyDB.World, MyDB”
    $tnsEntry | Add-Member -type NoteProperty -name Protocol -value $lineBreakup[“PROTOCOL”]
    $tnsEntry | Add-Member -type NoteProperty -name Host -value $lineBreakup[“Host”]
    $tnsEntry | Add-Member -type NoteProperty -name Port -value $lineBreakup[“Port”]

    #If there are more than one ADDRESS’s, make the second one ADDRESS2
    #Begin Addition (for SteveA)
    if ($lineBreakup[“ADDRESS2”])
    {
    $tnsEntry | Add-Member -type NoteProperty -name Protocol2 -value $lineBreakup[“PROTOCOL2”]
    $tnsEntry | Add-Member -type NoteProperty -name Host2 -value $lineBreakup[“Host2”]
    $tnsEntry | Add-Member -type NoteProperty -name Port2 -value $lineBreakup[“Port2”]
    }
    #End Addition (for SteveA)

    $tnsEntry | Add-Member -type NoteProperty -name Service -value $(if ($lineBreakup[“SERVICE_NAME”] -eq $null) {$lineBreakup[“SID”]} else {$lineBreakup[“SERVICE_NAME”]}) #One of the two will have the value. Pick the one that does!

    #Make sure we ignore entries created due to empty lines or mal-formed structure
    ….

    1. Well, it’s closer but not yet correct. For example, this tnsnames.ora entry:
      OFNDEV1=
      (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=vdcdb01-mip)(PORT=1525))
      (ADDRESS=(PROTOCOL=TCP)(HOST=vdcdb02-mip)(PORT=1525))
      (CONNECT_DATA=(SERVICE_NAME=ofndev.threed.someserversomewhere.com))
      )

      Yields this result:
      Name : OFNDEV1
      SimpleName : OFNDEV1
      Protocol : TCP
      Host : vdcdb02-mip
      Port : 1525
      Service : ofndev.threed.someserversomewhere.com

      Note that the final result contains only the last entry, not both entries. Also, a couple of errors are thrown:

      ConvertFrom-StringData : Data item ‘SERVER’ in line ‘SERVER=dedicated’ is already defined.
      At line:80 char:16
      + $lineBreakup = ConvertFrom-StringData -StringData $line.Replace(“;”,”`n”)
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [ConvertFrom-StringData], PSInvalidOperationException
      + FullyQualifiedErrorId : InvalidOperation,Microsoft.PowerShell.Commands.ConvertFromStringDataCommand

      ConvertFrom-StringData : Data item ‘SERVER’ in line ‘SERVER=dedicated’ is already defined.
      At line:80 char:16
      + $lineBreakup = ConvertFrom-StringData -StringData $line.Replace(“;”,”`n”)
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [ConvertFrom-StringData], PSInvalidOperationException
      + FullyQualifiedErrorId : InvalidOperation,Microsoft.PowerShell.Commands.ConvertFromStringDataCommand

      1. Sorry I did not include the second set of columns. This is the version I have now which seems to generate the right output for the sample you gave me. Perhaps, I can try if you obfuscate sensitive hostnames etc and post your whole tnsnames.ora.

        
        function Get-OraTnsAdminEntries
        {
            param
            (
                [System.IO.FileInfo] $File
            )
            
            begin {}
            
            process
            {
                [object[]] $tnsEntries = @()        
        
                if ($_)
                {
                    $File = [System.IO.FileInfo] $_
                }
                if (!$File)
                {
                    Write-Error "Parameter -File  is required."
                    break
                }
                if (!$File.Exists)
                {
                    Write-Error "'$File.FullName' does not exist."
                    break
                }
                
                [string] $data = gc $File.FullName | ? {!$_.StartsWith('#')}
        
        
        
                #Sample TNS entry
                
                
                #Here is what we have to do to identify individual TNS entries..
                #----------------------------------------------------------------
                #1) Replace all "`n" with ""...so that we flatten out the file
                #2) Replace all " " with ""...so that there are no spaces
                #3) Replace all "`t" with ""...so that there are no tabs
                #4) Replace all ")))(" with "))("...remove false positive ))) as in the case of 8iQA01 for example which has ))) in the middle. for ADDRESS_LIST vs ADDERESS
                #5) Replace all ")))" with ")))`n"...so that we separate out individual entries into their own line
                #6) Replace all "=(" with "=;"...to separate by and identify individual entries
                #7) Replace all "(" with ""...to remove open brackets
                #8) Replace all ")" with ";"...to replace close brackets with ";"
                #9) Replace all ";;" with ";"...to not have multiple ;
                #10) Replace all ";;" with ";" again...to not have multiple ;
                
                $lines = $data.Replace("`n","").Replace(" ","").Replace("`t","").Replace(")))(","))(").Replace(")))",")))`n").Replace("=(","=;").Replace("(","").Replace(")",";").Replace(";;",";").Replace(";;",";").Split("`n")
        
        
                #At this point each line should look like this
                #----------------------------------------------------------------
                #$Service,$Service.WORLD=;DESCRIPTION=;ADDRESS=;PROTOCOL=$Protocol;Host=$Hostname;Port=$Port;CONNECT_DATA=;SERVICE_NAME=$Service;
        
                foreach ($line in $lines)
                {
                    if ($line.Trim().Length -gt 0)
                    {
                        #If there are more than one ADDRESS's, make the second one ADDRESS2
                        #Begin Addition (for SteveA)
                        $re = [regex]'ADDRESS'
                        $line = $re.Replace($line, 'ADDRESS2', 1)
                        $re = [regex]'PROTOCOL'
                        $line = $re.Replace($line, 'PROTOCOL2', 1)
                        $re = [regex]'HOST'
                        $line = $re.Replace($line, 'HOST2', 1)
                        $re = [regex]'PORT'
                        $line = $re.Replace($line, 'PORT2', 1)
                        #End Addition (for SteveA)
        
                        #Replace ";" with "`n" so that each can become a name=value pair in a hash-table
                        $lineBreakup = ConvertFrom-StringData -StringData $line.Replace(";","`n")
        
                        #At this point $linebreakup would look like this
                        #----------------------------------------------------------------
                        
        
                        $entryName = $line.Split("=")[0]       #Everything to the left of the first "=" in "$Service,$Service.WORLD=;DESCRIPTION=;ADDRESS=;PROTOCOL=$Protocol;Host=$Hostname;Port=$Port;CONNECT_DATA=;SERVICE_NAME=$Service;"
        
                        $tnsEntry = New-Object System.Object 
                        $tnsEntry | Add-Member -type NoteProperty -name Name     -value $entryName
                        $tnsEntry | Add-Member -type NoteProperty -name SimpleName -value ($entryName.Split(",")[0].Trim().Split(".")[0].Trim())  #Pick "MyDB" from "MyDB, MyDB.World" or "MyDB.World, MyDB"
                        $tnsEntry | Add-Member -type NoteProperty -name Protocol -value $lineBreakup["PROTOCOL"]
                        $tnsEntry | Add-Member -type NoteProperty -name Host     -value $lineBreakup["Host"]
                        $tnsEntry | Add-Member -type NoteProperty -name Port     -value $lineBreakup["Port"]
        
                        #If there are more than one ADDRESS's, make the second one ADDRESS2
                        #Begin Addition (for SteveA)
                        if ($lineBreakup["HOST2"])
                        {
                            $tnsEntry | Add-Member -type NoteProperty -name Protocol2 -value $lineBreakup["PROTOCOL2"]
                            $tnsEntry | Add-Member -type NoteProperty -name Host2     -value $lineBreakup["Host2"]
                            $tnsEntry | Add-Member -type NoteProperty -name Port2     -value $lineBreakup["Port2"]
                        }
                        else
                        {
                            $tnsEntry | Add-Member -type NoteProperty -name Protocol2 -value $null
                            $tnsEntry | Add-Member -type NoteProperty -name Host2     -value $null
                            $tnsEntry | Add-Member -type NoteProperty -name Port2     -value $null
                        }
                        #End Addition (for SteveA)
        
                        $tnsEntry | Add-Member -type NoteProperty -name Service  -value $(if ($lineBreakup["SERVICE_NAME"] -eq $null) {$lineBreakup["SID"]} else {$lineBreakup["SERVICE_NAME"]})  #One of the two will have the value. Pick the one that does!
        
        
                        
        
                        #Make sure we ignore entries created due to empty lines or mal-formed structure
                        if ($tnsEntry.Service.Trim().Length -gt 0)
                        {
                            $tnsEntries += $tnsEntry
                        }
                        else
                        {
                            #Make sure people notice problem entries!
                            if ($line.Trim().Length -gt 0)
                            {
                                Write-Warning "Ignoring empty/mal-formed entry: [{0}]" -f $line
                            }
                        }
                    }
                }
        
                $tnsEntries
            }
            
            end {}
        }
        
        
  3. This is looking a lot better, but there are a couple of issues still. I’d be happy to send a tnsnames file to you, but I’d rather not post it publicly. Can I email it to you?

  4. Final code for Steve’s conditions:

    #Inspiration source: http://poshcode.org/1602
    #20150820 Jana – Variation of the function Get-TnsAdminEntries since the original noted above
    # does not work well when the “Name” or “Host” has hyphens + “Service_Name” could be aliased as “SID”
    # This version is easier to enhance/maintain since it does not use regular expressions (which most people are not familiar with)

    #Usage:

    
    function Get-OraTnsAdminEntries
    {
        param
        (
            [System.IO.FileInfo] $File
        )
        
        begin {}
        
        process
        {
            [object[]] $tnsEntries = @()        
    
            if ($_)
            {
                $File = [System.IO.FileInfo] $_
            }
            if (!$File)
            {
                Write-Error "Parameter -File  is required."
                break
            }
            if (!$File.Exists)
            {
                Write-Error "'$File.FullName' does not exist."
                break
            }
            
            [string] $data = gc $File.FullName | ? {!$_.StartsWith('#')}
    
    
    
            #Sample TNS entry
            
            
            #Here is what we have to do to identify individual TNS entries..
            #----------------------------------------------------------------
            #1) Replace all "`n" with ""...so that we flatten out the file
            #2) Replace all " " with ""...so that there are no spaces
            #3) Replace all "`t" with ""...so that there are no tabs
            #4) Replace all ")))(" with "))("...remove false positive ))) as in the case of 8iQA01 for example which has ))) in the middle. for ADDRESS_LIST vs ADDERESS
            #5) Replace all ")))" with ")))`n"...so that we separate out individual entries into their own line
            #6) Replace all "=(" with "=;"...to separate by and identify individual entries
            #7) Replace all "(" with ""...to remove open brackets
            #8) Replace all ")" with ";"...to replace close brackets with ";"
            #9) Replace all ";;" with ";"...to not have multiple ;
            #10) Replace all ";;" with ";" again...to not have multiple ;
            
            $lines = $data.Replace("`n","").Replace(" ","").Replace("`t","").Replace(")))(","))(").Replace(")))",")))`n").Replace("=(","=;").Replace("(","").Replace(")",";").Replace(";;",";").Replace(";;",";").Split("`n")
    
    
            #At this point each line should look like this
            #----------------------------------------------------------------
            #$Service,$Service.WORLD=;DESCRIPTION=;ADDRESS=;PROTOCOL=$Protocol;Host=$Hostname;Port=$Port;CONNECT_DATA=;SERVICE_NAME=$Service;
    
            foreach ($line in $lines)
            {
                if ($line.Trim().Length -gt 0)
                {
    
                    #If there are more than one ADDRESS's, make the first one ADDRESS2
                    #Begin Addition (for SteveA)
                    $re = [regex]';ADDRESS'
                    $line = $re.Replace($line, ';ADDRESS2', 1)                
                    $re = [regex]';PROTOCOL'
                    $line = $re.Replace($line, ';PROTOCOL2', 1)
                    $re = [regex]';HOST'
                    $line = $re.Replace($line, ';HOST2', 1)
                    $re = [regex]';PORT'
                    $line = $re.Replace($line, ';PORT2', 1)
                    #End Addition (for SteveA)
    
                    #Replace ";" with "`n" so that each can become a name=value pair in a hash-table
                    $lineBreakup = ConvertFrom-StringData -StringData $line.Replace(";","`n")
    
                    #At this point $linebreakup would look like this
                    #----------------------------------------------------------------
                    
    
                    $entryName = $line.Split("=")[0]       #Everything to the left of the first "=" in "$Service,$Service.WORLD=;DESCRIPTION=;ADDRESS=;PROTOCOL=$Protocol;Host=$Hostname;Port=$Port;CONNECT_DATA=;SERVICE_NAME=$Service;"
    
    
                    $tnsEntry = New-Object System.Object 
                    $tnsEntry | Add-Member -type NoteProperty -name Name     -value $entryName
                    $tnsEntry | Add-Member -type NoteProperty -name SimpleName -value ($entryName.Split(",")[0].Trim().Split(".")[0].Trim())  #Pick "MyDB" from "MyDB, MyDB.World" or "MyDB.World, MyDB"
                    $tnsEntry | Add-Member -type NoteProperty -name Protocol -value $lineBreakup["PROTOCOL2"]
                    $tnsEntry | Add-Member -type NoteProperty -name Host     -value $lineBreakup["Host2"]
                    $tnsEntry | Add-Member -type NoteProperty -name Port     -value $lineBreakup["Port2"]
    
                    #If there are more than one ADDRESS's, make the second one ADDRESS2
                    #Begin Addition (for SteveA)
                    if ($lineBreakup["HOST"])
                    {
                        $tnsEntry | Add-Member -type NoteProperty -name Protocol2 -value $lineBreakup["PROTOCOL"]
                        $tnsEntry | Add-Member -type NoteProperty -name Host2     -value $lineBreakup["Host"]
                        $tnsEntry | Add-Member -type NoteProperty -name Port2     -value $lineBreakup["Port"]
                    }
                    else
                    {
                        $tnsEntry | Add-Member -type NoteProperty -name Protocol2 -value $null
                        $tnsEntry | Add-Member -type NoteProperty -name Host2     -value $null
                        $tnsEntry | Add-Member -type NoteProperty -name Port2     -value $null
                    }
                    #End Addition (for SteveA)
    
                    $tnsEntry | Add-Member -type NoteProperty -name Service  -value $(if ($lineBreakup["SERVICE_NAME"] -eq $null) {$lineBreakup["SID"]} else {$lineBreakup["SERVICE_NAME"]})  #One of the two will have the value. Pick the one that does!
    
    
                    
    
                    #Make sure we ignore entries created due to empty lines or mal-formed structure
                    if ($tnsEntry.Service.Trim().Length -gt 0)
                    {
                        $tnsEntries += $tnsEntry
                    }
                    else
                    {
                        #Make sure people notice problem entries!
                        if ($line.Trim().Length -gt 0)
                        {
                            Write-Warning "Ignoring empty/mal-formed entry: [{0}]" -f $line
                        }
                    }
                }
            }
    
            $tnsEntries
        }
        
        end {}
    }
    
    
  5. Does anyone have the original Add-TnsAdminEntry function? The link to it does not seem to work anymore. Would appreciate getting it.

    Thanks!

    Best

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s