Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help searching for dates - Oracle ALERT log

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help searching for dates - Oracle ALERT log
# 1  
Old 07-15-2016
Help searching for dates - Oracle ALERT log
Hi,

I am searching for some specific string in an Oracle DB alert log and then possibly print the latest date string that I can find that the error happen.

I can't work out how to search for date strings more so searching in some specific direction, i.e backward or forward.

At the moment, I am cheating in some and instead extracting some lines from the log adding and subtracting x-number of lines. At the moment I am adding and subtracting 3 from the line where the search string is found. This works alright in some case but not on other times.

To illustrate it, I am uploading the files x.ksh, x.log and below is the output from running the script. x.sh = x.ksh. UNIX.com does not allow uploading .ksh file so I renamed it to x.sh

Code:
server_dtq > $: ./x.ksh unusable

- file_to_check = x.txt

- UNUSABLE found = 8

10:Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
11:Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable
13:Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
14:Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable
20:Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
21:Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable
29:Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
30:Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable

- lineno :: sed print 7 to 10 // 10:Some indexes or index [sub]partitions of table TEST_XX have been marked unusable //
  Current log# 4 seq# 229 mem# 0: /db/test1/redolog/test1_redo_4a.dbf
  Current log# 4 seq# 229 mem# 1: /db/test1/mirrlog/test1_redo_4b.dbf
Sun Jul 10 16:54:33 2016
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable

- lineno :: sed print 8 to 11 // 11:Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable //
  Current log# 4 seq# 229 mem# 1: /db/test1/mirrlog/test1_redo_4b.dbf
Sun Jul 10 16:54:33 2016
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable

- lineno :: sed print 10 to 13 // 13:Some indexes or index [sub]partitions of table TEST_XX have been marked unusable //
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable
Sun Jul 10 17:33:30 2016
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable

- lineno :: sed print 11 to 14 // 14:Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable //
Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable
Sun Jul 10 17:33:30 2016
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable

- lineno :: sed print 17 to 20 // 20:Some indexes or index [sub]partitions of table TEST_XX have been marked unusable //
Private strand flush not complete
  Current log# 4 seq# 229 mem# 0: /db/test1/redolog/test1_redo_4a.dbf
  Current log# 4 seq# 229 mem# 1: /db/test1/mirrlog/test1_redo_4b.dbf
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable

- lineno :: sed print 18 to 21 // 21:Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable //
  Current log# 4 seq# 229 mem# 0: /db/test1/redolog/test1_redo_4a.dbf
  Current log# 4 seq# 229 mem# 1: /db/test1/mirrlog/test1_redo_4b.dbf
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable

- lineno :: sed print 26 to 29 // 29:Some indexes or index [sub]partitions of table TEST_XX have been marked unusable //
Thread 1 cannot allocate new log, sequence 231
Private strand flush not complete
  Current log# 5 seq# 230 mem# 0: /db/test1/redolog/test1_redo_5a.dbf
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable

- lineno :: sed print 27 to 30 // 30:Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable //
Private strand flush not complete
  Current log# 5 seq# 230 mem# 0: /db/test1/redolog/test1_redo_5a.dbf
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable

As you can see, from the test run, the following fails to get a date Smilie

Code:
- lineno :: sed print 17 to 20 // 20:Some indexes or index [sub]partitions of table TEST_XX have been marked unusable //
Private strand flush not complete
  Current log# 4 seq# 229 mem# 0: /db/test1/redolog/test1_redo_4a.dbf
  Current log# 4 seq# 229 mem# 1: /db/test1/mirrlog/test1_redo_4b.dbf
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable

- lineno :: sed print 18 to 21 // 21:Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable //
  Current log# 4 seq# 229 mem# 0: /db/test1/redolog/test1_redo_4a.dbf
  Current log# 4 seq# 229 mem# 1: /db/test1/mirrlog/test1_redo_4b.dbf
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable

- lineno :: sed print 26 to 29 // 29:Some indexes or index [sub]partitions of table TEST_XX have been marked unusable //
Thread 1 cannot allocate new log, sequence 231
Private strand flush not complete
  Current log# 5 seq# 230 mem# 0: /db/test1/redolog/test1_redo_5a.dbf
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable

- lineno :: sed print 27 to 30 // 30:Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable //
Private strand flush not complete
  Current log# 5 seq# 230 mem# 0: /db/test1/redolog/test1_redo_5a.dbf
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable

I could get a date only if I subtract by more than 3.

What I am trying to achieve eventually if possible is run the script where I pass at least one parameter for the string to search which is an error or warning string. In the test run, I am passing the string unusable, in some cases, I might pass the string ORA-00600.

Then the script is to search for that string in the log and then start working out what is the closest datetime that this string/error/warning occurs. In most instance, searching backward for the datetime strng is what's required.

However, because the log is getting rotated, there may be some scenario where searching backward won't result to finding a datetime string. In this case, I want to search forward and find a datetime string. Example of such scenario is as below where searching backward won't find a datetime string for the first two occurrence of the unusable string. In that case, I would want to search forward for a datetime string instead.

Code:
  Current log# 3 seq# 228 mem# 1: /db/test1/mirrlog/test1_redo_3b.dbf
