Featured Database Articles

Purging Oracle Database's Alert Log with ADRCI - Usage and Warning

Oracle Database's ADR purge command is superior for purging alert log entries. Just be forewarned on how to actually use it since the desired or perceived outcome is not always apparent.

Alert Log Scraping with Oracle’s ADRCI Utility looks at extracting Oracle alert errors from the alert log and Rotating Oracle’s Alert Log with ADRCI discussed taking those extracted errors and making backup copies of them by day in separate directories. However, we still have a bunch of alert log information lying around, either in the log.xml file or in separate files under the diag directory structure. Towards the end of the second article there was mention of purging the old data from the alert log with a “PURGE –AGE” command that took the form of:

PURGE –AGE 7200 –TYPE ALERT;

While the syntax is correct the desired or perceived outcome is not always apparent. For this reason, let’s examine a simple example, looking what the alert log (log.xml) contains, issuing the purge command, and then looking again at the alert log.

3. Issue the purge command to attempt to remove data from the alert log. As the manuals state, purging is done for diagnostic data that is older than the amount of time (minutes) given in the purge command. So let’s attempt purging for diagnostic data that is over 1 day old (1440 minutes).

adrci> PURGE -age 1440 -type ALERT

4. Now all we need to do is look again at the alert log to see if there are any changes.

Very quickly, from the show alert command, we can see that the purge did not do what we might have expected or wished for--that it would remove data from the alert log for us. After all, the manuals state that the purge command will purge diagnostic data in the current ADR home according to purging policies (1440 minutes).

So what is really happening here is the question that needs to be answered. In a nutshell, the purge command only acts upon diagnostic data at the file level; not looking inside any file and the data it contains. Just because the alert log contains diagnostic data older than 1440 minutes, the file itself has been accessed and updated within one day so it is not purged.

Granted, if we changed our purge command to purge data that is older than one minute, clearly our alert log (log.xml) will be purged (removed). We can see this in another very simple example by looking at the files in the alert diagnostic data before and then after issuing the purge command:

Since there wasn’t any diagnostic data written to the alert log (log.xml) during the time of our little example, when we issue the purge command the whole alert log is removed from disk.

This may or may not be the desired result we want depending on how we rotate or want to clean alert logs from our system. Before I offer a suggestion, or you conclude there is really one additional piece of information you need to know.

Since many of us like to rotate alert logs or save alert information, it is good to know, since ADRCI works on the file level, what files are affected by the purge command. We know that the log.xml file (the default alert log file) is affected but let’s perform a little experiment to nail this down a bit.

Here we will create four additional alert log files; simulating typical naming conventions that I’ve often seen used when rotating alert log files; and see how they are affected by the purge command. Again we will list the files within the diag/alert area, check the date, issue the purge command, and then see what has happened to the files.

1. Look at the timestamp on the current simulated alert log files created. Here you can see that I’ve created an alert<date>.log, alert<date>.xml, a log<date>.xml, and anyfilename for the file names.

Very plainly you can see that three of the files were not affected by the purge command. The only files that were affected had a format where they were prefixed with “log” and had a suffix of “xml”. In my simple example I specifically used “log.” and “.xml” but you can have any file name that begins with “log” and ends with “xml”. For instance all of the following filenames work with the purge command.

And, as a warning, it probably isn’t advisable to name any file or directory under the diag alert directory structure with the log/xml pattern. For instance, if you were to create a directory, call it logxml, and then ran the purge command you’d get the following error:

The purge command within the ADRCI utility is very powerful and can help aid in the rotation of alert logs. Just remember that it only works at the file level and if that file has been accessed in the number of minutes you specify in the purge command. Also remember that since it acts on the file level do not, at least at this time, create structures that match the pattern the purge command is looking for--in particular anything prefixed with log and suffixed with xml.