Oracle Database

Oracle Database is a leading database management system available both on-premises and as a cloud solution. Oracle Database comes with a rich set of logging which helps to monitor database security and integrity and aid in troubleshooting database and server issues when they arise. However, different Oracle components store logs in various locations and formats, which can be overwhelming to manage, especially when handling critical issues. It is, therefore, essential to consolidate logging from different sources so that it can be easily accessed and processed when required. NXLog can simplify this by collecting and normalizing the different types of logs before storing them in a central location or forwarding the logs to a third-party analytics platform.

NXLog can also integrate with Oracle Database by reading from and writing to an Oracle-based database.

Oracle Database audit trails

Oracle Database includes a fine-grained auditing mechanism providing several options for how audit logs are stored or output. They can be stored either internally in Oracle audit tables, output to disk as flat files, or sent directly to an operating system-specific logging facility. Different types of database auditing may support some or all of these options. This section provides information on configuring NXLog to collect and process the various Oracle audit log formats.

For more in-depth information about database auditing, refer to the Oracle documentation on Monitoring Database Activity with Auditing.

Configuring database auditing

To see the current audit settings, run the following SQL*Plus command:

SQL> SHOW PARAMETER audit

Example output:

NAME                         TYPE    VALUE
---------------------------- ------- -------------------------------
audit_file_dest              string  C:\ORACLE\18.0.0\ADMIN\XE\ADUMP
audit_sys_operations         boolean TRUE
audit_trail                  string  DB
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog      boolean FALSE

audit_file_dest specifies the location of the log files when audit_trail is set to OS on UNIX/Linux platforms and XML or XML,EXTENDED on all platforms.

audit_sys_operations specifies whether operations executed by users with the SYSDBA or SYSOPER role will be audited. This includes the SYS user. Sys audit logs are written on file on UNIX/Linux platforms, and to the Windows Event Log on Microsoft Windows.

audit_trail specifies the output type and can have one of the following values:

  • NONE or FALSE: auditing is disabled.

  • DB or TRUE: audit records are written to the database audit trail (SYS.AUD$ table).

  • DB,EXTENDED: same as DB; additionally, the SQL_BIND and SQL_TEXT columns are also populated.

  • XML: audit records are written to file in XML format.

  • XML,EXTENDED: same as XML and additionally the SQL_BIND and SQL_TEXT fields are also populated.

  • OS: audit records are written to file in plain text format on UNIX/Linux, and to the Windows Event Log on Microsoft Windows.

The output type can be changed by executing the following SQL command and specifying the preferred audit_trail option, for example:

SQL> alter system set audit_trail=OS scope=spfile;

The database needs to be restarted for the new settings to be applied.

Collecting plain text audit logs

NXLog can process logs from text files using the im_file input module. Since single log records in a database audit log file span multiple lines, the xm_multiline extension must be used to specify the beginning and end of a record.

Example 1. Reading a plain text audit file

The configuration below reads audit records from a plain text log file. Furthermore regex parsing is done in an Exec block to parse the data into fields. The record is then converted to JSON format.

Sample input

The following audit record sample is for an INSERT operation on a table named users by database user jdoe.

Wed Feb  10 14:23:19 2021 +01:00
LENGTH : '329'
ACTION :[102] 'INSERT INTO users (user_id, username, email)
              VALUES (123, 'testuser', 'test@example.com')'
DATABASE USER:[4] 'jdoe'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[0] 'john'
CLIENT TERMINAL:[7] 'UNKNOWN'
STATUS:[1] '0'
DBID:[10] '2943533768'
SESSIONID:[10] '3444967295'
USERHOST:[3] 'PC1'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[1] '3'
nxlog.conf
<Extension json>
    Module        xm_json
</Extension>

<Extension ln_audit_txt>
    Module        xm_multiline

    # The header line of a record is the timestamp in ANSI C's asctime() format
    HeaderLine    /^... ...  \d{1,2} \d\d:\d\d:\d\d \d{4} .\d\d:\d\d/

    # A blank line defines the end of the record
    EndLine       /^\n/
</Extension>

