Automatic Diagnostic Repository Management

With Release 11g Oracle introduced the concept of the Automatic Diagnostic Repository (ADR) which is an independent directory structure for all kinds of logs and traces for the database, listener and other Oracle products. The directory diag is located in $ORACLE_BASE so you should ensure that that variable has been set to the correct directory. If it’s not set the files will be stored in $ORACLE_HOME/diag.

With Oracle 11g it was a starting point and several parts of the oracle database, like Grid infrastructure were not aware of the new structure. With Oracle 12c now most logs, traces and cores are in the various directories underneath $ORACLE_BASE/diag (except for asmcmd which is using $ORACLE_BASE/log/diag!).

Logs and traces are often stored twice in this structure like the good old alert file is stored in $ORACLE_BASE/diag/rdbms/<DB_UNIQUE_NAME>/<ORACLE_SID>/alert as an xml-file and in the corresponding trace directory in the old ascii layout.

As many of you already experienced the ADR can become huge over the time. I’ve seen alert.log and listener.log with a size of several GByte which can bring the entire Oracle database environment to a stop as the filesystem gets full.

ADRCI

But one of the advantages of ADR is that it’s able to manage the files without human intervention. There is a purge mechanism associated with it so that old logs and traces are automatically removed. Unfortunately the default retention for most of those logs is one year! There are two parameters: SHORTP_POLICY (default 720 hours = 30 days) and LONGP_POLICY (default 8720 hours = 365 days).

Guess normally it’s not necessary to keep one year old logfiles online so you might want to change the parameters using the ADR command line interface:

% adrci

If you only have one database and no special settings there should only be one ADR-Home and you can continue changing the values immediate. If, like in my environment, you have several databases you need to select the environment first:

adrci> show homes 
diag/rdbms/rac112/RAC1122 diag/rdbms/rac112/TEST
diag/rdbms/rac112/RAC1121 diag/rdbms/ron112/RON112_2
diag/rdbms/ron112/RON1121 diag/rdbms/ron112/RON112_1 
adrci> set home
diag/rdbms/rac112/RAC1122

Now You can change the parameters for that specific environment:

adrci> 
show control 
ADR Home = /u01/app/oracle/diag/rdbms/rac112/RAC1122: 
************************************************************************* 
ADRID      SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME                     ... 
---------- ------------- ------------ --------------------------------- 
1122254562           720         8760 2011-01-11 13:08:51.956565 +01:00 ... 
1 rows fetched 
adrci> set control (SHORTP_POLICY = 168) 
adrci> set control (LONGP_POLICY = 720) 
adrci> exit

This will purge old logfiles, traces, etc. automatically and free up the space again. If you are in a hurry you can purge the files manually using adrci as well:

adrci> set home diag/rdbms/rac112/RAC1122 
adrci> purge

Listener.log

One more hint to the listener.log: If you have a webinterface you might encounter a huge listener.log file because every connect/disconnect is going to be logged. Most recently one of my customer had a 10 GB listener.log file and it filled within minutes.

In this case you better switch off logging for the listener at all:

% lsnrctl 
LSNRCTL> show log_status 
Connecting to (DESCRIPTION= (ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) 
LISTENER parameter “log_status” set to ON 
The command completed successfully 
LSNRCTL> set log_status off

This will help to free up you diag destination.

If you have any comments or annotations feel free contacting me.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top