Solved

shell script for TNS entries

Posted on 2011-03-01
11
1,330 Views
Last Modified: 2012-05-11
Hello experts,

I want a shell script which can update the TNS entries on the server by just passing the parameter like host name,port, sid or servicename.

So that I don't have to do it manually every time logging into solaris system where oracle 11g will be already installed.

0
Comment
Question by:pinkuray
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 35014114
I think there is a way using sqlplus in a shell script using AWK/CUT to get the data out of the entry and then insert it into an Oracle database.
See below as an example for the oracle select which can be replaced with the insert part.

http://en.allexperts.com/q/Oracle-1451/inserting-data-oracle-shell.htm
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35014340
I got this link to verify if the TNS entry are valid or not.:
http://www.orafaq.com/scripts/unix/cktnsnms.txt

But I want to add a TNS into the Oracle TNS entries.
0
 
LVL 77

Expert Comment

by:arnold
ID: 35014621
Oh, misread/misunderstood what you are trying to do.
http://www.orafaq.com/wiki/Tnsnames.ora

Does it matter which scripting/interpreted language is  used? is perl an option?

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:pinkuray
ID: 35014656
I was looking in ksh as few other scripts are also running on the same server.
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35014660
Where If I want to add a new  entry I dont have to do it manually just running a script by passing parameter to it.

0
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 35014783
Here is an example where the parameters are eighter passed on the command line
script <address> <sid> <hostname> <port>
or the user will be prompted for information.

One way you can convey these additions is through the use of an NFS share or an scp of a file containting the four parameters to the server.
One thing is to add this functionality to workstations such that you can define the connections for user's use. On the server, I'd be more cautions.

The below is merely an example and lacks any verification to see whether the parameters being passed already exist.

Perl in my opinion is simpler since you can parse the file get the pertinent info and check whether what you are adding is already in and at least warn of a duplicate in case you provide a new name for an existing SID, hostname, port.
#!/bin/ksh

if ( [ -z "$1" ] ) ; then
    echo -n "Please provide The addressname for configuring a connection: "
    read address
    while ( [ -z "$address" ] ) do
           read address
    done
else
address=$1
fi
if ( [ -z "$2" ] ) ; then
    echo -n "Please provide The SID for configuring a connection: "
    read sid
    while ( [ -z "$sid" ] ) do
           read sid
    done
else
sid=$2
fi
if ( [ -z "$3" ] ) ; then
    echo -n "Please provide The IP for configuring a connection: "
    read ip
    while ( [ -z "$ip" ] ) do
           read ip
    done
else
ip=$3
fi
if ( [ -z "$4" ] ) ; then
    echo -n "Please provide The port for configuring a connection: "
    read port
    while ( [ -z "$port" ] ) do
           read port
    done
else
port=$4
fi
echo "$address =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = $hostname)(Port = $port))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = $sid)
 )
)"

Open in new window

0
 
LVL 69

Expert Comment

by:Qlemo
ID: 35015335
You are aware you usually do not need the full-size TNS connect descriptor syntax starting with 10g? The EZConnect String is easier to read and much shorter, and would not even require a TNSNames entry. The TNS string (in above echo) would than look like
   $address = $hostname:$port/$sid

BTW, SID is not the same as service name - the latter may contain suffixes (e.g. computername, domain name, global suffix ...). The EZConnect syntax needs the (full) service name.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35016823
>>manually every time logging into solaris system

I don't understand what you mean my 'every time you log in'.  typically you will have a 'master' tnsnames file and if you need to change it you just recopy it where you need it.

>>EZConnect String is easier to read and much shorter

The issue in using EZConnect is when the DBA/Sysadmin decides to change something and the EZConnect string is hard-coded everywhere.

If you stick the the TNS or LDAP method, things can easily and seamlessly change.
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35016996
@slightwv:

My question was
"I want a shell script which can update the TNS entries on the server by just passing the parameter like host name,port, sid or servicename."

Why I said service or SID boz when a script should be run it should prompt for SID to be passed or Service name , so the conclusion is one should be passed without null.

So just by running the script with few param you should be able to load the TNS entries to solaris system.

It would be great if you could clearly provide the solution rather then thinking something else.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 35017971
slightwv,

As stated, EZConnect works in TNSNames, too. I agree to the "name it and hide the details" approach using TNSNames, but most of the Oracle DBAs I met do not take advantage of that. They just create a "service name" type entry with the complete descriptor, which is a waste IMHO.

IF I use the long descriptor syntax, I omit the (ADDRESS_LIST = ) part, and start with (ADDRESS = ...) immediately. ADDRESS_LIST ist only useful for failover.
Further I do not break the entry into several lines. Line breaks are distorting the layout, and (much more important) making scripted modification more difficult.
0
 
LVL 4

Author Closing Comment

by:pinkuray
ID: 35024646
This solved my issue.... Thanks a lot.
as validation was not required on service and SID boz only SID will be present.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question