[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

shell script for TNS entries

Posted on 2011-03-01
11
Medium Priority
?
1,463 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 80

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 80

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 80

Accepted Solution

by:
arnold earned 2000 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 71

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 77

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 71

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

656 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