As you know, since 11g we have a Automatic Diagnostic Repository (ADR). To better manage it, we also have a Command-line Interface, called ADRCI.
ADR contains all diagnostic information for database (logs, traces, incidents, problems, etc).
ADR Structure
ADRCI is a powerful tool, but unfortunately misunderstood and sub-used.
But I’m not going to retype all that Tim Hall already done for us. 😀
The objective of this post, however, isn’t to show all good from ADRCI, but share a how configure retention policiy and a quick script to clean logs from all homes in the server:
1. Setting Retention Policy:
First thing is to understand these two guys:
– LONGP_POLICY (long term) defaults to 365 days and relates to things like Incidents and Health Monitor warnings.
– SHORTP_POLICY (short term) defaults to 30 days and relates to things like trace and core dump files
They are setted by default with 720 hours (30 days) for the Short Term and 8760 hours (One year) for the long term category. See:
adrci> show control ADR Home = /u01/app/oracle/diag/rdbms/mydb/mydb: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- 1067873839 720 8760 2013-08-10 15:42:04.686159 +00:00 2016-04-25 20:53:28.159552 +00:00
We can change this by using the ADRCI command ‘set control’. Look at example for changing the retention to 15 days for the Short Term policy attribute:
(note it’s defined by hours!)
adrci> set control (SHORTP_POLICY=360) adrci> show control ADR Home = /u01/app/oracle/diag/rdbms/mydb/mydb: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- 1067873839 360 8760 2016-04-29 13:30:03.361811 +00:00 2016-04-25 20:53:28.159552 +00:00
Now let’s run a “Purge” using this parameter:
adrci> purge
And see last autopurge:
adrci> show control ADR Home = /u01/app/oracle/diag/rdbms/mydb/mydb: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- 1067873839 360 8760 2016-04-29 13:30:03.361811 +00:00 2016-04-29 13:30:29.153407 +00:00
2. Ad-Hoc Purge Script:
The manual execution basically uses “purge -age” clause. The important thing is that in this case you must user minutes instead of hours. Pay attention!
There is a lot of scripts on the net. But my personal script for ad-hoc/manual purges is:
AGE7DAYS=10080 AGE10DAYS=14400 AGE15DAYS=21600 AGE30DAYS=43200 PURGETARGET=$AGE15DAYS for f in $( adrci exec="show homes" | grep -v "ADR Homes:" ); do echo "Purging ${f}:"; adrci exec="set home $f; purge -age $PURGETARGET ;" ; done
That’s it!
Have a nice day!
Matheus.
Hello
The above variable script will pickup multiple home if multiple instance running on server ?
How to execute the above purge scripts
HI.
Greate article, do you know script for adrci on windows server?
Hi Eli, sure thing. You can use it on the same way you use the linux one (of course with some windows path adjustments).
Try navigating with your command prompt to the $ORACLE_HOME/bin
and from there you can ./adrci.exe.
Cheers!
brilliant! thanks
Pingback: Managing listener.log and log.xml with Linux Logrotate – GREPORA
Pingback: /bin/rm: cannot execute [Argument list too long] – GREPORA
Pingback: Scheduler Job for OS Audit Trail Cleanup Routine – GREPORA