Thread 1 advanced to log sequence 229 (LGWR switch)
  Current log# 4 seq# 229 mem# 0: /db/test1/redolog/test1_redo_4a.dbf
  Current log# 4 seq# 229 mem# 1: /db/test1/mirrlog/test1_redo_4b.dbf
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable
Sun Jul 10 17:33:30 2016
Some indexes or index [sub]partitions of table TEST_XX have been marked unusable

At the moment, I've changed my script to subtract/add 10 instead from where the search string is found. Not ideal, but will do for now.

Any advice will be very much appreciated. Thanks in advance.
x.txt (2.0 KB) 
x.sh (488 Bytes) 
# 2  
Old 07-15-2016
Not sure I understand your request. You want to print lines containing a keyword together with the last timestamp encountered? Try
Code:
LC_ALL=C locale abmon abday |  awk -vSRCH="$1" '
NR <= 2         {gsub (/;/, "|")
                 if (NR == 1)   MONS = $0
                   else         DAYS = $0
                 next
                }
$1 ~ DAYS &&
$2 ~ MONS       {DATE = $0
                }
                {TMP = tolower ($0)
                }
TMP ~ SRCH      {print DATE ": " $0
                }
' - /tmp/x.txt
Sun Jul 10 16:54:33 2016: Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Sun Jul 10 16:54:33 2016: Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable
Sun Jul 10 17:33:30 2016: Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Sun Jul 10 17:33:30 2016: Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable
Sun Jul 10 17:36:41 2016: Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Sun Jul 10 17:36:41 2016: Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable
Mon Jul 11 04:34:49 2016: Some indexes or index [sub]partitions of table TEST_XX have been marked unusable
Mon Jul 11 04:34:49 2016: Some indexes or index [sub]partitions of table TEST_XX_HISTORY have been marked unusable

Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Grepping only dates from a log file

Hi All, I have a log file where every line contains a date and some other data, i want to grep only the date from every line to a different file. Please help how to get this. Thanks in advance !! (25 Replies)
Discussion started by: nanz143
25 Replies

2. Shell Programming and Scripting

Check log file size every 10 minute. Alert if log not update

How to check log size every 10min. by script (can use crontab) if log size not change with alert "Log not update" Base run on SunOS 5.8 Generic_Virtual sun4u sparc SUNW,SPARC-Enterprise logFiles="log1.log log2.log" logLocation="/usr/home/test/log/" Out put. Tue Jan 31... (3 Replies)
Discussion started by: ooilinlove
3 Replies

3. Shell Programming and Scripting

Oracle Alert log script

Hi, I'm trying to write a shell script on HP-UX to search through Oracle alert logs for errors which always start with ORA-. If it does find an error I'd like it to print the date line (which precedes the error by a line or two normally) as well as the error line. So in the example below I'd like... (1 Reply)
Discussion started by: capesong
1 Replies

4. Shell Programming and Scripting

Pull log between two dates.

Hi, I am trying to pull out lines from logs between two dates, the logs has lines like this : conn=552 op=3 msgId=17228 - RESULT err=0 tag=97 nentries=0 etime=0 dn="cn=amldapuser,ou=dsame u sers,dc=abc,dc=com" conn=553 op=-1 msgId=-1 - fd=34 slot=34 LDAP connection from 52.99.164.14 to... (4 Replies)
Discussion started by: john_prince
4 Replies

5. Shell Programming and Scripting

Getting list of all the log files between two dates

I need to get the list of all the log files for a particular duration, say between two dates,i.e I need to get the list of all the log files between date1 and date2.The two dates are entered by the user. The format of the log file is : /path_name/graph_name_20080801180308.log. I dont... (1 Reply)
Discussion started by: avishekp
1 Replies

6. Shell Programming and Scripting

find log file between two dates

Dear All, Please can you help me to crack this query? If the log files for the task above all had a naming convention of myoutput_YearMonthDay.log (i.e. myoutput_20060215) How would you find only those log files created between the 10th and the 20th of each month going back the last 365 days.... (1 Reply)
Discussion started by: justin_mca
1 Replies

7. UNIX for Advanced & Expert Users

Select entries between two dates by converting Unix timestamp in Oracle Database.

Hi, I need to select the entries between two dates from an Oracle db. The Oracle db has a column with Unix timestamps. I use the following querry, but it doesnt seem to be working as desired. select count(*) from reporter_status where to_char(FIRSTOCCURRENCE, 'mm-dd-yy') between ('08-07-06')... (1 Reply)
Discussion started by: amitsayshii
1 Replies

8. Shell Programming and Scripting

help searching log file with dates

Im tyring to create a script that will show me any lines in a file with todays date and yesterdays, the date format in the file is as follows ----- amqxfdcx.c : 728 -------------------------------------------------------- 07/12/05 09:53:20 AMQ6109: An internal WebSphere MQ error has... (3 Replies)
Discussion started by: csaunders
3 Replies

9. Shell Programming and Scripting

Searching between dates

Hello people, I have a file containing dated entries. Is there a way to search between dates? for example, in a file <file.text> with entries 04-jul-2005 login: pty0 xxxxxx xxxxxx 09-jul-2005 logout pty34 xxxxxx xxxxxx 11-aug-2005 arp port17 xxxxxx xxxxxx 30-sep-2005... (9 Replies)
Discussion started by: Khoomfire
9 Replies
Login or Register to Ask a Question