IBM Support

** Troubleshooting ** "Connection failed" ... "ORA-12505: TNS:listener does not currently know of SID given in connect descriptor" when testing database connection

Troubleshooting


Problem

I.T. administrator launches "Controller Configuration" and creates new database connection to Oracle database schema. Administrator clicks 'Test Connection' but receives an error message.

Symptom

Connection failed

Error Details


ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Cause

There are many possible causes for the "ORA-12505: TNS:listener ..." error, such as:

  • Scenario #1 - Incorrect configuration of Oracle database server
    • Specifically, the LISTENER on the Oracle database server was not configured correctly.
  • Scenario #2 - Incorrect configuration of Oracle database client (installed on Controller application server)
    • For more details, see separate IBM Technote #1447103.

Diagnosing The Problem

Scenario #1
Logon to the Oracle database server and launch a command prompt. Type the following command:

    lsnrctl status

In one real-life example, the message included the following text:
    The listener supports no services

Resolving The Problem

Scenario #1 - Incorrect configuration of Oracle database server
Ask your I.T. department's Oracle DBA to reconfigure the Oracle server's listener correctly.

    Real-life example:
    In one real-life example, the customer wanted to connect to a database called "CCR11G". In this case the solution was to modify the "listener.ora" file from:
      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MYSERVERNAME)(PORT = 1521))
      )
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
      )

      ADR_BASE_LISTENER = E:\app\Administrator\product\11.2.0\dbhome_2\log

    to:
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_2)
      (PROGRAM = extproc)
      )
      (SID_DESC=
      (GLOBAL_DBNAME=ccr11g)
      (ORACLE_HOME=E:\app\Administrator\product\11.2.0\dbhome_2)
      (SID_NAME=ccr11g)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = MYSERVERNAME)(PORT = 1521))
      )
      )

      ADR_BASE_LISTENER = E:\app\Administrator\product\11.2.0\dbhome_2\log

    Afterwards, restart the listener by typing:
    • lsnrctl stop
    • lsnrctl start

Scenario #2 - Incorrect configuration of Oracle database client
  • See separate IBM Technote #1447103.

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.1.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21606207

Page Feedback