5

I have a strange problem with jdbc connection to an oracle database server.

We've applications on a tomcat server running. These application use an oracle database. All applications use the same credentials.

Applications running fine the whole day. At night there is no activity. In the morning we get a few (2 or 3) ORA-01017 (invalid username/password) errors when the applications trying to reconnect themselves to the database.

Then reconnection works and the applications will operate normal.

This works for some days (around 5 days) and then one or more of the application block! All reconnection attempts fail.

We've traced network communication and found that if the connection fails with ORA-01017 NO CREDENTIALS where sent.

Of course no one touches the system at night.

One attempt for a workaround is that we restart the tomcat server every morning at 6 o'clock to clean up every connection cache or pool. It does not help.

Whats wrong? Any ideas?

The continued 5 day interval in the appearance of a total blockage (while restating every day) looks very strange to me.

Config :

Database Oracle 10.2, JDBC Driver 11.2 thin, tomcat 6.0.24, JDK 6, OS is windows, some of the applications are Dialogs for the Avaya Voice Portal 5.0.

Our own (non VP) applications use simple Connections (no pooling).

The system was originally setup on a Windows 2003 server with a WAN between apserver and tomcat server.

The system is now migrated to a linux (CentOS) server near the database server and works fine. No ORA-01017 anymore.

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
Hajo Thelen
  • 1,095
  • 1
  • 11
  • 16
  • Are the credentials tied to an LDAP account, and if so, does that account have restrictions on what time of day it's allowed to login (e.g. causing the overnight issues)? – jefflunt Dec 08 '11 at 18:30
  • try to think where do you hold the credentials, and what else happens at night, are there any resources down for backups or something ? – A.B.Cade Dec 08 '11 at 19:03
  • @normalocity : simple database users configured in local config files. No LDAP. – Hajo Thelen Dec 09 '11 at 14:52
  • @A.B.Cade : credentials are stored in local files. my information is that systems run 24/7. – Hajo Thelen Dec 09 '11 at 14:56
  • I've forgotten one detail which might be important : the oracle database is virtualized. – Hajo Thelen Dec 09 '11 at 14:58
  • 3
    I've seen this kind of issue due to app server connection pools not releasing resources well when database is down for overnights backups. The workaround is stop the app server before the backups and starting it againd after the backups. Are you sure that the database is not shutdown overnight? – JuanZe Dec 15 '11 at 13:13

4 Answers4

1

For me incompatible version of OracleDriver was causing this issue Your application should either register oracle driver manually (which jar that I needed to work with was doing) or agter java 6 ojdbc.jar should be in the classpath for your application. So google compatible driver version for your oracle installation and either declare it in your pom file (with needed plugin to put it in the resulting jar) and reference it manually from code or put the ojdbc.jar somewhere your jar can see it Usefull links:

About connecting to oracle db: https://www.codejava.net/java-se/jdbc/connect-to-oracle-database-via-jdbc

About java classpath: https://docs.oracle.com/javase/7/docs/technotes/tools/windows/classpath.html

Nikita Poberezkin
  • 51
  • 7
0

As Nikita Poberezkin said, I checked my JDBC driver versions and saw it was different from our test server. I removed it and installed the same exact version of the test server and I guess it's solved now.

Bünyamin Şentürk
  • 620
  • 5
  • 20
0

Odd. Some ideas:

  1. Log the user name and password for a few days just to make sure they are correct. Some bug in the code might overwrite a value that you don't expect.

  2. Consider to use JNDI with a connection pool provided by Tomcat. DBCP has some really advanced options to check whether a connection is still alive and how to reconnect it. After that, you shouldn't see any connection related issues in your logs anymore. This would also improve security because none of the apps need to know the DB password anymore.

  3. It might be a problem with resource leaks (happens if an app never returns the connection) but I'd expect a different error message, then.

  4. Some databases (DB2, H2) allow to create views that make remote tables from other databases visible like local tables. Not sure whether Oracle supports this but if it does, then maybe the user name/password for this remote table is wrong.

Also consider the points in this blog post: Oracle ORA-01017 tips

At first glance, nothing in the post could cause your problems but maybe some script is manipulating the tnsnames.ora (for example distributing a new copy).

Or the DBA disabled all users for the time of the daily backup.

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • Thanks for your answer. I'll check out some of your suggestions. One Point : we're using the thin driver. That means no tnsname.ora is used. Looks that I'm missing that point in my original post. – Hajo Thelen Dec 19 '11 at 17:29
  • because the error does not occur anymore (switched from windows server to centos system (may be just coincidence)) and because of useful suggestions i'll set this to answered. – Hajo Thelen Jun 08 '12 at 07:47
0

I'd check Oracle listener and trace logs. As Aaron Digulla said, it does sound like some resource exhaustion.

Nicholas Sushkin
  • 13,050
  • 3
  • 30
  • 20