[ORACLE 12c JDBC connection] ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

환경 :  Oracle Cloud 12c + Spring framework

문제 :  JDBC로 PDB 연결시, 아래 에러 발생.

         ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

해결 : 

1. JDBC 연결방법 변경 ( app.jdbc.url=jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521/pdb1.moosangkim.oraclecloud.internal )

2. Oracle DB Cloud 에 ssh로 접속하여 USE_SID_AS_SERVICE_listener=on 추가 및 listener 재기동

* How to check service name of PDB :  

[oracle@ORCL1 opc]$ lsnrctl status

....

Service "ORCL.moosangkim.oraclecloud.internal" has 1 instance(s).

  Instance "ORCL", status READY, has 1 handler(s) for this service...

Service "ORCLXDB.moosangkim.oraclecloud.internal" has 1 instance(s).

  Instance "ORCL", status READY, has 1 handler(s) for this service...

Service "pdb1.moosangkim.oraclecloud.internal" has 1 instance(s).

  Instance "ORCL", status READY, has 1 handler(s) for this service...

The command completed successfully

JDBC Connections to PDBs

It has already been mentioned that you must connect to a PDB using a service. This means that by default many JDBC connect strings will be broken. Valid JDBC connect strings for Oracle use the following format.

# Syntax
jdbc:oracle:thin:@[HOST][:PORT]:SID
jdbc:oracle:thin:@[HOST][:PORT]/SERVICE

# Example
jdbc:oracle:thin:@ol6-121:1521:pdb1
jdbc:oracle:thin:@ol6-121:1521/pdb1

When attempting to connect to a PDB using the SID format, you will receive the following error.

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Ideally, you would correct the connect string to use services instead of SIDs, but if that is a problem the USE_SID_AS_SERVICE_listener_name listener parameter can be used.

Edit the "$ORACLE_HOME/network/admin/listener.ora" file, adding the following entry, with the "listener" name matching that used by your listener.

USE_SID_AS_SERVICE_listener=on

Reload or restart the listener.

$ lsnrctl reload

Now both of the following connection attempts will be successful as any SIDs will be treated as services.

jdbc:oracle:thin:@ol6-121:1521:pdb1
jdbc:oracle:thin:@ol6-121:1521/pdb1

참고 사이트 :  https://oracle-base.com/articles/12c/multitenant-connecting-to-cdb-and-pdb-12cr1#jdbc-connections-to-pdbs