I have a RHEL 8.9 server that we are trying to connect to an Oracle 19c database. I installed the JRE 11 OpenJDK, and the Oracle 21.8.0.0-ojdbc11 driver. When I set up the DB connection under “Datasource” and click create or update, I get the following error:
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
I can connect to the database with the given service name using sqlplus without an issue. How can I make this work? I do have a stack trace on the error and I can provide that if needed.
Also, I do notice that the driver shows “Update datasource connection Oracle 12c (Release 1)” on the Datasource page, but our Oracle server is 19c.
Thanks,
Harry
OS: RHEL 8.9
Java Version: 11.0.23
Tomcat Version: 9.0.85.0
Lucee Version: 6.0.1.83
This is more of an oracle issue than a lucee one
You could do something like this in your Application.cfc
this.oracle = {
driver : ‘oracle.jdbc.OracleDriver’,
url : ‘jdbc:oracle:thin:@:/’, // Replace placeholders
username : ‘#username#’,
password : ‘#password#’
};
more than likely your listener config is screwed up.
check the lister status
in oracle cli
lsnrctl status
login to oracle
sqlplus sys/oracle as sysdba
tnsping xe
if this is more than a few milliseconds, edit your /etc/localhost to include the missing host entry and bind it to localhost , ie 127.0.0.1 my.oracle.orc
Our application doesn’t have an Application.cfc. And the error I’m getting is happening within the Lucee Server Admin pages when I’m trying to create the datasource. It errors out and I don’t believe that anything was ever configured for me to look at the JDBC settings that you reference.
Thanks,
Harry
Is Lucee on the same server as your oracle db?
did you install the Lucee Oracle extension?
The error is the symptom not the cause of your issue.
if you want to post the raw logs that would be helpful in troubleshooting your issue, though oracle support will tell you the same thing.
its always, at every step check logs.
for oracle database troubleshooting.
verify the host of the db can connect to itself
verify the listerner(s) are correctly configured
verify the correct security is applied to the database(s) / tables for the given resource
verify connectivity between server 1 & server 2 (ping)
verify the ports are open between server 1 & server 2
verify server 2 at the cli connect to server1
Lucee is not on the same machine as the Oracle DB. The DB is on a separate VM. As a test, I tried configuring the datasource to use the same hostname and database name as the system that we are doing away with. That system is Lucee 5.3.9.160. The same error occurs.
I can connect via the command line using sqlplus to the database using the correct username and password. I can ping back and forth. I ran tcpdump on both servers and filtered on port 1521, and I the traffic flows correctly. I just can’t figure out why the DB server is telling me that the SID is not known. My DBA has verified that settings are correct. The fact that I get the same error against a DB server that is currently in use and works concerns me. It makes me think that the issue is with Lucee 6.0.1.83 itself.
Thanks,
Harry
I was not clear enough, We need logs, and details.
on the oracle server (server 1) do the following:
sqlplus /nolog
conn system
alter system register;
exit
lsnrctl status
then manually go over to server 2 (lucee) and run the very sql connect string manually
again, this is not a lucee issue. Your connection string is wrong
I think I got this working now, but the solution is not intuitive (and I think it is a bug in Lucee).
I made my Oracle connection but unchecked “Verify Connection”
I stopped Lucee: /etc/init.d/lucee_ctl stop
Then I edited vim /opt/lucee/tomcat/lucee-server/context/.CFConfig.json, and in my connection settings, under “dsn”, I edited “dsn”: “jdbc:oracle:{drivertype}:@{host}:{port}/{database}” and changed the colon between {port} and {database} to a /.
I also had to change “port”:”1521” to “port”:1521 to eliminate the “Invalid number format for port number” error that I started getting instead.
Restarted Lucee: /etc/init.d/lucee_ctl start
Now the connection verifies and I can get our page to show up.
Thanks,
Harry