<Input text_audit_trail>
    Module        im_file
    File          "/path/to/oracle/audit.aud"
    InputType     ln_audit_txt
    <Exec>
        if $raw_event =~ /^(... ...  \d{1,2} \d\d:\d\d:\d\d \d{4} .\d\d:\d\d)/
            $EventTime = parsedate($1);

        if $raw_event =~ /LENGTH : '(.*)'\n/
            $Length = integer($1);

        if $raw_event =~ /ACTION :\[.*\] '(?s)(.*?)'\n/
           $Action = $1;
           # Strip newline and white space characters from the Action field
           $Action =~ s/\n\s{1,}/ /g;

        if $raw_event =~ /DATABASE USER:\[.*\] '(?s)(.*?)'\n/
            $DatabaseUser = $1;

        if $raw_event =~ /PRIVILEGE :\[.*\] '(?s)(.*?)'\n/
            $Privilege = $1;

        if $raw_event =~ /CLIENT USER:\[.*\] '(?s)(.*?)'\n/
            $ClientUser = $1;

        if $raw_event =~ /STATUS:\[.*\] '(?s)(.*?)'\n/
            $Status = $1;

        if $raw_event =~ /DBID:\[.*\] '(?s)(.*?)'\n/
            $DBID = $1;

        if $raw_event =~ /SESSIONID:\[.*\] '(?s)(.*?)'\n/
            $SessionID = $1;

        if $raw_event =~ /USERHOST:\[.*\] '(?s)(.*?)'\n/
            $Host = $1;

        if $raw_event =~ /ACTION NUMBER:\[.*\] '(?s)(.*?)'\n/
            $ActionNumber = $1;
    </Exec>
    Exec          to_json();
</Input>
Sample output
{
  "EventReceivedTime": "2021-02-10T14:30:39.477539+01:00",
  "SourceModuleName": "text_audit_trail",
  "SourceModuleType": "im_file",
  "EventTime": "2021-02-10T14:23:19.000000+01:00",
  "Length": 329,
  "Action": "INSERT INTO users (user_id, username, email) VALUES (123, 'testuser', 'test@example.com')",
  "DatabaseUser": "jdoe",
  "Privilege": "SYSDBA",
  "ClientUser": "john",
  "Status": "0",
  "DBID": "2943533768",
  "SessionID": "4294967295",
  "Host": "PC1",
  "ActionNumber": "3"
}

Collecting XML audit logs

NXLog can process logs from XML files using a combination of the im_file input module and the xm_xml extension module.

Example 2. Reading an XML audit file

The configuration below reads records from an XML audit log file. Since single records span multiple lines, the xm_multiline extension module is used to specify the beginning and end of a record. In the case of Oracle audit logs in XML format, this can be identified by the <AuditRecord> and </AuditRecord> tags respectively. Once a record is parsed, it is then converted to JSON format.

Sample input

The following audit record sample is for an INSERT operation on a table named users by database user jdoe.

