Oracle Alert Log and MySQL Error Log - Oracle to Aurora MySQL Migration Playbook
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle Alert Log and MySQL Error Log

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

One star feature compatibility

N/A

N/A

Use Event Notifications Subscription with Amazon Simple Notification Service (SNS).

Oracle Usage

The primary Oracle error log file is the alert log. It contains verbose information about database activity including informational messages and errors. Each event includes a timestamp indicating when the event occurred. The alert log filename format is alert<sid>.log.

The alert log is the first place to look when troubleshooting or investigating errors, failures, and other messages indicating a potential database problem. Common events logged in the alert log include:

  • Database startup or shutdown.

  • Database redo log switch.

  • Database errors and warnings, which begin with ORA- followed by an Oracle error number.

  • Network and connection issues.

  • Links for a detailed trace files about specific database events.

The Oracle Alert Log can be found inside the database Automatic Diagnostics Repository (ADR), which is a hierarchical file-based repository for diagnostic information: $ADR_BASE/diag/rdbms/{DB-name}/{SID}/trace.

In addition, several other Oracle server components have unique log files such as the database listener and the Automatic Storage Manager (ASM).

Examples

The following screenshot displays partial contents of the Oracle database alert log file.

Oracle database alert log file

For more information, see Monitoring Errors and Alerts in the Oracle documentation.

MySQL Usage

MySQL provides detailed logging and reporting of errors that occur during the database and connected sessions life cycle. In an Amazon Aurora deployment, these informational and error messages are accessible using the Amazon RDS console.

MySQL and Oracle Error Codes

Oracle MySQL

ORA-00001: unique constraint string.string violated.

Error [1062][23000]: Duplicate entry value for key column.

For more information, see Server Error Message Reference in the MySQL documentation.

Error Log Types

MySQL provides several types of logs.

Log type Information written to log

Error log

Problems encountered starting, running, or stopping mysqld.

General query log

Established client connections and statements received from clients.

Binary log

Statements that change data (also used for replication).

Relay log

Data changes received from a replication master server.

Slow query log

Queries that took more than long_query_time seconds to execute.

DDL log (metadata log)

Meta-data operations performed by DDL statements.

For more information, see MySQL Server Logs in the MySQL documentation.

Examples

Access the MySQL error log using the Amazon RDS or Amazon Aurora Management Console:

  1. Log in to the Amazon Management Console, choose RDS, and then choose Databases.

  2. Choose the instance name.

  3. Choose Logs & events and select the log to inspect. For example, select the log during the hour the data was experiencing problems. The following screenshot displays partial contents of a MySQL database error log as viewed from the Amazon RDS Management Console.

    MySQL database error log

MySQL Error Log Configuration

Several parameters specify the location of the MySQL log and errors files. The following table identifies common Amazon Aurora configuration options.

Parameter Description

log_error

Sets the file name and path for the error log. You can modify it through an Aurora Database Parameter Group.

log_error_verbosity

Sets the message levels that are logged such as error, warning, note messages, and so on. You can modify it through an Aurora Database Parameter Group.

USE SLOW LOG

Sets the minimum execution time above which statements are logged in ms. You can modify it through an Aurora Database Parameter Group.

Note

Modifications of certain parameters, such as log_error are turned off for Aurora MySQL instances.