Analyzing Listener Logs

16.6.9 Analyzing Listener Logs

The listener log file records information about audit trails, service registration-related events, direct hand-off events, subscriptions for Oracle Notification Service (ONS) node-down events, and Oracle Clusterware notifications.

Listener Log Audit Trails
The audit trail information in the listener log file enables you to analyze network usage statistics, client connection requests, commands issued by the Listener Control utility, and so on.
Listener Service Registration Events
The service registration events information in the listener log file enables you to analyze registration-related statistics, such as service names for instances, instance names, service handlers, load information, dynamic listening endpoints, and so on.
Listener Handler Block Information
The listener handler block information in the listener log file enables you to analyze events about blocked and unblocked handlers.
Listener Direct Hand-Off Information
The direct hand-off information in the listener log file enables you to analyze direct hand-off events to dispatchers.
Listener Subscription for ONS Node-Down Event Information
The listener subscribes to the Oracle Notification Service (ONS) node-down event on startup if the ONS configuration file is available. The ONS node-down event information in the listener log file enables you to analyze these messages.
Listener Oracle Clusterware Notification Information
If the required Oracle Clusterware libraries are installed and Oracle Clusterware is started on the host, then the listener notifies Oracle Clusterware about its status during start and stop processes. The Oracle Clusterware notification information in the listener log file enables you to analyze these messages.

Parent topic: Logging Error Information for Oracle Net Services

16.6.9.1 Listener Log Audit Trails

The audit trail information in the listener log file enables you to analyze network usage statistics, client connection requests, commands issued by the Listener Control utility, and so on.

Listener Log Audit Trail Information
The audit trail information comprises statistics about network usage, client connection requests, and commands issued by the Listener Control utility (such as RELOAD, START, STOP, STATUS, or SERVICES).
Format of the Listener Log Audit Trail
This is the format in which audit trail text fields are captured in the listener log file.

Parent topic: Analyzing Listener Logs

16.6.9.1.1 Listener Log Audit Trail Information

The audit trail information comprises statistics about network usage, client connection requests, and commands issued by the Listener Control utility (such as RELOAD, START, STOP, STATUS, or SERVICES).

You can use the audit trail information to view trends and user activity by first storing it in a table and then collating it in a report format. To import the data into a table, use an import utility such as SQL*Loader.

Parent topic: Listener Log Audit Trails

16.6.9.1.2 Format of the Listener Log Audit Trail

This is the format in which audit trail text fields are captured in the listener log file.

Timestamp * Connect Data [* Protocol Info] * Event [* SID | Service] * Return Code

Properties for the audit trail are:

Each field is delimited by an asterisk (*).
Protocol address information and service name or SID information appear only when a connection is attempted.
A successful connection or command returns a code of zero.
A failure produces a code that maps to an error message.

Example 16-5 shows a log file excerpt with RELOAD command request.

Example 16-5 Listener Log Event for Successful RELOAD Request

14-OCT-2022 00:29:54 *
(connect_data=(cid=(program=)(host=sales-server)(user=jdoe))(command=reload)
(arguments=64)(service=listener)(version=135290880))
* reload * 0

Example 16-6 shows a log file excerpt with a successful connection request.

Example 16-6 Listener Log Events for a Successful Connection Request

14-OCT-2022 15:28:58 * 
(connect_data=(service_name=sales.us.example.com)(cid=(program=)(host=sales-server)
(user=jdoe))(CONNECTION_ID=abcdefgtx42abCde6V/aB602xAbCDe==))(TARGET_INSTANCE=sales)  
* (address=(protocol=tcp)(host=192.0.2.35)(port=41349)) * establish 
* sales.us.example.com * 0

Example 16-7 shows a log file excerpt with a successful execution of the STATUS command by host sales-server. It is followed by an unsuccessful connection attempt by a client with an IP address of 192.0.2.35. This connection attempt results in an ORA-12525: Listener Has Not Received Client's Request in Time Allowed error message. This error occurs when a client fails to complete its connection request in the time specified by the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. This client could be attempting a denial-of-service attack on the listener.

