Log Maintenance is the task that always seems to be overlooked after you set up a new database, get the data migrated, and live through the night the database goes live. Logs are the many different files we use to determine if a benign warning message is a portent to something larger or something that is just wasting precious disk space.
The problem is one of our own desires. As administrators we need to have a deeper understanding of all the connected systems. Did the listener connect to a session? Was there a critical error in the database, and it stopped running? Are Oracle’s watchdog processes still alive?
Introduction of a Tool
Tracking several different types of logs that reside in multiple directories sprawled across the disk is a chore that has become cumbersome over time. Oracle recognized this and created a centralized platform called the “Automatic Diagnostic Repository” (ADR). We will concentrate on the Log Maintenance component of this much larger tool.
ADR is accessed via the Automatic Diagnostic Repository Command Interpreter (ADRCI). This utility will interact with the various diagnostic homes set via the DIAGNOSTIC_DEST parameter in the database, or if that parameter is not set, it will scan your $ORACLE_BASE directory.
Using ADRCI
Invoking ADRCI is as simple as:
oracle$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Tue Sep 27 15:07:50 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
The first thing you will likely want to do after invoking ADRCI is show all the directories that ADR maintains. This can be accomplished with the show homes command:
adrci> show homes
ADR Homes:
diag/rdbms/mydb1/mydb1
diag/rdbms/mydb2/mydb2
diag/tnslsnr/myserver/listener
diag/clients/user_oracle/host_123456789_21
diag/clients/user_oracle/host_123456789_23
Next, we will want to set the homepath and tell the utility that any further commands should only happen inside of this path:
adrci> set homepath diag/rdbms/mydb1/mydb1
Now that we have our homepath set, we can start looking at the actual log maintenance. The PURGE command is how we remove these records and files. The PURGE has a couple of different options:
Purging alert data older than 30 days would be accomplished by running the following command:
adrci> purge -age 43200 -type alert
Exceptions
Now, if you’ve gotten this far, you are wondering what the gotchas might be. There are 2 notable exceptions that will still require manual handling:
Database Alert logs
Listener Logs
Of course, with any exception, there is an exception – in 19c or higher, you can set the following listener.ora parameters to rotate the listener logs:
LOG_FILE_NUM_<Listener>
LOG_FILE_SIZE_<Listener>
Also, be aware that if you use different users for multiple instances of the database, or if you are using grid infrastructure, you may need to run this process as the various users who own the objects.
Conclusion
This blog should give you the basic components needed to create a database log maintenance solution. By iterating through the homepaths and purge types and then manually rotating and purging the Database Alert log and Listener log, you should be able to keep the logging in check and not get any surprise disk alerts.
As always if you have any additional questions or if we can help, contact us here. We would love to see how House of Brick can help you.
