Oracle数据库alert log的位置

下图来自Oracle官方文档：

如果只关心文件内容而不是位置，用adrci即可：

$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Mon Dec 6 07:34:20 2021

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show alert

Choose the home from which to view the alert log:

1: diag/rdbms/orcl/ORCL
2: diag/tnslsnr/dbserver/listener
Q: to quit

Please select option: 1
Output the results to file: /tmp/alert_19748_1404_ORCL_1.ado

先来看一个19c中的数据情况：

SQL> select name, value from v$diag_info;

NAME                 VALUE
-------------------- ------------------------------------------------------------
Diag Enabled         TRUE
ADR Base             /u01/app/oracle
ADR Home             /u01/app/oracle/diag/rdbms/orcl/ORCL
Diag Trace           /u01/app/oracle/diag/rdbms/orcl/ORCL/trace
Diag Alert           /u01/app/oracle/diag/rdbms/orcl/ORCL/alert
Diag Incident        /u01/app/oracle/diag/rdbms/orcl/ORCL/incident
Diag Cdump           /u01/app/oracle/diag/rdbms/orcl/ORCL/cdump
Health Monitor       /u01/app/oracle/diag/rdbms/orcl/ORCL/hm
Default Trace File   /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_19445.trc
ORACLE_HOME          /u01/app/oracle/product/19c/dbhome_1


SQL> show parameter diagnostic_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle

ADR Base通常设置为ORACLE_BASE：

$ echo $ORACLE_BASE
/u01/app/oracle

以下命令可以显示alert log的位置：

$ echo $ORACLE_BASE/diag/rdbms/${ORACLE_UNQNAME,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log

其中${ORACLE_UNQNAME,,}表示转小写。

参考

• https://docs.oracle.com/en/database/oracle/oracle-database/19/ntqrf/about-alert-logs.html
• https://dba.stackexchange.com/questions/77184/where-is-the-oracle-12c-logfile
• https://oracle-base.com/articles/11g/automatic-diagnostics-repository-11gr1