Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

JDBC connection issue #87

Open
Yuuki-Sakura opened this issue Feb 25, 2025 · 8 comments
Open

JDBC connection issue #87

Yuuki-Sakura opened this issue Feb 25, 2025 · 8 comments
Assignees
@gvenzl
Labels
question Further information is requested

Comments

@Yuuki-Sakura
Copy link

Yuuki-Sakura commented Feb 25, 2025 •
edited
Loading

Unable to connect to Oracle using JDBC. I have tried multiple connection strings but none of them were successful.

When using the connection string jdbc:oracle:thin:@localhost:1521:FREE, the following error is received: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

docker run: docker run -d --name oracledb -p 1521:1521 -e ORACLE_PASSWORD=test -e APP_USER=test -e APP_USER_PASSWORD=test gvenzl/oracle-free:slim-faststart

container logs:

CONTAINER: starting up...
CONTAINER: first database startup, initializing...
CONTAINER: starting up Oracle Database...

LSNRCTL for Linux: Version 23.0.0.0.0 - Production on 25-FEB-2025 07:21:11

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

Starting /opt/oracle/product/23ai/dbhomeFree/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 23.0.0.0.0 - Production
System parameter file is /opt/oracle/product/23ai/dbhomeFree/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/e20567b71e76/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_FREE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_FREE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 23.0.0.0.0 - Production
Start Date                25-FEB-2025 07:21:12
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           FREE
Listener Parameter File   /opt/oracle/product/23ai/dbhomeFree/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/e20567b71e76/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_FREE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully
ORACLE instance started.

Total System Global Area 1603787632 bytes
Fixed Size		    5421936 bytes
Variable Size		  654311424 bytes
Database Buffers	  939524096 bytes
Redo Buffers		    4530176 bytes
Database mounted.
Database opened.

CONTAINER: Resetting SYS and SYSTEM passwords.

User altered.


User altered.

CONTAINER: Creating app user for default pluggable database.

Session altered.


User created.


Grant succeeded.

CONTAINER: DONE: Creating app user for default pluggable database.

#########################
DATABASE IS READY TO USE!
#########################

####################################################################
CONTAINER: The following output is now from the alert_FREE.log file:
####################################################################
Thread 1 advanced to log sequence 12 (LGWR switch),  current SCN: 2747163
  Current log# 1 seq# 12 mem# 0: /opt/oracle/oradata/FREE/redo01.log
Thread 1 cannot allocate new log, sequence 13
Checkpoint not complete
  Current log# 1 seq# 12 mem# 0: /opt/oracle/oradata/FREE/redo01.log
2025-02-25T07:21:20.922184+00:00
Thread 1 advanced to log sequence 13 (LGWR switch),  current SCN: 2747213
  Current log# 2 seq# 13 mem# 0: /opt/oracle/oradata/FREE/redo02.log
2025-02-25T07:21:20.989200+00:00
Resize operation completed for file# 18, fname /opt/oracle/oradata/FREE/undotbs01.dbf, old size 11264K, new size 21504K
FREEPDB1(3):ORA-08102 at location 2 obj# 18457, rdba: 0x00001473(file# 22, block# 5235) - Check trace file:
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_m004_173.trc
FREEPDB1(3):********************************************************************
A process (pid: 173) has internally requested a dump of selected
redo. This usually happens following a specific internal error, when 
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by 
all the instances) during the past 12 hours, in case additional redo
dumps are required to help with the diagnosis.
********************************************************************
2025-02-25T07:21:21.470178+00:00
FREEPDB1(3):Errors in file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_m004_173.trc:
2025-02-25T07:21:22.983125+00:00
FREEPDB1(3):********************************************************************
A process (pid: 173) has internally requested a dump of selected
redo. This usually happens following a specific internal error, when 
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by 
all the instances) during the past 12 hours, in case additional redo
dumps are required to help with the diagnosis.
********************************************************************
2025-02-25T07:21:23.389880+00:00
Thread 1 advanced to log sequence 14 (LGWR switch),  current SCN: 2747971
  Current log# 1 seq# 14 mem# 0: /opt/oracle/oradata/FREE/redo01.log
Thread 1 cannot allocate new log, sequence 15
Checkpoint not complete
  Current log# 1 seq# 14 mem# 0: /opt/oracle/oradata/FREE/redo01.log
2025-02-25T07:21:23.749959+00:00
Thread 1 advanced to log sequence 15 (LGWR switch),  current SCN: 2748788
  Current log# 2 seq# 15 mem# 0: /opt/oracle/oradata/FREE/redo02.log
