Single Article

Rotate 11G XE database and listener logs

Category Oracle Database

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.

Comments

  • praveen · 24 Mar 2016  

    Valuable information thanks for sharing 

        
  • Jari Laine · 23 Oct 2012  

    Hi Damir,

    Ok.

    Thanks for sharing.

    Regards, Jari

        
  • Damir · 22 Oct 2012  

    Rotating alert log is not in mine solution-but this is just two lines of: 1) renam 2) touch without restarting (tested on 10g, 11g and NOT particularly on 11 XE) Better is in mine case is that for rotating listeners I do not have to restart them but all is online and the best of all, I find mine scripts easier to use-out of the box script and do not have to worry about anything. Hope now is all more clear and apologize in front if mine words has made different affect-didn't mean to.

    rg Damir Vadas

    http://damir-vadas.blogspot.com/

        
  • Jari Laine · 22 Oct 2012  

    Hi Damir,

    Could you please tell how your solution is better? Does it work for 11G XE? Does it rotate e.g. alert log?

    Regards, Jari

        
  • Damir · 21 Oct 2012  

    Better way:

    http://damir-vadas.blogspot.com/2010/05/rotate-listener-log.html

    and

    http://damir-vadas.blogspot.com/2010/05/rotate-listener-log-tns-12547-tns-12560.html

    Rg, Damir Vadas
    http://damir-vadas.blogspot.com

        

Global Right Column

Search

Archives

Subscribe in a Reader