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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Extracting Sysdate-1 ORA Errors - Can you help me in this UNIX Script?
# 1  
Old 04-16-2013
Extracting Sysdate-1 ORA Errors - Can you help me in this UNIX Script?

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
Code:
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
Code:
Apr 15 Apr 16

And from here I want to substitute the Apr 15 Apr 16 in awk as
Code:
[[[ awk '/Apr 15/,/Apr 16/' alert_dbname.log | sed '$d' | grep ORA  ]]]]

And for this substitution I have written in the script
Code:
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

Last edited by Franklin52; 04-16-2013 at 03:06 AM.. Reason: Please use code tags for code and data samples
# 2  
Old 04-16-2013
pls provide sample lines of your log file.... We first need to know how does it look like.
# 3  
Old 04-16-2013
Based on what you said, I think this is what you want:
Code:
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  
Old 04-16-2013
Quote:
Originally Posted by hanson44
Based on what you said, I think this is what you want:
Code:
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

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 ----------

Quote:
Originally Posted by Naga06
pls provide sample lines of your log file.... We first need to know how does it look like.
This could be the log file,
Code:
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"


Last edited by Franklin52; 04-16-2013 at 04:04 AM.. Reason: Please use code tags
# 5  
Old 04-16-2013
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:
Code:
$ 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

Code:
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:
Code:
a=`date '+%b %d' -d "yesterday"`
b=`date '+%b %d'`
echo a = $a, b = $b
sed -n "/$a/,/$b/ p" log | sed '$d'

Code:
$ ./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

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Check for “errors” or “ORA-”

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)
Discussion started by: haadiya
6 Replies

2. Shell Programming and Scripting

Shell script to capture Current day ORA errors from Alert Log

Please provide Shell script to capture ORA errors from Alert Log for a given date or Current date. -Veera (1 Reply)
Discussion started by: Veera_V
1 Replies

3. Shell Programming and Scripting

How to turn off ora errors in shell script?

I have a shell script which select total count from a table and use its value in a if condition like below connect_string="username/password@tnsname" tot=`sqlplus -s $connect_string << EOF set echo off set feedback off set head off select count(*) from test_table; EOF ` if then echo... (2 Replies)
Discussion started by: vel4ever
2 Replies

4. Shell Programming and Scripting

Shell script to capture ORA errors from Alert Log

Hi, as the title says, I am after a simple script, which will open the Alert log from an 11.2.0.1 Linux environment and mail the error message and description to a recipient email address. I can then schedule this job via cron and let it run every 15 minutes. I have searched online... (16 Replies)
Discussion started by: jnrpeardba
16 Replies

5. UNIX for Advanced & Expert Users

grep all ORA errors except one ORA error

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)
Discussion started by: Mansoor8810
7 Replies

6. Solaris

maxuprc and maxusers - ORA-27300, ORA-27301, ORA-27302

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)
Discussion started by: newbie_01
1 Replies

7. Shell Programming and Scripting

How to get ORA errors in alertlog file using shell script.

Hi, Can anyone tell me how to get all ORA errors between two particular times in an alertlog file using shell script. Thanks (3 Replies)
Discussion started by: suman_dba1
3 Replies

8. Shell Programming and Scripting

Unix shell script (grep -A 6 -B 2 "ORA-" filename

BACKGROUND: I am using Solaris 10. Some of my boxes have gnu grep and I can use -A and -B flags on those. However, the solaris flavor of grep won't use the flags -A or -B. And some of my boxes won't be getting gnu grep. Should I try using perl, awk, or sed? Actual PROBLEM: I am... (7 Replies)
Discussion started by: el_guero
7 Replies

9. UNIX for Dummies Questions & Answers

sysdate -1 in unix

I need to automate the creation of a file. It needs to have the date of the previous day. In sqlplus we use sysdate -1 but I checked the man page for date and didn't find a similar command. This is what I use for creating a file for the current date: rciind`date '+%m%d%Y'`.txt What do I... (1 Reply)
Discussion started by: kskywr
1 Replies

10. UNIX for Advanced & Expert Users

Getting sysdate - 2 by an unix command

How can I get an equivalent in unix for the following Oracle SQL command: select sysdate - 2 from dual; Thanks José (4 Replies)
Discussion started by: josecollantes
4 Replies
Login or Register to Ask a Question

Featured Tech Videos