As you know, Oracle 11G generates *.trc and *.trm log files as well as constantly generating alert log and listener log entries. Instead of writing shell scripts, you can use the 11G ADRCI utility and the linux logrotate command to manage these logs automatically.
Using ADRCI
ADRCI is fairly simple. Just fire it up and set the parameters accordingly. Note that you need to do this for each database on the host because they have separate ADRCI Homes.
Set the database environment as usual:
. oraenv
ORACLE_SID = [PRODDB] ?
The Oracle base remains unchanged with value /u01/app/oracle
Start up adrci:
adrci
ADRCI: Release 11.2.0.2.0 - Production on Fri Oct 5 08:07:40 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle/admin/PRODDB"
Show the current settings for the log management:
adrci> show control
ADR Home = /u01/app/oracle/admin/PRODDB/diag/rdbms/PRODDB/PRODDB: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 1117245929 720 8760 2012-06-06 09:08:17.132534 +10:00 2012-10-01 00:02:57.274304 +10:00 1 2 80 1 2012-06-06 09:08:17.132534 +10:00
1 rows fetched
Note the figures in red – these are the defaults, in hours.
This means that trace and dump files are kept for 30 days and incidents and health reports are kept for a year.
To change these to a more reasonable level (7 days for trace and dump files, 1 month for the others):
adrci> set control (SHORTP_POLICY=168);
adrci> set control (LONGP_POLICY=720);
The SHORTP_POLICY determines how long things like core dumps and trace files are kept for.
The LONGP_POLICY determines how long incidents and some health warnings are kept for.
Once set, you need to issue a 'purge' command to enable it (see below) otherwise it will wait for the next time it checks, which could be hours.
adrci> show control
ADR Home = /u01/app/oracle/admin/PRODDB/diag/rdbms/PRODDB/PRODDB: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 349985628 168 720 2012-09-12 12:47:41.695214 +10:00 2012-09-30 00:09:18.714054 +10:00 2012-09-12 12:48:37.311103 +10:00 1 2 80 1 2012-07-26 09:12:35.866728 +10:00
1 rows fetched
That’s it for the dump and trace files. They will now be automatically deleted after the specified time.
Note you can also delete existing ones if there are too many using the purge command:
adrci> purge -age 10080 -type trace
Note that this figure is in minutes, not hours (wonderful!). This will delete any trace files older than 7 days.
If there is more than one database on the host, you must set
the environment and adcri base accordingly before running the commands:
. oraenv
PRODDB1
adrci> set
base /u01/app/oracle/admin/PRODDB1
adrci>
show homes
ADR
Homes:
diag/rdbms/proddb/PRODDB
diag/rdbms/proddb1/PRODDB1
adrci>
show control
DIA-48448:
This command does not support multiple ADR homes
If you see
this, you need to set the homepath to run commands:
adrci>
set homepath diag/rdbms/proddb1/PRODDB1
adrci>
show control
ADR
Home = /u01/app/oracle/admin/PRODDB1/diag/rdbms/proddb1/PRODDB1:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME
LAST_AUTOPRG_TIME
LAST_MANUPRG_TIME
ADRDIR_VERSION
ADRSCHM_VERSION
ADRSCHMV_SUMMARY
ADRALERT_VERSION CREATE_TIME
--------------------
-------------------- --------------------
----------------------------------------
---------------------------------------- ----------------------------------------
-------------------- -------------------- --------------------
-------------------- ----------------------------------------
3297102354 168 720 2012-10-30 11:56:36.369326
+11:00 2012-10-30 11:16:13.086140
+11:00
1 2 80 1 2012-07-20 15:01:07.750391
+10:00
1 rows fetchedUsing linux logrotate
This is a bit more involved and needs some configuration files and a cron entry.
First, determine the name and location of the alert log and the listener log files:
sqlplus “/ as sysdba”
show parameter back
background_dump_dest string /u01/app/oracle/admin/PRODDB/diag/rdbms/PRODDB/PRODDB/trace
lsnrctl stat
Listener Log File /u01/app/oracle/product/11.2.0.2/db_1/log/diag/tnslsnr/linux01/listener/alert/log.xml
Check to see the sizes:
ls -al /u01/app/oracle/admin/PRODDB/diag/rdbms/PRODDB/PRODDB/trace/alert_PRODDB.log
-rw-r----- 1 oracle oinstall 8759629 Oct 5 08:01 /u01/app/oracle/admin/PRODDB/diag/rdbms/PRODDB/PRODDB/trace/alert_PRODDB.log
ls -al /u01/app/oracle/product/11.2.0.2/db_1/log/diag/tnslsnr/linux01/listener/alert/log.xml
-rw-r----- 1 oracle oinstall 4064218 Oct 5 08:01 /u01/app/oracle/product/11.2.0.2/db_1/log/diag/tnslsnr/linux01/listener/alert/log.xml
Create a logrotate.status file:
vi /home/oracle/dba/ora_logs/logrotate.status
with these contents:
logrotate state -- version 2 "/u01/app/oracle/admin/PRODDB/diag/rdbms/PRODDB/PRODDB/trace/alert_PRODDB.log" 2012-09-10
Make the date about a month in the past. This will be updated when the job runs.
Now make a oracle_logrotate.conf file:
vi /home/oracle/dba/ora_shells/oracle_logrotate.conf
With these contents:
# Logrotate configuration file for Oracle Alert Logs
#
# missingok - do not issue an error message if file is missing
# copytruncate - copy the file and truncate the original instead of deleting it
# rotate 7 - keep 7 copies, then delete old ones
# compress - compress old logs
# notifempty - don't do anything if the file is empty
#
/u01/app/oracle/admin/PRODDB/diag/rdbms/PRODDB/PRODDB/trace/alert_PRODDB.log {
missingok
copytruncate
rotate 7
compress
notifempty
}
This should be all that is needed – now run it and check the log sizes again:
/usr/sbin/logrotate -f -s /home/oracle/dba/ora_logs/logrotate.status /home/oracle/dba/ora_shells/oracle_logrotate.conf
ls -al /u01/app/oracle/admin/PRODDB/diag/rdbms/PRODDB/PRODDB/trace/alert_PRODDB.log
-rw-r----- 1 oracle oinstall 0 Oct 5 08:01 /u01/app/oracle/admin/PRODDB/diag/rdbms/PRODDB/PRODDB/trace/alert_PRODDB.log
The size should be 0.
Add this as a regular cron job.
This runs once a week at 09:00:
00 09 * * 0 /usr/sbin/logrotate -f -s /home/oracle/dba/ora_logs/logrotate.status /home/oracle/dba/ora_shells/oracle_logrotate.conf 1>/home/oracle/dba/ora_logs/logrotate.log 2>&1
Repeat the process for the listener log:
Create the status file:
vi /home/oracle/dba/ora_logs/logrotate_listener.status
logrotate state -- version 2 “/u01/app/oracle/product/11.2.0.2/db_1/log/diag/tnslsnr/linux01/listener/alert/log.xml” 2012-9-10
Create the conf file:
vi /home/oracle/dba/ora_shells/oracle_listener_logrotate.conf
/u01/app/oracle/product/11.2.0.2/db_1/log/diag/tnslsnr/linux01/listener/alert/log.xml
{
missingok
copytruncate
rotate 7
compress
notifempty
}
Run the command:
/usr/sbin/logrotate -f -s /home/oracle/dba/ora_logs/logrotate_listener.status /home/oracle/dba/ora_shells/oracle_listener_logrotate.conf
Check the size:
ls -al /u01/app/oracle/product/11.2.0.2/db_1/log/diag/tnslsnr/linux01/listener/alert/log.xml
-rw-r----- 1 oracle oinstall 0 Oct 5 08:01 /u01/app/oracle/product/11.2.0.2/db_1/log/diag/tnslsnr/linux01/listener/alert/log.xml
Create a cron job:
00 09 * * 0 /usr/sbin/logrotate -f -s /home/oracle/dba/ora_logs/logrotate_listener.status /home/oracle/dba/ora_shells/oracle_listener_logrotate.conf 1>/home/oracle/dba/ora_logs/logrotate_listener.log 2>&1
Finally (?) delete the audit logs, using the old-fashioned find and remove :
00 12 * * 0 /usr/bin/find /u01/app/oracle/admin/PRODDB/adump -name '*.aud' -mtime +33 -exec rm {} \;
The logs should now rotate and be managed automatically.
