Oracle 18c XE: Connecting to a PDB using SID (for legacy application)

Question

I just installed the Oracle 18c XE Dadtabase and created a PDB (XEPDB1) inside the CDB. I then created a new schema inside the PDF and I can connect to that schema successfully using the Service Name (SQL Developer).

But my legacy application requires a SID connection, which seems to be allowed using the USE_SID_AS_SERVICE_XE = on line in listener.ora. I also added the SID_DESC to the SID_LIST, but I still get the error:

TNS:listener does not currently know of SID given in connect descriptor

This is my listener.ora:

DEFAULT_SERVICE_LISTENER = XE
USE_SID_AS_SERVICE_XE = on

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\michael\product\18.0.0\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\michael\product\18.0.0\dbhomeXE\bin\oraclr18.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = XE
      (ORACLE_HOME = C:\app\michael\product\18.0.0\dbhomeXE)
      (SID_NAME = XEPDB1
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = persik.ddns.vmware.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

And this is the tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

Admin · Answer

It is supposed to be USE_SID_AS_SERVICE_LISTENER_NAME refer section 7.4.19 in your case listener name is LISTENER i.e USE_SID_AS_SERVICE_LISTENER

Test case with Java code and 18xe docker container.

Commented out line in listener.ora and running java code

[oracle@240946cde855 admin]$ cat listener.ora  | grep on
# listener.ora Network Configuration File:
#USE_SID_AS_SERVICE_LISTENER=on

 $grep "1521" Conn.java
  Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.99.128:1521:xepdb1", "hr", "hr");

$ java -cp "ojdbc8.jar" Conn
Exception in thread "main" 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

Uncommenting and bouncing listener

[oracle@240946cde855 admin]$ sed -i 's|#USE|USE|g' listener.ora
[oracle@240946cde855 admin]$ cat listener.ora  | grep on
# listener.ora Network Configuration File:
USE_SID_AS_SERVICE_LISTENER=on
[oracle@240946cde855 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 22-JUL-2020 10:39:52

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

$ java -cp "ojdbc8.jar" Conn
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

Oracle 18c XE: Connecting to a PDB using SID (for legacy application)

1 Answers