UNIX for Dummies Questions & Answers

I am having a shell script that connects to database half hourly and pulls out the backlog from some tables. Now that logfile is growing too big and I need to housekeep it! effectively I want to keep last 30 days data in that file and move rest to archived file.
The file contents are as below.
the algorithm I want is as follow :

take input file my.log
go to first line, check if this is more than 90 days
if it is then
pick those lines fom log file till the next sql plus ( i.e. that block) and append to some archive file ( difficult ?)
delete these lines from current file

if it is not then leave it as it is

can somebody help me writting this please ?

---------------------------------------------------------------------------------SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 27 19:20:00 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Enter user-name:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> SQL> 76834445

SQL> 76834445

SQL> 2 3 4 5 6 7 8 9 ELFORDE
75241084 1593361 25-NOV-02 18:18:10

HEWITTSA
76238384 596061 20-DEC-02 09:18:09
SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Pro
duction
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 27 19:50:00 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Enter user-name:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
---------------------------------------------------------------------------------

Thanks in advance.

I think I'm following you.. it would help if you could post a portion of the log file to look at.

herer it is again !
***************************************************

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 27 19:20:00 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Enter user-name:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> SQL> 76834445

SQL> 76834445

SQL> 2 3 4 5 6 7 8 9 ELFORDE
75241084 1593361 25-NOV-02 18:18:10

HEWITTSA
76238384 596061 20-DEC-02 09:18:09

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Pro
duction
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 27 19:50:00 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Enter user-name:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> SQL> 76834451

SQL> 76834451

SQL> 2 3 4 5 6 7 8 9 ELFORDE
75241084 1593367 25-NOV-02 18:18:10

HEWITTSA
76238384 596067 20-DEC-02 09:18:09


SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Pro
duction
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 27 20:20:01 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> SQL> 76834459

SQL> 76834459

SQL> 2 3 4 5 6 7 8 9 ELFORDE
75241084 1593375 25-NOV-02 18:18:10

HEWITTSA
76238384 596075 20-DEC-02 09:18:09


SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
***************************************************

This process runs for every half an hour!

THanks in advance.

Okay, but most of the time a log is the same set of information written to a file, such as

06 30 2003 Data Data Data
07 01 2003 Data Data Data
07 02 2003 Data Data Data

What you've posted looks more like screen output.. the version of SQL you're using.. the SQL> prompt..

If that is what's being written to your log, how do you know what day that entry is for? Because you need to know that day in order to determine how old the entry is and delete it from the log..

Are you saying that the entry you included here is from Thu Feb 27 19:20:00 2003 ?

Yeah, the log contains the router log information and helps us keeping watch on how much the lag is! So it may be repeating information in case routers are not working! ( and In that case we need to make them work )

I have posted the part of spool file of what's happening.

When the file will be run ( it runs every half an hour!), we will know what date it is , we can spool out the date! and then calculate the 90 days before that day, and remove the block of lines from that date! ( i.r. I need to maintain the information only for last 90 days and not before that !) .

Now what I need is that once I have the 90_days_before date how to delete all lines before that line ?

am I elaborate enough ?

I'm just curious to know why you don't write the log to a new file everyday....say ccyymmddhhss.log format. If you're already writing a script to pull the data from the database, I assume with a here statement that connects and unload your data, you can easily create this datetime variable to use as your log filename....then all you have to do is do a find exec rm against any files over 90 days...
Of course, this assumes you don't need to keep appending to the log file...

Just wondering...

Yeah, That sure would have been an easy win, If I was allowed to do that , BUt I am supposed to keep it in single file only!