ORA-12514 error

Has anyone observed this error in a TeamCity build?

[08:44:20]: [script] java.sql.SQLException: Listener refused the connection with the following error:

[08:44:20]: [script] ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

I've observed it 3 times over the past couple of months, but cannot figure out the reason. We are using Oracle 10g with maxConnections set to 5 (per TC installation instructions). I'd appreciate any help in finding the cause.

Thanks,

Oleg.

9 comments
Comment actions Permalink

Oleg,

Could you please specify your TeamCity version/build number?

0
Comment actions Permalink

Oleg, what is your connectionUrl property value?
Do you use Oracle 10g express edition or not?

Thanks

0
Comment actions Permalink

4.5.3 build 9035

0
Comment actions Permalink

connectionUrl=jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST= oracle-prod)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST= oracle-prod-ds)(PORT=1521))(CONNECT_DATA=(service_name= proddg.world)))

Oracle 10.2.0.4 Enterprise

0
Comment actions Permalink

Oleg,

we didn't test TeamCity with such style connection URLs.

Could you please specify the URL in a simple style as exampled below:

  connectionUrl=jdbc:oracle:thin:@oracle-prod-ds:1521:proddg.world

Best regards,
Leo

0
Comment actions Permalink

Leonid,

I realize that it is not mainsteam usage, so I should explain this scenario. This connection string allows TeamCity to connect to our production Oracle server normally and switch to a backup server if the main server fails for some reason and we fail over to the backup server. This way I don't have to modify the connection string, and everything is automatically handled by our infrastructure (especially good if people are out of the office). According to our DB admin, the backup server is the standby server in the dataguard configuration, so always in recovery while it's the standby. Data is synced between two Oracle servers, so there shouldn't be data loss. The connection string seems to work MOST of the time except for those 3 times that I've seen ORA-12514 error. We checked connections to Oracle from TeamCity, and it showed 5 inactive connections around the time of the failure. Why is the recommended setting for maxConnections=5? Could we possibly increase maxConnections to something like 10? Should that number be at least the same as the number of agents to match the number of simultaneous builds that might connect to the DB? Also (and this is a long shot), are there some connections that are left open by TeamCity if there are too many builds going on at the same time? Our DB admin reported that he normally sees 5 connections from TeamCity, but that's the limit we set (there is no limit on DB side).

I would prefer to keep connectionUrl as it is since it seems to work most of the time and gives a confidence boost for continuity in case the Oracle server goes down. Could you test this scenario with two servers and large number of simultaneous builds and see if you observe this error? I would imagine there are other users who would be interested in this usage.

Thanks,

Oleg.

0
Comment actions Permalink

Oleg,

thank you for the explanation.
As of number of connections, I recommend you to increase it up to 40-50. But, it seems will not cure the problem.

As of connection url... I've got a question: have you got installed Oracle Client on the host where TeamCity is installed? if yes it may be a reason to try to use oci8 jdbc driver with native Oracle client. But this option we didn't test too

Best regards,
Leonid

0
Comment actions Permalink

I have increased it to 10 per recommendation of our DBA. We do have Oracle Client installed, so I will try the oci8 driver and see how it works. Can you please tell me which jars/dlls from the client I need and where to put them in TeamCity installation?

0
Comment actions Permalink

Hi Oleg,

In order to use OCI driver you can do the following:

1. Ensure that your PATH environment variable contains the path to the oracle binaries.
2. Create correct database alias (you can use Oracle Net Configuration Assitant or edit TNSNAMES.ORA manually).
3. Modify the "database.properties" file, change connectUrl to the following one:

jdbc:oracle:oci:@<database_alias>

and start TeamCity. It should work.

I just verified it on:

TeamCity 5.0

Oracle 11g SE, 64-bit

Java 1.6, 64-bit

P.S. ensure that the <TeamCity_home>/webapps/ROOT/WEB-INF/lib directory contains ojdbc6.jar and orai18n.jar and doesn't contain other oracle jdbc files.

Best regards!

0

Please sign in to leave a comment.