3

Issue:

We're attempting to do upserts to Salesforce from a Lawson (Oracle) database for a client, using Jitterbit. We are using the Oracle [JDBC] driver on the default port. We have the Jitterbit Agent running on Windows Server 2008 on a machine inside the client's network, and we are assured by the client (but not 100% certain) that the server can connect to the Lawson database. When we try to initiate a connection to the Oracle database from within Jitterbit Studio (Studio is NOT running from the Windows Server, we're running it from a local machine) we're refused with:

Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA

The question I'm hoping someone can help with is - what exactly is the listener lacking the SID for? I'm familiar on a basic level with listener.ora and how SID information needs to be provided for the listener to allow incoming connections to the database.

Does the SID in the error refer to:

  • the Lawson database? (this seems unlikely, as my understanding is that the listener referred to here is the listener sitting on the server for the Lawson database)
  • the machine that the Agent is sitting on (the Windows Server 2008)?
  • or the local machine that we're running Jitterbit Studio from?

I'm waiting to get listener.ora and tnsnames.ora from the client, I just wanted to see if anyone had an idea of the root block from their own experience.

Stack trace, in case it's helpful:

Details: oracle.net.ns.NetException - Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA

    at org.jitterbit.integration.client.ui.interchange.locatable.actions.TestConnectionResultDisplayer.showResult(TestConnectionResultDisplayer.java:62)
    at org.jitterbit.integration.client.ui.interchange.locatable.actions.TestConnectionJob.runImpl(TestConnectionJob.java:55)
    at org.jitterbit.application.ui.job.UiJob$2.run(UiJob.java:509)
    at org.jitterbit.application.worker.DefaultApplicationWorker$RunnableWrapper.run(DefaultApplicationWorker.java:105)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)

Screenshot of where connection parameters are supplied:

enter image description here

tnsnames.ora:

PSILSFD1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.33.22.224)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PSILSFD1)
    )
  )

listener.ora:

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = psidevdba01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
Brian Mansfield
  • 370
  • 2
  • 5
  • 18
  • How are you connecting from Jitterbit Studio to Oracle - how is the connection string or parameters configured? Are you using a TNS alias, and if so how is that defined in your tnsnames.ora on the local machine? Do you think you're using a SID or a service name? – Alex Poole Jul 14 '15 at 16:53
  • @AlexPoole I never explicitly defined a connection string, there's a form in the UI where you provide Driver, Server Name, and auth credentials, so I assume it's generating some compatible connection string based on the driver that we select. I'm updating the post with a screenshot of that form, plus the two config Oracle files. I'm not familiar with what a TNS alias is :( – Brian Mansfield Jul 14 '15 at 18:26

1 Answers1

4

The Jitterbit support pages include:

Configure a source or target with the Oracle JDBC driver
- Create a new source and give it a name.
- From the Type drop-down, select Database.
- Click Select... and locate the Oracle driver. Make sure the JDBC check-box is selected. You may have to refresh the driver list if you recently installed the driver.
- Depending on how the Oracle server is configured you may just have to enter the Server Name, Login and Password (and port if the server runs on a non-standard port). However, in most cases you will have to construct the connection string manually, see below.
...

with emphasis added. You seem to have followed those instructions, but since there is nowhere to supply the service name, you'll need to do the manual setup:

To use a manual connection string, expand the Options section and check "Construct connection string manually". Type in the connection string and test the transformation.

Based on what is in your tnsnames.ora, the connection string will be:

jdbc:oracle:thin:@//10.33.22.224:1521/PSILSFD1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318