12C 启用客户端通过SID 连接PDB，防止ORA-12545-白红宇

12C 启用客户端通过SID 连接PDB，防止ORA-12545

阅读量：5306 次

发布时间：2019-06-14

本文共 3382 字，大约阅读时间需要 11 分钟。

12C 通过SID 连接PDB 报ora-12545

通过在监听 listener.ora 加上配置：

USE_SID_AS_SERVICE_

listener name

=ON

在重新配置TNS:

测试连接：

参考文档：

文档 ID 1644355.1

APPLIES TO:Oracle Net Services - Version 12.1.0.1 and later

Information in this document applies to any platform.

SYMPTOMS

Client connection string uses SID to connect to TEST database .For example :

net12c =

(DESCRIPTION =

(ADDRESS=(protocol = tcp)(HOST=test.oracle.com)(port = 1521))

(CONNECT_DATA=(SERVER=DEDICATED)(SID = TEST))

)

The TEST database is changed to a pluggable database and client connection fais with ORA-12505.

C:\Users\test>sqlplus sys/test_12c@net12c as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 16 18:15:25 2014

ERROR:

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

descriptor

CHANGES

The TEST database is now a PDB. Connections to a pluggable database use service name .

CAUSE

PDB is not an instance, so using SID in the connection string will not work unless the following listener.ora file

setting is in place: USE_SID_AS_SERVICE_listener name=ON

When the database is an Oracle Database 12c container database, the client must specify a service name in order to connect to it.

Listener status shows TEST as only a service :

     Listening Endpoints Summary... 
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.oracle.com)(PORT=1521))) 
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) 
Services Summary... 
Service "CDB1" has 1 instance(s). 
Instance "cdb1", status READY, has 1 handler(s) for this service... 
Service "CDB1XDB" has 1 instance(s). 
Instance "cdb1", status READY, has 1 handler(s) for this service... 
Service "CLRExtProc" has 1 instance(s). 
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service. 
Service "TEST" has 1 instance(s). 
Instance "cdb1", status READY, has 1 handler(s) for this service... 
The command completed successfully  

SOLUTION

Use USE_SID_AS_SERVICE_listener_name=on in listener.ora and restart the listener . This will enable the system identifier (SID) in the connect descriptor to be interpreted as a service name when a user attempts a database connection. Database clients with earlier releases of Oracle Database that have hard-coded connect descriptors can use this parameter to connect to a container or pluggable database.

Example of usage in listener.ora:

LISTENER=

(DESCRIPTION=

(ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

)

USE_SID_AS_SERVICE_LISTENER=ON

The connection will work after this change:

C:\Users\test>sqlplus sys/test_12c@TNSNet12c as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 16 18:28:40 2014

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt