Database Monitoring   «Prev  Next»
Lesson 5 Contents of Alert log
ObjectiveIdentify the different types of content in the alert log.

Alert Log Contents

How do I identify the different types of content in the alert log for Oracle 19c?
The alert log in Oracle 19c is a critical diagnostic file that records important database-related events, errors, warnings, and other messages. Understanding how to identify and categorize content in the alert log is essential for effective database administration. Here's how you can identify different types of content in the alert log:
  1. Startup and Shutdown Messages
    • Description: Logs details about database startup and shutdown operations.
    • How to Identify: Look for keywords like Starting up, Database opened, Shutting down instance, or Instance terminated.
    • Example: 
      Starting up ORACLE RDBMS Version 19.0.0.0.0.
Instance terminated by USER, pid = 12345.
  2. Checkpoint and Archive Log Activities
    • Description: Indicates log switches, archive operations, and checkpoint completions.
    • How to Identify: Look for keywords like Log file switch, Archived log, or Completed checkpoint.
    • Example: 
      Thread 1 advanced to log sequence 12345 (LGWR switch).
Archived log entry 12345 added for thread 1 sequence 12345.
  3. Background Process Messages
    • Description: Logs activities of background processes like DBW, LGWR, CKPT, and others.
    • How to Identify: Look for process names in square brackets (e.g., [LGWR], [DBW0]).
    • Example: 
      Monitored standby redo transport on destination LOG_ARCHIVE_DEST_2.
Background process [LGWR] stopped.
  4. Errors and Warnings
    • Description: Includes critical errors (ORA-XXXXX), warnings, and diagnostic information.
    • How to Identify: Look for ORA-, Warning:, or Errors in file.
    • Example: 
      ORA-01555: snapshot too old: rollback segment number with name "_SYSSMU3$" too small.
Warning: Log file 3 is unavailable.
  5. Database Configuration Changes
    • Description: Logs events related to changes in initialization parameters, datafiles, or other configurations.
    • How to Identify: Look for keywords like alter database, alter system, or parameter changes.
    • Example: 
      ALTER SYSTEM SET db_cache_size = 67108864 SCOPE=BOTH;
Added datafile '+DATA/ORCL/datafile/system.123.456' to tablespace SYSTEM.
  6. Automatic Tasks and Maintenance
    • Description: Logs information about automated database tasks like statistics gathering, space management, and health checks.
    • How to Identify: Look for terms like Scheduler, Automatic Segment Advisor, or Health Monitor.
    • Example: 
      Automatic Segment Advisor completed for tablespace USERS.
Health Monitor: Check data block integrity completed.
  7. Space Management Events
    • Description: Logs space-related issues, such as tablespace exhaustion or file auto-extension.
    • How to Identify: Look for phrases like Tablespace, Out of space, or Autoextend.
    • Example: 
      ORA-01653: unable to extend table USERS.TBL_TEST by 128 in tablespace USERS.
Autoextend enabled on datafile '+DATA/USERS.123.456'.
  8. Recovery Operations
    • Description: Logs events during recovery, such as applying archive logs or rollbacks.
    • How to Identify: Look for terms like Recovery, Applied log, or Crash recovery.
    • Example: 
      Media recovery complete.
Applied archive log sequence 12345 to thread 1.
  9. Trace File Information
    • Description: References trace files for more detailed diagnostic information.
    • How to Identify: Look for file paths and terms like Trace file, Dump, or Errors in file.
    • Example: 
      Errors in file /u01/app/oracle/diag/rdbms/orcl/trace/alert_orcl.log.
Dump file generated: /u01/app/oracle/diag/rdbms/orcl/trace/orcl_ora_12345.trc.
  10. Patch and Upgrade Notifications
    • Description: Logs events related to patches, upgrades, or version changes.
    • How to Identify: Look for version numbers, patch details, or OPatch.
    • Example: 
      Applying patch 12345678 - Database Patch Set Update 19.10.0.0.0.

Tips for Parsing the Alert Log
  • Use tools like `grep` or log monitoring utilities to filter specific types of content (e.g., grep "ORA-" alert.log for errors).
  • Use Oracle ADRCI (Automatic Diagnostic Repository Command Interface) to query and analyze log entries: 
    adrci> show alert -tail 100
  • Regularly rotate and archive alert logs to avoid excessive file size.

By categorizing and filtering alert log content, you can effectively diagnose issues, monitor database health, and maintain optimal performance in Oracle 19c.
The alert log is a text file that contains text messages generated by the Oracle software as the database is running. These messages are written to alert you to problems and to record significant events in the life of the database. The most common log entry types that you will see are described in this Slide Show:
log entry types 1
The image contains text-based information about redo log switch messages in a database context.
Redo Log Switch Message
Entries:
  • Thread 1 cannot allocate new log, sequence 16
  • Checkpoint not complete

  1. Thread 1 advanced to log sequence 13
    • Current log# 1 seq# 13 mem# 0: B:\ORADATA\COIN\REDOCOIN01A
    • Current log# 1 seq# 13 mem# 1: B:\ORADATA\COIN\REDOCOIN01B
  2. Thread 1 advanced to log sequence 14
    • Current log# 2 seq# 14 mem# 0: B:\ORADATA\COIN\REDOCOIN02A
    • Current log# 2 seq# 14 mem# 1: B:\ORADATA\COIN\REDOCOIN02B
  3. Thread 1 advanced to log sequence 15
    • Current log# 3 seq# 15 mem# 0: B:\ORADATA\COIN\REDOCOIN03A
    • Current log# 3 seq# 15 mem# 1: B:\ORADATA\COIN\REDOCOIN03B

Description:
  • Entries for redo log switches represent the most routine events recorded in an alert log.
  • Redo log switches are normal operations and occur frequently.
  • These events are nothing to worry about.

log entry types 1
The image contains a log message and explanation related to Oracle databases. Here’s the textual analysis extracted:
Checkpoint Not Complete Message
Log Entry:
  • Thread 1 cannot allocate new log, sequence 16
  • Checkpoint not complete

Description:
  • "Checkpoint not complete" messages occur when Oracle needs to advance to the next redo log file but cannot because the changes recorded in the current log file have not yet been written out to disk.
  • This forces Oracle to wait until the data is written, which negatively affects performance.
  • Frequent "Checkpoint not complete" messages indicate a problem.
  • Solution: Add more redo log file groups to the database.
log entry types 1
3) log entry types 1
log entry types 1
4) log entry types 1
log entry types 1
5) log entry types 1
log entry types 1
6) log entry types 1

Many of the messages recorded in the alert log are routine and do not require any special action. Some routine messages, such as those that record physical changes, are useful in recovery situations. Error messages should always be looked at, especially the 600 series of errors.

Handling Alert Log Errors

The truth of the matter is, I do not always chase down each and every error that I see in the alert log. Perhaps I should, and perhaps I shouldn't admit that I don't. Usually what I do is watch to see if an error repeats. If it's a one-time thing, I'll ignore it, especially if I don't have a lot of time to spare. If I get complaints from users or from developers, though, I sometimes find the error messages in the alert log to be an invaluable resource. A developer may remember getting a strange error at a certain time of day, but may not remember what that error was. By looking at the messages in the alert log from around the same time of day, I have a chance of discovering what error occurred and resolving it.
In the next lesson, you will delete and rename old alert log entries.

SEMrush Software Target 5SEMrush Software Banner 5