Almost every time I connect on a new client server, I find that the Oracle logfiles are not properly and/or not totally purged which, one day or another, leads to a FS > XX % which triggers a page, wake a DBA up, etc ...
I must admit that the Oracle logfile management may be confusing and this is what this blog is meant for: to make the Oracle logfile management clear and offer few short scripts to efficiently purge / rotate all the Oracle logfiles once for all.
1/ A brief history of the Oracle logfile management
Before 11g, all logfiles were text based and Oracle had no mechanism to purge / rotate its logfiles which used to trigger endless discussions between the DBA and the Sys Admin :
From 11g onwards, Oracle manages its logfiles in XML format, its tracefiles as .trc and .trm files and these ones are purged by a tool named ADR (Automatic Diagnostic Repository). I guess we all thought that this new feature would resolve all our previous troubles related to the logfiles and well ... not really. Indeed, Oracle also maintains text based logfiles (like the before 11g logfiles) and audit files which are both are not managed by ADR. Also worth mentioning that the Grid Infrastructure has a slightly different behavior.
2/ What logfiles are managed by ADR
Let's start by summing up of what is purged / rotated by ADR in the below table:
| Component | XML and *.tr[cm] logfiles | Text based logfiles | Audit files |
|---|
| Database Homes |
Yes |
No |
No |
| GI Homes |
Yes, for some |
No |
No |
Not that much, huh ? I do agree and this is not a bug but a feature as documented in "
Adrci Purge Does Not Clear the Text-formatted Alert.Log located in the Trace Folder (Doc ID 751082.1)" and we'll be talking about the orange remark later on.
3/ Logrotate to complete ADR
It is clear from the above table that ADR needs help to finally purge / rotate all the Oracle logfiles and then to have a
Yes in each cell of the table.
I strongly recommend using
logrotate and in some case a bit of shell in a crontab.
Indeed logrotate is :
- Available on any Unix
- Easy to learn
- Well documented
- Most likely already running on your system so you only have to add a small configuration file in /etc/logrotate.d for the Oracle logfiles and you are good to go !
- . . . name it . . .
Before jumping in a simple logrotate config file, let's remind that the non-ADR managed logfiles are located in a directory pointed by the Oracle parameter
diagnostic_dest:
SQL> show parameter diag
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle
SQL>
And you'll find the text based Oracle logfiles
subdirectories which are built as below:
<diagnostic_dest>/diag/<component>/<DB_UNIQUE_NAME>/<INSTANCE_NAME>/trace/*.log -- for a database
<diagnostic_dest>/diag/<component>/<HOSTNAME>/<LISTENER_NAME>/trace/*.log -- for a listener
On a well standardized system, you'll then find ALL these trace files to rotate with a simple command:
[oracle@exadatadb01]$ ls /u01/app/oracle/diag/*/*/*/trace/*.log
. . .
[oracle@exadatadb01]$
We can then purge / rotate ALL these files in a few lines logrotate config file:
[oracle@exadatadb01]$ cat oracle
/u01/app/oracle/diag/*/*/*/trace/*.log
{
daily <= Consider rotating the logs every day
rotate 7 <= Keep 7 versions before purging
compress <= Compress the rotated logs
copytruncate <= Copy and truncate the logfiles to keep the same inode
missingok <= Do not raise any alert if logrotate finds no logfile
nodateext <= Put a number instead of a date as extension
size 20M <= Rotate the logfile if it is bigger than 20 M
}
[oracle@exadatadb01]$
This is an example, logrotate can rotate weekly, monthly, keep 60 versions of the rotated logs, etc . . . I personally like the size based rotation but it is totally up to you. What is important here is that you will purge all the text base trace files in few lines only. And as we used some "*" in the path, any new database trace files will be taken care by logrotate with no required action from anyone.
4/ The Database Homes
Now than we saw a simple logrotate config file, let's detail every case and see how it can apply.
4.1/ XML logfiles, *.tr[cm] logfiles
As we saw previously, the database Homes XML logfiles are automatically managed by ADR, you just have to set the SHORTP_POLICY And LONGP_POLICY in ADR to fit your needs and unless you hit a bug, the XML and trace files (*.tr[cm]) will be purged automatically.
4.2/ Text based logfiles
These ones have to be purged by you then as we saw previously, I recommend using logrotate to easily purge and rotate them then a simple config file like this one would do the job for all the databases, for all potential listeners running on your Database Homes and also for all coming databases as you may use the same diagnostic_dest directory for all the databases:
[oracle@exadatadb01]$ cat oracle
/u01/app/oracle/diag/*/*/*/trace/*.log
{
daily
rotate 7
compress
copytruncate
missingok
nodateext
size 20M
}
[oracle@exadatadb01]$
4.3/ Audit files
You have to manually purge the audit files; they are pointed by the audit_file_dest Oracle parameter:
SQL> show parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/DB_NAME/adump
SQL>
Same as previously, as you may have everything well standardized on your system, you can find ALL the audit files on a server with a simple command:
[oracle@exadatadb01]$ find /u01/app/oracle/admin/*/adump
. . .
[oracle@exadatadb01]$
And you will purge them with a single command as well (the below example purges the audit files older than 7 days, modify it to suit your needs):
[oracle@exadatadb01]$ find /u01/app/oracle/admin/*/adump -type f -name '*.aud' -mtime +7 -delete
[oracle@exadatadb01]$
You can integrate this purge in the logrotate config file in a firstaction or lastaction section as below:
[oracle@exadatadb01]$ cat oracle
/u01/app/oracle/diag/*/*/*/trace/*.log
{
daily
rotate 7
compress
copytruncate
missingok
nodateext
size 20M
lastaction
find /u01/app/oracle/admin/*/adump -type f -name '*.aud' -mtime +7 -delete
endscript
}
[oracle@exadatadb01]$
The only thing is that the firstaction / lastaction logrotate parts are executed only if logrotate finds a logfile to rotate so in case it doesn't (in my example if one day there is no logfile bigger than 20MB to rotate), the audit files won't be purged. It may or may not be an issue for you but I recommend using a crontab script for these audit files to be 100% sure they are purged every day as you may have a lot of these files. To cron it, I personally prefer copying a small script in /etc/cron.daily/ than using the crontab. Indeed, I find it more handy.
[root@exadatadb01 ~]# cat /etc/cron.daily/oracle
#!/bin/bash
# Fred Denis -- September 5th 2018
# Delete more than 3 days old audit files
#
find /u01/app/oracle/admin/*/adump -type f -name '*.aud' -mtime +3 -delete
[root@exadatadb01 ~]#
5/ The GI Homes
If you run a GI, you'll also have to take care of the GI's logfiles on top of the database Homes's one.
5.1/ XML logfiles, *.tr[cm] logfiles
From 11g onwards, the GI logfiles are XML based and the purge setting is set in ADR
but there is no automatic process to run the purge as documented in "
How to purge GI files from ADR HOME in 12.1.0.2 and above (Doc ID 1969131.1)" (I have to confess that I fell from my chair when I first discovered this). We then have to manually execute the ADRCI "purge" command in order to delete these trace files. As specified in the previous MOS note:
The correct way and only supported way to do this removal is with the ADRCI command line utility's PURGE command. (It might be tempting to do it with a native Operating System command such as UNIX 'rm', but doing it that way does not provide the proper coordination with other programs that are using the ADR home.)
I did some research on the subject and I found that it is indeed documented in the
11g,
12c and
18c documentation:
Some Oracle products, such as Oracle Database, automatically purge diagnostic data at the end of its life cycle. Other products and components require you to purge diagnostic data manually with this command. You can also use this command to purge data that is due to be automatically purged.
It may look kind of mysterious but Note 1969131.1 is a bit more precise about the "Other products . . . require you to purge diagnostic data manually with this command.":
Unlike the Oracle database, GI does not have a background process that purges old ADR data automatically. All GI daemons use a trace file rotation scheme that limits each daemon to a fixed total amount of trace data, but command line programs (such as crsctl) do not.
Note: You may find more details on how the GI deamons manage their logfiles size-based rotation in
this documentation
After checking on my GI Homes, I found that ASM and MGMT ONLY are automatically purged so not the CRS home nor any listener nor anything else (you can see those who are automatically purged checking the LAST_AUTOPRG_TIME column from adrci show control).
This then explains why the below notes are indeed bugs as ASM automatic purge is supposed to work:
- ADR Auto Purge Not Working In ASM Instance (Doc ID 1191704.1)
- Bug 9530322 - ADR auto purge does not occur in ASM instance or standby database (Doc ID 9530322.8)
Having said that, it is clear that GI does not automatically purges all its logfiles and as you want to go with a supported solution, you have to schedule a purge command to run on a regular basis
for every GI ADR home except those that are GI daemon based and . . . there's quite a few as shown on the below example:
[grid@exadatadb01 ~]$ adrci
ADRCI: Release 12.1.0.2.0 - Production on Sun Nov 4 18:35:52 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/grid"
adrci> show home
ADR Homes:
diag/asmtool/user_root/host_2411203453_82
diag/asmtool/user_grid/host_2411203453_82
diag/asm/+asm/+ASM1
diag/crs/exadatadb01/crs
diag/tnslsnr/exadatadb01/listener_scan1
diag/tnslsnr/exadatadb01/listener
diag/tnslsnr/exadatadb01/listener_scan3
diag/tnslsnr/exadatadb01/listener_scan2
diag/clients/user_root/host_2411203453_82
diag/clients/user_grid/host_2411203453_82
diag/clients/user_oracle/host_2411203453_82
adrci>
I found no exhaustive list of daemon based GI component and as I do not want to have some ADR homes hard coded in case of another component comes into play one day or another, I then use these 2 lines to execute a "purge" on all the ADR homes:
ASM_OWN=`ps -ef | grep pmon | grep asm | awk '{print $1}'`
su - $ASM_OWN -c '. oraenv <<< `ps -ef | grep pmon | grep asm | sed s"/^.*+/+/" | head -1` > /dev/null; for A in `adrci exec="show home" | grep -v "ADR Homes:"`; do adrci exec="set home $A; show home; purge -age 10080;"; done'
Note: I save the ASM instance owner (which is the GI installation owner) and then su to it as I run all the purge scripts as root
Note 2: I force a "purge -age 10080" (10080 being the default) as I found that a simple "purge" was not working properly in some cases
Note 3: you may want to "grep -v ASM | grep -v MGMT" as they are both supposed to be automatically purged but I personally haven't for few reasons:
- I found no exhaustive list of daemon based GI component so I may miss one even if I "grep -v ASM | grep -v MGMT"
- A patch or upgrade may add or remove one daemon based GI component
- An extra purge of these Homes won't hurt
- I also found many bugs related to these purges not happening that these extra purges may be very beneficial at the end of the day
I can now include this purge in my /etc/cron.daily/oracle cron script:
[root@exadatadb01 ~]# cat /etc/cron.daily/oracle
#!/bin/bash
# Fred Denis -- September 5th 2018
# Delete more than 3 days old audit files
#
find /u01/app/oracle/admin/*/adump -type f -name '*.aud' -mtime +3 -delete
# Added the GI XML and *.tr* files to be purged using adrci purge as specified in
# How to purge GI files from ADR HOME in 12.1.0.2 and above (Doc ID 1969131.1)
ASM_OWN=`ps -ef | grep pmon | grep asm | awk '{print $1}'`
su - $ASM_OWN -c '. oraenv <<< `ps -ef | grep pmon | grep asm | sed s"/^.*+/+/" | head -1` > /dev/null; for A in `adrci exec="show home" | grep -v "ADR Homes:"`; do adrci exec="set home $A; show home; purge -age 10080;"; done'
5.2/ Text based logfiles
For the text base logfiles, same as for the databases, we can list them all checking the diagnostic_dest parameter:
SQL> show parameter diag
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/grid
SQL>
And then get all the logfiles to rotate using few stars in the path:
[grid@exadatadb01 ~]$ ls /u01/app/grid/diag/*/*/*/trace/*.log
. . .
[grid@exadatadb01 ~]$
And we just have to add the path to the already made logrotate config file for the database Homes:
[oracle@exadatadb01]$ cat oracle
/u01/app/oracle/diag/*/*/*/trace/*.log /u01/app/grid/diag/*/*/*/trace/*.log
{
daily
rotate 7
compress
copytruncate
missingok
nodateext
size 20M
}
[oracle@exadatadb01]$
And you are done !
5.3/ Audit files
Same as previously, let's check the ASM audit_file_dest parameter:
SQL> show parameter audit_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/12.1.0.2/grid/rdbms/audit
SQL>
And add a find command to purge then in the already existing
/etc/cron.daily/oracle cron script:
[root@exadatadb01 ~]# cat /etc/cron.daily/oracle
#!/bin/bash
# Fred Denis -- September 5th 2018
# Delete more than 3 days old audit files
# This is on top of the logrotate configuration which is in /etc/logrotate.d/oracle
#
find /oracle/*/admin/*/adump -type f -name '*.aud' -mtime +3 -delete
find /u01/app/12.1.0.2/grid/rdbms/audit -type f -name '*.aud' -mtime +3 -delete
# Added the GI XML and *.tr* files to be purged using adrci purge as specified in
# How to purge GI files from ADR HOME in 12.1.0.2 and above (Doc ID 1969131.1)
ASM_OWN=`ps -ef | grep pmon | grep asm | awk '{print $1}'`
su - $ASM_OWN -c '. oraenv <<< `ps -ef | grep pmon | grep asm | sed s"/^.*+/+/" | head -1` > /dev/null; for A in `adrci exec="show home" | grep -v "ADR Homes:"`; do adrci exec="set home $A; show home; purge -age 10080;"; done'
[root@exadatadb01 ~]#
Easy peasy !
6/ Sum up
6.1/ Table
Let's now have a look at what the table on top of this document now looks like (How are the Oracle logfiles purged / rotated):
| Component |
XML and *.tr[cm] logfiles |
Text based logfiles |
Audit files |
| Database Homes |
ADR |
/etc/logrotate.d/oracle |
/etc/cron.daily/oracle |
| GI Homes |
/etc/cron.daily/oracle |
/etc/logrotate.d/oracle |
/etc/cron.daily/oracle |
An all green table is always better, isn't it ?
6.2/ Scripts
As a reference, please find the the scripts we built in this blog:
6.2.1/ /etc/logrotate.d/oracle
[root@exadatadb01 ~]# cat /etc/logrotate.d/oracle
/u01/app/oracle/diag/*/*/*/trace/*.log /u01/app/grid/diag/*/*/*/trace/*.log
{
daily
rotate 7
compress
copytruncate
missingok
nodateext
size 20M
}
[root@exadatadb01 ~]#
6.2.2/ /etc/cron.daily/oracle
Please keep in mind that this script has to be executable (
chmod u+x /etc/cron.daily/oracle)
[root@exadatadb01 ~]# cat /etc/cron.daily/oracle
#!/bin/bash
# Fred Denis -- September 5th 2018
# Delete more than 3 days old audit files
# This is on top of the logrotate configuration which is in /etc/logrotate.d/oracle
#
find /u01/app/oracle/admin/*/adump -type f -name '*.aud' -mtime +3 -delete
find /u01/app/12.1.0.2/grid/rdbms/audit -type f -name '*.aud' -mtime +3 -delete
# Added the GI XML and *.tr* files to be purged using adrci purge as specified in
# How to purge GI files from ADR HOME in 12.1.0.2 and above (Doc ID 1969131.1)
ASM_OWN=`ps -ef | grep pmon | grep asm | awk '{print $1}'`
su - $ASM_OWN -c '. oraenv <<< `ps -ef | grep pmon | grep asm | sed s"/^.*+/+/" | head -1` > /dev/null; for A in `adrci exec="show home" | grep -v "ADR Homes:"`; do adrci exec="set home $A; show home; purge -age 10080;"; done'
#********************************************************************************#
# E N D O F S O U R C E *#
#********************************************************************************#
[root@exadatadb01 ~]#
7/ A word on logrotate
Logrotate config files have to be owned by root and located in /etc/logrotate.d. I personally build my logrotate config file as oracle in /home/oracle and I copy it when ready:
[root@exadatadb01 ~]# cp /home/oracle/oracle /etc/logrotate.d/.
I recommend testing your logrotate config file before deploying them, you can test them (see everything that logrotate would do without doing it) with the -d option:
[oracle@exadatadb01 ~]$ logrotate -d oracle -s /tmp/ostatus
reading config file oracle
reading config info for /u01/app/oracle/diag/*/*/*/trace/*.log /u01/app/grid/diag/*/*/*/trace/*.log
. . .
[oracle@exadatadb01 ~]$
Note: You have to specifiy a status file with the -s option if you test tour logrotate config file as a non root user.
You may also want to force a logrotate execution with the -f option:
[root@exadatadb01 ~]# logrotate -f /etc/logrotate.d/oracle
. . .
[root@exadatadb01 ~]#
Note: logrotate will rotate every logfile even if it doesn't think it is necessary
7/ If you have RAC / Exadata
These purge / rotate configuration files / scripts act locally only so you have to deploy them on each node of your RAC / Exadata.
I hope you enjoyed this article and you could use it on your servers to totally remove the FS full pages due to the Oracle logfiles !