<AuditRecord>
  <Audit_Type>4</Audit_Type>
  <Session_Id>4294967295</Session_Id>
  <StatementId>63</StatementId>
  <EntryId>60</EntryId>
  <Extended_Timestamp>2021-02-10T09:34:08.212000Z</Extended_Timestamp>
  <DB_User>jdoe</DB_User>
  <OS_User>John</OS_User>
  <Userhost>PC1</Userhost>
  <OS_Process>15972:5072</OS_Process>
  <Terminal>unknown</Terminal>
  <Instance_Number>0</Instance_Number>
  <Returncode>0</Returncode>
  <Scn>0</Scn>
  <Comment_Text>(ADD</Comment_Text>
  <OSPrivilege>SYSDBA</OSPrivilege>
  <DBID>258947886</DBID>
  <Current_User>jdoe</Current_User>
  <Sql_Text>insert into users (user_id, username, email) values
    (123, &apos;testuser&apos;, &apos;test@example.com&apos;)</Sql_Text>
</AuditRecord>
nxlog.conf
<Extension json>
    Module        xm_json
</Extension>

<Extension xml>
    Module        xm_xml
</Extension>

<Extension ln_audit_xml>
    Module        xm_multiline
    HeaderLine    /^<AuditRecord>/
    EndLine       /^<\/AuditRecord>/
</Extension>

<Input xml_audit_trail>
    Module        im_file
    File          "/path/to/oracle/audit.xml"
    InputType     ln_audit_xml
    Exec          parse_xml();
    Exec          to_json();
</Input>
Sample output
{
  "EventReceivedTime": "2021-02-10T11:04:48.371814+01:00",
  "SourceModuleName": "xml_audit_trail",
  "SourceModuleType": "im_file",
  "AuditRecord.Audit_Type": "4",
  "AuditRecord.Session_Id": "4294967295",
  "AuditRecord.StatementId": "63",
  "AuditRecord.EntryId": "60",
  "AuditRecord.Extended_Timestamp": "2021-02-10T09:34:08.212000Z",
  "AuditRecord.DB_User": "jdoe",
  "AuditRecord.OS_User": "John",
  "AuditRecord.Userhost": "PC1",
  "AuditRecord.OS_Process": "15972:5072",
  "AuditRecord.Terminal": "unknown",
  "AuditRecord.Instance_Number": "0",
  "AuditRecord.Returncode": "0",
  "AuditRecord.Scn": "0",
  "AuditRecord.Comment_Text": "(ADD",
  "AuditRecord.OSPrivilege": "SYSDBA",
  "AuditRecord.DBID": "258947886",
  "AuditRecord.Current_User": "jdoe",
  "AuditRecord.Sql_Text": "insert into users (user_id, username, email) values (123, 'testuser', 'test@example.com')"
}

Collecting logs from the database audit trail

NXLog can read records from an Oracle database using the im_odbc input module. This module requires an ODBC client to be installed to establish a connection to the database. See Setting up ODBC for more information.

im_odbc requires the ConnectionString directive to be specified and the connection must be made with a database user having the AUDIT_VIEWER role. Run the following command to grant this role to a user:

SQL> GRANT AUDIT_VIEWER TO <user>;

The SQL directive must specify a SELECT statement that returns an incremental id field and contains a WHERE clause specifying the same field to filter the result.

Example 3. Reading the database audit trail

The configuration below reads records from the SYS.DBA_AUDIT_TRAIL view. Since the view does not contain an integer-base unique ID, it uses the timestamp as the identifier field and specifies the IdType directive. Once a record is processed, the id field is renamed back to its original name and the record is converted to JSON format.

The DBA_AUDIT_TRAIL view is populated when traditional auditing is used. When unified auditing is enabled, audit records can be accessed from the UNIFIED_AUDIT_TRAIL view.

In this example, only a small subset of the available fields are retrieved. Refer to the Oracle documentation for a list of fields provided by the DBA_AUDIT_TRAIL and UNIFIED_AUDIT_TRAIL views.

Sample input

The following audit record sample is for an INSERT operation on a table named TEST_TABLE by user TEST_USER.

EXTENDED_TIMESTAMP OWNER USERNAME OBJ_NAME TERMINAL ACTION …​

9/2/2021 03:54:47 PM EUROPE/BERLIN

TEST_USER

TEST_USER

TEST_TABLE

PC1

INSERT

…​

nxlog.conf
<Extension json>
    Module              xm_json
</Extension>

<Input db_audit_trail>
    Module              im_odbc
    ConnectionString    DRIVER=Oracle 19 ODBC driver;Uid=<user>; \
                        /Pwd=<password>;Dbq=<server>/<service_name>;
    SQL                 SELECT EXTENDED_TIMESTAMP id, \
                        OWNER, USERNAME, TERMINAL, \
                        OBJ_NAME, ACTION_NAME,  \
                        FROM SYS.DBA_AUDIT_TRAIL \
                        WHERE EXTENDED_TIMESTAMP > ?
    IdType              timestamp
    Exec                rename_field($id, $EXTENDED_TIMESTAMP);
    Exec                to_json();
</Input>
Sample output
{
  "EXTENDED_TIMESTAMP": "2021-02-09T15:54:47.265483+01:00",
  "OWNER": "TEST_USER",
  "USERNAME": "TEST_USER",
  "OBJ_NAME": "TEST_TABLE",
  "TERMINAL": "PC1",
  "ACTION": "INSERT",
  "EventReceivedTime": "2021-02-09T15:54:49.409771+01:00",
  "SourceModuleName": "db_audit_trail",
  "SourceModuleType": "im_odbc"
}

Collecting logs from Windows Event Log

Audit trails for Oracle Database instances installed on Microsoft Windows platforms are written to Windows Event Log when auditing is configured with OS output. NXLog can read these events using the im_msvistalog input module.

Refer to the Oracle documentation on Reading Event Viewer for more information on Oracle audit trail events.

Example 4. Reading audit logs from the Windows Event Log

The configuration below reads the Application channel and filters for events with source Oracle.XE and event ID 34 identify it as an audit trail event. Once an event is parsed, the $Message field is removed since this data will be available in the $Data field, and the record is converted to JSON format.

Sample input

The following audit event sample is for an INSERT operation on a table named users by database user jdoe.

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
 <System>
  <Provider Name="Oracle.xe" />
  <EventID Qualifiers="16386">34</EventID>
  <Level>4</Level>
  <Task>0</Task>
  <Keywords>0x80000000000000</Keywords>
  <TimeCreated SystemTime="2021-02-10T11:00:45.999481800Z" />
  <EventRecordID>2475991</EventRecordID>
  <Channel>Application</Channel>
  <Computer>PC1</Computer>
  <Security />
 </System>
 <EventData>
  <Data>LENGTH: '454' ACTION :[89] 'insert into users (user_id, username,
    email) values (123, 'testuser', 'test@example.com')' DATABASE USER:[4]
    'jdoe' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[4] 'John' CLIENT TERMINAL:[7]
    'unknown' STATUS:[1] '0' DBID:[9] '258947886' SESSIONID:[10] '4294967295'
    USERHOST:[3] 'PC1' CLIENT ADDRESS:[52] '(ADDRESS=(PROTOCOL=tcp)
    (HOST=127.0.0.1)(PORT=55962))' ACTION NUMBER:[1] '2'</Data>
 </EventData>
</Event>
nxlog.conf
<Extension json>
    Module    xm_json
</Extension>

<Input evt_audit_trail>
    Module    im_msvistalog
    <QueryXML>
        <QueryList>
            <Query Id="0" Path="Application">
                <Select Path="Application">
                    *[System[Provider[@Name='Oracle.XE'] and (EventID=34)]]
                </Select>
            </Query>
        </QueryList>
    </QueryXML>
    Exec      delete($Message);
    Exec      to_json();
</Input>
Sample output
{
  "EventTime": "2021-02-10T12:00:45.999481+01:00",
  "Hostname": "PC1",
  "Keywords": "36028797018963968",
  "EventType": "INFO",
  "SeverityValue": 2,
  "Severity": "INFO",
  "EventID": 34,
  "SourceName": "Oracle.xe",
  "TaskValue": 0,
  "RecordNumber": 2475991,
  "ExecutionProcessID": 0,
  "ExecutionThreadID": 0,
  "Channel": "Application",
  "Data": "LENGTH: '454' ACTION :[89] 'insert into users (user_id, username, email) values (123, 'testuser', 'test@example.com')' DATABASE USER:[4] 'jdoe' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[4] 'John' CLIENT TERMINAL:[7] 'unknown' STATUS:[1] '0' DBID:[9] '258947886' SESSIONID:[10] '4294967295' USERHOST:[3] 'PC1' CLIENT ADDRESS:[52] '(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=55962))' ACTION NUMBER:[1] '2' ",
  "EventReceivedTime": "2021-02-10T12:03:30.646432+01:00",
  "SourceModuleName": "evt_audit_trail",
  "SourceModuleType": "im_msvistalog"
}

Collecting syslog audit logs

For instances installed on UNIX/Linux platforms, Oracle audit trails can be configured to log data to syslog when auditing is configured with OS output. NXLog can receive the syslog records using the im_tcp or im_udp input modules and process the logs using the xm_syslog extension module. Two steps are required to enable syslog auditing:

  1. Configure the syslog parameter in the Oracle initialization file.

  2. Update the syslog daemon configuration file for your OS to forward Oracle logs to NXLog.

Refer to the Oracle documentation on how to configure syslog auditing:

Example 5. Collecting syslog audit logs over TCP

The configuration below uses all available network interfaces to listen for inbound TCP connections on port 1514. Log records are parsed using the parse_syslog() procedure and then converted to JSON format.

Sample input

The following syslog audit log sample is for a logon operation by database user jdoe. Each of the original log records was stored one record per line. This sample event is shown as multiple lines for the sake of readability.

<134>Feb 11 03:49:04 oracle-server
journal[3580]: Oracle Audit[3580]:
LENGTH: "396"
SESSIONID:[7] "1970008"
ENTRYID:[1] "1"
STATEMENT:[1] "1"
USERID:[4] "JDOE"
USERHOST:[13] "WORKGROUP\PC1"
TERMINAL:[3] "PC1"
ACTION:[3] "100"
RETURNCODE:[1] "0"
COMMENT$TEXT:[126] "Authenticated by: DATABASE;AUTHENTICATED IDENTITY: JDOE;
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.102)(PORT=49804))"
OS$USERID:[4] "John"
DBID:[10] "1676771236"
PRIV$USED:[1] "5"
CURRENT_USER:[4] "JDOE"
nxlog.conf
<Extension json>
    Module        xm_json
</Extension>

<Extension syslog>
    Module        xm_syslog
</Extension>

<Input syslog_audit_trail>
    Module        im_tcp
    ListenAddr    0.0.0.0:1514
    Exec          parse_syslog();
    Exec          to_json();
</Input>
Sample output
{
  "MessageSourceAddress": "10.0.0.101",
  "EventReceivedTime": "2021-02-11T04:35:30.599189+01:00",
  "SourceModuleName": "tcp",
  "SourceModuleType": "im_tcp",
  "SyslogFacilityValue": 16,
  "SyslogFacility": "LOCAL0",
  "SyslogSeverityValue": 6,
  "SyslogSeverity": "INFO",
  "SeverityValue": 2,
  "Severity": "INFO",
  "Hostname": "oracle-server",
  "EventTime": "2021-02-11T04:35:30.000000+01:00",
  "SourceName": "journal",
  "ProcessID": "4952",
  "Message": "Oracle Audit[3580]: LENGTH: \"396\" SESSIONID:[7] \"2020010\" ENTRYID:[1] \"1\" STATEMENT:[1] \"1\" USERID:[4] \"JDOE\" USERHOST:[13] \"WORKGROUP\\PC1\" TERMINAL:[3] \"PC1\" ACTION:[3] \"100\" RETURNCODE:[1] \"0\" COMMENT$TEXT:[126] \"Authenticated by: DATABASE;AUTHENTICATED IDENTITY: JDOE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.102)(PORT=50656))\" OS$USERID:[4] \"John\" DBID:[10] \"1676771236\" PRIV$USED:[1] \"5\" CURRENT_USER:[4] \"JDOE\""
}

Oracle Database diagnostic logs

Oracle Database includes a logging infrastructure to aid in detecting and diagnosing problems related to the database. Diagnostic data is stored in the file-based repository known as the Automatic Diagnostic Repository (ADR). The ADR directory structure comprises an ADR base that contains other ADR home directories for the various Oracle Database components. The list of all important ADR locations for a database instance can be viewed by executing the following SQL query:

SQL> SELECT * FROM V$DIAG_INFO;

Example output:

INST_ID    NAME            VALUE
-------    ----            -----
1          Diag Enabled    TRUE
1          ADR Base        C:\ORACLE\18.0.0
1          ADR Home        C:\ORACLE\18.0.0\diag\rdbms\xe\xe
1          ADR Trace       C:\ORACLE\18.0.0\diag\rdbms\xe\xe\trace
1          ADR Alert       C:\ORACLE\18.0.0\diag\rdbms\xe\xe\alert
1          ADR Incident    C:\ORACLE\18.0.0\diag\rdbms\xe\incident

Refer to the Oracle documentation for more information on the Automatic Diagnostic Repository (ADR).

Database alert log

An important diagnostic log in the ADR is the database alert log, which includes chronological logging of administrative operations, critical errors, and database events. For more information, see the Oracle documentation About Monitoring Errors with the Alert Log.

Database alert logs can either be collected by reading the log file directly or by reading records from the V$DIAG_ALERT_EXT database view.

Example 6. Collecting Oracle alert log records from file

The configuration below reads the alert log XML file using the im_file input module and xm_xml extension module. Since single records span multiple lines, the xm_multiline extension module is used to specify the beginning and end of a record. In the case of alert logs, these can be identified by the <msg> and </msg> tags respectively. <msg> tags contain attributes with useful information, therefore the ParseAttributes directive of the xm_xml module is set to TRUE to parse these values into fields. The log record is then converted to JSON format.

Oracle Database does not control the size of the database alert log. The Oracle documentation on Controlling the Size of an Alert Log states that the file should be deleted or archived and a new one will be created. NXLog can take care of this by using the xm_fileop extension module in conjunction with a Schedule block. In this example, NXLog checks the size of the database alert log every hour, and if it is equal to or exceeds 5MB, the file is rotated using the file_cycle() procedure.

Sample input

The following is a record from the alert log XML file.

<msg time='2021-02-15T09:42:59.170+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:3820:2802784106' type='NOTIFICATION' group='admin_ddl'
 level='16' host_id='ORACLE_SERVER' host_addr='10.0.0.101'
 module='oradim.exe' pid='3740' con_uid='1'
 con_id='1' con_name='CDB$ROOT'>
 <txt>Completed: alter database open</txt>
</msg>
nxlog.conf
define DB_ALERT_LOG    /path/to/alert/log.xml

<Extension file_operations>
    Module             xm_fileop
    <Schedule>
        Every          1 hour
        <Exec>
              if (file_exists('%DB_ALERT_LOG%') and
                  (file_size('%DB_ALERT_LOG%') >= 5M))
              {
                  file_cycle('%DB_ALERT_LOG%', 5);
              }
        </Exec>
    </Schedule>
</Extension>

<Extension json>
    Module             xm_json
</Extension>

<Extension xml>
    Module             xm_xml
    ParseAttributes    TRUE
</Extension>

<Extension ln_alert_log>
    Module             xm_multiline
    HeaderLine         /^<msg/
    EndLine            /^<\/msg>/
</Extension>

<Input db_alert_log>
    Module             im_file
    File               "%DB_ALERT_LOG%"
    InputType          ln_alert_log
    Exec               parse_xml();
    Exec               to_json();
</Input>
Sample output
{
  "EventReceivedTime": "2021-02-15T11:57:53.670969+01:00",
  "SourceModuleName": "db_alert_log",
  "SourceModuleType": "im_file",
  "msg.time": "2021-02-15T09:42:59.170+01:00",
  "msg.org_id": "oracle",
  "msg.comp_id": "rdbms",
  "msg.msg_id": "opiexe:3820:2802784106",
  "msg.type": "NOTIFICATION",
  "msg.group": "admin_ddl",
  "msg.level": "16",
  "msg.host_id": "ORACLE_SERVER",
  "msg.host_addr": "10.0.0.101",
  "msg.module": "oradim.exe",
  "msg.pid": "3740",
  "msg.con_uid": "1",
  "msg.con_id": "1",
  "msg.con_name": "CDB$ROOT",
  "msg.txt": "Completed: alter database open"
}
Example 7. Collecting database alert log records from the V$DIAG_ALERT_EXT view

The configuration below uses the im_odbc input module to read records from the V$DIAG_ALERT_EXT view. This module requires an ODBC client to be installed to establish a connection to the database. See Setting up ODBC for more information.

im_odbc expects the ConnectionString directive to be specified and a SQL SELECT statement that specifies how to query the data. The statement must return an incremental id field and contain a WHERE clause specifying the same field to filter the result.

The user used to connect to the database must have the SELECT ANY DICTIONARY privilege to be able to read from the V$DIAG_ALERT_EXT view.

In this example, only a small subset of the available fields are retrieved. Refer to the Oracle documentation for a complete list of fields provided by the V$DIAG_ALERT_EXT view.

nxlog.conf
<Extension json>
    Module              xm_json
</Extension>

<Input db_alert_log>
    Module              im_odbc
    ConnectionString    DRIVER=Oracle 19 ODBC driver;Uid=<user>; \
                        /Pwd=<password>;Dbq=<server>/<service_name>;
    SQL                 SELECT RECORD_ID id, \
                        ORIGINATING_TIMESTAMP, CONTAINER_NAME, \
                        HOST_ID, HOST_ADDRESS,  \
                        MESSAGE_TYPE, MESSAGE_LEVEL, MESSAGE_TEXT \
                        FROM V$DIAG_ALERT_EXT \
                        WHERE RECORD_ID > ?
    Exec                rename_field($id, $RECORD_ID);
    Exec                to_json();
</Input>
Sample output
{
  "RECORD_ID": 2882,
  "ORIGINATING_TIMESTAMP": "2021-02-15T09:42:59.170000+01:00",
  "CONTAINER_NAME": "CDB$ROOT",
  "HOST_ID": "ORACLE_SERVER",
  "HOST_ADDRESS": "10.0.0.101",
  "MESSAGE_TYPE": "5",
  "MESSAGE_LEVEL": "16",
  "MESSAGE_TEXT": "Completed: alter database open\n",
  "EventReceivedTime": "2021-02-15T14:41:58.569714+01:00",
  "SourceModuleName": "db_alert_log",
  "SourceModuleType": "im_odbc"
}

Oracle Database Net Listener logs

The Oracle Net Listener is the process that listens for client connections and manages traffic to the database server. The Net Listener writes log data to a file in XML format. Its logs can help troubleshoot connectivity problems. NXLog can process Oracle Net Listener logs using a combination of the im_file input module and the xm_xml extension module.

The Net Listener log file location can be determined by using the Listener Control Utility as follows:

$ lsnrctl status <listener_name>

Example output:

Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 18.0.0.0.0 - Production
Start Date                11-FEB-2021 12:15:16
Uptime                    0 days 1 hr. 19 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oracle\18.0.0\dbhomeXE\network\admin\listener.ora
Listener Log File         C:\oracle\18.0.0\diag\tnslsnr\oracle-server\listener\alert\log.xml

Refer to the Oracle documentation for more information about the Oracle Net Listener and Listener Control Utility.

Example 8. Collecting Oracle Net Listener logs

The configuration below reads the Oracle Net Listener XML log file. Since single records span multiple lines, the xm_multiline extension module is used to specify the beginning and end of each record. In the case of Net Listener logs, these can be identified by the <msg> and </msg> tags respectively. <msg> tags contain attributes with useful information, therefore the ParseAttributes directive of the xm_xml module is set to TRUE to parse these values into fields. The log record is then converted to JSON format.

The Net Listener log file size limit and rotation can be configured by specifying the LOG_FILE_SIZE_<listener_name> and LOG_FILE_NUM_<listener_name> parameters in the listener parameter file. See the Oracle documentation on Diagnostic Parameters for Oracle Net Listener for more information.
Sample input

The following Net Listener log record sample shows a successful connection attempt by user jdoe using the Oracle SQL Developer.

<msg time='2021-02-11T12:16:23.130+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='PC1'
 host_addr='10.0.0.102' pid='11012'>
 <txt>11-FEB-2021 12:16:23 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)
   (HOST=__jdbc__)(USER=jdoe))(SERVICE_NAME=XE)) * (ADDRESS=(PROTOCOL=tcp)
   (HOST=10.0.0.101)(PORT=53725)) * establish * XE * 0
 </txt>
</msg>
nxlog.conf
<Extension json>
    Module             xm_json
</Extension>

<Extension xml>
    Module             xm_xml
    ParseAttributes    TRUE
</Extension>

<Extension ln_net_listener>
    Module             xm_multiline
    HeaderLine         /^<msg/
    EndLine            /^<\/msg>/
</Extension>

<Input oracle_net_listener>
    Module             im_file
    File               "/path/to/netlistener/log.xml"
    InputType          ln_net_listener
    Exec               parse_xml();
    Exec               to_json();
</Input>
Sample output
{
  "EventReceivedTime": "2021-02-11T12:50:47.901905+01:00",
  "SourceModuleName": "oracle_net_listener",
  "SourceModuleType": "im_file",
  "msg.time": "2021-02-11T12:16:23.130+01:00",
  "msg.org_id": "oracle",
  "msg.comp_id": "tnslsnr",
  "msg.type": "UNKNOWN",
  "msg.level": "16",
  "msg.host_id": "PC1",
  "msg.host_addr": "10.0.0.102",
  "msg.pid": "11012",
  "msg.txt": "11-FEB-2021 12:16:23 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=jdoe))(SERVICE_NAME=XE)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.101)(PORT=53725)) * establish * XE * 0\n "
}

Reading logs from a database

NXLog can read logs from various data sources, including Oracle databases, via the im_odbc input module. This module requires an ODBC client to establish a connection to the database. See Setting up ODBC for more information.

