Oracle Listener Log Parsing

An Oracle listener is a standalone process residing on the same server as the Oracle instance. Oracle Listener plays an important role as an important communication component of client process connection instances. This article starts with the Listener log and analyzes the common behavior of reading listener logs and daily listeners.

1 , Oracle Listener Functions

In the author's "Speak Oracle Listener" (http://space.itpub.net/17203031/viewspace-682627) series, it has been relatively clear how Oracle listener works and the system status. Here briefly to be described.

An Oracle listener is a stand-alone operating system process that runs in the list of operating system processes. Has a start-up and Terminate command Console (LSNRCTL) independent of the DB instance;

The Oracle listener process waits on a specific port on the server (default is 1521), waiting for client requests to be attached to the server through the Oracle Net service;

On the other hand, Oracle listeners accept a list of services for a DB instance based on system settings (various environment variables). Service services are the name of a service that is provided externally to the local naming service, which is connected to it based on the service name. The service is the corresponding database SID, which is the specified Oracle instance database location. The process of establishing and maintaining a list of services is called registering register;

Registration register process is divided into two types, static registration and dynamic registration. Static registration is done by writing the service name directly to the Listener.ora file. Dynamic registration is through the Pmon background process periodic to the listener to register;

When a client requests a listener that accesses a specific port through the TCP protocol, the Oracle Listener compares the service name in the request with the list of registered service that is mastered. If it does not match, reject it. If it matches, it locates to a specific instance, including the Oracle_home address;

The listener is not responsible for authenticating the user name password, and a server process is requested from the Oracle instance to interact with the client process;

2 , Oracle Listener status information

Under most platforms, we can interact with and control the listener through the Lsnrctl Listener control program. Where you can get the current listener state information using the status command.

C:\Documents and Settings\administrator>lsnrctl status

Lsnrctl for 32-bit windows:version 10.2.0.1.0-production on 2 September-June-2011 13:41:27

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (Description= (address= (protocol=tcp) (HOST=ACCA-119HS63YXC) (port=1521)

))

The STATUS of LISTENER

------------------------

Alias LISTENER

Version Tnslsnr for 32-bit windows:version 10.2.0.1.0-produ

Ction

Start date 2 September-June-2011 10:51:26

Uptime 0 days 2 hours 50 minutes 3 seconds

Trace level off

Security On:local OS Authentication

SNMP OFF

Listener parameter File C:\tool\oracle\oracle\product\10.2.0\db_1\network\admi

N\listener.ora

Listener log File C:\tool\oracle\oracle\product\10.2.0\db_1\network\log\listener.log

Listener Endpoint Summary ...

(Description= (address= (protocol=tcp) (HOST=ACCA-119HS63YXC) (port=1521)))

Service Summary:

The service "Otsxdb" consists of 1 routines.

Routine "OTs", state ready, contains 1 handlers for this service ...

The service "OTS_XPT" consists of 1 routines.

Routine "OTs", state ready, contains 1 handlers for this service ...

The service "OTS" consists of 1 routines.

Routine "OTs", state ready, contains 1 handlers for this service ...

Command execution succeeded

Command status to view all the information for the current listener. The current listener's services include three, Otsxdb, OTX_XPT, and OTs respectively. Three service names are used externally, and the Oracle instances that correspond to them are all OTS.

Listener process work has the corresponding log information, in the status display, we can also see the log file location (C:\tool\oracle\oracle\product\10.2.0\db_1\network\log\listener.log). Let's look at the listener work from a new log file.

3 , Oracle Listener Log Parsing

Here is the listener fragment we intercepted, the length of the reason, there are omitted.

Start with pid=2736.

Monitoring: (Description= (Address= (protocol=tcp) (HOST=ACCA-119HS63YXC) (port=1521)))

Listener completed notification to CRS on start

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

2 September-June -2011 10:51:27 * (Connect_data= (cid= (program=) (host=) (User=administrator)) (Command=status) (arguments=64) ( Service=listener) (version=169869568)) * Status * 0

2 September-June -2011 10:51:39 * (Connect_data= (cid= (program=) (host=) (User=administrator)) (Command=status) (arguments=64) ( Service=listener) (version=169869568)) * Status * 0

2 September-June -2011 10:52:00 * (Connect_data= (sid=ots) (Cid= (program=c:\tool\oracle\oracle\product\10.2.0\db_1\perl\5.8.3\ Bin\mswin32-x86-multi-thread\perl.exe) (HOST=ACCA-119HS63YXC) (User=system))) * (Address= (PROTOCOL=tcp) (HOST= 10.1.39.93) (port=3126)) * Establish * OTS * 12505

Tns-12505:tns: The listener currently does not recognize the SID given in the connection descriptor