Example 16-7 Listener Log Events for an Unsuccessful Connection Request

03-OCT-2022 16:41:57 * 
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=sales-server)(USER=jdoe))(COMMAND=status)
(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153092352)) * status * 0
03-OCT-2022 16:42:35 * <unknown connect data> *
(ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.35)(PORT=53208)) * establish * 
<unknown sid> * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
TNS-12604: TNS: Application timeout occurred

Related Topics

Oracle Database Error Messages Reference

Parent topic: Listener Log Audit Trails

16.6.9.2 Listener Service Registration Events

The service registration events information in the listener log file enables you to analyze registration-related statistics, such as service names for instances, instance names, service handlers, load information, dynamic listening endpoints, and so on.

Listener Service Registration Event Information
The service registration information comprises statistics related to database service registration events, such as service_register, service_update, and service_died.
Format of the Listener Service Registration Information
This is the format for each of the service registration event messages captured in the listener log file.

Parent topic: Analyzing Listener Logs

16.6.9.2.1 Listener Service Registration Event Information

The service registration information comprises statistics related to database service registration events, such as service_register, service_update, and service_died.

During service registration, the Listener Registration (LREG) process provides the listener with information about:

Service names for each running instance of the database
Instance names of the database
Service handlers (dispatchers or dedicated servers) available for each instance
Dispatcher, instance, and node load information
Dynamic listening endpoints

Table 16-15 Service Registration Event Log Information

Event Description

Event	Description
`service_register`	Registration information that the listener receives for an instance.
`service_update`	Updated registration information that the listener receives for a particular instance, such as dispatcher or instance load information. These messages also display all the update operations performed during this `service_update`.
`service_died`	Lost connection information for the connections that the listener is unable to establish with LREG. All registration information for the instance is discarded. Clients are unable to connect to the instance until LREG registers it again.

service_register

Registration information that the listener receives for an instance.

service_update

Updated registration information that the listener receives for a particular instance, such as dispatcher or instance load information.

These messages also display all the update operations performed during this service_update.

service_died

Lost connection information for the connections that the listener is unable to establish with LREG. All registration information for the instance is discarded. Clients are unable to connect to the instance until LREG registers it again.

Parent topic: Listener Service Registration Events

16.6.9.2.2 Format of the Listener Service Registration Information

This is the format for each of the service registration event messages captured in the listener log file.

Table 16-16 Service Registration Events Format

Event	Format
service_register	`Timestamp * Address * Event * Instance Name * Return Code`
service_update	`Timestamp * Event * Registration Update Operation * Instance Name * Return Code`
service_died	`Timestamp * Event * Instance Name * Return Code`

Properties of service registration fields are:

Each field is delimited by an asterisk (*).
It is normal for the events to appear multiple times in a row for one instance.
A successful registration returns a code of zero, meaning the client can connect to the instance.
A failure produces a code that maps to an error message.

Example 16-8 shows a log file with service registration events. The listener is able to receive a client request after a successful service_register event, but is unable to receive client requests after a service_died event.

Example 16-8 Listener Log with Service Registration Events

