Synopsis

The page contains patterns examples of Database Logs such as Oracle, SQL, and DB2.
Each database generates log files containing messages that record all types of events, including startup and shutdown information, errors, warning messages, and access information.

Examples

Example 1 - Oracle Alert Log:

The alert log is a chronological log of all messages and errors that occur, including the administrative operations. The alert log is maintained as both an XML-formatted file and a text-formatted file.

Oracle Alert Log Sample (XML-formatted file):

<msg time='2014-08-18T05:23:58.391-05:00' org_id='oracle' comp_id='rdbms'
msg_id='opistr_real:963:3971575317' type='NOTIFICATION' group='startup'
level='16' host_id='ola-ran' host_addr='192.168.111.14'
pid='13599' version='1'>
<txt>Starting ORACLE instance (normal)
</txt>
</msg>

Oracle Alert Log Pattern (XML-formatted file):

<msg{regexp:Date,refName=record;columnType=Date;dateFormat=yyyy-MM-dd'T'HH:mm:ss.SSSXXX,time='([^']*)}{regexp:org_id,refName=record,org_id='([^']*)}{regexp:comp_id,refName=record,comp_id='([^']*)}{regexp:msg_id,refName=record,msg_id='([^']*)}{regexp:type,refName=record,type='([^']*)}{regexp:group,refName=record,group='([^']*)}{regexp:level,refName=record,level='([^']*)}{regexp:host_id,refName=record,host_id='([^']*)}{regexp:host_addr,refName=record,host_addr='([^']*)}{regexp:pid,refName=record,pid='([^']*)}{regexp:version,refName=record,version='([^']*)}{regexp:txt,refName=record,<txt>(.*)</txt>}{string:record}

Example 2 - DB2 Audit Log:

The audit log is an invaluable resource for security-conscious DBAs that need to monitor database events such as failed access attempts, database object modifications, and user validation. Auditing takes place at the instance level, meaning that once it is started, it audits the activity for all databases in that instance.

DB2 Audit Log Sample:

"2014-08-14-23.59.01.914515","AUDIT","ARCHIVE",0,0,"db2p20","DB2P20","",0,0,"*LOCAL_APPLICATION","db2audit",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"/sapdatastore/P20/RSA/DB2/DB2Audit/DB2AuditArchive/"

"2014-08-14-23.59.02.072760","AUDIT","ARCHIVE",0,0,"db2p20","DB2P20","",0,0,"*LOCAL_APPLICATION","db2audit",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"/sapdatastore/P20/RSA/DB2/DB2Audit/DB2AuditArchive/"

"2014-08-15-23.59.01.902297","AUDIT","FLUSH",0,0,"db2p20","DB2P20","",0,0,"*LOCAL_APPLICATION","db2audit",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

DB2 Audit Log Pattern:

"{date:Date,yyyy-MM-dd-HH.mm.ss.SSSSSS;yyyy-MM-dd HH:mm:ss}","{text:Category}","{text:Audit event}",{text:Event correlator},{text:Event status},"{text:User ID}","{text:Authorization ID}","{text:Text 8}",{text:Text 9},{text:Text 10},"{text:APPID}","{text:APPNAME}"{text:Text 13}

Example 3 - DB2 Validate Log:

The validate log verifies the basic functions of a DB2 copy by checking the state of installation files, instance setup, and local database connections.

DB2 Validate Log Sample:

"2014-08-14-23.59.02.038162","VALIDATE","AUTHENTICATION",0,0,"","db2p20","DB2P20","db2p20",0,0,"*LOCAL_APPLICATION","db2audit","CLIENT",,,,,"IBMOSauthclient",,,,,,,,,

"2014-08-15-00.00.00.692773","VALIDATE","AUTHENTICATION",0,0,"","db2p20","DB2P20","db2p20",0,0,"*LOCAL_APPLICATION","db2acd 0","CLIENT",,,,,"IBMOSauthclient",,,,,,,,,

"2014-08-15-00.00.00.696135","VALIDATE","AUTHENTICATION",0,0,"","db2p20","DB2P20","db2p20",0,0,"*LOCAL_APPLICATION","db2acd 0","CLIENT",,,,,"IBMOSauthclient",,,,,,,,,

DB2 Validate Log Pattern:

"{date:Date,yyyy-MM-dd-HH.mm.ss.SSSSSS}","{text:Category}","{text:Audit event}",{text:Event correlator},{text:Event status},"{text:Database Name}","{text:User ID}","{text:Authorization ID}","{text:Execution ID}",{text:Origin Node Number},{text:Coordinator Node Number},"{text:Application ID}","{text:Application Name}","{text:Authentication Type}"{text}"{text:Package Name}"{text}

Links

Template applying instructions:

Download the templates - Oracle - Template, DB2 - Templates
Import the templates (see Importing a Template)
Apply the templates on multiple logs (see Applying a Template on Multiple Logs)