Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Conecting to PDB via JDBC failed with ORA-12505

Uwe AppmeierJan 8 2024 — edited Jan 8 2024

Hi there
I am playing around with setting ub CDB/PDB logic for my client
Now I get an error when connecting via JDBC to the PDB. From e.g. sqlDeveloper locally it works.

We are not using listner.ora by now

Entry in tnsnames.ora:

#
ORCDB01E2,ORCDB01E2.<Domain>=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rcsorcdb01e2.<domain>)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcdb01e2)))
UZNACHDB,UZNACHDB.<domain>=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rcsorcdb01e2.<domain>)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=uznachdb)))

lsnrctl status:

[oracle@rcsorcdb01e2 admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 08-JAN-2024 11:13:26
Copyright (c) 1991, 2022, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                08-JAN-2024 10:48:23
Uptime                    0 days 0 hr. 25 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/rcsorcdb01e2/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rcsorcdb01e2.<domain>)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rcsorcdb01e2.<domain>)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcdb01e2/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
 Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
 Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "0c8dde113f3f5cdce063a40ff40aadad" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
Service "0d059036ea1b44e7e063a40ff40a2098" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
Service "0e6bcb0de1023765e063a40ff40a8c7c" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
Service "orcdb01e2" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
Service "orcdb01e2XDB" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
Service "uznachdb" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...

lsnrctl services:

[oracle@rcsorcdb01e2 admin]$ lsnrctl services
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 08-JAN-2024 11:15:57
Copyright (c) 1991, 2022, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
 Instance "+ASM", status READY, has 1 handler(s) for this service...
   Handler(s):
     "DEDICATED" established:0 refused:0 state:ready
        LOCAL SERVER
Service "+ASM_DATA" has 1 instance(s).
 Instance "+ASM", status READY, has 1 handler(s) for this service...
   Handler(s):
     "DEDICATED" established:0 refused:0 state:ready
        LOCAL SERVER
Service "0c8dde113f3f5cdce063a40ff40aadad" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
   Handler(s):
     "DEDICATED" established:1 refused:0 state:ready
        LOCAL SERVER
Service "0d059036ea1b44e7e063a40ff40a2098" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
   Handler(s):
     "DEDICATED" established:1 refused:0 state:ready
        LOCAL SERVER
Service "0e6bcb0de1023765e063a40ff40a8c7c" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
   Handler(s):
     "DEDICATED" established:1 refused:0 state:ready
        LOCAL SERVER
Service "orcdb01e2" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
   Handler(s):
     "DEDICATED" established:1 refused:0 state:ready
        LOCAL SERVER
Service "orcdb01e2XDB" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
   Handler(s):
     "D000" established:0 refused:0 current:0 max:1022 state:ready
        DISPATCHER <machine: rcsorcdb01e2.<domain>, pid: 15518>
        (ADDRESS=(PROTOCOL=tcp)(HOST=rcsorcdb01e2.<domain>)(PORT=39553))
Service "uznachdb" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
   Handler(s):
     "DEDICATED" established:1 refused:0 state:ready
        LOCAL SERVER
Service "uzwildb" has 1 instance(s).
 Instance "orcdb01e2", status READY, has 1 handler(s) for this service...
   Handler(s):
     "DEDICATED" established:1 refused:0 state:ready
        LOCAL SERVER
The command completed successfully

Error Message from script which uses jdbc-connection:

used jdbc-connection: jdbc:oracle:thin:@rcsorcdb01e2.<domain>:1521:uznachdb'

Unexpected error running Liquibase: 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
SEVERE 1/8/24 10:33 AM: liquibase: 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
liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: 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
       at liquibase.integration.commandline.CommandLineUtils.createDatabaseObject(CommandLineUtils.java:127)
       at liquibase.integration.commandline.Main.doMigration(Main.java:958)
       at liquibase.integration.commandline.Main.run(Main.java:188)
       at liquibase.integration.commandline.Main.main(Main.java:103)
Caused by: liquibase.exception.DatabaseException: 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
       at liquibase.database.DatabaseFactory.openConnection(DatabaseFactory.java:247)
       at liquibase.database.DatabaseFactory.openDatabase(DatabaseFactory.java:151)
       at liquibase.integration.commandline.CommandLineUtils.createDatabaseObject(CommandLineUtils.java:85)
       ... 3 more

Connection from my local PC via sqlDeveleoper works fine with service name : uznachdb

We are using liquibase (a quiet old version) from different server which has no oracle client installed.
All runs connecting a single database or tthe CDB works - only CDB will return user:passsword error - which is clearly and understandable

Thanks for any hint

Uwe

This post has been answered by Uwe Appmeier on Jan 8 2024
Jump to Answer

Comments

Sdhamoth-Oracle Oct 16 2024

Check the error reported in “Control panel→System and Securities→Administrative Tools→Event Viewer” under Windows Log→Application.

How did you set the Instant Client path in PATH? In command prompt?

If you have set it in a command prompt, then make sure you start that command prompt as administrator (i.e. opening it by right clicking on command prompt icon and select “run as administrator” from popped context menu). Also, you should start ODBC data source administrator by executing “odbcad32” from that command prompt. Have you?

pczurak-JavaNet Oct 16 2024

Yes I started the cmd as run as Administrator.

I am using ODBC 64 bit not 32 bit.

I set the path using Windows UI (System properties), and if I open a cmd window and type set I can see the correct path.

starting “odbcad32” from the command line did not help.

I tried the same process on a different computer and got the same results.

Sdhamoth-Oracle Oct 16 2024

odbcad32 is for ODBC 64 bit on Windows x64 versions. You need to start odbcad32 from syswow64 directory to configure ODBC 32 bit on Windows x64 versions.

Have you checked the Application error logs in Event viewer? Did you find any error related to your ODBC application?

pczurak-JavaNet Oct 16 2024

I am running the 64bit version, if I start odbcad32 from syswow64 it starts the 32bit version.

There are no errors in the Event Viewer.

pczurak-JavaNet Oct 17 2024

I have tried basic driver version 19.24, but when I click on the Test button, I get the following error:

---------------------------
Testing Connection
---------------------------
Unable to connect
SQLState=S1000
[Oracle][ODBC][Ora]ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

---------------------------
OK
---------------------------

Sdhamoth-Oracle Oct 17 2024

Looks like the service name is missing in your connection string.

Here is a sample of connection string:

sales=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com)))
The part that is highlighted with bold characters seems to be missing in your connection string. Fix it.

pczurak-JavaNet Oct 17 2024

This is what I am using.

JDEPRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testoda0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jdeprd.test.com)
)
)

Sdhamoth-Oracle Oct 17 2024

Are you able to connect to your database using SQL*Plus?

pczurak-JavaNet Oct 17 2024

No:

F:\instantclient_23_5>sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 17 07:55:39 2024
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle. All rights reserved.

Enter user-name: testuser
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:

pczurak-JavaNet Oct 17 2024

The issue is fixed

In the ODBC Driver Configuration.

For the TNS Service Name you cannot type the name even if you type it exactly as it shows in the dropdown, you must select it from the dropdown, otherwise it won't work.

1 - 10

Post Details

Added on Jan 8 2024
1 comment
671 views