Solved

shell script for TNS entries

Posted on 2011-03-01
11
1,397 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 78

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 78

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 78

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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

688 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