Where is the Alert Log

  • by
For Oracle 10g downward, the alert file is usually at:
$ORACLE_HOME/admin/db_name/bdump
Or possible this one:
$ORACLE_BASE/admin/db_name/bdump
But your database could be different from the convention listed above. You can use the statement below to make sure the location.
SQL> show parameter background_dump_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/admin/ORACLE9I
                                                 /bdump

By default, the file name would be alert_<SID>.log. In my opinion, you don't have to remember SID, because there should be only one file named alert* in the background dump destination.
[oracle@test bdump]# tail -f aler*
If the shell complained about the file name confusing, I guess you have a backup and obsolete alert log in the same path.

Since 11g, Oracle uses Automatic Diagnostic Repository (ADR) infrastructure to redefine and rearrange the alert log and trace files, we should find ADR BASE and ADR HOME for identify the location of trace files. So now, where is ADR BASE?

ADR BASE (= DIAGNOSTIC_DEST)

ADR BASE is usually as same as $ORACLE_BASE. If $ORACLE_BASE is not set, it will be $ORACLE_HOME/log. Or you can check the parameter DIAGNOSTIC_DEST directly.
SQL> show parameter diagnostic_dest;

ADR HOME

If ADR BASE is identified, ADR HOME is at:
<ADR_BASE>/diag/product_type/product_id/instance_id
For example, if your $ORACLE_BASE is at /u01/app/oracle, then ADR HOME is at /u01/app/oracle/diag/rdbms/orcl/rac1.

If you still can't find out the path to the alert log, you may try the last resort:
SQL> SELECT * FROM V$DIAG_INFO;

INST_ID NAME                  VALUE
------- --------------------- -------------------------------------------------------------
      1 Diag Enabled          TRUE
      1 ADR Base              /u01/oracle
      1 ADR Home              /u01/oracle/diag/rdbms/orclbi/orclbi
      1 Diag Trace            /u01/oracle/diag/rdbms/orclbi/orclbi/trace
      1 Diag Alert            /u01/oracle/diag/rdbms/orclbi/orclbi/alert
      1 Diag Incident         /u01/oracle/diag/rdbms/orclbi/orclbi/incident
      1 Diag Cdump            /u01/oracle/diag/rdbms/orclbi/orclbi/cdump
      1 Health Monitor        /u01/oracle/diag/rdbms/orclbi/orclbi/hm
      1 Default Trace File    /u01/oracle/diag/rdbms/orclbi/orclbi/trace/orcl_ora_22769.trc
      1 Active Problem Count  8
      1 Active Incident Count 20

For more information about ADR infrastructure, you may check the documentation below.
Managing Diagnostic Data

Leave a Reply

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