Mining information from listener logs

When you mention logs, you may suddenly think of operating system logs and database logs, but today we will not talk about those logs. Today we will talk about Database Listener logs, which record all operations on the listener, including successes and failures, attackers can also find attacks against listeners from logs, because listeners are often the primary target for hackers to attack Oracle databases, this article mainly introduces some useful information from listener logs.

Listener log is a standard text file, but it may be a little difficult to open it directly in a text editor, such as Notepad, to find the desired information. Here is another method, use the extended table and SQL statement to search. Fill each row in the log into every record in the extended table. The following describes the implementation steps.

　1. Create a log directory object

Create Directory LISTENER_LOG_DIR

　　 As C: oracleproduct10.2.0db _ 1 etworklog

　　 /

If you do not know where your listener logs are stored, you can run the lsnrctl status Command to check the path of the listener log file in the Command output result.

2. Create an extension table　

Create Table Full_listener_log

(

Line Varchar2 ( 4000 ))

Organization external (

Type oracle_loader

　　 Default Directory LISTENER_LOG_DIR

Access parameters (

Records delimited By Newline

Nobadfile

Nologfile

Nodiscardfile

)

Location ( Listener. log )

)

Reject limit unlimited

　　 /

If multiple listeners exist, modify the location. The log file name here must be consistent with the lsnrctl status output.

You can now query the full_listener_log table, for example, query the log write information:

SQL > Select * From Full_listener_log

　　 2 Where Line Like Log messages written %

　　 3 /

However, the queried information is still very primitive. Our goal is to find useful information, so we need to break down the content of each row of the log file. Generally, most lines of the listener log file contain the following fields:

A) date and time stamp of log entries

B) string used for client connection

C) protocol information used by the client (TCP/IP, port number, etc)

D) Client behavior, such as status and connection Establishment

E) service name in the client connection string

F) code returned by client behavior. If 0 is returned, the operation is successful. Otherwise, the error code is displayed.

Each field is separated by an asterisk. Note that not every log entry follows this format, as shown in the following log Content:

TNSLSNR For 32 - Bit Windows: Version 10.2 . 0.1 . 0 - Production On 01 - September - 2008 11 : 48 : 15

Copyright (c) 1991

The system parameter file is C: oracleproduct. 10.2 . 0 Db_1etworkadminlistener.ora

Write C: oracleproduct 10.2 . 0 Db_1etwork Log Log information of listener. log

Write C: oracleproduct 10.2 . 0