Solved

shell script for TNS entries

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

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 76

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

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 68

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 68

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now