Oracle Listener Test

The listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server.

If listener logging is enabled, then the listener log file will log audit trail information that will enable you to gather and analyze network usage statistics, as well as information indicating the following:

  • A client connection request

    A RELOAD, START, STOP, STATUS, or SERVICES command issued by the Listener Control utility In addition, the log file will log the following:

  • Service-registration related events;
  • Direct hand-off events;
  • Messages indicating the failure of the listener’s subscription to the Oracle Notification Service (ONS) node down event
  • Messages indicating that the listener successfully notified CRS (Cluster Ready Service) that its libraries were installed

One problem that happens in very active databases is that the listener.log file can grow very large. If this growth is not controlled soon, then the directory storing the log file may run out of space, thereby rendering the log file incapable of capturing new messages.

Using this test, you can not only determine whether listener logging is enabled or not, but also continuously track the usage of space by the listener log file, so that you can detect any rapid growth in log file size early and take adequate measures to curb the growth. Also, when clients complaint of inaccessibility of the Oracle database server, you can use this test to determine whether/not the listener process is currently available on the target server. By reporting how long the process has been up and running, the test also intimates you of intermittent breaks in availability of the listener process.

Note:

This test will not report metrics for an Oracle 12c PDB server.

Target of the test : An Oracle server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for the Oracle server being monitored.

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The host for which the test is to be configured
  3. Port - The port on which the server is listening

  4. User – In order to monitor an Oracle database server, a special database user account has to be created in every Oracle database instance that requires monitoring. A Click here hyperlink is available in the test configuration page, using which a new oracle database user can be created. Alternatively, you can manually create the special database user. When doing so, ensure that this user is vested with the select_catalog_role and create session privileges.

    The sample script we recommend for user creation (in Oracle database server versions before 12c) for eG monitoring is:

    create user oraeg identified by oraeg

    create role oratest;

    grant create session to oratest;

    grant select_catalog_role to oratest;

    grant oratest to oraeg;

    The sample script we recommend for user creation (in Oracle database server 12c) for eG monitoring is:

    alter session set container=<Oracle_service_name>;

    create user <user_name>identified by <user_password> container=current default tablespace <name_of_default_tablespace> temporary tablespace <name_of_temporary_tablespace>;

    Grant create session to <user_name>;                                

    Grant select_catalog_role to <user_name>;

    The name of this user has to be specified here.

  5. Password – Password of the specified database user

    This login information is required to query Oracle’s internal dynamic views, so as to fetch the current status / health of the various database components.

  6. Confirm password – Confirm the password by retyping it here.
  7. ISPASSIVE – If the value chosen is yes, then the Oracle server under consideration is a passive server in an Oracle cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
  8. oracle home – By default, this test auto-discovers the full path to the Oracle installation directory. This is why, the oracle home parameter is set to none by default.

  9. LISTENER LOG DIR - By default, none is set against this text box indicating that the eG agent will discover the exact location in which the listener.log file resides.

  10. LISTENER NAME - Specify the name of the Oracle listener service associated with the managed Oracle instance. By default, this parameter is set to none. This means that by default the test will attempt to connect to and check the status of the 'default listener'. However, if the server has been configured using a 'named listener', then you should change the value of the LISTENER NAME parameter. If not, then the eG agent will generate a false alert on the Listener Status measure.

  11. IS MULTIPLE LISTENER - By default, this flag is set to No indicating that a single listener is configured to listen to the target database instance. However, if you have configured multiple listeners to listen to the same database instance, set this flag to Yes. In this case, this test will report one set of metrics for each listener listening to the target database instance.

  12. SSL- By default, this flag is set to No, as the target Oracle database is not SSL-enabled by default. If the target database is SSL-enabled, then set this flag to Yes.
  13. SSL Cipher-This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none. A cipher suite is a set of cryptographic algorithms that are used before a client application and server exchange information over an SSL/TLS connection. It consist of sets of instructions on how to secure a network through SSL (Secure Sockets Layer) or TLS (Transport Layer Security). In this text box, provide a comma-seperated list of cipher suites that are allowed for SSL/TLS connection to the target database. By default, this parameter is set to none.
  14. TRUSTSTORE FILE- This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none. TrustStore is used to store certificates from Certified Authorities (CA) that verify and authenticate the certificate presented by the server in an SSL connection. Therefore, the eG agent should have access to the truststore where the certificates are stored to authenticate and connect with the target database and collect metrics. For this, first import the certificates into the following default location <eG_INSTALL_DIR>/lib/security/mytruststore.jks. To know how to import the certificate into the truststore, refer toPre-requisites for monitoring Oracle Cluster. Then, provide the truststore file name in this text box. For example: mytruststore.jks. By default, none is specified against this text box.
  15. TRUSTSTORE TYPE-This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none.Specify the type of truststore that contains the certificates for server authentication in this text box. For eg.,JKS. By default, this parameter is set to the value none.
  16. TRUSSTORE PASSWORD-This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none. If a Truststore File name is provided, then, in this text box, provide the password that is used to obtain the associated certificate details from the Truststore File. By default, this parameter is set to none.
  17. IS SUDO ENABLED - By default, the eG agent does not require any special permissions to parse and read messages from the log file to be monitored. This is why, the Is Sudo Enabled parameter is set to No by default. In some highly-secure Unix environments however, the eG agentinstall user may not have the permission to read the log file to be monitored. In such environments, you will have to follow the steps below to ensure that the test is able to read the log file and report metrics:

    • Edit the SUDOERS file on the target host and append an entry of the following format to it:

      <eG_agent_install_user> ALL=(ALL) NOPASSWD: <Log_file_with_path>

    • For instance, if the eG agent install user is eguser, and the log file to be monitored is /usr/bin/logs/procs.log, then the entry in the SUDOERS file should be:

    • eguser ALL=(ALL) NOPASSWD: /usr/bin/logs/procs.log

    • Finally, save the file.

    • Then, when configuring this test using the eG admin interface, set the Is Sudo Enabled parameter to Yes. Once this is done, then every time the test runs, it will check whether the eG agent install user has the necessary permissions to read the log file. If the user does not have the permissions, then the test runs the sudo command to change the permissions of the user, so that the eG agent is able to read from the log file.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Size of the listener log:

