Oracle database housekeeping methods – Unix log rotation

January 10, 2014 by Leave a comment 

Since Oracle Database 11g, all traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the ADR, a file-based Automatic Diagnostic Repository for simplified database diagnostic data management. In spite the housekeeping or purging of those files got not simplified, unfortunately the new Oracle utility ADRCI can not purge all the files that Oracle database generates in ADR. Thus, you should use the other OS methods and tools to accomplish Oracle database housekeeping.

This time I share a method of rotating Oracle database log files using Unix logrotate mechanism. The below example will rotate the Oracle alert.log and listener.log files in the following way:

– Rotation is based on the file size (you can use “M” or “k” for Mb and Kb)
– Unix Logrotate utility creates a copy of the original log file, then truncates it
– The rotated files will be compressed with Unix gzip utility with the following name <logfile>-yyyymmdd.gz
– Only recent 20 rotated log files are kept
– Rotation frequency is default = weekly
– No errors generated if the log file is missing

Unix Logrotate configuration

– As root Unix user create an “oracle” log rotation configuration file in /etc/logrotate.d directory
– Add Unix logrotate configuration information in that file – one section per log file:

/opt/oracle/MCELT/diag/rdbms/mcelt/MCELT/trace/alert_MCELT.log {
notifempty
size 20M
copytruncate
dateext
missingok
rotate 20
compress
}

/opt/oracle/MCELT/diag/tnslsnr/deherdzcln001/listener_mcelt/trace/listener_mcelt.log {
notifempty
size 20M
copytruncate
dateext
missingok
rotate 20
compress
}

Notes:
– If more than 1 database instance is running, add the above mentioned lines for each database.
– Always adjust the actual location of the alert log and listener log files.

Run Unix Logrotate

Run below mentioned command as root:

/usr/sbin/logrotate /etc/logrotate.conf

Notes:
– It should immediately rotate the compressed logs and name them <logfile>-yyyymmdd.gz
– The file ownership and rights should be identical to original log file
– To unzip the rotated file later do the following:
# gunzip alert_orakl11.log-20140108.gz

So, that was a housekeeping method for Oracle 11g database log files using Unix logrotate mechanism.

Enjoyed this article? Please share it with others using the social site of your choice:

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email