Oracle tnsnames.ora Distribution Management System!

What?

Does tnsnames.ora file need a Distribution Management System?

In our case, the answer is yes. It is a headache to implement our client requirements without some sort of automation.

What is tnsnames.ora file?

tnsnames.ora is a text file that resides on the ORACLE_HOME/network/admin and contains some simple information about our databases connection information, it is like:

What is problem that we need to fix?

Our client has two production Exadata machines (total database nodes =5= two separate clusters), plus a normal linux server for DR, so we have total of 6 database nodes.

Following is the high level architecture:

There are many databases consolidated in these servers (about 40 databases), and these databases have many database links referring to other non-Exadata databases.

In each database server, our client has 3 database homes for: 12c,11.2.0.4 & 11.2.0.3.

Each database home contains its own tnsnames.ora file related to the databases resides on this home. So, we have 18 tnsnames.ora file across all the database servers/homes.

Due to the huge number of connection strings (about 150 tns entries and keep increasing each day with consolidation,the tnsnames.ora file is currently about 1500 line) and the difficulty of mapping each TNS entry to which database/home/node/cluster, so, our client decided to create only one master tnsnames.ora file that contains all connection strings across all database nodes/database homes and maintain only this file.

Now, the main points we want to address are:

• How will we create the consolidated master tnsnames.ora?
• How will we validate the consolidated tnsnames.ora has no typo errors?
• Where we will keep this master tnsnames.ora file?
• If there is a change in the master tnsnames.ora file (e.g adding/deleting/modifying new entries), then how will we distribute the new file across all nodes/homes without much manual intervention?

The solution that I will present below is my solution with my colleague “Moustafa Hassan”, but I’m quite sure that there is another better solutions/options.

1-     Creating the Consolidated Master tnsnames.ora file

Actually, this step is a little bit manual. We simply copied the tnsnames .ora file for each database home from all nodes/homes into one big master file, then manually we started to search about any repetition and remove it.

This is simply done using some powerful editors like “Notepad ++ or sublime or even vi”.

Please note that in the current situation, each database home has its own tnsnames.ora file which may be different or partially similar to that of other homes.

Now, we have one consolidated file contains all TNS entries from all local tnsnames.ora files at all nodes/homes.

2-     Validating the Consolidated Master tnsnames.ora file

Because we were afraid of having any typo errors while preparing the master tnsnames.ora file, so, we thought of a way to validate this file. The best option is to use the “tnsping” utility to ping each entry in this file and ensure a successful ping. Doing this manually for about 150 entry takes time, so, we wrote the following shell script command to do it for us:

for t in `egrep ‘^[a-zA-Z].*=’ Master_tnsnames_Exadata_v1.ora| cut -d”=” -f1`

echo “=========”

do echo $t;

echo “=========”

tnsping $t

done | tee tnsping.out

Simply this shell command/script is parsing the tnsnames.ora file for lines that are not starting with space (i.e lines that contain the connect string names).

Let us take the following tnsnames.ora as an example:

So, the first line in our script will return the names of the connection strings, like:

egrep ‘^[a-zA-Z].*=’ Master_tnsnames_Exadata_v1.ora| cut -d”=” -f1

Then it will pass this set of tns entries to the “tnsping” utility and then spool the output to a file, while seeing it on the screen also.

This will produce the following output:

=========

XPS

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS)))

OK (20 msec)

=========

XPS10

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS10)))

OK (0 msec)

=========

XPS20

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS20)))

OK (0 msec)

=========

RAC

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prod-cluster-scan.rac.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS.rac.com)))

TNS-12535: TNS:operation timed out

=========

mcwb

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:58:48

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.97.58.45)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mcwb)))

TNS-12543: TNS:destination host unreachable

=========

SPROD

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:58:48

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = insightproddb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = SPROD)))

TNS-12545: Connect failed because target host or object does not exist

=========

PROD.WORLD

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:58:52

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = afattah-lap)(PORT = 1524))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))

TNS-12545: Connect failed because target host or object does not exist

Doing that, it will be very simple to catch any wrong tns entries and fix them before going forward. Also, this command enable you to find out any outdated entries that reference severs or databases that are no longer exist.

3-     Location of the Consolidated Master tnsnames.ora file

As we have three different environment. Two Exaxdata machines and another standalone DR server, the question was where we should locate our master file?.

Some options:

