Shell Programming and Scripting

Hi All,
Solaris 10 o/s
With your help I developed the following script.

Code:

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec   egrep –c  'NS Primary Error' '{}' '+'

which returns the counts I needed nelow:

Code:

/oracle/diag/rdbms/musidp/musidp/trace/abcdef_d001_21751.trc:15
/oracle/diag/rdbms/musidp/musidp/trace/ abcdef _d000_21750.trc:20
/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d001_22002.trc:1524
/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d000_22001.trc:1291

There is one additional requirement and those counts can only be related to today’s date. Right now those counts include several dates in each trace file. I have come up with the following but it returns no data. I know for a fact that when I inspected the trace files I see that date 2015-01-22 in the trace file.

Code:

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec egrep  'NS Primary Error' '{}' '+' | awk '/2015-01-22/ {print}'

Is there some other way to write this
Any assistance would be appreciated.
Thanks

try this:

Code:

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec awk '/2015-01-22/ && /NS Primary Error/' '{}' '+'

Here is a solution that displays the count of lines containing "NS Primary Error" within all files containing "2015-01-22":

Code:

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec awk '
    function pa() { if(FN) print FN":"CNT }
    FNR==1{pa(); FN=CNT=x }
    /2015-01-22/ {FN=FILENAME}
    /NS Primary Error/{CNT++}
    END{pa()}' '{}' '+'

Morning,

Thanks for this. I will run it and get back to you on the results today.

regards
al

---------- Post updated at 07:11 AM ---------- Previous update was at 06:57 AM ----------

Morning Chubler,

I ran your script and it errored out. See below.

Code:

-trace> find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec awk '
>     function pa() { if(FN) print FN":"CNT }
>     FNR==1{pa(); FN=CNT=x }
>     /2015-01-22/ {FN=FILENAME}
>     /NS Primary Error/{CNT++}
>     END{pa()}' '{}' '+'
awk: syntax error near line 2
awk: bailing out near line 2

Here is some output from a trace file to help better understand what is needed. Each block has a different time stamp and needs to be included in the count. Notice they are NOT on the same line.

Code:

*** 2015-01-03 15:00:18.382
error encountered when answering new connection:
  NS Primary Error: TNS-12560: TNS:protocol adapter error
  NT Generic Error: TNS-00530: Protocol adapter error
  Solaris Error: 130: Software caused connection abort
 
*** 2015-01-05 10:23:09.180
unexpected error 12560 for connection:
  NS Primary Error: TNS-12535: TNS:operation timed out
  NS Secondary Error: TNS-12560: TNS:protocol adapter error
  NT Generic Error: TNS-00505: Operation timed out

Thanks for looking at this for me.

regards

al

Sorry about the delay getting back to you on this. Real world commitments and a Public holiday are to blame.

On Solaris try /usr/xpg4/bin/awk instead, which is POSIX awk (or nawk if that is not available).

Your demo input is interesting and raises a couple of further questions:

1) should the date "2015-02-22" and "NS Primary Error" both occur ANYWHERE in the file or within the same error block?

2) Is the format of these error blocks consistent i.e starting with "***" and a blank line between them?

Hi

No rush we are all busy.

I wrote up a detail for ease of understanding. Sorry if it is long. See below:

My operating system is:

OPERATING SYSTEM:
uname -a
SunOS snslcsunu04 5.10 Generic_150400-13 sun4u sparc SUNW,SPARC-Enterprise

DESCRIPTION OF PROBLEM:

We have 5 servers the each have over a hundred oracle databases. The issue isn’t with the databases it is with the servers. We are getting a lot of dropped connections on the server but don’t know which databases are refusing connections. I need to design a metric that can track those databases via numbers as to which one is getting worse or better.

We need to analyze the trace files every day on the “TNS-12535: TNSperation timed out” for example for all the databases in the
/oracle/diag/rdbms/*/*/trace directory. Where the * is the name of the database twice for clarification.

Here is MY THINKING and what I got so far.

find /oracle/diag/rdbms/*/*/trace –type f -name '*d00*.trc' -mtime 0 -exec egrep -c 'TNS-12535: TNSperation timed out' '{}' '+'
OUTPUT:
/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d001_21751.trc:11
/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d000_21750.trc:27
/oracle/diag/rdbms/ghijkl/ghijkl/trace/ghijkl_d001_22002.trc:61
/oracle/diag/rdbms/ghijkl/ghijkl/trace/ghijkl_d000_22001.trc:57

GOAL: My final OUTPUT will be the same as above but with lesser numbers and/or lesser lines.

Now I need to go through these 4 above looking for the data in this case ‘2015-01-26’. First though I need to remove the excess information on the string the colon and the number . I do that by using awk. Here is my syntax:

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0 -exec egrep -c 'TNS-12535: TNSperation timed out' '{}' '+' | awk -F: '{print $1}'

OUTPUT: Notice there are no trailing : or numbers.

/oracle/diag/rdbms/musidp/musidp/trace/musidp_d001_21751.trc
/oracle/diag/rdbms/musidp/musidp/trace/musidp_d000_21750.trc
/oracle/diag/rdbms/musiop/musiop/trace/musiop_d001_22002.trc
/oracle/diag/rdbms/musiop/musiop/trace/musiop_d000_22001.trc

You would think that the following addition to the syntax would work
| egrep –c ‘{}’ ‘+’ would work returning some or all of the same output files as before but fewer numbers for example.

These numbers are what I am looking for. These represent all trace files for all databases for only today that had disconnects with different timestamps during that day.

Ex.

/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d001_21751.trc:6
/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d000_21750.trc:14
/oracle/diag/rdbms/ghijkl/ghijkl/trace/ghijkl_d001_22002.trc:25
/oracle/diag/rdbms/ghijkl/ghijkl/trace/ghijkl_d000_22001.trc:31

But it doesn’t. It produces the following:

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0 -exec egrep 'TNS-12535: TNSperation timed out' '{}' '+' | awk -F:| egrep -c '2015-01-26'

OUTPUT:
awk: syntax error near line 1
awk: bailing out near line 1
0

Can someone please suggest what I is written wrong in the syntax above or maybe an entirely another way of accomplishing this project?

It would be much appreciated.

Thanks in advance.

Try this:

Code:

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec \
/usr/xpg4/bin/awk  '/NS Primary/ && /2015-01-03/ {F++} END{ if(F) print FILENAME":"F}' RS="" '{}' ';'

Replace /usr/xpg4/bin/awk marked in red above with, nawk if you get /usr/xpg4/bin/awk is not found errors.