Oracle Utilities - Reading the listener.log from the database

Author: Packt Publishing Published on: 4th Jan 2010

One particular extension of the above procedure is to read the listener.log file. This file has a specific star-delimited field file format which can be advantageous, and eases the read by means of the Loader driver.

Ads

The file format is as follows:

Sample Code

21-JUL-2008 00:39:50 * (CONNECT_DATA=(SID=beta)(CID=(PROGRAM=perl)(HOS
T=alpha.us.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1
92.168.2.10)(PORT=8392)) * establish * beta * 0
21-JUL-2008 00:39:56 * (CONNECT_DATA=(SID=beta)(CID=(PROGRAM=perl)(HOS
T=alpha.us.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1
92.168.2.10)(PORT=8398)) * establish * beta * 0
21-JUL-2008 00:40:16 * service_update * beta * 0
21-JUL-2008 00:41:19 * service_update * beta * 0
21-JUL-2008 00:44:43 * ping * 0

The file has a format that can be deduced from the above data sample:

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE

As you can see this format, even though it is structured, it may have a different number of fields, so at loading time this issue must be considered.

In order for us to map this table to the database, we should consider the variable number of fields to have the External Table created. We'll create a temporary table so that this doesn't create an additional transactional overhead.

Now, let's create an External Table based on this format that points to:

$ORACLE_HOME/network/log

Sample Code

create directory NETWORK_LOG_DIR
as '$ORACLE_HOME/network/log'
Now, let's create the External Table:
create table LISTENER_LOG (
TIMESTAMP date,
CONNECT_DATA varchar2(2048),
PROTOCOL_INFO varchar2(64),
EVENT varchar2(64),
SID varchar2(64),
RETURN_CODE number(5)
)
organization external (
type ORACLE_LOADER
default directory NETWORK_LOG_DIR
access parameters (
records delimited by NEWLINE
nobadfile
nodiscardfile
nologfile
fields terminated by "*" LDRTRIM
reject rows with all null fields
(
"TIMESTAMP" char date_format DATE mask "DD-MON-YYYY HH24:MI:SS
",
"CONNECT_DATA",
"PROTOCOL_INFO",
"EVENT",
"SID",
"RETURN_CODE"
)
)
location ('listener.log')
)
reject limit unlimited

The structure of interest is specified above, so there will be several rows rejected. Seeing as this file is not fully structured, you will find some non formatted information; the bad file and the log file are not meaningful in this context.

Another application of the LISTENER_LOG External Table is usage trend analysis. This query can be issued to detect usage peak hours.

Sample Code

SQL> select to_char(round(TIMESTAMP, 'HH'), 'HH24:MI') HOUR,
2 lpad('#', count(*), '#') CX
3 from listener_log
4 group by round(TIMESTAMP, 'HH')
5 order by 1
HOUR CX
----- ------------------------------------------------
14:00 ###
15:00 ##########################
16:00 ######################
17:00 #####################
18:00 #####################
19:00 ###############

Reading the listener.log file this way allows the DBA not only to keep track of the listener behavior, but also it allows a security administrator to easily spot hacking attempts.

Let's find out who is trying to access the database with sqlplus.exe.

Sample Code

SQL> select timestamp, protocol_info
2 from listener_log
3 where connect_data like '%sqlplus.exe%'
4 /
TIMESTAMP PROTOCOL_INFO
-------------------- ----------------------------------------------------
01-SEP-2008 14:30:37 (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.101)
(PORT=3651))
01-SEP-2008 14:31:08 (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.101)
(PORT=3666))
01-SEP-2008 14:31:35 (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.101)
(PORT=3681))

The use of External Tables to analyze the listener.log can be used not only to have an in-database version of the listener.log perform periodic and programmatic analysis of the listener behavior, but also to determine usage trends and correlate information with the audit team so that unauthorized connection programs can be easily and quickly spotted. Further useful applications can be found by reading the listener.log file. There are two fields that must be further parsed to get information out of them, but parsing those fields goes beyond the scope of this chapter. The structure that the analysis should consider is detailed next:

Connect String

1. SID: The Database Oracle SID, which is populated if the connection was performed by SID, otherwise it is NULL.
2. CID: It contains two subfields, PROGRAM and HOST
3. SERVER: This field indicates the connection type, either dedicated or shared
4. SERVICE_NAME: This field is populated when the connection is performed by a Service instead of SID.
5. COMMAND: The command issued by the user.
6. SERVICE: Present only when listener commands are issued.
7. FAILOVER_MODE: In Real Application Clusters (RAC) environments this field is used if the client performed a connection due to a failover. It shows the failover mode used.

Ads

Protocol

1. PROTOCOL: Indicates the used to perform the connection; this will be TCP most of the times.
2. HOST: This is the client's IP Address.
3. PORT: The port number of the oracle server used to establish the connection.