2025-02-25T07:21:24.026537+00:00
FREEPDB1(3):Resize operation completed for file# 23, fname /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf, old size 11264K, new size 21504K
2025-02-25T07:21:28.634256+00:00
Thread 1 cannot allocate new log, sequence 16
Checkpoint not complete
  Current log# 2 seq# 15 mem# 0: /opt/oracle/oradata/FREE/redo02.log
2025-02-25T07:21:28.754440+00:00
Thread 1 advanced to log sequence 16 (LGWR switch),  current SCN: 2751767
  Current log# 1 seq# 16 mem# 0: /opt/oracle/oradata/FREE/redo01.log
2025-02-25T07:22:18.504448+00:00
TABLE SYS.WRP$_REPORTS: ADDED AUTOLIST FRAGMENT SYS_P462 (3) VALUES (( 1459785199, TO_DATE(' 2025-02-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ))
TABLE SYS.WRP$_REPORTS_DETAILS: ADDED AUTOLIST FRAGMENT SYS_P463 (3) VALUES (( 1459785199, TO_DATE(' 2025-02-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ))
TABLE SYS.WRP$_REPORTS_TIME_BANDS: ADDED AUTOLIST FRAGMENT SYS_P466 (3) VALUES (( 1459785199, TO_DATE(' 2025-02-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ))
2025-02-25T07:31:20.427436+00:00
Thread 1 advanced to log sequence 17 (LGWR switch),  current SCN: 2753137
  Current log# 2 seq# 17 mem# 0: /opt/oracle/oradata/FREE/redo02.log
2025-02-25T07:31:21.248905+00:00
Resize operation completed for file# 3, fname /opt/oracle/oradata/FREE/sysaux01.dbf, old size 387328K, new size 459008K
2025-02-25T07:32:19.542981+00:00
FREEPDB1(3):Resize operation completed for file# 23, fname /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf, old size 21504K, new size 31744K
2025-02-25T07:32:20.805142+00:00
Thread 1 cannot allocate new log, sequence 18
Checkpoint not complete
  Current log# 2 seq# 17 mem# 0: /opt/oracle/oradata/FREE/redo02.log
2025-02-25T07:32:21.138298+00:00
Thread 1 advanced to log sequence 18 (LGWR switch),  current SCN: 2754841
  Current log# 1 seq# 18 mem# 0: /opt/oracle/oradata/FREE/redo01.log

docker info:

Client:
 Version:    27.5.1
 Context:    orbstack
 Debug Mode: false
 Plugins:
  buildx: Docker Buildx (Docker Inc.)
    Version:  v0.20.1
    Path:     /Users/sakura/.docker/cli-plugins/docker-buildx
  compose: Docker Compose (Docker Inc.)
    Version:  v2.32.4
    Path:     /Users/sakura/.docker/cli-plugins/docker-compose
WARNING: Plugin "/Users/sakura/.docker/cli-plugins/docker-dev" is not valid: failed to fetch metadata: fork/exec /Users/sakura/.docker/cli-plugins/docker-dev: no such file or directory
WARNING: Plugin "/Users/sakura/.docker/cli-plugins/docker-extension" is not valid: failed to fetch metadata: fork/exec /Users/sakura/.docker/cli-plugins/docker-extension: no such file or directory
WARNING: Plugin "/Users/sakura/.docker/cli-plugins/docker-sbom" is not valid: failed to fetch metadata: fork/exec /Users/sakura/.docker/cli-plugins/docker-sbom: no such file or directory
WARNING: Plugin "/Users/sakura/.docker/cli-plugins/docker-scan" is not valid: failed to fetch metadata: fork/exec /Users/sakura/.docker/cli-plugins/docker-scan: no such file or directory
WARNING: Plugin "/Users/sakura/.docker/cli-plugins/docker-scout" is not valid: failed to fetch metadata: fork/exec /Users/sakura/.docker/cli-plugins/docker-scout: no such file or directory

Server:
 Containers: 6
  Running: 1
  Paused: 0
  Stopped: 5
 Images: 18
 Server Version: 27.5.1
 Storage Driver: overlay2
  Backing Filesystem: btrfs
  Supports d_type: true
  Using metacopy: false
  Native Overlay Diff: true
  userxattr: false
 Logging Driver: json-file
 Cgroup Driver: cgroupfs
 Cgroup Version: 2
 Plugins:
  Volume: local
  Network: bridge host ipvlan macvlan null overlay
  Log: awslogs fluentd gcplogs gelf journald json-file local splunk syslog
 Swarm: inactive
 Runtimes: io.containerd.runc.v2 runc
 Default Runtime: runc
 Init Binary: docker-init
 containerd version: c507a0257ea6462fbd6f5ba4f5c74facb04021f4
 runc version: 59923ef18c98053ddb1acf23ecba10344056c28e
 init version: de40ad0
 Security Options:
  seccomp
   Profile: builtin
  cgroupns
 Kernel Version: 6.12.15-orbstack-00304-gd0ddcf70447d
 Operating System: OrbStack
 OSType: linux
 Architecture: aarch64
 CPUs: 8
 Total Memory: 7.81GiB
 Name: orbstack
 ID: 8c255493-f190-4313-9102-161f928811a9
 Docker Root Dir: /var/lib/docker
 Debug Mode: false
 Experimental: false
 Insecure Registries:
  127.0.0.0/8
 Live Restore Enabled: false
 Product License: Community Engine
 Default Address Pools:
   Base: 192.168.97.0/24, Size: 24
   Base: 192.168.107.0/24, Size: 24
   Base: 192.168.117.0/24, Size: 24
   Base: 192.168.147.0/24, Size: 24
   Base: 192.168.148.0/24, Size: 24
   Base: 192.168.155.0/24, Size: 24
   Base: 192.168.156.0/24, Size: 24
   Base: 192.168.158.0/24, Size: 24
   Base: 192.168.163.0/24, Size: 24
   Base: 192.168.164.0/24, Size: 24
   Base: 192.168.165.0/24, Size: 24
   Base: 192.168.166.0/24, Size: 24
   Base: 192.168.167.0/24, Size: 24
   Base: 192.168.171.0/24, Size: 24
   Base: 192.168.172.0/24, Size: 24
   Base: 192.168.181.0/24, Size: 24
   Base: 192.168.183.0/24, Size: 24
   Base: 192.168.186.0/24, Size: 24
   Base: 192.168.207.0/24, Size: 24
   Base: 192.168.214.0/24, Size: 24
   Base: 192.168.215.0/24, Size: 24
   Base: 192.168.216.0/24, Size: 24
   Base: 192.168.223.0/24, Size: 24
   Base: 192.168.227.0/24, Size: 24
   Base: 192.168.228.0/24, Size: 24
   Base: 192.168.229.0/24, Size: 24
   Base: 192.168.237.0/24, Size: 24
   Base: 192.168.239.0/24, Size: 24
   Base: 192.168.242.0/24, Size: 24
   Base: 192.168.247.0/24, Size: 24

Thank you for your help!
@gvenzl
Copy link
Owner

gvenzl commented Feb 26, 2025

Hey @Yuuki-Sakura,

Thanks a lot for using these images!

Try the following: jdbc:oracle:thin:@localhost:1521/freepdb1

There is a big difference between the : and / and also you should almost always be connecting to the freepdb1 database, not the container database.

@Yuuki-Sakura
Copy link
Author

I tried, but I still cannot connect.
Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

@andytael
Copy link

andytael commented Feb 28, 2025 •
edited
Loading

@Yuuki-Sakura

I would try jdbc:oracle:thin:@//localhost:1521/freepdb1, I think Gerald missed the double-slashes

@victorherraiz
Copy link

victorherraiz commented Mar 5, 2025 •
edited
Loading

Use jdbc:oracle:thin:@localhost:1521/FREE, it appears that works on arm64 images (e.g. Apple M)

@Yuuki-Sakura
Copy link
Author

Use jdbc:oracle:thin:@localhost:1521/FREE, it appears that works on arm64 images (e.g. Apple M)

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

@gvenzl gvenzl self-assigned this Mar 6, 2025
@gvenzl gvenzl added the question Further information is requested label Mar 6, 2025
@gvenzl
Copy link
Owner

gvenzl commented Mar 8, 2025

Hi @Yuuki-Sakura,

Unfortunately, I hoped this would be a quick fix but unfortunately, it turns out that something else is going on here.
To determine what's going on, I'll have to ask some more questions.

I can already tell that you can reach the port and the Listener process inside the container because you are getting ORA-12514, TNS:listener does not currently know of service requested in connect descriptor which is an error generated by the Listener process.
So what that tells us is:

  • You can reach port 1521 (no firewall, etc. issues)
  • You can reach the listener inside the container and it is running (otherwise some error "no listener" would come back)
  • The Listener cannot determine to which database services FREE or FREEPDB1 belong. There is only one database inside the container, but the Listener has a registration process for each database and from what it looks like right now, that has gone wrong.

To determine what's going on, we will unfortunately have to go into the container.

When the container is up and running and you are getting this error from JDBC, could you please do the following:

  1. See whether jdbc:oracle:thin:@//localhost:1521/freepdb1 makes a difference, the difference is the 2 // in front of localhost
  2. ssh into the container via docker exec -ti oracledb bash
    2.1. Run lsnrctl status and please upload the output.
    2.2. Run sqlplus / as sysdba
    2.2.1. Run the following SQL query and upload its output: select name from v$active_services;
    2.2.2. Run the following SQL query and upload its output: select name, open_mode from v$pdbs;

@Yuuki-Sakura
Copy link
Author

Hi @Yuuki-Sakura,

Unfortunately, I hoped this would be a quick fix but unfortunately, it turns out that something else is going on here. To determine what's going on, I'll have to ask some more questions.

I can already tell that you can reach the port and the Listener process inside the container because you are getting ORA-12514, TNS:listener does not currently know of service requested in connect descriptor which is an error generated by the Listener process. So what that tells us is:

  • You can reach port 1521 (no firewall, etc. issues)
  • You can reach the listener inside the container and it is running (otherwise some error "no listener" would come back)
  • The Listener cannot determine to which database services FREE or FREEPDB1 belong. There is only one database inside the container, but the Listener has a registration process for each database and from what it looks like right now, that has gone wrong.

To determine what's going on, we will unfortunately have to go into the container.

When the container is up and running and you are getting this error from JDBC, could you please do the following:

  1. See whether jdbc:oracle:thin:@//localhost:1521/freepdb1 makes a difference, the difference is the 2 // in front of localhost
  2. ssh into the container via docker exec -ti oracledb bash
    2.1. Run lsnrctl status and please upload the output.
    2.2. Run sqlplus / as sysdba
    2.2.1. Run the following SQL query and upload its output: select name from v$active_services;
    2.2.2. Run the following SQL query and upload its output: select name, open_mode from v$pdbs;

Still cannot connect.

Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
(CONNECTION_ID=4AZRRtFATRqo+BTdJD4HlQ==).

output:

bash-4.4$ lsnrctl status

LSNRCTL for Linux: Version 23.0.0.0.0 - Production on 10-MAR-2025 03:28:27

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_FREE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 23.0.0.0.0 - Production
Start Date                10-MAR-2025 03:27:51
Uptime                    0 days 0 hr. 0 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           FREE
Listener Parameter File   /opt/oracle/product/23ai/dbhomeFree/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/7861b5d5b809/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_FREE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully
bash-4.4$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Mon Mar 10 03:28:41 2025
Version 23.6.0.24.10

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


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.6.0.24.10

SQL> select name from v$active_services;

NAME
----------------------------------------------------------------
freeXDB
SYS$BACKGROUND
SYS$USERS
freepdb1
free

SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY

FREEPDB1
READ WRITE


SQL>

@gvenzl
Copy link
Owner

gvenzl commented Mar 16, 2025

Hi @Yuuki-Sakura,

Thank you very much for the additional information!

It appears that the listener somehow never managed to register the services provided by the database (according to the lsnrctl status output), yet the services are running (as shown by the queries from v$active_services and v$pdbs).

On a normal container startup, lsnrctl status should show some of the same services in its output, for example:

[gvenzl@localhost ~]$ podman run -d --name oracledb -p 1521:1521 -e ORACLE_PASSWORD=test -e APP_USER=test -e APP_USER_PASSWORD=test gvenzl/oracle-free:slim-faststart
ff5deeb20105b1e3e94e8a22b48ebe859bdb75ac226202b1dc932dc27f78eaa3
[gvenzl@localhost ~]$ podman exec -ti oracledb bash
bash-4.4$ lsnrctl status

LSNRCTL for Linux: Version 23.0.0.0.0 - Production on 16-MAR-2025 17:47:43

Copyright (c) 1991, 2025, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_FREE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 23.0.0.0.0 - Production
Start Date                16-MAR-2025 17:47:14
Uptime                    0 days 0 hr. 0 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           FREE
Listener Parameter File   /opt/oracle/product/23ai/dbhomeFree/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/ff5deeb20105/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_FREE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Services Summary...
Service "2fc4f8c53cbc1261e0630f02000a5dfc" has 1 instance(s).
  Instance "FREE", status READY, has 1 handler(s) for this service...
Service "FREE" has 1 instance(s).
  Instance "FREE", status READY, has 1 handler(s) for this service...
Service "FREEXDB" has 1 instance(s).
  Instance "FREE", status READY, has 0 handler(s) for this service...
Service "freepdb1" has 1 instance(s).
  Instance "FREE", status READY, has 1 handler(s) for this service...
The command completed successfully

Unfortunately, at this stage it's a question of why the database isn't communicating with the listener, which is not my area of expertise. Hence I will have to do some research and ask my colleagues how we can triage this further.
But my gut feeling is that this is somewhat related to the networking infrastructure provided inside the container. So if anything comes to mind at your end that could perhaps prevent the two from communicating (I can't think of anything myself) then please let me know.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@gvenzl gvenzl

Labels
question Further information is requested
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@gvenzl @andytael @victorherraiz @Yuuki-Sakura