Oracle alert log on linux
It is important that you monitor the operation of your database on a regular basis. Doing so not only informs you of errors that have not yet come to your attention but also gives you a better understanding of the normal operation of your database. Being familiar with normal behavior in turn helps you recognize when something is wrong.
- Monitoring Errors and Alerts
You can monitor database errors and alerts to prevent, detect, and solve problems. - Monitoring Performance
Monitoring performance includes monitoring locks and wait events and querying a set of data dictionary views. - Monitoring Quarantined Objects
Object quarantine enables an Oracle database to function even when there are corrupted, unrecoverable objects. The V$QUARANTINE view contains information about quarantined objects.
8.1 Monitoring Errors and Alerts
You can monitor database errors and alerts to prevent, detect, and solve problems.
The easiest and best way to monitor the database for errors and alerts is with the Database Home page in Oracle Enterprise Manager Cloud Control (Cloud Control). See the Cloud Control online help for more information. This section provides alternate methods for monitoring, using data dictionary views, PL/SQL packages, and other command-line facilities.
- Monitoring Errors with Trace Files and the Alert Log
A trace file is a file that contains diagnostic data used to investigate problems. An alert log is a file that provides a chronological log of database messages and errors. - Monitoring a Database with Server-Generated Alerts
A server-generated alert is a notification from the Oracle Database server of an impending problem.
8.1.1 Monitoring Errors with Trace Files and the Alert Log
A trace file is a file that contains diagnostic data used to investigate problems. An alert log is a file that provides a chronological log of database messages and errors.
- About Monitoring Errors with Trace Files and the Alert Log
The trace file and alert log contain information about errors. - Controlling the Size of an Alert Log
To control the size of an alert log, you must manually delete the file when you no longer need it. Otherwise the database continues to append to the file. - Controlling the Size of Trace Files
You can control the maximum size of all trace files (excluding the alert log) using the initialization parameter MAX_DUMP_FILE_SIZE . - Controlling When Oracle Database Writes to Trace Files
Background processes always write to a trace file when appropriate. - Reading the Trace File for Shared Server Sessions
If shared server is enabled, each session using a dispatcher is routed to a shared server process, and trace information is written to the server trace file only if the session has enabled tracing (or if an error is encountered). Therefore, to track tracing for a specific session that connects using a dispatcher, you might have to explore several shared server trace files.
8.1.1.1 About Monitoring Errors with Trace Files and the Alert Log
The trace file and alert log contain information about errors.
Each server and background process can write to an associated trace file . When an internal error is detected by a process, it dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, and other information is for Oracle Support Services. Trace file information is also used to tune applications and instances.
Critical errors also create incidents and incident dumps in the Automatic Diagnostic Repository. See Diagnosing and Resolving Problems for more information.
The alert log is a chronological log of messages and errors, and includes the following items:
- All internal errors ( ORA-00600 ), block corruption errors ( ORA-01578 ), and deadlock errors ( ORA-00060 ) that occur
- Administrative operations, such as some CREATE , ALTER , and DROP statements and STARTUP , SHUTDOWN , and ARCHIVELOG statements
- Messages and errors relating to the functions of shared server and dispatcher processes
- Errors occurring during the automatic refresh of a materialized view
- The values of all initialization parameters that had nondefault values at the time the database and instance start
Oracle Database uses the alert log to record these operations as an alternative to displaying the information on an operator’s console (although some systems also display information on the console). If an operation is successful, a «completed» message is written in the alert log, along with a timestamp.
The alert log is maintained as both an XML-formatted file and a text-formatted file. You can view either format of the alert log with any text editor or you can use the ADRCI utility to view the XML-formatted version of the file with the XML tags stripped.
Check the alert log and trace files of an instance periodically to learn whether the background processes have encountered errors. For example, when the log writer process (LGWR) cannot write to a member of a log group, an error message indicating the nature of the problem is written to the LGWR trace file and the alert log. Such an error message means that a media or I/O problem has occurred and should be corrected immediately.
Oracle Database also writes values of initialization parameters to the alert log, in addition to other important statistics.
The alert log and all trace files for background and server processes are written to the Automatic Diagnostic Repository, the location of which is specified by the DIAGNOSTIC_DEST initialization parameter. The names of trace files are operating system specific, but each file usually includes the name of the process writing the file (such as LGWR and RECO).
- «Diagnosing and Resolving Problems» for information about the Automatic Diagnostic Repository (ADR).
- «Alert Log» for additional information about the alert log.
- «Viewing the Alert Log»
- Oracle Database Utilities for information on the ADRCI utility.
- Your operating system specific Oracle documentation for information about the names of trace files
How to find and tail the Oracle alert log
When you take your first look at an Oracle database, one of the first questions is often «where’s the alert log?». Grid Control can tell you, but its often not available in the environment. I posted some bash and Perl scripts to find and tail the alert log on my blog some time back, and I’m surprised to see that post still getting lots of hits. The technique used is to lookup background_dump_dest from v$parameter. But I only tested this on Oracle Database 10g. Is there a better approach than this? And does anyone know if this still works in 11g?
2 Answers 2
Am sure it will work in 11g, that parameter has been around for a long time.
Seems like the correct way to find it to me.
If the background_dump_dest parameter isn’t set, the alert.log will be put in $ORACLE_HOME/RDBMS/trace
@cagcowboy hey, thanks for the tip about what happens if the parameter is not set . the scripts I have don’t take that into account
Once you’ve got the log open, I would consider using File::Tail or File::Tail::App to display it as it’s being written, rather than sleeping and reading. File::Tail::App is particularly clever, because it will detect the file being rotated and switch, and will remember where you were up to between invocations of your program.
I’d also consider locking your cache file before using it. The race condition may not bother you, but having multiple people try to start your program at once could result in nasty fights over who gets to write to the cache file.
However both of these are nit-picks. My brief glance over your code doesn’t reveal any glaring mistakes.
@pjf thanks .. good points for tailing in Perl. For this use however, I wanted to stay within the standard Perl modules that are part of the Oracle installation. Currently doesn’t include File::Tail or File::Tail::App I believe.
Alert Log file location in Oracle
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.
[[email protected] trace]$ cd /u01/app/oracle/diag/rdbms/msdb/msdb1/trace [[email protected] trace]$ [[email protected] trace]$ ls -ltrh alert_msdb1.log -rw-r----- 1 oracle dba 18M Jul 16 15:01 alert_msdb1.log [[email protected] trace]$
You can use the Linux Find command to find location of alertlog as follows.
[[email protected] 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 [[email protected] 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.
[[email protected] trace]$ [[email protected] 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 [[email protected] 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.