Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to parse the Oracle listener log. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Oracle listeners are stand-alone processes that reside on the server where the Oracle instance resides. As an important communication component of client process connection instance, Oracle listener plays an important role. This article will start with the listener log and analyze the common behavior of reading the listener log and daily listeners.

1. Oracle listener function

The ü Oracle listener is a stand-alone operating system process that runs in the list of operating system processes. Has a startup and termination command console (lsnrctl) independent of the database instance

The ü Oracle listener process waits on a specific port of the server (default is 1521), waiting for client requests to connect to the server through Oracle Net Service

On the other hand, the Oracle listener accepts the service list of the database instance according to the system setup (various environment variables). The service Service is the name of the service provided externally, which is connected to the local naming service according to the Service name. Service is the corresponding database SID, that is, the specified database location of the Oracle instance. The process of establishing and maintaining a list of services is called registering register

U registration register process is divided into two types, static registration and dynamic registration. Static registration is done by writing the service name directly in the listener.ora file. Dynamic registration is registered periodically to the listener through the pmon background process

When a client requests a listener to access a specific port through the TCP protocol, the Oracle listener compares the Service name in the request with the list of registered Service. If it doesn't match, refuse. If there is a match, the specific instance is located, including the Oracle_home address

The listener is not responsible for verifying the user name and password. At this point, it will request a Server Process from the Oracle instance to interact with client Process.

2. Oracle listener status information

On most platforms, we can interact and control operations with listeners through the lsnrctl listener control program. The status information of the current listener can be obtained by using the status command.

C:\ Documents and Settings\ Administrator > lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0-Production on 29-June-2011 13:41:27

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

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=acca-119hs63yxc) (PORT=1521))

))

STATUS of LISTENER

-

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0-Produ

Ction

Launch date 29-June-2011 10:51:26

Normal running time 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

Listening Endpoint Summary.

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=acca-119hs63yxc) (PORT=1521))

Service summary..

The service "OTSXDB" contains 1 routine.

Routine "ots", status READY, contains 1 handler for this service.

The service "OTS_XPT" contains 1 routine.

Routine "ots", status READY, contains 1 handler for this service.

The service "ots" contains 1 routine.

Routine "ots", status READY, contains 1 handler for this service.

Command executed successfully

The command status can view all the information about the current listener. Currently, there are three corresponding services for listeners, namely otsxdb, otx_xpt and ots. The three service names are for external use, and the corresponding Oracle instances are all ots in nature.

All listener processes have 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 take a new log file and watch the listener work.

3. Log resolution of Oracle listener

The following is a snippet of the listener we intercepted, which is omitted for reasons of length.

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

29-June-2011 10:51:27 * (CONNECT_DATA= (CID= (PROGRAM=) (HOST=) (USER=Administrator)) (COMMAND=status) (ARGUMENTS=64) (SERVICE=LISTENER) (VERSION=169869568)) * status * 0

29-June-2011 10:51:39 * (CONNECT_DATA= (CID= (PROGRAM=) (HOST=) (USER=Administrator)) (COMMAND=status) (ARGUMENTS=64) (SERVICE=LISTENER) (VERSION=169869568)) * status * 0

29-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

29-June-2011 10:52:00 * service_register * ots * 0

29-June-2011 10:52:11 * (CONNECT_DATA= (CID= (PROGRAM=) (HOST=) (USER=Administrator)) (COMMAND=status) (ARGUMENTS=64) (SERVICE=LISTENER) (VERSION=169869568)) * status * 0

29-June-2011 10:52:48 * service_update * ots * 0

29-June-2011 10:53:00 * service_update * ots * 0

(there are omissions. )

29-June-2011 10:53:17 * ping * 0

29-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

29-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

29-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

29-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

29-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

29-June-2011 10:54:03 * service_update * ots * 0

29-June-2011 10:54:09 * service_update * ots * 0

The log contains a lot of content, and we are divided into several key parts.

In the log header, Oracle tells us the log format and uses * to split the content.

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

This includes connection time, request data Data and other related event information. These can help us analyze the contents of the log.

The listener cannot find the specified service

Corresponding segment:

29-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 analyzing the log format, you can find that at this time (10:52), the Oracle listener receives access to the program from the host (ACCA-119HS63YXC), logs in using the system user, and the program name is perl.exe. Because the current system does not register Service information, nor does it register SID information, it is not possible to connect to identify SID=OTS requests.

SID is not recognized when an error is reported.

U instances take the initiative to register dynamically

At this point, the database server starts before the listener, and by default it takes a long time for dynamic registration to occur. We use the alter system register; command to manually start the dynamic registration process. The registration process of the pmon process is found on the listener and recorded in the log.

29-June-2011 10:52:00 * service_register * ots * 0

Register the three instances corresponding to the database sid=ots.

ü user Client connection process

Log when a connection request occurs.

29-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

According to the log format, it can be understood that the listener is accessed from a remote (HOST=LIUZIYU-PC,IP=10.1.39.40) host through the TCP protocol, requiring a connection to the ots service. The program used is pl/sql developer.

ü periodic pmon registration

Dynamic registration is a frequent registration behavior, and pmon registers with the listener every few minutes.

29-June-2011 10:55:33 * service_update * ots * 0

4. Conclusion

The listener is one of the database components we often deal with. Like the alert_log of the database, listener logs can help us solve many daily problems.

On how to parse the Oracle listener log is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.