Hi,
I need hostname from below entry.How do I write a script on this?tnsping is utility in oracle -if I do tnsping ora11gb then it would give one output and I want to extract hostname from this output which come after "HOST="
oracle@brookford:~> tnsping ora11gb|grep -i HOST
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bovina.int.westgroup.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora11gb)))
Best regards,
Vishal
Hello admin_db,
Could you please try following and let me know if this helps you.
awk -F"[)(]" '{sub(/.*=/,X,$6);print $6}' Input_file
Output will be as follows.
bovina.int.westgroup.com
EDIT: Also adding a sed solution on same.
sed 's/\(.*HOST=\)\([^)]*\)\(.*\)/\2/g' Input_file
Thanks,
R. Singh
How about
tnsping ora11gb | awk 'match ($0, /HOST=[^)]*/) {print substr ($0, RSTART+5, RLENGTH-5)}'
bovina.int.westgroup.com
Small adaption to RavinderSingh13's sed proposal:
tnsping ora11gb | sed -n 's/\(.*HOST=\)\([^)]*\)\(.*\)/\2/p'
Thanks RudiC and Ravinder ! Both script are working fine.
Now,I have task as follows :
I get standby tnsentry from primary database like I get destination from below query:
SQL> select DESTINATION from v$archive_dest where target='REMOTE';
DESTINATION
------------------------------
ora11gb
Then I would extract hostname from tnsping and will transfer passwordfile to that host from primary.
Best regards,
Vishal
Please rephrase.
Please allow me to rephrase here .
We have a task of password change activity for databases and servers every quarter.
Now, in case I need to change db sys user password at primary I have to transfer password file to the standby database server.For this I should know after logging to primary that what is the standby site of this primary where I need to scp the file.
Now I am on primary site and my target is to copy password file to standby via script .First I will look at below query:
SQL> select DESTINATION from v$archive_dest where target='REMOTE';
DESTINATION
------------------------------
ora11gb
Now,I know tnsentry of standby .I will grep hostname from tnsentry and will scp password file to standby.So, this all I am planning to do in a script.
1. login to server ( will login to server from a group of servers via script)
2.set the environment of database one by one in case of multiple databases
3.login to database
4.change sys password
5.Look if the primary has a standby and grep hostname from tnsentry of standby
6. scp password file to standby
Best regards,
Vishal
---------- Post updated at 06:30 AM ---------- Previous update was at 06:00 AM ----------
Hi,
For a start I have written below code but giving error and one more query what's difference if I write
x= echo $i|cut -f1 -d':'
or
x=echo $i|cut -f1 -d':'
(removed space after "=" between x and echo)
oracle@brookford:~> cat test_passwordcopy.sh
for i in `cat /etc/oratab|grep -v '^#'`
do
x= echo $i|cut -f1 -d':'
export ORACLE_SID=$x
echo $x
y= echo $i|cut -f2 -d':'
export ORACLE_HOME=$y
echo $y;
sqlplus / as sysdba<<EOF
select DESTINATION into $z from v\$archive_dest where target='REMOTE';
EOF
echo $z;
done
oracle@brookford:~> ./test_passwordcopy.sh
./test_passwordcopy.sh: line 13: warning: here-document at line 9 delimited by end-of-file (wanted `EOF')
./test_passwordcopy.sh: line 14: syntax error: unexpected end of file
oracle@brookford:~>
Best regards,
Vishal
Hi,
Not tested your complete code,on paper I can see below are wrong.
x= echo $i|cut -f1 -d':'
y= echo $i|cut -f2 -d':'
instead it should be
x=`echo $i|cut -f1 -d':'`
y=`echo $i|cut -f2 -d':'`
Hi,
This is not working as expected.I am looking for hostname from tnsping output.
c456veq {/home/oracle}: tnsping ora426b |sed -n 's/\(.*HOST=\)\([^)]*\)\(.*\)/\2/p'
c456veq {/home/oracle}: tnsping ora426b
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-MAR-2016 11:13:05
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.4/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = c299njy.int.thomsonreuters.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora426b.int.thomsonreuters.com)))
OK (0 msec)
Best regards,
Vishal
The = is surrounded by spaces that you need to capture
sed -n 's/\(.*HOST *= *\)\([^)]*\)\(.*\)/\2/p'
You can omit the non-needed brackets
sed -n 's/.*HOST *= *\([^)]*\).*/\1/p'
Thanks!
Since this is manual entry in tnsnames.ora file so somewhere this would be like
HOST=
HOST =
host =
host=
Can we get all these possibilities covered in the script ?
Best regards,
Vishal
A straight-forward conversion of MadeInGermany's script to use case insensitive matches would be:
sed -n 's/.*[Hh][Oo][Ss][Tt] *= *\([^)]*\).*/\1/p'
Some versions of sed have an option to perform case insensitive matches, but the above should work with any version of sed .
Thanks Sir!
Best regards,
Vishal