Saturday, February 12, 2011

Is ADR really Automatic?

Oracle has consolidated the diagnostic information of database under one umbrella called as Automatic Diagnostic Repository (ADR). This repository is XML-based and that opens up new vistas for future Oracle diagnostic tools to manipulate the hierarchical data.

The beauty of ADR is that it cleanly separates out the diagnostic data churned out by different components, like RDBMS and ASM instances have their own corresponding ADR homes. Each ADR home then divides different types of traces and dumps in their corresponding and familiar sub-directories like alert file in alert directory and trace files from background processes, SQL traces etc in trace directories. All these homes corresponds to one ADR base which is set by initialization parameter DIAGNOSTIC_DEST.

ADR also maintains the client side tracing and creates it’s separate home under the same base. If the user accessing the database hasn’t got rights on the ADR base at the OS level, then the trace files are generated in that user’s home directory. In order to disable that give write rights to that user on ADR base at OS level, and if you want to disable the ADR client side tracing, put DIAG_ADR_ENABLED=OFF in sqlnet.ora file at the server.

There is much more the ADR and it needs improvement in many corners like respecting the automatic rotation of files through short and long policies, giving more control over generation of files, purging and rotation of all the trace files including legacy alert and listener log files, and also containing the huge number of files generated. These problems with ADR doesn’t justify the word ‘Automatic’ in ADR’s name IMHO.

No comments:

Collaborate14

Presenting at

My Book

Speaking at:

It's All About Passion

Oracle ACE. Exadata Certified Specialist, Oracle Blogger, Oracle DBA, OCP in 11g DBA and 10g DBA and working in Oracle products since 2001. Speaker at conferences like Oracle Open World, UKOUG, Collaborate, Insync etc around the globe. Currently working with Pythian Australia as Senior Oracle DBA.