Andrew Fraser DBA

This code cleans up old trace files, log files, core dumps, etc. It is designed to be run from cron. It takes a somewhat brutal approach by deleting files after just 7 days – good for e.g. dev/test servers, but in production you would probably want to modify this to keep files for longer.

For this example all oracle files of interest were under directory “/ora” – that would need to be changed to suit other sites.

# 1) Remove old oracle-owned aud, trc, trw, core files
#    Also remove old recv error files (these have format ".err_[0-9]")
#    Also remove access_log., error_log., emoms.log. files - which are generated in large numbers
#    Only core files named "core." are removed: because many other files with "core" in their name are not core dumps, are used by oracle.

echo "*** REMOVE OLD ORACLE AUDIT FILES and OLD RECV Error Files and OLD TRACE FILES ***"
find /ora -mtime +7 -user oracle \( \
  -name '*.aud' -o \
  -name '*.trc*' -o \
  -name '*.trw' -o \
  -name 'core.[0-9]*' -o \
  -name '*.err_[0-9]*' -o \
  -name 'access_log.[0-9]*' -o \
  -name 'error_log.[0-9]*' -o \
  -name 'emoms.log.[0-9]*' \) -exec rm {} \;

# 2) Cut down alert logs and listener logs, and also access_log and event_log (webcache/portal/etc.)
#    Old log files are ignored - only log files modified in the last 7 days are worked on.
#    Small log files are ignored - only files bigger than 3mb (=6144*512 byte blocks) are worked on. 3mb is approximately 30,000 lines of text.

echo "*** CUT DOWN THE ALERT LOGS and ORACLE LISTENER.LOG FILE ***"
for FILE in `find /ora -mtime -7 -size +6144 -user oracle \( \
  -name 'alert_*.log' -o \
  -name 'listener*.log' -o \
  -name 'access_log' -o \
  -name 'event_log' -o \
  -name 'http-web-access.log' -o \
  -name 'server.log' \)`
do
  echo "*** cutting $FILE ***"
  cp $FILE $FILE.tmp
  tail -10000 $FILE.tmp > $FILE
  rm $FILE.tmp
done

echo "*** CUT DOWN MESSAGES and WARN FILES  ***"
for FILE in `find /var/log/ -mtime -7 -size +6144 \( \
  -name 'messages' -o \
  -name 'warn'  \)`
do
  echo "*** cutting $FILE ***"
  cp $FILE $FILE.tmp
  tail -10000 $FILE.tmp > $FILE
  rm $FILE.tmp
done

#End of file.

Attempting to shrink files which have a lot of empty space can fail with this error:

SQL> alter database datafile '/ora1data/TEST/TEST_ts_data1_f1.dbf' resize 2g ;

ORA-03297: file contains used data beyond requested RESIZE value

Provided you can get an outage, one fix for this is to temporarily move segments to another tablespace using a scripts like this. (Note that this script includes the commands to move LOBs).

set pages 9999 lines 132
spool go.tmp
select 'alter table '||owner||'.'||table_name||' move tablespace ts_data2 ;'
from dba_tables
where tablespace_name = 'TS_DATA1'
order by 1
/
select 'alter index '||owner||'.'||index_name||' rebuild tablespace ts_data2 ;'
from dba_indexes
where tablespace_name = 'TS_DATA1'
and index_type != 'LOB'
order by 1
/
select 'alter table '||owner||'.'||table_name||' move tablespace ts_data2 lob ('||column_name||') store as (tablespace ts_data2) ;'
from dba_lobs
where tablespace_name = 'TS_DATA1'
order by 1
/
spool off
ed go.tmp

They can be moved back afterwards, once the original tablespace has been shrunk, if desired.

One thing to watch out for is user tablespace quotas during the moves. If you get errors like this:

ORA-01950: no privileges on tablespace 'TS_DATA2'

You either need to temporarily grant unlimited tablespace privilege to the user affected, or grant them a quota on the new tablespace.

1) To tar files up into a tarball:

tar cvfp - file1 file2 file3 > tarfile.tar

And to untar:

tar xvfp tarfile.tar

2) Now, same thing but with compress added in:

tar cvfp - file1 file2 file3 | compress > tarfile.tar.Z
zcat tarfile.tar.Z | tar xvfp -

3) And, the same thing using gzip rather than compress:

tar cvfp - file1 file2 file3 | gzip > tarfile.tar.gz
gunzip -c tarfile.tar.gz | tar xvfp -

(Or, if you prefer, “gzip -cd” is the same as “gunzip -c”)

Gzip compresses down in size slightly better than compress, but tends to take slightly longer to do so.