Alert Log   «Prev  Next»
Lesson 3Types of alert log messages
Objective Identify the main types of alert log messages.

Types of alert log messages

I am working as an Oracle DBA using Oracle 12c.
Identify the main types of alert log messages which exist in Oracle 12c
In Oracle Database 12c, an alert log is a chronological log of messages and errors, a critical component in effective database management. As an Oracle Database Administrator (DBA), understanding the different types of alert log messages will provide insights into the operational status of your databases and can help troubleshoot any issues that may arise. The main categories of alert log messages include:
  1. Startup and Shutdown Messages: These messages are recorded each time the database is started up or shut down. These include details such as startup time, the Oracle release number, the parameter files used, the type of shutdown, and database close messages.
  2. Errors: All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur are recorded. Other severe errors, such as ORA-7445 (indicating an OS exception), are also documented. When Oracle encounters an error condition, it logs the message to the alert log and sometimes writes trace files to the diagnostic dest location as well.
  3. Administrative Operations: These include activities such as CREATE, ALTER, and DROP of tablespaces or datafiles, changing system settings with ALTER SYSTEM command, and refreshing Materialized Views.
  4. Backup and Recovery Messages: Any messages related to RMAN backups, data pump export/import jobs, or messages from media recovery (ARCH) processes are logged.
  5. Checkpoints: Checkpoints are points in the database's timeline to which data changes are saved. The alert log records each time a checkpoint has been completed.
  6. Errors During the Automatic Refresh of a Materialized View: When there are errors during the automatic refresh of a materialized view, it is recorded in the alert log.
  7. Resource Manager Events: These include messages about the creation, modification, or deletion of resource plans and resource consumer groups.
  8. Resizing of the Redo Log Buffer: The alert log contains entries whenever the redo log buffer is resized.
  9. Job Scheduler Errors: If a job executed via DBMS_JOB or DBMS_SCHEDULER fails, it will be recorded in the alert log.
  10. Others: Messages about various events such as parameter changes, online log switches, and archive log operations are logged as well.

As a best practice, it's recommended to regularly review the alert log to identify and mitigate issues early. Alert logs are located in the ADR base directory, under the path: ADR_base/diag/rdbms/db_unique_name/instance_name/alert.
By familiarizing yourself with these alert log messages, you'll be better prepared to troubleshoot issues, ensuring the smooth operation of your Oracle Database 12c environment.
The Oracle alert log contains several types of informative messages. While all of the alert log messages provide information about events within the Oracle database, we'll look at these messages according to the severity of the situation.

Informational Messages

The alert log was originally created by Oracle to hold internal messages about the status of the database. As such, we see the following "noise" messages in the alert log messages that are informational in nature and that are not necessarily important to monitor.
These include:
  1. Startup and shutdown messages
  2. Log switch messages

Task termination messages

These messages indicate an individual task that does not hurt the overall Oracle database. These messages include:
  1. Failure to extend a rollback segment
  2. Snapshot too old message

Diagnostic Events

These are set as init.ora parameters or with the ALTER SYSTEM SET EVENTS command.

Serious Oracle messages

In addition, you will also see important error messages that indicate the status of the database and the tasks that are executing within the database. Sometimes these messages will be found when the database crashes, and other times the database will continue to run after a serious message. These include:
  1. Corrupt Oracle blocks (ORA-1578)
  2. Unknown serious error (ORA-600)
  3. Internal error (ORA-7445)

The alert file grows over time

The alert log is a never-ending file in that new messages are always appended to the end of the existing alert log file. Over a period of a year, an alert log file can become quite large, so the Oracle DBA will periodically purge the alert log. Oracle will automatically re-initialize the alert.log file the next time a message needs to be written.
In the next two lessons, you will take a closer look at the messages in the alert log and learn what to do when you find them.