Collect logs from Oracle Database
Oracle Database is a leading database management system available both on-premises and as a cloud solution. It 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 your Oracle database logs from different sources so that they 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 them 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 logs
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 auditExample 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:
-
NONEorFALSE: auditing is disabled. -
DBorTRUE: audit records are written to the database audit trail (SYS.AUD$ table). -
DB,EXTENDED: same asDB; additionally, theSQL_BINDandSQL_TEXTcolumns are also populated. -
XML: audit records are written to file in XML format. -
XML,EXTENDED: same asXMLand additionally theSQL_BINDandSQL_TEXTfields 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 Oracle logs from plain text files
NXLog can process Oracle 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 the of a record.
The configuration below reads Oracle 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.
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'<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>{
"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 Oracle logs from XML files
NXLog can process logs from XML files using a combination of the im_file input module and the xm_xml extension module.
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 the 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.
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, 'testuser', 'test@example.com')</Sql_Text>
</AuditRecord><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>{
"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 Oracle logs from a 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 Oracle Database 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.
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.
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 |
… |
<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>{
"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 Oracle logs via 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.
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.
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><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>{
"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 Oracle logs from syslog
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:
-
Configure the syslog parameter in the Oracle initialization file.
-
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:
-
Using the Syslog Audit Trail on UNIX Systems for traditional auditing.
-
Writing the Unified Audit Trail Records to Syslog for unified auditing.
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.
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"<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>{
"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\incidentRefer 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.
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 the 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.
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>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>{
"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"
}V$DIAG_ALERT_EXT viewThe 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 Oracle Database 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.
<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>{
"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 logs 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.xmlRefer to the Oracle documentation for more information about the Oracle Net Listener and Listener Control Utility.
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 the 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.
|
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><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>{
"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 an Oracle 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 Oracle Database 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.
integer typeThe following configuration reads Oracle log records from the ACCESS_LOGS table in the TESTAPP namespace and converts the data to JSON format.
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
);<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>{
"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 an Oracle 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 Oracle Database for more information.
om_odbc requires the ConnectionString directive to be specified and a SQL INSERT statement that specifies how to write the data.
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.
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
);<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 testing the connection using isql on Linux or the ODBC Data Source Administrator on Windows.
