Search code examples
oracleoracle11gcrystal-reports

Crystal Reports fails to find a listener (ORA-12541) when connecting to Oracle 11g Database

Allow me to preface this post by saying I am aware this is a common error code to have and having it appear in Crystal Reports as it tries to connect to an Oracle database, even specifically CR08 connecting to 11gR2 is very well documented on the internet.

I have been troubleshooting on my own for days now and have tried (and list in the post) almost everything, which is why I have decided to create a new post for my case.

I have no education relating to computer technologies and have very little experience with SQL or Oracle, making it sometimes frustrating to read documentation which assumes knowledge of jargon, as I'm constantly googling what things mean. All of my knowledge on the subject is due to the troubleshooting of this issue.

Set-up

  • Windows 7 Professional SP1, 64-bit installation
  • Oracle 11g Release 2 Express Edition Database, 32-bit installation
  • Oracle 11g Release 2 Client, 32-bit installation
  • Crystal Reports 2008. 32-bit installation (Evaluation Edition, it is licensed on production environment but not here, this should not cause my problem)

  • PATH system variable points to:

    C:\oraclexe\product\11.2.0\client_1\bin; C:\oraclexe\app\oracle\product\11.2.0\server\bin

  • TNS_ADMIN system variable points to:

    C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN

  • TNS_NAMES system variable points to:

    C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN

  • C:\oraclexe is the Oracle Home

What am I trying to do?/The Problem

I need Crystal Reports to successfully connect to an Oracle Database hosted on the same computer. After I have made the connection I can continue my normal scope of work.

I am attempting to connect to the database by following the path of Database > Database Expert > Create New Connection > OLE DB (ADO) > Microsoft OLE DB Provider for Oracle.

I then enter the Service as "xe", the user ID as "system", and the password as "password" (I will write as if that is the system user's password for the rest of this post, it is a randomly generated alphanumeric string). I am returned with an error:ORA-12154.

After some troubleshooting I was able to clear this error, and instead was thrown error:ORA-12541 I explain how I got to here below.

Troubleshooting I've done

Solving ORA-12154:

My first idea was to check that tnsnames.ora actually exists and is configured correctly. Mine is in directory:

"C:\oraclexe\product\11.2.0\client_1\NETWORK\ADMIN" along with listener.ora, sqlnet.ora and 3 other files that use the file extension .bak and seem to be based off sqlnet.or and listener.ora, I do not touch these.

My tnsnames.ora is as follows:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = IEAS-KEAGAN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  ) 
system_xe =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

The top 3 are there by default, I added the last one.

Next came checking my sqlnet.ora and listener.ora files, here they are respectively:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Originally this had no NAMES.DIRECTORY_PATH, I added it.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

This has not been modified since installation, except to change HOST variable from "IEAS-KEAGAN" to "127.0.0.1", 5th line from the bottom.

At this point I began to lost hope, and based on a comment from another user on another post here I decided to next search my disk for ALL instances of tnsnames.ora

I found a "TNSNAMES.ora" in my client_1\NETWORK\ADMIN directory as well, and updated it to match the server's TNSNAMES.ora. This contained no entries before this point, just comments from the installation.

Solving ORA-12541:

This created a different error that appeared at the exact spot as ORA-12154 did, but it now gave the error ORA-12541, TNS:no listener.

This is bizarre as I can execute "tnsping system_xe" in the CMD with no problems (I was also able to do this when ORA-12154 was the thrown error code).

I decided to run "lsnrctl stat" from Documents and Settings>Keaga (my user) and was returned with these results. Obviously these results indicate that my issue is with the "listener".

So, I began somewhere familiar and decided to search for all instances of LISTENER.ora on my C:\ as I did with the tnsnames before.

There are 2 listener.ora file's, the one for my server installation which I quoted already above, and another one located in my client_1\NETWORK\ADMIN directory which contains the contents:

# listener.ora Network Configuration File: C:\oraclexe\product\11.2.0\client_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

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

ADR_BASE_LISTENER = C:\oraclexe

After some more searching, I was able to find an old post where someone solves this error by changing "127.0.0.1" to "localhost" when setting the HOST parameter in his listener.ora and tnsnames.ora files. After doing this to both the files in my client and server installations, I tried connected again and was presented with another error at the same step, ORA-12514: ORA-12514

Solving ORA-12514/ORA-12505:

In another old Stack Overflow post, Brad Rippe talks about having this same issue and the fix for him was to make sure "SERVICE_NAME", found in tnsnames.ora, is a valid name in the database. I decided to try this first.

However when I tried to open SQL Developer I also received an ORA-12514 when attempting to connect. "tnsping system_xe" still work's okay, however.

After some frustrating trouble shooting I made it past this step, by adding to the SID_LIST to make a new entry for "xe", SQLPlus is now able to connect to the database, however SQL Developer now throws ORA-12505 when attempting to connect, and Crystal Reports throws ORA-12514 when attempting to connect.

Solution

  • To fix my issue of the listener (Specifically the one Crystal Reports was using) I edited the listener.ora files for both my client and server (note they are on the same machine) to call the host "localhost" instead of "127.0.0.1".

    This fixed my issue and brought another issue to my attention which I described in my last edit under "Solving ORA-12514/ORA-12505:". I am currently troubleshooting this issue.