• For each machine put the master tnsnames.ora file in any location and then adjust the TNS_ADMIN for all oracle homes to point to that location.
• Another option which is similar to the above one, but with using a symbolic link under the oracle home to point to the master file.
• One option was to create a NFS mount point that is seen from all servers and putting the master file there and adjusting the TNS_ADMIN also.
• The last option that I chose was to keep the master file in one server offline, and when you need to update it, just update it in that server then run a script to distribute it to all servers/homes (you need to validate it before distribution as we did above). Also, the script will backup the existent local tnsnames.ora files under each ORACL_HOME before replacing them.

4-     Maintaining the Consolidated Master tnsnames.ora file

Now, assume that we have the master tnsnames.ora file at server1 (db_node1) with name “Master_tnsnames_Exadata_v1.ora” and we want to modify it and add new entries, the procedure will be as follows:
1- Copy the Master_tnsnames_Exadata_v1.ora to Master_tnsnames_Exadata_v2.ora
2- Edit the new file “Master_tnsnames_Exadata_v2.ora”
3- Add the new entries
4- Run the following script distribute_tnsnames.sh to complete the mission:

./distribute_tnsnames.sh Master_tnsnames_Exadata_v2.ora

The above script will deploy the new master file across all servers and database homes.

Note that you will need to setup a trust ssh link between the server that contains the master tnsnames.ora file “db_node1) and all the other servers, so, you will not be prompted to enter the password for each server.

This is simply done by copying the entry in the “id_rsa.pub” file in the master server, to the “authorized_keys” file in all other servers. Please note that these files exist under /home/oracle/.ssh directory.

5-     Distributing the Consolidated Master tnsnames.ora file

The following shell script “distribute_tnsnames.sh” will take care of backing up the current local tnsnames.ora file files before replacing them with the new master tnsnames.ora file.

This script accepts only one parameter which is the name of the new master file.

Usage should be as follows:

./distribute_tnsnames.sh Master_tnsnames_Exadata_v2.ora

The source code of this script is listed below:

#!/bin/bash

script_dir=`dirname $0`

script_name=`basename $0`

if [ $# -ne 1 ]; then

echo “Usage: $script_name {master_tns_file_nama}”

exit 1

fi

master_file=$1

if [ ! -f $master_file ]; then

echo “Error: master file: $script_dir/$master_file not found”

exit 2

fi

for node in `cat $script_dir/db_nodes | egrep -v “^#”`; do

if [ $node != “db_node1” ]; then

scp -p /home/oracle/master_tns/$master_file $node:/home/oracle/master_tns/

fi

done

OH3=”/u01/app/oracle/product/11.2.0.3/dbhome_1″

OH4=”/u01/app/oracle/product/11.2.0.4/dbhome_1″

OH12=”/u01/app/oracle/product/12.1.0/dbhome_1″

TNS3=$OH3″/network/admin”

TNS4=$OH4″/network/admin”

TNS12=$OH12″/network/admin”

# Backup current tnsnames.ora

# ===========================

dcli -g $script_dir/db_nodes  -l oracle “cp -p $TNS3/tnsnames.ora $TNS3/tnsnames.ora_bkup_`date +%d%b%y_%H%M`”

dcli -g $script_dir/db_nodes  -l oracle “cp -p $TNS4/tnsnames.ora $TNS4/tnsnames.ora_bkup_`date +%d%b%y_%H%M`”

dcli -g $script_dir/db_nodes  -l oracle “cp -p $TNS12/tnsnames.ora $TNS12/tnsnames.ora_bkup_`date +%d%b%y_%H%M`”

# Distribute the new tnsnames.ora

# ===============================

dcli -g $script_dir/db_nodes  -l oracle “cp -p /home/oracle/master_tns/$master_file $TNS3/tnsnames.ora”

dcli -g $script_dir/db_nodes  -l oracle “cp -p /home/oracle/master_tns/$master_file $TNS4/tnsnames.ora”

dcli -g $script_dir/db_nodes  -l oracle “cp -p /home/oracle/master_tns/$master_file $TNS12/tnsnames.ora”

We are using the “scp” command to copy the new master tnsnames.ora file from the first node to a specific directory “/home/oracle/master_tns/” across all servers.

The dcli is a powerful utility available with Exadata, that enables you to run the same command across all nodes (or subset of nodes).

We are simply using the “dcli” utility to do two things:

• Backup the current local tnsnames.ora file under each oracle home at all servers.
• Replace the current local tnsnames.ora file under each oracle home at all servers with the new master consolidated tnsnames.ora file.

The db_nodes file is a simple test file that contains the names of the database servers, it is like:

Also, if you want to partially distribute the new master file to subset of the above nodes, you can simply comment the un-wanted servers, like:

Caution:

Don’t use this script before validating the new master tnsnames.ora!

Use this procedure at your own risk!.

Thanks
Ahmed