Indicates the current size of the listener log file.

MB

A high value for this measure or a consistent increase in its value indicates that the listener log is rapidly growing and may end up occupying too much space on the volume.

Often people may overlook the growth in the log file size until they stop getting new log messages or the volume/filing system
holding the log file is running out of space.

Remember that on most 32bit operating systems, there is usually a 2 GB file size limit.

On most 64bit OS’s the file can grow a lot larger, often 1TB for a single file. Sometimes this can be the cause of using all the available space on a volume. You may notice that even after you delete the file, the space does not come back as being usable. Specifically, on Unix/Linux - If you delete a file that is open for writing, the space doesn’t get freed up until the process that is writing the file terminates.

Normally if you rename or delete the listener.log file it will create a new file, but it won’t free up the space taken by the old file. This is because the file is open for writing the whole time by the listener. If you can stop the listener, then rename the file and then restart it, so that the space is freed.

Is listener logging enabled?:

Indicates whether listener logging is enabled or not.

 

The values that this measure reports and their corresponding numeric values are as follows:

Measure Value Numeric Value

Yes

 100

No

 0

Note:

By default, this measure reports the Measure Values displayed in the table above to indicatus the listener log status. In the graph of the measure however, the status is represented using the corresponding numeric equivalents - i.e., 0 and 100.

Listener status:

Indicatewhether the listener is currently available or not.

 

The values that this measure reports and their corresponding numeric values are as follows:

 

Measure Value Numeric Value

Up

 100

Down

 0

Note:

By default, this measure reports the Measure Values displayed in the table above to indicate the listener status. In the graph of the measure however, the status is represented using the corresponding numeric equivalents - i.e., 0 and 100.

Uptime of the listener:

Indicates how long since the last measurement period, the listener has been up and running.

Minutes

A high value is typically desired for the measure. A low value indicates that the listener process rebooted recently.