Oracle uses the ADR (Automatic Diagnostic Repository) directory structure to store all log files concerning the health of the database. This directory structure is stored under the $ORACLE_BASE/diag directory. Each background process of the Oracle RDBMS maintains its own logs in addition to recording information to the central alert log. Two versions of oracle logs are maintained by the ADR: XML and plain text. Background processes may place more detailed information into trace files referenced in the log file messages. General steps for troubleshooting database issues.
- Logon to SQLPLUS with sysdba privileges.
- Determine the location of each log and trace file type by quering the view V$DIAG_INFO as shown below:
SQL> set linesize 200
SQL> column name format a30
SQL> column value format a150
SQL> select name, value from v$diag_info;
Diag Enabled TRUE
ADR Base /opt/app/oracle/diag
ADR Home /opt/app/oracle/diag/diag/rdbms/orcl/ORCL
Diag Trace /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/trace
Diag Alert /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/alert
Diag Incident /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/incident
Diag Cdump /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/cdump
Health Monitor /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/hm
Default Trace File /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/trace/ORCL_ora_15788.trc
- Examine the log files starting with the alert log in directory: $ORACLE_BASE/diag/rdbms/<DB_NAME>/<SID>/trace directory.
This alert log will contain the following:
- Internal errors (ORA-00600)
- Block Corruption errors (ORA-01578)
- Deadlock errors (ORA-00060)
- All DDL statements: CREATE, ALTER, and DROP.
- All SHUTDOWN, STARTUP, and ARCHIVELOG statements.
- Errors with shared processes, server process, and dispatcher processes.
- Errors from automatic refresh operations in materialized views.
- Values of non-default initialization parameters.
- Background and Archiving processes.
- Diagnostics is based on a problem or incident:
PROBLEM – critical error in the database, internal error such ORA- error.
INCIDENT – is a single occurrence of a problem and is tracked by a specific number.
Each INCIDENT records the following:
- Entry is made to alert log.
- Incident alert is sent to EM.
- Diagnostic info is sent to dump file in the ADR subdirectory for that incident type.
- Take corrective action based on information received from INCIDENT referenced in logs and trace files.
- Re-execute process causing original issue and repeat troubleshooting if necessary.
- Additional steps of SQL TRACING and getting assistance from ORACLE SUPPORT may be required in some situations.