I will explain Alert Log file locations in Oracle in this post.
Alert Log file locations in Oracle
Alert log file is most important log file for Oracle DBAs, because this file includes chronological log of messages and errors of Oracle database like startup, shutdown, log switches, partition add , session kill and etc.
You can learn the Alert log file location of Oracle instance using the following query.
SQL> select value from GV$DIAG_INFO WHERE name='Diag Trace'; VALUE ------------------------------------------------------------------ /u01/app/oracle/diag/rdbms/msdb/msdb2/trace /u01/app/oracle/diag/rdbms/msdb/msdb1/trace SQL>
My database is cluster and it has 2 nodes, so you saw two location for 2 instances.
When I go to /u01/app/oracle/diag/rdbms/msdb/msdb1/trace directory, i can see the alert log of database as follows.
[oracle@msdbdbadm01 trace]$ cd /u01/app/oracle/diag/rdbms/msdb/msdb1/trace [oracle@msdbdbadm01 trace]$ [oracle@msdbdbadm01 trace]$ ls -ltrh alert_msdb1.log -rw-r----- 1 oracle dba 18M Jul 16 15:01 alert_msdb1.log [oracle@msdbdbadm01 trace]$
You can use the Linux Find command to find location of alertlog as follows.
[oracle@msdbdbadm01 admin]$ find /u01 -name alert*log /u01/app/oracle/diag/crs/msdbdbadm01/crs/trace/alert.log /u01/app/oracle/diag/rdbms/repdb/REPDB1/trace/alert_REPDB1.log /u01/app/oracle/diag/rdbms/msdb/msdb1/trace/alert_msdb1.log /u01/app/oracle/diag/rdbms/flexcity/FLEXCITY1/trace/alert_FLEXCITY1.log [oracle@msdbdbadm01 admin]$
Alert log file Content in Oracle
If you want to review the Alert log of Oracle database, you can display it using the Tail command as follows.
[oracle@msdbdbadm01 trace]$ [oracle@msdbdbadm01 trace]$ tail -33f alert_msdb1.log ALTER SYSTEM ARCHIVE LOG Fri Jul 16 14:00:05 2021 Thread 1 advanced to log sequence 253167 (LGWR switch) Current log# 5 seq# 253167 mem# 0: +DATAC1/msdb/ONLINELOG/group_5.5021.1043359481 Current log# 5 seq# 253167 mem# 1: +RECOC1/msdb/ONLINELOG/group_5.39409.1043359497 Fri Jul 16 14:00:05 2021 Archived Log entry 867643 added for thread 1 sequence 253166 ID 0x97866c7 dest 1: Fri Jul 16 14:00:12 2021 ALTER SYSTEM ARCHIVE LOG Fri Jul 16 14:00:12 2021 Thread 1 advanced to log sequence 253168 (LGWR switch) Current log# 6 seq# 253168 mem# 0: +DATAC1/msdb/ONLINELOG/group_6.5010.1043359545 Current log# 6 seq# 253168 mem# 1: +RECOC1/msdb/ONLINELOG/group_6.51693.1043359589 Fri Jul 16 14:00:12 2021 Archived Log entry 867646 added for thread 1 sequence 253167 ID 0x97866c7 dest 1: Fri Jul 16 15:00:03 2021 ALTER SYSTEM ARCHIVE LOG Fri Jul 16 15:00:03 2021 Thread 1 advanced to log sequence 253169 (LGWR switch) Current log# 7 seq# 253169 mem# 0: +DATAC1/msdb/ONLINELOG/group_7.1789.1043359607 Current log# 7 seq# 253169 mem# 1: +RECOC1/msdb/ONLINELOG/group_7.53428.1043359623 Fri Jul 16 15:00:03 2021 Archived Log entry 867650 added for thread 1 sequence 253168 ID 0x97866c7 dest 1: Fri Jul 16 15:00:13 2021 ALTER SYSTEM ARCHIVE LOG Fri Jul 16 15:00:15 2021 Thread 1 advanced to log sequence 253170 (LGWR switch) Current log# 5 seq# 253170 mem# 0: +DATAC1/msdb/ONLINELOG/group_5.5021.1043359481 Current log# 5 seq# 253170 mem# 1: +RECOC1/msdb/ONLINELOG/group_5.39409.1043359497 Fri Jul 16 15:00:15 2021 Archived Log entry 867656 added for thread 1 sequence 253169 ID 0x97866c7 dest 1: Fri Jul 16 15:01:57 2021 Resize operation completed for file# 5, old size 8893440K, new size 8902400K ^C [oracle@msdbdbadm01 trace]$
Alert log Views in Oracle
You can use the following scripts to display alertlog of Oracle database.
set linesize 150 set pagesize 150 ---version 1 SELECT SUBSTR (MESSAGE_TEXT, 1, 300) MESSAGE_TEXT, TO_CHAR (ORIGINATING_TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') Occur_date FROM X$DBGALERTEXT WHERE (MESSAGE_TEXT LIKE '%ORA-%' OR UPPER (MESSAGE_TEXT) LIKE '%ERROR%' OR UPPER (MESSAGE_TEXT) LIKE '%ALTER SYSTEM%' OR UPPER (MESSAGE_TEXT) LIKE '%ALTER DATABASE%') AND CAST (ORIGINATING_TIMESTAMP AS DATE) > SYSDATE - 1; ---version 2 - count SELECT SUBSTR (MESSAGE_TEXT, 1, 300) MESSAGE_TEXT, COUNT (*) cnt FROM X$DBGALERTEXT WHERE (MESSAGE_TEXT LIKE '%ORA-%' OR UPPER (MESSAGE_TEXT) LIKE '%ERROR%') AND CAST (ORIGINATING_TIMESTAMP AS DATE) > SYSDATE - 1 GROUP BY SUBSTR (MESSAGE_TEXT, 1, 300); exit select TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.yyyy') MESSAGE_TIME ,message_text ,host_id ,inst_id ,adr_home from V$DIAG_ALERT_EXT A where component_id='rdbms' and message_text like '%ORA-%' order by 1 desc;
If you want to learn Listener log location and content, read the following post.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )