We've had just about every sort of database connection issue with our two Questionmark Perception servers, so in the interest of saving someone else the kind of struggles we've gone through, here are some things to look for.

1. tnsnames.ora issues: There is a file that lives in a subdirectory of the Oracle folder - this directory may be called Oracle_Home, Oracle9 etc, and is often but not always in the root directory of the server.

   A typical path to the tnsnames.ora file on a server is
   

   E:\\Oracle_Home\network\Admin\tnsnames.ora

   There are a variety of things that could be incorrect in this file.

   1. Hidden control characters brought in during copy and paste: If you copy and paste the settings from some email programs into this file, you may be adding hidden control characters to the tnsnames.ora file. You won't be able to see them but they're there, and they're screwing things up.
      More after the jump...

      ---

      Ads by Google

      ---

      Posted by ellen at February 01, 2005 05:22 PM

      
      To avoid this, either use the net assistant to set up the tnsnames.ora file, or type the settings in by hand using notepad. Or go ahead and copy and paste, but if you have problems check the file in an application that allows you to see all the spaces, tabs, etc. I use BBEdit, a Mac application.

   2. missing punctuation
      Oracle expects certain brackets and other punctuation to be in the right place. Type carefully! Use another entry as an example. Double check your work.
   3. SID is wrong: The SID identifies the Oracle system ID of the database server to which to connect. The TNSNAMES.ORA file uses the same SID defined in the server's LISTENER.ORA file. Our connection failed because we had left in a ".world" suffix in the SID where it wasn't expecting one.

      Look to see if there is also a sqlnet.ora file in the same directory. If there is, and if it contains a line like:

      NAMES.DEFAULT_DOMAIN = company.com

      or
      NAMES.DEFAULT_DOMAIN = .world

      then your SID or SERVICE_NAME in the tnsnames.ora file should not have the suffix stated in sqlnet.ora appended to it.

      so for instance if the sqlnet.ora contains

      NAMES.DEFAULT_DOMAIN = company.com

      then the SERVICE_NAME in tnsnames.ora should be

      dbname

      and not

      dbname.company.com
      See Avoiding the "ORA-12154: TNS: could not resolve service name" error for more information on tnsnames configuration.
      

2. Not using the Microsoft ODBC driver for Oracle. The Oracle driver does not work with Perception.
   This is set in the Data Sources (ODBC) control panel.

3. Also in the same control panel settings:
   ODBC driver DSN server name does not match tnsnames entry name (the name before the "=" at the top of each entry
4. No user name/password in the perception3.ini file. This happens when changing the DSN settings, since the default installation sets up DSN's for the Access databases, and points the ini file to those. The Access databases don't require usernames and passwords and so those lines in the ini file are commented out. This is most likely to happen if you do not use the GUI provided by login in to the Perception system administration screens, and instead add the information manually. It's even more likely to happen if the people making changes to the system have varying levels of familiarity with different parts of the system, as can easily happen when one person does exclusively dba work, another does Perception server management, another does Windows server management - in other words, it's a communication issue.

Ads by Google