I'm getting an error when trying to JDBC connect to Oracle regarding SID

[msmith]

I'm getting the following error from my midserver - anyone have any idea's what might be wrong ... The Oracle DB TSA is suggesting that I have to register the SID somewhere.

Do I need to set up the "Probe" for output to get this working?   - Any direction welcome   thx MSA

MID Server reported error: java.sql.SQLException: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
oc11d0-scan.devfg.rbc.com:1527:pl00_dev2

oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:441)
oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
java.sql.DriverManager.getConnection(DriverManager.java:664)
java.sql.DriverManager.getConnection(DriverManager.java:208)
com.service_now.mid.connections.jdbc.JDBCConnection.connect(JDBCConnection.java:74)
com.service_now.mid.connections.jdbc.JDBCConnectionFactory.create(JDBCConnectionFactory.java:49)
com.service_now.mid.connections.ConnectionCachePool.getAvailableConnection(ConnectionCachePool.java:79)
com.service_now.mid.connections.ConnectionCache.get(ConnectionCache.java:90)
com.service_now.mid.probe.JDBCProbe.getJDBCConnection(JDBCProbe.java:662)
com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:97)
com.service_now.mid.probe.AProbe.process(AProbe.java:66)
com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:104)
com.service_now.mid.queue_worker.AManagedThread.run(AManagedThread.java:36)

com.service_now.mid.probe.JDBCProbe.getJDBCConnection(JDBCProbe.java:666)
com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:97)
com.service_now.mid.probe.AProbe.process(AProbe.java:66)
com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:104)
com.service_now.mid.queue_worker.AManagedThread.run(AManagedThread.java:36)

[msmith]

Hi Jack... I'm still have no luck.   I have worked with the TSA on the Oracle side and he can't work it out

Asked if I could change the JDBC to JDBC URL instead - but I can't see an option.... We then confirmed I can connect from the cmd to the DB (see screenshot below)

Honestly, I am not an expert on Oracle - the discussion you provide in the oracle community did not help.... I'm not sure why the OOB is not functioning

I would appreciate anyones assistance on this ...Thanks Marlene

[Brian Bush]

The issue is that you are attempting to use the Oracle Service Name in place of the SID.

A Service Name is typically setup for clustered Oracle instances.

The format to connect to a SID (the only Service Now option) is:
<Host FQDN>:<Port>:<SID>

The format to connect to a Service Name is:

<Host FQDN>:<Port>/<Service Name>

So, obviously, I wish that Service Now would add the option to connect with a Service Name, because I need it now.

[richfred · Accepted solution]

You can resolve this by overwriting the Connect URL string in the Data Source form:

1- Navigate to System Import Sets > Administration > Data Sources

2- Click on the Cog icon right below the filter on the Data Sources List

3- On the personalize List Columns Window, choose Connection URL and move it from the Available to Selected window.

4- Click OK, and now you can see the URL for each entry in the list.

5- Create a new Data Source.

6- Save the new data source record.

7- Back in the Data Source list double-click on the connection URL and replace the default URL with your RAC Database jdbc string:

jdbc:oracle:thin@//oc11d0-scan.devfg.rbc.com:1527/pl00_dev2

Every time you modify this record, it will overwrite the "Connect URL" because there is a business rule that is fired every time a change is made to it; therefore you will have to replace the URL with the RAC string every time this information is changed.  

[Brian Bush]

Absolutely right. That is the workaround we put in place, but I forgot to update this thread.

I also opened a HI ticket in hopes that they will simply add a Service Name field in the future.

[Brian Bush]

Also, I should mention the drawback. If you make a change to the data source record, it will revert to the SID formatted Connection URL and you will have to modify it again.

FTASK24156 was submitted as an enhancement request.