Automatic Diagnostic Repository

The Automatic Diagnostic Repository (ADR) is a system-wide tracing and logging central repository for database diagnostic data. It includes the following items:

 Background trace files :  Background trace files store information from database

background processes. When a process detects an internal error, the process dumps

information about the error to its trace file. Some of the information written to a

trace file is intended for the database administrator, whereas other information is

for Oracle Support Services. Typically, database background process trace file

names contain the Oracle system identifier (SID), the background process name,

and the operating system process number

  An example of a trace file for the RECO process is mytest_reco_10355.trc.

Common Background Processes Generating Trace Files:

 

PMON (Process Monitor): Monitors the other background processes and performs process recovery.

SMON (System Monitor): Performs instance recovery when an instance fails.

LGWR (Log Writer): Writes redo log entries to disk.

DBWn (Database Writer): Writes modified blocks from the database buffer cache to data files.

CKPT (Checkpoint): Performs database checkpoints to synchronize the data files and control files.

Location of Background Trace Files:

Background trace files are typically located in the Automatic Diagnostic Repository (ADR).

The ADR home path can be obtained using the SHOW PARAMETER DIAGNOSTIC_DEST

 

Enabling and Disabling Tracing:

Tracing for background processes can be enabled or disabled dynamically using the ALTER SYSTEM or ALTER SESSION commands.

For example, 

to enable tracing for the LGWR process:

 ALTER SYSTEM SET EVENTS 'trace[LGWR]:[Level]';

Foreground trace file:Foreground trace files store information from server processes. When a process detects an internal error, the process

dumps information about the error to its trace file. Server process trace file names

contain the Oracle SID, the

string ora, and the operating system process number. An example of a server

process trace file name  is mytest_ora_10304.trc.

 Location of Foreground Trace Files:

 

Foreground trace files are typically located in the Automatic Diagnostic Repository (ADR).

The ADR home path can be obtained using the SHOW PARAMETER DIAGNOSTIC_DEST

 

 

Enabling and Disabling Tracing for a Session:

 

Tracing for a specific user session can be dynamically enabled or disabled using the ALTER SESSION command.

For example, to enable tracing for a session: ALTER SESSION SET TRACEFILE_IDENTIFIER='trace_identifier';

Dump files: are a special type of trace file that contains detailed point-in-time information about a state or structure. A dump file is typically a one-time output of diagnostic data in response to an event, whereas a trace file tends to be a continuous output of diagnostic data.

 

Health monitor reports: Health monitor reports include results of diagnostic checks from the Health Monitor framework. Health checks detect file corruptions, physical and logical block corruptions, undo and redo corruptions, data dictionary corruptions, and more. The health checks generate reports of their findings and, in many cases, recommendations for resolving problems.

 Incident package:Incident package contain information for uploading diagnostic data to Oracle Support. A package is a collection of metadata that is stored in the ADR and points to diagnostic data files and other files both in and outside of the ADR. When you create a package, you select one or more problems to add to the package. The Support Workbench then automatically adds to the package the problem information, incident information, and diagnostic data (such as trace files and dumps) associated with the selected problems.

 Incident dump: Incident dumps contain diagnostic data that Oracle Database collects when an incident occurs. The database writes one or more dumps to the incident directory created for the incident. Incident dumps also contain the incident number in the file name.

 

Alert log file The Oracle alert log file is a critical component for monitoring and troubleshooting Oracle Database instances. The alert log provides a chronological record of messages and events that occur during the operation of the database. Database administrators (DBAs) regularly review the alert log to identify issues, errors, or significant events affecting the database. Here are key points about the Oracle alert log file:

Location of the Alert Log:

The alert log is typically located in the Automatic Diagnostic Repository (ADR), specifically in the "alert" subdirectory.

The default file name is alert_<SID>.log, where <SID> represents the system identifier of the database.

Purpose of the Alert Log:

The alert log serves as a comprehensive record of database activities, including startup and shutdown events, errors, warnings, and noteworthy occurrences.

It is a critical source of information for diagnosing issues, monitoring the health of the database, and maintaining a historical record of significant events.

Content of the Alert Log:

The alert log contains messages generated by the Oracle Database instance, including:

Startup and shutdown information.

Critical error messages.

Warnings and informational messages.

Changes in database configuration.

Log switches.

Block corruption messages.

Background process trace file names.

Automatic Diagnostic Repository (ADR):

The alert log is part of the Automatic Diagnostic Repository (ADR), which is a centralized location for database diagnostic data.

ADR simplifies the management of diagnostic data and provides tools like ADRCI (Automatic Diagnostic Repository Command Interpreter) for accessing and managing diagnostic information.

Rotating and Archiving:

The alert log file may become large over time, and Oracle Database has mechanisms for rotating and archiving the log to prevent it from growing indefinitely.

Log rotation typically involves switching to a new log file, and archived logs may be stored for historical purposes.

Viewing the Alert Log:

DBAs can view the alert log using various tools, including Oracle Enterprise Manager (EM), SQL*Plus, and ADRCI

SHOW PARAMETER background_dump_dest;

Monitoring Tools:

Database monitoring tools often include features to alert DBAs to critical messages in the alert log.

Oracle Enterprise Manager provides a graphical interface for monitoring the alert log and receiving alerts for important events.

Alert Log for Database Startup Issues:

The alert log is particularly crucial for diagnosing issues during the startup of the database. Any errors or warnings encountered during startup are typically recorded in the alert log.

Integration with Oracle Support:

When troubleshooting issues, Oracle Support often requests copies of the alert log to assist in diagnosing problems. Keeping a record of the alert log is essential for obtaining support.

Regularly reviewing the Oracle alert log is a fundamental practice for database administrators to ensure the health and reliability of the Oracle Database instance. It provides real-time insights into the operational status of the database and serves as a primary resource for diagnosing and resolving issues.