?
Solved

shell script for TNS entries

Posted on 2011-03-01
11
Medium Priority
?
1,424 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 79

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 79

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 79

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 70

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 70

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
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
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

765 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