although I am assuming that you had a typo in the output you said you wanted....
The above script produces the output:
Code:
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
while the output you said you wanted was:
Code:
PRI pri 10.0.3.7,1521 10.0.3.17,1521
STDBY stubby 10.0.3.5,1521 10.0.3.15,1521 10.0.3.25.1521
AIXSNAP aixsnap 10.9.0.5,1521
If you can't see how the index() and substr() functions are working to extract the data you want, you can uncomment the commented out print and printf() statements to see values being used.
If someone wants to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk.
Dear Don Cragun
Thank you for your reply, although some difficulties,
I'll go check some command usage, and study your answer,
Thank you for your help.
---------- Post updated at 08:36 PM ---------- Previous update was at 08:25 PM ----------
Dear RudiC
Thank you for your reply,
I understand you provide answers.
I will try this code and study awk command usage.
Thank you.
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
$
$
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)
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)
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)
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)
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)
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)
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)
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)
