Tnsnames.ora and XML Solution
by Seth Miller
I have always found the Oracle connection strings (typically tnsnames.ora) difficult to manage. Even when using LDAP instead of the file, the connection strings still need to be managed even though it only has to be done in one place. After some planning and learning (and relearning) some atrophied perl skills, I created a set of scripts to manage the Oracle connection strings using XML and perl.
If you look at the Oracle connection string, it looks a lot like XML. Instead of tags using the format
I converted the existing tnsnames.ora to XML using Vim’s search and replace and a whole lot of regexp. After converting the file and having a parseable XML file, I realized that the possibilities with this are endless. It doesn’t have to be limited to just the connection strings, a single database entry can contain any amount of information you might want to track with each database. But, we’ll get to more of that later.
Here is what an Example XML entry will look like.
The script requires certain elements to properly function. I will detail them here using XPATH syntax. I have chosen /TNSNAMES as the root but it doesn’t really matter what you use. The required children are as follows:
TNS[@NAME=""] TNS/DESCRIPTION/ADDRESS_LIST/ADDRESS TNS/DESCRIPTION/CONNECT_DATA TNS/DESCRIPTION/CONNECT_DATA/SERVICE_NAMES/SERVICE_NAME[@ID="1"]
There are many more options to add to an Oracle connection string, and there will be additional functionality added in future releases but for now, these additional paths are optional.
The INSTANCES/INSTANCE[@ID=”1″] was added because I am using this script to create multiple entries for the same connection to be used in the Oracle wallet for different users. When I am connecting as the SYS user to a database to change the password, I am specifying that I want to connect to (CONNECT_DATA(INSTANCE=ORCL1)) so that I know which password file is being changed, therefore making it easy to then copy that changed password file to the rest of the nodes in a cluster. The script is shown here and is being hosted on GitHub. The script, the example XML file above and the result file below are all directly linked to GitHub and will change as updates are made.
Details on how to use the script can be found in the script and listed using the help flag for brief information and the man flag for the full documentation.
This shows what the XML example file above looks like after being parsed using no script options.
I will update this post with usage examples and details of additional functionality as it is added.
I have also written another script that parses the same XML format but the output produces a connections file that can be loaded into Toad. I have my install of Toad set up to use an Oracle wallet that I have populated with all of the database passwords that I use. I use the parser script to produce both the tnsnames.ora that I am using for the connection strings as well as the connections text file that I loaded into Toad to match. I call this script ToadBuilder and I will load it into GitHub as well. I haven’t decided if I will combine the two scripts yet or not since there is a lot of repeated code but I don’t want the script options to become too complicated.
Please feel free to post feedback or contribute to the scripts. I am an amateur with perl and improvements are very much welcome.