Home » Infrastructure » Unix » Shell Script to extract "Particular Content" from a Text File.
Shell Script to extract "Particular Content" from a Text File. [message #582071] Fri, 12 April 2013 21:51 Go to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Hi Folks,

Thanks to all helping me in the past learning Oracle stuffs and I am now in need of an assistance in Unix Shell Scripting.

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.

Say for example March 01 to March 31 alert log generation.

I am looking for a shell script where if I supply a date, Mar 24 for example, it should extract all the content generated on Mar 24 and should create a text file in the same location with all the Mar 24 contents extracted.

Can anyone please help me creating this particular unix shell script? All your time is deeply appreciated.

Thank You.
Raja
Re: Shell Script to extract "Particular Content" from a Text File. [message #582072 is a reply to message #582071] Fri, 12 April 2013 22:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Oracle version?
If 11g, it is a builtin functionality.
Just query X$DBGALERTEXT.

Re: Shell Script to extract "Particular Content" from a Text File. [message #582073 is a reply to message #582072] Fri, 12 April 2013 22:46 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

I am using 10G Mahesh.

Could you please help me achieving this functionality?

Either as a Unix Script or in some other way that you may know of?

Thanks.
Re: Shell Script to extract "Particular Content" from a Text File. [message #582074 is a reply to message #582073] Fri, 12 April 2013 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+alert_SID.log+external+table
Re: Shell Script to extract "Particular Content" from a Text File. [message #582075 is a reply to message #582073] Fri, 12 April 2013 22:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
A simple awk could do. Something like this.
This will get April 11 entries. Hopefully your logfile does not have entries more than a year Smile
You can make it any fancier.

oracle@ark#awk '/Apr 11/,/Apr 12/' alert_freeze.log | sed '$d'
Thu Apr 11 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 11 02:00:00 2013
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Thu Apr 11 19:15:03 2013
DM00 started with pid=41, OS id=8489, job DBADMIN.SYS_EXPORT_FULL_01
Thu Apr 11 19:15:06 2013
DW00 started with pid=35, OS id=8493, wid=1, job DBADMIN.SYS_EXPORT_FULL_01
Thu Apr 11 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 11 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 11 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 11 22:00:00 2013
Starting background process VKRM
Thu Apr 11 22:00:00 2013
VKRM started with pid=28, OS id=15233
Thu Apr 11 22:00:04 2013
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Thu Apr 11 22:00:23 2013
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"


And just redirect the output.
oracle@ark#awk '/Apr 11/,/Apr 12/' alert_freeze.log | sed '$d' > thisLog
oracle@ark#ls -lart thisLog
-rw-r--r-- 1 oracle dba 1805 Apr 13 00:02 thisLog

[Updated on: Fri, 12 April 2013 23:03]

Report message to a moderator

Re: Shell Script to extract "Particular Content" from a Text File. [message #582077 is a reply to message #582075] Sat, 13 April 2013 00:06 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Hey Mahesh,

You are rocking, thanks much for your time and efforts, it works awesome.

I was however trying to add somemore stuffs which fails to execute.

read month
read num
read month1
read num2

awk '/$month $num/,/$month1 $num2/' alert_freeze.log | sed '$d'

exit
EOF

Am I doing something wrong?

Raja

[Updated on: Sat, 13 April 2013 00:06]

Report message to a moderator

Re: Shell Script to extract "Particular Content" from a Text File. [message #582080 is a reply to message #582073] Sat, 13 April 2013 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am using 10G Mahesh.


You can use an external table on the alert.log file then you have power of SQL to do what you want:
-- Retrieve alert.log file name and directory
define bdump = ' '
define alert = ' '
col value heading "Background dump dest" new_value bdump
col log   heading "Alert log"            new_value alert
select value, 'alert_'||instance_name||'.log' log       -- Unix 
from v$instance, v$parameter 
where name='background_dump_dest'
/

-- Create Oracle directory
create or replace directory aol_bdump as '&&bdump'
/

-- Create external table for the alert.log file
create table alert_log_ext (line varchar2(2000))
organization external (
   type oracle_loader
   default directory aol_bdump
   access parameters (
      records delimited by newline
      nobadfile
      nologfile
      nodiscardfile
      fields 
      missing field values are null
      (line position (1:2000))
      )
   location ('&&alert') 
   )
reject limit unlimited
/

Regards
Michel

[Updated on: Sat, 13 April 2013 00:48]

Report message to a moderator

Re: Shell Script to extract "Particular Content" from a Text File. [message #582081 is a reply to message #582080] Sat, 13 April 2013 00:56 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Michel,

Thanks for your input, however we are not allowed to use SQLloader here.

I wish I should have an Unix script to serve this purpose.

This command works good - awk '/Apr 12/,/Apr 13/' alert_freeze.log | sed '$d'

However, I wish to replace Apr 11 by sysdate -1 and Apr 13 - sysdate in order NOT to edit the shell script everyday for changing dates.

I have also tried this but it fails.

a=`date '+%b %d' -d "yesterday"`
b=`date '+%b %d'`
awk '/$a/,/$b/' tete.log | sed '$d'

It seems AWK not getting any inputs and please throw me some light to find a solution.

Thanks,
Raja


Re: Shell Script to extract "Particular Content" from a Text File. [message #582082 is a reply to message #582081] Sat, 13 April 2013 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Thanks for your input, however we are not allowed to use SQLloader here.


You do not use SQL*Loader with my solution, you just create a table (and a directory, if you have not the privilege you can ask your dba to do it).

Regards
Michel
Re: Shell Script to extract "Particular Content" from a Text File. [message #582083 is a reply to message #582082] Sat, 13 April 2013 01:07 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Michel,

I am exclusively looking for a unix shell script to serve the purpose.

Is it possible if you can do something on the below stuff to bring the result?

a=`date '+%b %d' -d "yesterday"`
b=`date '+%b %d'`
awk '/$a/,/$b/' tete.log | sed '$d'

Thanks
Re: Shell Script to extract "Particular Content" from a Text File. [message #582086 is a reply to message #582083] Sat, 13 April 2013 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am exclusively looking for a unix shell script to serve the purpose.


So why don't you post it in a Unix forum instead of an Oracle one?

Regards
Michel

[Updated on: Sat, 13 April 2013 01:51]

Report message to a moderator

Re: Shell Script to extract "Particular Content" from a Text File. [message #582087 is a reply to message #582086] Sat, 13 April 2013 01:54 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Michel,

I believe I am in an Unix Category on an Oracle Forum.

Thank You.
Re: Shell Script to extract "Particular Content" from a Text File. [message #582088 is a reply to message #582087] Sat, 13 April 2013 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but this forum is for (quoting the main page):

Oracle Installation, Configuration, Troubleshooting and Scripting on Unix platforms (Solaris, HP-UX, AIX, etc.)

Do not expect expert in shell script here and so you would have better and faster answer in a real Unix forum.*

In addition you said: "Either as a Unix Script or in some other way that you may know of?" and I provided you this other way.

Regards
Michel
Re: Shell Script to extract "Particular Content" from a Text File. [message #582089 is a reply to message #582088] Sat, 13 April 2013 02:45 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Michel,

I would much appreciate the time you have for others to assist and the other way I meant is again with Unix with a different command and I fully understand its an Oracle forum, anyway, thanks for your time and your continued support.

Raja
Re: Shell Script to extract "Particular Content" from a Text File. [message #582097 is a reply to message #582077] Sat, 13 April 2013 05:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
No need to EOF as you are not using a "here" document in this case.

Keep it simple to start with.
oracle@ark#cat someScript 
awk "/$1 $2/,/$3 $4/" alert_freeze.log | sed '$d' > thisLog
oracle@ark#./someScript Apr 11 Apr 12

[Updated on: Sat, 13 April 2013 06:01]

Report message to a moderator

Re: Shell Script to extract "Particular Content" from a Text File. [message #582213 is a reply to message #582097] Mon, 15 April 2013 22:56 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Mahesh Rajendran wrote on Sat, 13 April 2013 16:29
No need to EOF as you are not using a "here" document in this case.

Keep it simple to start with.
oracle@ark#cat someScript 
awk "/$1 $2/,/$3 $4/" alert_freeze.log | sed '$d' > thisLog
oracle@ark#./someScript Apr 11 Apr 12



Mahesh,

I am sorry for the delayed response.

I am still finding difficult to bring the results.

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.

The ultimate aim of this script is to find Day-1 ORA errors and I want this to be automated by an Unix Shell Script.

Could you please help me out again?

Thanks,
Raja

[Updated on: Mon, 15 April 2013 23:56] by Moderator

Report message to a moderator

Re: Shell Script to extract "Particular Content" from a Text File. [message #582215 is a reply to message #582213] Mon, 15 April 2013 23:56 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

And do not post 10-20 empty lines at the bottom of your post.

Regards
Michel
Re: Shell Script to extract "Particular Content" from a Text File. [message #582216 is a reply to message #582215] Tue, 16 April 2013 00:04 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Agreed Michel. Sorry for taking your time on this.
Re: Shell Script to extract "Particular Content" from a Text File. [message #582283 is a reply to message #582213] Tue, 16 April 2013 09:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> The ultimate aim of this script is to find Day-1 ORA errors and I want this to be automated by an Unix Shell Script.
Please be aware that this is just a forum and you cannot keep the scope creeping.
Especially when you do not provide the exact version information (OS in this case).
And statements like these
>> But this doesn't bring me the required output.
are of no use for us as we have no idea what you are trying to do.
Just copy and paste the session. Verbatim.

Depending on your OS, certain tools may or may not work with certain option. OS is Unix is very generic statement.
In certain flavors of unix, 'date -d' does not work.
If you are planning to deploy this across multiple platforms, I would look into perl/python.

And no need to have an 'exit' in your code.
You did not 'begin' or start anything to exit.
Re: Shell Script to extract "Particular Content" from a Text File. [message #582284 is a reply to message #582283] Tue, 16 April 2013 09:55 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Mahesh,

All the inputs you have provided here are much useful and it is just that I was not knowing certain stuffs to develop the script for my required result.
However I tried this concept ( == > Connection ) and it seem to work but I will have to check running the same script in different environment and see if it gets the required output and I shall come back again and I understand this is a forum where I can be of some helping hands like you and may I be creeping a bit but soon I would develop myself reading this forum and thanks leaving your input again I do not have to have the exit part and much thanks again taking more time helping out beginners and sharing your knowledge, Cheers.

== > Connection

oracle@ark#cat someScript
awk "/$1 $2/,/$3 $4/" alert_freeze.log | sed '$d' > thisLog
oracle@ark#./someScript Apr 11 Apr 12

Raja

[Updated on: Tue, 16 April 2013 09:55]

Report message to a moderator

Re: Shell Script to extract "Particular Content" from a Text File. [message #582285 is a reply to message #582284] Tue, 16 April 2013 09:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What is your OS?
Post the output of
uname -a

Re: Shell Script to extract "Particular Content" from a Text File. [message #582286 is a reply to message #582285] Tue, 16 April 2013 10:10 Go to previous message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Mahesh,

I am in home at the moment and I shall post you the version while I reach office tomorrow. I'd really appreciate people like Michel Cadot, BlackSwan, John Watson and not to forget my country owned Mahesh for making this forum much knowledgeable and just to let you know I was a search analyst for about 5 years and I worked both for the british and the americans and while the contracts expired I become jobless and learned Oracle DBA, passed the international certification OCP and now working as a DB Admin in a product based company, Chennai.

Its my carrier change and most the things were pretty new for me, I was not too techie and all I did in the previous was totally different, when I look into the past, it kinda hurts and learning at this point of time to improve my carrier level on the DBA stuff, its more painful but then the other side I find it interesting and I believe with all the helping hands I would improve as possible as I can and tune myself for a better future.

Raja

[Updated on: Tue, 16 April 2013 10:10]

Report message to a moderator

Previous Topic: putty configuration for standalone(local pc) with oracle database installation
Next Topic: Apache 2.2 on AIX 6.1 64 bit platform can't load a sample module
Goto Forum:
  


Current Time: Thu Apr 17 00:32:58 CDT 2014

Total time taken to generate the page: 0.06463 seconds