Rotate 11G XE database and listener logs

Here is how you can rotate 11G XE database and listener logs on Linux using logrotate.

If you use SELinux it might prevent logrotate working in oracle user home. That is why we move logs out of oracle user home. For that create new directory as root:

mkdir -p /var/log/oracle/admin/XE/adump

Change directory owner to oracle and group to dba:

chown -R oracle:dba /var/log/oracle

Start SQL*Plus and connect to the database as SYS specifying the SYSDBA role. For example:

sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

You can run query to see where logs are currently stored

SELECT * FROM V$DIAG_INFO;

Change diagnostic_dest:

ALTER system SET diagnostic_dest='/var/log/oracle' scope=spfile;

Change audit_file_dest:

ALTER system SET audit_file_dest='/var/log/oracle/admin/XE/adump' scope=spfile;

Stop and start database so new parameter values are taken in to use:

shutdown immediate;
startup;

You can run query to check changes

SELECT * FROM V$DIAG_INFO;

Exit from SQLPlus. Check is alert log created to new directory

ls -l /var/log/oracle/diag/rdbms/xe/XE/alert

Next we need move listener logs. Edit listener.ora file

nano /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora

Add end of file:

ADR_BASE_LISTENER = /var/log/oracle

Stop and start listener so new parameter value is taken in to use:

lsnrctl stop
lsnrctl start

Note! You might need stop and start listener as oracle user.

Listener logs can be now found /var/log/oracle/diag/tnslsnr/host_name/listener/ where host_name is your machine host name.

You can backup old ADR directory and remove it.

tar -cvzf oracle-xe-old-adr.tar /u01/app/oracle/diag
rm -rf /u01/app/oracle/diag

As last step create config file for logrotate:

nano /etc/logrotate.d/oracle-xe

Place to file:

/var/log/oracle/diag/rdbms/xe/XE/trace/alert_XE.log {
  firstaction
    /bin/tar cpf /var/log/oracle/diag/rdbms/xe/XE/trace/hist_trace_trc.tar /var/log/oracle/diag/rdbms/xe/XE/trace/*.trc --remove-files > /dev/null 2>/dev/null || true
    /bin/chown oracle:dba /var/log/oracle/diag/rdbms/xe/XE/trace/hist_trace_trc.tar > /dev/null 2>/dev/null || true
    /bin/tar cpf /var/log/oracle/diag/rdbms/xe/XE/trace/hist_trace_trm.tar /var/log/oracle/diag/rdbms/xe/XE/trace/*.trm --remove-files > /dev/null 2>/dev/null || true
    /bin/chown oracle:dba /var/log/oracle/diag/rdbms/xe/XE/trace/hist_trace_trm.tar > /dev/null 2>/dev/null || true
    /bin/tar cpf /var/log/oracle/admin/XE/adump/hist_adump_aud.tar /var/log/oracle/admin/XE/adump/*.aud --remove-files > /dev/null 2>/dev/null || true
    /bin/chown oracle:dba /var/log/oracle/admin/XE/adump/hist_adump_aud.tar > /dev/null 2>/dev/null || true
  endscript
  missingok
  notifempty
  sharedscripts
  daily
  create 0640 oracle dba
  rotate 7
}
/var/log/oracle/diag/rdbms/xe/XE/alert/log.xml {
  missingok
  notifempty
  daily
  create 0640 oracle dba
  rotate 7
}
/var/log/oracle/diag/tnslsnr/host_name/listener/alert/log.xml {
  missingok
  notifempty
  daily
  create 0640 oracle dba
  rotate 7
}
/var/log/oracle/diag/tnslsnr/host_name/listener/trace/listener.log {
  missingok
  notifempty
  daily
  create 0640 oracle dba
  rotate 7
}
/var/log/oracle/diag/rdbms/xe/XE/trace/hist_trace_trc.tar {
  missingok
  notifempty
  nocreate
  daily
  rotate 7
}
/var/log/oracle/diag/rdbms/xe/XE/trace/hist_trace_trm.tar {
  missingok
  notifempty
  nocreate
  daily
  rotate 7
}
/var/log/oracle/admin/XE/adump/hist_adump_aud.tar {
  missingok
  notifempty
  nocreate
  daily
  rotate 7
}

Remember replace host_name in listener log path with correct host name.

In this example I keep logs seven days. If you like change that or other parameters check logrotate help.

Now you just wait and see are logs rotated when next daily cron job runs.