------------------------------- 
01-OCT-2022 11:40:06 * (ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.35)(PORT=46750)) * service_register * sales * 0
01-OCT-2022 11:40:26 * (connect_data=(service_name=sales.us.example.com)(cid=(program=)(host=sales-server)(user=jdoe)))
* (address=(protocol=tcp)(host=192.0.2.35)(port=41349)) * establish * sales.us.example.com * 0
01-OCT-2022 11:41:51 * service_update * inst_upd=1 handler_upd=2 * sales * 0
01-OCT-2022 11:41:57 * service_update * inst_upd=1 handler_upd=2 * sales * 0
01-OCT-2022 11:42:06 * service_update * inst_upd=1 handler_upd=2 * sales * 0
01-OCT-2022 11:42:08 * (connect_data=(service_name=sales.us.example.com)(cid=(program=)(host=sales-server)(user=jdoe)))
* (address=(protocol=tcp)(host=192.0.2.35)(port=41365)) * establish * sales.us.example.com * 0
01-OCT-2022 11:57:02 * service_died * sales * 12537
01-OCT-2022 11:57:10 * (connect_data=(service_name=sales.us.example.com)(cid=(program=)(host=sales-server)(user=jdoe)))
* (address=(protocol=tcp)(host=192.0.2.35)(port=41406)) * establish * sales.us.example.com * 12514
TNS-12514: TNS:Cannot connect to database. Service sales.us.example.com is not registered with the listener at host 192.0.2.35 port 41365. 
(CONNECTION_ID=4VIdFEpcSe3gU+FoRmR0aA==)
--------------------------------

Related Topics

Oracle Database Error Messages Reference

Parent topic: Listener Service Registration Events

16.6.9.3 Listener Handler Block Information

The listener handler block information in the listener log file enables you to analyze events about blocked and unblocked handlers.

When a service handler is blocked or unblocked by a database instance or listener, the log message displays blocked or unblocked handler details along with load information in the following format:

Timestamp * Handler Blocked or Unblocked by Instance or Listener: Load Information * Instance Name

Example 16-9 Listener Log for Handler Block Events

19-OCT-2022 06:13:51 * dedicated handler blocked by instance : load = 79 * sales
19-OCT-2022 06:13:51 * dedicated handler blocked by listener : load = 79 * sales
19-OCT-2022 06:15:05 * dedicated handler unblocked: load = 74 * sales
19-OCT-2022 07:51:13 * dedicated handler blocked by instance * sales
19-OCT-2022 07:51:13 * dedicated handler unblocked by instance * sales

Parent topic: Analyzing Listener Logs

16.6.9.4 Listener Direct Hand-Off Information

The direct hand-off information in the listener log file enables you to analyze direct hand-off events to dispatchers.

These events are formatted into the following fields:

Timestamp * Presentation * Handoff  * Error Code

Properties of direct hand-off fields are as follows:

Each field is delimited by an asterisk (*).
A successful connection or command returns a code of zero.
A failure produces a code that maps to an error message.

The following example shows a direct hand-off event in the log file.

Example 16-10 Listener Log Event for Direct Hand-Off

21-MAY-2012 10:54:55 * oracle.aurora.net.SALESHttp2 * handoff * 0

Parent topic: Analyzing Listener Logs

16.6.9.5 Listener Subscription for ONS Node-Down Event Information

The listener subscribes to the Oracle Notification Service (ONS) node-down event on startup if the ONS configuration file is available. The ONS node-down event information in the listener log file enables you to analyze these messages.

The subscription enables the listener to remove the affected service when it receives node-down event notification from ONS. The listener uses asynchronous subscription for the event notification.

The following warning message is recorded to the listener log file on each STATUS command if the subscription has not completed, such as the ONS daemon is not running on the host.

WARNING: Subscription for node down event still pending

The listener cannot receive the ONS event while subscription is pending. Other than that, no other listener functionality is affected.

Parent topic: Analyzing Listener Logs

16.6.9.6 Listener Oracle Clusterware Notification Information

If the required Oracle Clusterware libraries are installed and Oracle Clusterware is started on the host, then the listener notifies Oracle Clusterware about its status during start and stop processes. The Oracle Clusterware notification information in the listener log file enables you to analyze these messages.

After a successful notification to Oracle Clusterware (shown as CRS in the following log messages), listeners record the event in the log. No message is recorded if the notification fails.

Listener completed notification to CRS on start
Listener completed notification to CRS on stop

Parent topic: Analyzing Listener Logs