Troubleshoot TNSListener

Products

• Data Loss Prevention
• Data Loss Prevention Enforce
• Data Loss Prevention Enterprise Suite
• Data Loss Prevention Oracle Standard Edition 2
• Data Loss Protection Oracle Standard Edition
• Data Loss Prevention Plus Suite

Issue/Introduction

This article will cover how to troubleshoot the TNSListener for Oracle, this will include checking the status as well as recreating the Listener.

Environment

This would apply to any environment where Oracle and Enforce are having problems communicating due to the TNSListener.

Resolution

1. Ensure your connection information is specified correctly on the Enforce Server

• Troubleshoot DLP Enforce connections to Oracle
• If you are seeing errors related to the TNSListener, the first thing you should check is to confirm that your configuration files are specifying the connection information correctly.
• Go through the above KB article to ensure that the 3 files specified all have matching information. If there is any conflicting information you should use the information provided by the tnsnames.ora file collected from your Oracle Server.

 

2. Check the status of the TNSListener (Note that the screenshots show version 12.2, but this is applicable to all supported versions)

• Log into your Oracle Server, the TNSListener commands MUST be run on the actual Oracle Server.
• Verify Oracle services are running as expected in OS services management.
• Next, Open a CMD Window.
• Enter in "lsnrctl status"
  • 
  • Here you will want to look for your Listener, typically called "protect"
  • You will also want to check the status, it should show as "READY"
• If the Listener is not showing a READY status, then you will want to try stopping the listener and restarting it.
  • Enter "lsnrctl stop"
    • 
    • You will see that the listener has successfully been stopped, you will also note that your connection string is listed here in case you need that information.
  • Enter "lsnrctl start"
    • 
    • This time it won't list the name as "protect" but you will see the full connection string, along with the notification that the command was executed successfully.
    • Go ahead and run the "lsnrctl status" command again, in order to confirm that the "protect" listener is actually up and running.

 

3. If you have run through the above steps, and the Listener is still not running, go ahead and simply recreate the TNSListener.

• Instructions for creating the TNSListener can be found in the Oracle Implementation Guide.

  • Symantec Data Loss Prevention Oracle 19c Implementation Guide

  • Please note that the "Configure TNS Listener and Net Service Name" section starts on Page 18 for Windows and page 36 for Linux in the Oracle 19c Implementation Guide.

  • Or you can find the information in our on-line help pages as noted here:
    

    For Linux: Configuring TNS Listener and Net Service Name
    For Windows: Configuring the TNS Listener and Net Service Name

• First you will need to launch the "netca" utility.
  • Open CMD
  • Enter "%ORACLE_HOME%\bin\netca" in order to launch the "Oracle Net Configuration Assistant"
    
    
• Select the "Listener configuration" option and hit "Next"
  • Select the "Delete" option and click "Next"
  • Follow the instructions on the screen to complete the deletion process.
  • Once Deleted, you simply need to recreate the listener.
    
    
• Select the "Listener configuration" again and click "Next"
  • This time select the "Add" option and click "Next"
  • Enter in the Listener name, by default this is "LISTENER"
  • Enter your Oracle Home user password, this is the password you created for the Oracle Home User during the Oracle installation process. Then click "Next"
  • Select the TCP Protocol and click "Next"
  • Use the standard port "1521" and click "Next"
  • click "Next" again to complete the Listener configuration.
  • click on "Finish" to close out the window and complete the process.

• Complete the final steps for configuring your Listener for DLP.
  • open CMD
  • enter "lsnrctl stop" to stop the listener
  • open the "listener.ora" file in notepad
    • C:\oracle\product\12.2.0.1\db_1\network\admin\listner.ora
    • Change the following values in the "listener.ora" file...
      • FROM: (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      • TO: (ADDRESS = (PROTOCOL = IPC)(KEY = protect))
    • Add the following value to the end of the "listener.ora" file...
      • ADD: SECURE_REGISTER_LISTENER = (IPC)
    • Save the changes and Close out the "listener.ora" file.
  • enter "lsnrctl start" to restart the listener.
  • Log into SQLPlus to alter the listener
    • Enter "sqlplus /nolog"
    • Enter "conn sys/<syspassword> as sysdba
    • Run the following commands, please note that this is case sensitive.
      • ALTER SYSTEM SET local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PROTECT)))' SCOPE=both;
      • ALTER SYSTEM REGISTER;
    • Enter "exit" to exit sql
  • Now go back through the lsnrctl status to confirm the new listener is up and running.