2 September-June -2011 10:52:00 * service_register * ots * 0

2 September-June -2011 10:52:11 * (Connect_data= (cid= (program=) (host=) (User=administrator)) (Command=status) (arguments=64) ( Service=listener) (version=169869568)) * Status * 0

2 September-June -2011 10:52:48 * service_update * ots * 0

2 September-June -2011 10:53:00 * service_update * ots * 0

(There are omitted ... ）

2 September-June -2011 10:53:17 * Ping * 0

2 September-June -2011 10:53:18 * (Connect_data= (cid= (program=) (host=) (User=system)) (Command=status) (arguments=64) (SERVICE= ( Address= (PROTOCOL=TCP) (HOST=ACCA-119HS63YXC) (port=1521)) (version=169869568)) * Status * 0

2 September-June -2011 10:53:29 * (Connect_data= (sid=ots) (Cid= (program=c:\tool\oracle\oracle\product\10.2.0\db_1\perl\5.8.3\ Bin\mswin32-x86-multi-thread\perl.exe) (HOST=ACCA-119HS63YXC) (User=system))) * (Address= (PROTOCOL=tcp) (HOST= 10.1.39.93) (port=3141)) * Establish * OTS * 0

2 September-June -2011 10:53:57 * (Connect_data= (service_name=ots) (cid= (Program=d:\plsql developer\plsqldev.exe) (HOST= LIUZIYU-PC) (User=liuziyu)) * (Address= (PROTOCOL=TCP) (host=10.1.39.40) (port=1760)) * Establish * ots * 0

2 September-June -2011 10:54:00 * (Connect_data= (service_name=ots) (cid= (Program=d:\plsql developer\plsqldev.exe) (HOST= LIUZIYU-PC) (User=liuziyu)) * (Address= (PROTOCOL=TCP) (host=10.1.39.40) (port=1761)) * Establish * ots * 0

2 September-June -2011 10:54:00 * (Connect_data= (service_name=ots) (cid= (Program=d:\plsql developer\plsqldev.exe) (HOST= LIUZIYU-PC) (User=liuziyu)) * (Address= (PROTOCOL=TCP) (host=10.1.39.40) (port=1762)) * Establish * ots * 0

2 September-June -2011 10:54:03 * service_update * ots * 0

2 September-June -2011 10:54:09 * service_update * ots * 0

A lot of log content, we are divided into several parts of the focus.

In the log header, Oracle informs us of the log format and uses * for content segmentation.

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

These include connection time, request data, and other related event information. These can help us analyze the log content.

Listener cannot find the specified service

Corresponding fragment:

2 September-June -2011 10:52:00 * (Connect_data= (sid=ots) (Cid= (program=c:\tool\oracle\oracle\product\10.2.0\db_1\perl\5.8.3\ Bin\mswin32-x86-multi-thread\perl.exe) (HOST=ACCA-119HS63YXC) (User=system))) * (Address= (PROTOCOL=tcp) (HOST= 10.1.39.93) (port=3126)) * Establish * OTS * 12505

Tns-12505:tns: The listener currently does not recognize the SID given in the connection descriptor

After parsing the log format, you can find that at this time (10:52), the Oracle listener receives access to the program, the program comes from the host (ACCA-119HS63YXC), logs on with the system user, and the program name is Perl.exe. Because the current system does not register the service information and does not register SID information, it is not possible to connect to identify the sid=ots request.

The error does not identify the SID.

Instance actively registering on the fly

At this point, the database server starts before the listener, and the default is to wait a long time for dynamic registration to occur. We use the ALTER system register, and the command starts the dynamic registration process manually. The registration process for the Pmon process is found on the listener and is recorded in the log.

2 September-June -2011 10:52:00 * service_register * ots * 0

Register the database sid=ots the corresponding three instances.

User Client Connection process

Log records When a connection request occurs.

2 September-June -2011 10:55:19 * (Connect_data= (service_name=ots) (cid= (Program=d:\plsql developer\plsqldev.exe) (HOST= LIUZIYU-PC) (User=liuziyu)) * (Address= (PROTOCOL=TCP) (host=10.1.39.40) (port=1782)) * Establish * ots * 0

Depending on the log format, it can be understood that the remote (host=liuziyu-pc,ip=10.1.39.40) host is accessed through the TCP protocol to the listener and requires a connection to the OTS service. The program used is PL/SQL developer.

Periodic Pmon Registration

Dynamic registration is a recurring sign-up behavior that pmon every few minutes to the listener.

2 September-June -2011 10:55:33 * service_update * ots * 0

4 , conclusions

Listeners are one of the database components that we often deal with. Like the database Alert_log, listener logs can help us solve many of our daily failures.

Oracle Listener Log Parsing