Basics of the Oracle 12c Database Alert Log

By Chris Ruel, Michael Wessler

By far the most important file to review for an Oracle 12c database is the alert log. This file is where database-level errors are written and operations such as startup, shutdown, and other events are logged. Oracle writes to this text-based file in a chronological order when the database is running.

The alert log is in the alert subdirectory and is named log.xml.

  • Whenever a problem occurs, review the alert log file.

  • Review the alert log file daily (if you’re the Database Administrator – DBA) to ensure errors are not occurring undetected.

Many DBAs even write scripts to scan the alert log for errors and have e-mail messages sent to them if key events are detected. Also, many Database Administrators copy off their alert log weekly to prevent it from becoming excessively large.

Here’s a sample of an alert log file in XML format:

<msg time='2013-07-19T13:22:18.955-04:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:3292:2802784106' type='NOTIFICATION' group='admin_ddl'
 level='16' host_id='oralinux1' host_addr='192.168.1.66'
 module='sqlplus@oralinux1 (TNS V1-V3)' pid='24286'>
 <txt>Completed: CREATE DATABASE &quot;dev12c&quot;
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE &apos;/u01/app/oracle/oradata/dev12c/system01.dbf&apos; SIZE 700M REUSE
 AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE &apos;/u01/app/oracle/oradata/dev12c/sysaux01.dbf&apos;
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE &apos;/u01/app/oracle/orada
ta/dev12c/temp01.dbf&apos; SIZE 20M REUSE AUTOEXTEND ON NEXT 640K
MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE &quot;UNDOTBS1&quot; DATAFILE &apos;
/u01/app/oracle/o
radata/dev12c/undotbs01.dbf&apos; SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K
MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 (&apos;/u01/app/oracle/oradata/dev12c/redo01.log&apos;) SIZE 50M,
GROUP 2 (&apos;/u01/app/oracle/oradata/dev12c/redo02.log&apos;) SIZE 50M,
GROUP 3 (&apos;/u01/app/oracle/oradata/dev12c/redo03.log&apos;) SIZE 50M
USER SYS IDENTIFIED BY USER SYSTEM IDENTIFIED BY
 </txt>
</msg>
<msg time='2013-07-19T13:22:19.033-04:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:3209:4222364190' type='NOTIFICATION' group='admin_ddl'
 level='16' host_id='oralinux1' host_addr='192.168.1.66'
 module='sqlplus@oralinux1 (TNS V1-V3)' pid='24311'>
 <txt>CREATE SMALLFILE TABLESPACE &quot;USERS&quot; LOGGING DATAFILE &apos;/u0
1/app/oracle/oradata/dev12c/users01.dbf&apos; SIZE 5M REUSE AUTOEXTEND ON NEXT
1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
 </txt>
</msg>

This code shows routine messages for a database creation.

A text-formatted version is still available for people using command-line editors like vi or Notepad. It is in the trace subdirectory and has the standard name format alert_SID.log (which is alert_dev12c.log in this example). Without the XML tags, you can easily read it via a command-line utility.

Here’s the same information as the XML file, but without the tags:

Fri Jul 19 13:21:21 2013
CREATE DATABASE "dev12c"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/u01/app/oracle/oradata/dev12c/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/dev12c/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/dev12c/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/oradata/dev12c/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/dev12c/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/dev12c/redo02.log') SIZE 50M,
GROUP 3 ('/u01/app/oracle/oradata/dev12c/redo03.log') SIZE 50M
USER SYS IDENTIFIED BY USER SYSTEM IDENTIFIED BY
Database mounted in Exclusive Mode
Lost write protection disabled
Ping without log force is disabled.
Using default pga_aggregate_limit of 2560 MB
Fri Jul 19 13:21:28 2013
db_recovery_file_dest_size of 4815 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Successful mount of redo thread 1, with mount id 3622234653
Using SCN growth rate of 16384 per second
Assigning activation ID 3622234653 (0xd7e6ea1d)
Starting background process TMON
Fri Jul 19 13:21:28 2013
TMON started with pid=24, OS id=24298
Thread 1 opened at log sequence 1
 Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/dev12c/redo01.log
Successful open of redo thread 1

Here’s what an Oracle error looks like from a trace file:

*** KEWROCISTMTEXEC - encountered error: (ORA-06525: Length Mismatch for CHAR or RAW data
ORA-06512: at "SYS.DBMS_STATS", line 40111

Review and manage the alert log regularly so you can catch small issues before they grow into large problems.