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
server_dtq > $: ./x.ksh unusable
- file_to_check = x.txt
- UNUSABLE found = 8
10:Some indexes or index partitions of table TEST_XX have been marked unusable
11:Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
13:Some indexes or index partitions of table TEST_XX have been marked unusable
14:Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
20:Some indexes or index partitions of table TEST_XX have been marked unusable
21:Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
29:Some indexes or index partitions of table TEST_XX have been marked unusable
30:Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
- lineno :: sed print 7 to 10 // 10:Some indexes or index 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 partitions of table TEST_XX have been marked unusable
- lineno :: sed print 8 to 11 // 11:Some indexes or index 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 partitions of table TEST_XX have been marked unusable
Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
- lineno :: sed print 10 to 13 // 13:Some indexes or index partitions of table TEST_XX have been marked unusable //
Some indexes or index partitions of table TEST_XX have been marked unusable
Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
Sun Jul 10 17:33:30 2016
Some indexes or index partitions of table TEST_XX have been marked unusable
- lineno :: sed print 11 to 14 // 14:Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable //
Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
Sun Jul 10 17:33:30 2016
Some indexes or index partitions of table TEST_XX have been marked unusable
Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
- lineno :: sed print 17 to 20 // 20:Some indexes or index 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 partitions of table TEST_XX have been marked unusable
- lineno :: sed print 18 to 21 // 21:Some indexes or index 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 partitions of table TEST_XX have been marked unusable
Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
- lineno :: sed print 26 to 29 // 29:Some indexes or index 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 partitions of table TEST_XX have been marked unusable
- lineno :: sed print 27 to 30 // 30:Some indexes or index 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 partitions of table TEST_XX have been marked unusable
Some indexes or index 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 
- lineno :: sed print 17 to 20 // 20:Some indexes or index 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 partitions of table TEST_XX have been marked unusable
- lineno :: sed print 18 to 21 // 21:Some indexes or index 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 partitions of table TEST_XX have been marked unusable
Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
- lineno :: sed print 26 to 29 // 29:Some indexes or index 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 partitions of table TEST_XX have been marked unusable
- lineno :: sed print 27 to 30 // 30:Some indexes or index 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 partitions of table TEST_XX have been marked unusable
Some indexes or index 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.
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 partitions of table TEST_XX have been marked unusable
Some indexes or index partitions of table TEST_XX_HISTORY have been marked unusable
Sun Jul 10 17:33:30 2016
Some indexes or index 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.