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:
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.
| 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 :
subdirectories which are built as below:
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 . . .
SQL> show parameter diag NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostic_dest string /u01/app/oracle SQL>And you'll find the text based Oracle logfiles
<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 listenerOn 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:
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:
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:
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:
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 can now include this purge in my /etc/cron.daily/oracle cron script:
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 documentationAfter 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 rootNote 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):
An all green table is always better, isn't it ?
| 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:
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:
You may also want to force a logrotate execution with the -f option:
[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 !
Good explaination
ReplyDeleteExcellent content
ReplyDeleteHi, Excellent post. I have a question, This part of the script is showing an error: ASM_OWN=`ps -ef | grep pmon | grep asm | awk '{print $1}'`
ReplyDeletesu - $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'
it no recognizes the command adrci, I think it is because the . oraenv +ASM setting is not working, could be? Regards
Hi Luis,
DeleteCan you try each command manually to narrow the issue ?
Thanks,
Fred
Fred, don't you purge core files?
ReplyDeleteWe are getting a lot of them...
Core are not supposed to happen so I don't purge them by default.
DeleteIf you have lots of them, you should add a line in logrotate or Cron script to purge them the time you open a SR to Oracle support about this to fix the issue causing these cores and then remove the purge line once fixed.
And what about these audit files
ReplyDelete/u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/audit
I don't see you mention them in your article, and to be honest I don't know why they are generated there...
Please check: 4.3/ Audit files
DeleteYes, I had already checked that, but there is no DBNAME in the path in mine... Please check it.
DeleteAnd the audit_file_dest does not point in there...
It is like an audit for when SYS does something on the DB.
DeleteIf your audit path is /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/audit then:
Deletefind /u01/app/oracle/product/*/*/rdbms/audit -type f -name '*.aud' -mtime +3 -delete
Will do the trick and remove any audit file older than 3 days.
I put * instead of the version and the home name then it will still work the day you have something like /u01/app/oracle/product/21.0.0.0/dbhome_2/rdbms/audit
The audit feature has been introduced in 11g, you can disable SYS audit using the audit_sys_operations parameter, here: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/AUDIT_SYS_OPERATIONS.html#GUID-58176267-238C-40B5-B1F2-BB8BB9518950
DeleteThe thing I am trying to say is that my audit is not /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/audit but I am getting .aud files in there...
ReplyDeleteIt seems that only SYS related files, is that normal?
That seems to be it, thanks.
ReplyDeleteThank you for high quality content. I'm tired sometime of oracle logfiles ))
ReplyDeleteHi
ReplyDeleteIn one of my RAC environment grid mount point occupied more space.I identified listener log has occupied more space i.e, 68GB log. which is located in this path:/grid/app/base/diag/tnslsnr/hostname/listener/trace.
How can i rotate or delete or trim that log without any effect.
Please suggest me
Thanks in advance
Hi,
DeleteCreate a file like this:
$ cat /etc/logrotate.d/rotate_my_listener
/grid/app/base/diag/tnslsnr/hostname/listener/trace/*.log
{
daily
rotate 7
compress
copytruncate
missingok
nodateext
size 1024M
}
Test it (and check that the test loks good, test should let you know what it would do):
$ logrotate -d rotate_my_listener -s /tmp/ostatus
=> ignore the "-s /tmp/ostatus" option if you run it as root, just use it if you run it as another user than root
Then logrotate will rotate it on a daily basis is size > 1GB keeping 7 compressed versions of it
You can force a rotation right now like that (as root):
# logrotate -f /etc/logrotate.d/rotate_my_listener
PS: update with the correct hostname here:
Delete/grid/app/base/diag/tnslsnr/hostname/listener/trace/*.log
Or put a star then you can use the same exact file on all of your hosts:
/grid/app/base/diag/tnslsnr/*/listener/trace/*.log
Hi,
ReplyDeleteStarting with 18c, you can add rotation parameters to your listener.ora, for example :
LOG_FILE_NUM_LISTENER=3
LOG_FILE_SIZE_LISTENER=128