A TNS name is a logical name that the users and applications use for connecting to the database. The name could be an alias to a service name, which is defined on a pluggable database, which is plugged into a container database. As a DBA, when you manage many databases, it could be a challenge to remember which container database and which pluggable database a TNS name is pointing to.
This script will help you to lookup a TNS name, and return the details of the database that it is pointing to: pluggable database name, container database name, service name, and host name.
pre-requsites:
You need to have a oracle client, and you to set the same password for user dbsnmp in all databases.
#!/bin/bash
function lookup
{
if ((${#ORACLE_HOME} < 1));
then
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
fi
sql="select '|' || sys_context('USERENV','DB_UNIQUE_NAME')|| '|' ||"
sql="${sql} sys_context('USERENV','SERVICE_NAME')|| '|' ||"
sql="${sql} regexp_substr(sys.database_name, '^[[:alnum:]]+') || '|' ||"
sql="${sql} regexp_substr(host_name, '^[[:alnum:]]+') info from v\$instance;"
result=$(echo ${sql} | sqlplus -S dbsnmp/passwd@${db_name})
if [[ ${result} == *"Invalid option"* ]];
then
echo "${db_name} cannot be reached, name is invalid or service is down"
else
database=$(echo ${result} | cut -d"|" -f2)
service=$(echo ${result} | cut -d"|" -f3)
pdb=$(echo ${result} | cut -d"|" -f4)
host=$(echo ${result} | cut -d"|" -f5)
if [ ${database} != ${pdb} ]; then
echo "pluggable: ${pdb}"
fi
echo "database : ${database}"
echo "service : ${service}"
echo "host_name: ${host}"
fi
}
if [ $# -eq 1 ];
then
db_name=$1
lookup
else
echo "usage: $0 name"
fi
Here is an exmaple,
$ tnslookup sales_dev pluggable: SALES01 database : ORCL2 service : SDEV.DOMAIN.COM host_name: server01
Notes 
