Extracting Sysdate-1 ORA Errors - Can you help me in this UNIX Script?

Shell Programming and Scripting
1

Hi Guys,

I wanted to create an Unix Shell Script that should fetch a particular string from a text file on a particular date.

We all know Oracle generates alert logs for each and every day for every actions in the database.

I have an alert log file now where it contains for about a months generated alert log.

I wanted to extract the ORA errors generated yesterday. ( APR 15 )

I was trying this,

===================================

SCRIPT

cd /back/alert/log
a=`date '+%b %d' -d "yesterday"`
b=`date '+%b %d'`
c="$a $b"
echo "$c"
awk "/$1 $2/,/$3 $4/" alert_dbname.log | sed '$d'
exit

=================================================

echo "$c" would output yesterday's date and today's date in the format

Apr 15 Apr 16

And from here I want to substitute the Apr 15 Apr 16 in awk as

[[[ awk '/Apr 15/,/Apr 16/' alert_dbname.log | sed '$d' | grep ORA  ]]]]

And for this substitution I have written in the script

awk "/$1 $2/,/$3 $4/" alert_dbname.log | sed '$d

'

But this doesn't bring me the required output.

Could you please help me working on this script?

Thanks,
Raja

2

pls provide sample lines of your log file.... We first need to know how does it look like.

3

Based on what you said, I think this is what you want:

a=`date '+%b %d' -d "yesterday"`
b=`date '+%b %d'`
awk '/y/,/t/ {print}' y=a t=b alert_dbname.log | sed '$d' | grep ORA
4

Hanson,

You are too close and this is what I am looking for, but the code produces a different result and it brings me ORA strings with different dates.

How can we get only the Day - 1 results?

Any help would be deeply appreciated.

---------- Post updated at 11:46 AM ---------- Previous update was at 11:44 AM ----------

This could be the log file,

Thu Apr 15 01:34:25 2013
Thread 1 advanced to log sequence 260 (LGWR switch)
  Current log# 2 seq# 260 mem# 0: +FREEZE/redo03.log
  Current log# 2 seq# 260 mem# 1: +FREEZE/redo04.log
Thu Apr 15 02:00:00 2013
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Thu Apr 15 19:15:03 2013
DM00 started with pid=41, OS id=8489, job DBADMIN.SYS_EXPORT_FULL_01
Thu Apr 15 19:15:06 2013
DW00 started with pid=35, OS id=8493, wid=1, job DBADMIN.SYS_EXPORT_FULL_01
Thu Apr 15 19:21:16 2013
Thread 1 cannot allocate new log, sequence 261
Private strand flush not complete
  Current log# 2 seq# 260 mem# 0: +FREEZE/redo03.log
  Current log# 2 seq# 260 mem# 1: +FREEZE/redo04.log
Thread 1 advanced to log sequence 261 (LGWR switch)
  Current log# 1 seq# 261 mem# 0: +FREEZE/redo01.log
  Current log# 1 seq# 261 mem# 1: +FREEZE/redo02.log
Thu Apr 15 19:40:28 2013
Thread 1 cannot allocate new log, sequence 262
Private strand flush not complete
  Current log# 1 seq# 261 mem# 0: +FREEZE/redo01.log
  Current log# 1 seq# 261 mem# 1: +FREEZE/redo02.log
Thread 1 advanced to log sequence 262 (LGWR switch)
  Current log# 2 seq# 262 mem# 0: +FREEZE/redo03.log
  Current log# 2 seq# 262 mem# 1: +FREEZE/redo04.log
Thu Apr 15 22:00:00 2013
Setting Resource Manager plan SCHEDULER[0x40BC3]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Thu Apr 15 22:00:00 2013
Starting background process VKRM
Thu Apr 15 22:00:00 2013
VKRM started with pid=28, OS id=15233
Thu Apr 15 22:00:04 2013
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Thu Apr 15 22:00:23 2013
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
5

I could not make the awk syntax work. The problem I ran into was using an awk command line variable inside a RE. Here was the closest I got:

$ cat log
Thu Apr 15 01:34:25 2013
Line 1 for April 15
Thu Apr 15 02:00:00 2013
Line 2 for April 15
Thu Apr 16 19:15:03 2013
Line 1 for April 16
Thu Apr 16 19:15:06 2013
Line 2 for April 16
Thu Apr 17 19:21:16 2013
Line 1 for April 17
Thu Apr 17 19:40:28 2013
Line 2 for April 17

a=`date '+%b %d' -d "yesterday"`
b=`date '+%b %d'`
awk '/$y/,/$t/ {print}' y="$a" t="$b" log

Maybe some other expert can provide an awk solution.

-----------------------------------------------

Here is a way to do this using sed:

a=`date '+%b %d' -d "yesterday"`
b=`date '+%b %d'`
echo a = $a, b = $b
sed -n "/$a/,/$b/ p" log | sed '$d'

$ ./temp.sh
a = Apr 15, b = Apr 16
Thu Apr 15 01:34:25 2013
Line 1 for April 15
Thu Apr 15 02:00:00 2013
Line 2 for April 15