12-15-2015
If Perl is an option in your system, then here's a parser:
Code :
$
$ cat tns.txt
PRI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.7)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.17)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pri)
(INSTANCE_NAME = pri)
)
)
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.5)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.15)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.25)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdby)
(INSTANCE_NAME = stdby)
)
)
AIXSNAP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.0.5)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = aixsnap)
(INSTANCE_NAME = aixsnap)
)
)
$
$ cat -n parse_tns.pl
1 #!/usr/bin/perl -w
2 # ==============================================================================
3 # Name : parse_tns.pl
4 # Desc : Perl program to parse Oracle's tnsnames.ora file. The tnsnames.ora file
5 # can have an net service entry per line. This script should work with
6 # such a file. The file must be well-formed i.e it should have equal
7 # numbers of open and close parentheses, host and port values etc.
8 # Usage: perl parse_tns.pl <tnsnames_file>
9 # E.g. : perl parse_tns.pl tns.txt
10 # : perl parse_tns.pl tns_1.txt
11 # ==============================================================================
12 use strict;
13 my $file = $ARGV[0]; # Accept the tnsnames file from the command-line
14 my $open_parens = 0;
15 my $close_parens = 0;
16 my ($name, @host, @port, $service_name);
17 open(FH, "<", $file) or die "Can't open $file: $!";
18 while (<FH>) {
19 chomp;
20 # We store the running counts of open-parentheses and close-parentheses for
21 # each net service entry. When the difference is zero, the entry has ended.
22 # Match the regex "open-parenthesis" in the current line and assign it to
23 # an anonymous array. Since the LHS is a scalar, the array is evaluated in
24 # scalar context and its size (no. of elements) is added to the running total.
25 $open_parens += () = $_ =~ /\(/g;
26 $close_parens += () = $_ =~ /\)/g;
27
28 # If the net service name is present, extract and assign it to $name
29 if (/^\s*([^)(]+?)\s*=/) { $name = $1 }
30
31 # Extract all host names or IP addresses and push into array @host
32 while (/HOST\s*=\s*(.*?)\)/g) { push(@host, $1) }
33
34 # Extract all port value and push into array @port
35 while (/PORT\s*=\s*(.*?)\)/g) { push(@port, $1) }
36
37 # If service name is present, extract and assign it to $service_name
38 if (/SERVICE_NAME\s*=\s*(.*?)\)/) { $service_name = $1 }
39
40 # If the difference between open and close parentheses counts is zero,
41 # print the information and flush the variables. The check for open_parens
42 # greater than 0 is to avoid printing on line 1 and when a new entry begins.
43 if (($open_parens - $close_parens) == 0 and $open_parens > 0) {
44 print $name, "\t", $service_name;
45 foreach my $i (0..$#host) { print "\t".$host[$i].",".$port[$i] }
46 print "\n";
47 ($open_parens, $close_parens) = (0, 0);
48 ($name, $service_name) = ("", "");
49 @host = ();
50 @port = ();
51 }
52 }
53 close(FH) or die "Can't close $file: $!";
54
$
$ perl parse_tns.pl tns.txt
PRI pri 10.0.3.7,1521 10.0.3.17,1521
STDBY stdby 10.0.3.5,1521 10.0.3.15,1521 10.0.3.25,1521
AIXSNAP aixsnap 10.9.0.5,1521
$
$
$ # tnsnames file with one entry per line
$ cat tns_1.txt
PRI = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.7)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.17)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pri) (INSTANCE_NAME = pri)))
STDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.5)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.15)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.25)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stdby) (INSTANCE_NAME = stdby)))
AIXSNAP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.0.5)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = aixsnap) (INSTANCE_NAME = aixsnap)))
$
$
$ perl parse_tns.pl tns_1.txt
PRI pri 10.0.3.7,1521 10.0.3.17,1521
STDBY stdby 10.0.3.5,1521 10.0.3.15,1521 10.0.3.25,1521
AIXSNAP aixsnap 10.9.0.5,1521
$
$
If you're comfortable with references and map in Perl, then here's another:
Code :
$
$ # Perl one-liner to parse tns.txt
$ perl -lne 'if (/^(\S+)\s*=/) {$name = $1; $x{$name} = [$name]}
while (/HOST\s*=\s*(.*?)\)/g){ push(@{$x{$name}->[1]}, $1) }
while (/PORT\s*=\s*(.*?)\)/g){ push(@{$x{$name}->[2]}, $1) }
if (/SERVICE_NAME\s*=\s*(.*?)\)/){ $x{$name}->[0] = $1 }
END {while(($k, $v) = each %x){ print join("\t", ($k, $v->[0], map {$v->[1]->[$_].",".$v->[2]->[$_]}(0..$#{$v->[1]}))) }}
' tns.txt
AIXSNAP aixsnap 10.9.0.5,1521
STDBY stdby 10.0.3.5,1521 10.0.3.15,1521 10.0.3.25,1521
PRI pri 10.0.3.7,1521 10.0.3.17,1521
$
$
$ # The same Perl one-liner to parse tns_1.txt, which has one entry per line
$ perl -lne 'if (/^(\S+)\s*=/) {$name = $1; $x{$name} = [$name]}
while (/HOST\s*=\s*(.*?)\)/g){ push(@{$x{$name}->[1]}, $1) }
while (/PORT\s*=\s*(.*?)\)/g){ push(@{$x{$name}->[2]}, $1) }
if (/SERVICE_NAME\s*=\s*(.*?)\)/){ $x{$name}->[0] = $1 }
END {while(($k, $v) = each %x){ print join("\t", ($k, $v->[0], map {$v->[1]->[$_].",".$v->[2]->[$_]}(0..$#{$v->[1]}))) }}
' tns_1.txt
AIXSNAP aixsnap 10.9.0.5,1521
STDBY stdby 10.0.3.5,1521 10.0.3.15,1521 10.0.3.25,1521
PRI pri 10.0.3.7,1521 10.0.3.17,1521
$
$
10 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
hi
i would like to know the path of init.ora in AIX Server
i tried with find command but i could not find it.
can somebody help me with that. (8 Replies)
Discussion started by: matrixmadhan
8 Replies
2. Solaris
what is the function of the init.ora file and suppose by mistake it gets deleted , what will be the consequences on the server (1 Reply)
Discussion started by: asalman.qazi
1 Replies
3. Solaris
Hi all,
Am intermittently getting the following errors on one of my databases.
Errors in file /oracle/HRD/saptrace/background/hrd_psp0_13943.trc:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302:... (1 Reply)
Discussion started by: newbie_01
1 Replies
4. UNIX for Advanced & Expert Users
Hi - I am trying to grep all "ORA" errors in a log files.I have to grep all ORA errors except one error for example ORA-01653.How can exclude that error in "grep" command?
In following "grep" command I want to exclude "ORA-01653" error
grep -i ORA alert.log >>/tmp/ora_errors.txt
... (7 Replies)
Discussion started by: Mansoor8810
7 Replies
5. HP-UX
Dear All,
i am not able to start the 9i oracle database because of the following problems.
I log in into unix
and then into sqlplus
export oracle_sid=SATEST
startup nomount
i am getting the following errors
ORA-27300: OS system dependent operation:semget failed with... (3 Replies)
Discussion started by: alokpattar
3 Replies
6. Shell Programming and Scripting
Hi all- i am having some hiccups while writing a korn shell to edit init.ora file for automating latest PSU patchset.(Oracle)
I am trying to edit(add) the ._fix_control and .event with certain parameters.
if anyone has any idea that will be helpful.
thanks. (1 Reply)
Discussion started by: sub
1 Replies
7. Shell Programming and Scripting
Hi,
I would like to modify, in script schell, the line right above (DESCRIPTION and check three cases :
if line contain ".world" then line=line-".world" concat "," concat line
if line dont contain ".world" then line=line concat "," concat line concat".world"
else line=line
Keep in... (10 Replies)
Discussion started by: elcaro
10 Replies
8. Shell Programming and Scripting
I want to check for "errors" or "ORA-" in Y.if there is an error then exit
Y=`sqlplus -s user/passwd<< EOF
exec test_Proc;
exit;
EOF`
if ; then
exit 1
fi
but this doesnt work (6 Replies)
Discussion started by: haadiya
6 Replies
9. Shell Programming and Scripting
Anymore have any code to easily parse the listener.ora to update the ORACLE_HOME for a specific sid?
thanks. (1 Reply)
Discussion started by: nugent
1 Replies
10. Red Hat
Hi,
User claim that job is running slow from their end.
I DBA found in database the below errors in alert log file.
ORA-27603: Cell storage I/O error, I/O failed on disk o/192.168.10.3/RECO_DM01_CD_01_drm01 at offset 13335789568 for data length 1048576
ORA-27626: Exadata error: 2201 (IO... (2 Replies)
Discussion started by: Maddy123
2 Replies