im_odbc requires the ConnectionString directive, which specifies the database connection parameters, and a SQL SELECT statement that defines how to query the data. The statement must return an incremental id field and contain a WHERE clause specifying the same field to filter the result.

Example 9. Reading log records with an ID of integer type

The following configuration reads records from the ACCESS_LOGS table in the TESTAPP namespace and converts the data to JSON format.

Sample table
CREATE TABLE "TESTAPP"."ACCESS_LOGS" (
  "ACCESS_ID" NUMBER,
  "ACCESS_TIME" TIMESTAMP (6),
  "USERNAME" VARCHAR2(40 BYTE),
  "PAGE" VARCHAR2(100 BYTE),
  "ACCESS_PERM" VARCHAR2(5 BYTE),
  CONSTRAINT "ACCESS_C_ID_PK" PRIMARY KEY ("ACCESS_ID") ENABLE
);
nxlog.conf
<Extension json>
    Module              xm_json
</Extension>

<Input oracledb>
    Module              im_odbc
    ConnectionString    DRIVER=Oracle 19 ODBC driver;Uid=<user>; \
                        /Pwd=<password>;Dbq=<server>/<service_name>;
    SQL                 SELECT ACCESS_ID id, \
                        ACCESS_TIME EventTime, \
                        USERNAME, \
                        PAGE, \
                        ACCESS_PERM \
                        FROM TESTAPP.ACCESS_LOGS \
                        WHERE ACCESS_ID > ?
    Exec                to_json();
</Input>
Sample output
{
  "ID": 4106,
  "EVENTTIME": "2021-02-08T12:17:52.885494+01:00",
  "USERNAME": "johndoe",
  "PAGE": "users/permissions",
  "ACCESS_PERM": "RW",
  "EventReceivedTime": "2021-02-08T12:18:06.380514+01:00",
  "SourceModuleName": "oracledb",
  "SourceModuleType": "im_odbc"
}

Writing logs to a database

The NXLog om_odbc output module can be used to write logs to Oracle Database via ODBC. This module requires an ODBC client to be installed to establish a connection to the database. See Setting up ODBC for more information.

om_odbc requires the ConnectionString directive to be specified and a SQL INSERT statement that specifies how to write the data.

Example 10. Writing log records to Oracle Database

The following configuration inserts records into the OUTPUT_LOGS table in the NXLOG namespace. The $EventTime and $Message fields in the event record are used for the EVENT_TIME and MESSAGE fields in the table.

Sample table
CREATE TABLE "NXLOG"."OUTPUT_LOGS" (
  "RECORD_ID" NUMBER GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 START WITH 1,
  "EVENT_TIME" TIMESTAMP (6),
  "MESSAGE" VARCHAR2(100 BYTE),
  CONSTRAINT "OUTPUT_C_ID_PK" PRIMARY KEY ("RECORD_ID") ENABLE
);
nxlog.conf
<Output oracledb>
    Module              om_odbc
    ConnectionString    DRIVER=Oracle 19 ODBC driver;Uid=<user>; \
                        /Pwd=<password>;Dbq=<server>/<service_name>;
    SQL                 "INSERT INTO NXLOG.OUTPUT_LOGS (EVENT_TIME, MESSAGE) \
                        VALUES (?,?)", $EventTime, $Message
</Output>

Setting up ODBC for Oracle Database

To connect to an Oracle database, the machine where NXLog is installed must be configured with an Oracle ODBC client. Refer to the Oracle Instant Client ODBC Installation Notes for instructions on how to install and configure ODBC for Oracle on UNIX/Linux and Microsoft Windows platforms.

When connecting to an Oracle Cloud Autonomous Database, additional steps are required to configure ODBC to use the database’s client credentials file (Wallet). See the Oracle documentation Connect Autonomous Database Using a Client Application for more information and configuration steps.

To verify that the ODBC driver has been set up correctly and that NXLog can connect to the database, we recommended to test the connection using isql on Linux or the ODBC Data Source Administrator on Windows.

Disclaimer

While we endeavor to keep the information in this topic up to date and correct, NXLog makes no representations or warranties of any kind, express or implied about the completeness, accuracy, reliability, suitability, or availability of the content represented here.

The accurateness of the content was tested and proved to be working in our lab environment at the time of the last revision with the following software versions:

NXLog 5.5.7535
Windows 10
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

Last revision: 9 September 2022