背景:
? 工作中,我們可能會遇到一些客戶,需要通過SID的方式連接到PDB下面操作,尤其在RAC環(huán)境中,需要連到單獨的節(jié)點上。
以下是基于19.3環(huán)境下,測試如何通過SID的方式,連接到PDB中。
1. 檢查監(jiān)聽狀態(tài)
[oracle@ora19c ~]$ lsnrctl status LISTENER_ORCLCDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-JUL-2022 22:44:46
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1522)))
STATUS of the LISTENER
Alias LISTENER_ORCLCDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 12-JUL-2022 22:40:32
Uptime 0 days 0 hr. 4 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/ora19c/listener_orclcdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora19c)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ORCLCDB" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "a8be8cc09f902cd2e0530d0011ac912e" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb1" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
結(jié)論:數(shù)據(jù)庫監(jiān)聽狀態(tài)正常,包含CDB和PDB服務 。
2. 檢查數(shù)據(jù)庫狀態(tài)
[oracle@ora19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 12 22:44:51 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@ORCLCDB> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
結(jié)論:數(shù)據(jù)庫狀態(tài)正常,讀寫狀態(tài)。?
3. 配置客戶端tnsnames.ora
ORCLPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1) ) ) ORCLPDB1_SID = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = ORCLPDB1) ) )
4. tnsping測試
-- 測試SERVICE_NAME 方式
C:\Users\admin>tnsping orclpdb1
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 11-7月 -2022 15:35:08
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的參數(shù)文件:
D:\app\admin\product\11.2.0\client_1\network\admin\sqlnet.ora
已使用 TNSNAMES 適配器來解析別名
嘗試連接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1)))
OK (20 毫秒)
-- 測試SID方式
C:\Users\admin>tnsping orclpdb1_sid
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 11-7月 -2022 15:35:13
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的參數(shù)文件:
D:\app\admin\product\11.2.0\client_1\network\admin\sqlnet.ora
已使用 TNSNAMES 適配器來解析別名
嘗試連接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = ORCLPDB1)))
OK (0 毫秒)
結(jié)論:SERVICE_NAME 方式和SID 方式,tnsping測試都沒有問題。?
5. 連接測試
-- 測試SERVICE_NAME 方式
C:\Users\admin>sqlplus sxc/sxc@orclpdb1
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 7月 11 15:34:47 2022
Copyright (c) 1982, 2010, Oracle. All rights reserved.
連接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> exit
從 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 斷開
-- 測試SID 方式
C:\Users\admin>sqlplus sxc/sxc@orclpdb1_sid
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 7月 11 15:34:52 2022
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12505: TNS: 監(jiān)聽程序當前無法識別連接描述符中所給出的 SID
請輸入用戶名:
結(jié)論:SERVICE_NAME 方式可以正常連接到數(shù)據(jù)庫,而SID 方式連接數(shù)據(jù)庫,拋出ORA-12505錯誤。
6. 解決辦法
Oracle Net 12c: How to enable Clients using SID to connect to PDB? (Doc ID 1644355.1)
SOLUTION:
Set the following control parameter in the listener.ora file and restart the listener:
USE_SID_AS_SERVICE_<listener_name> = ON
6.1 修改listener.ora文件
[oracle@ora19c admin]$ cat listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_ORCLCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
# 增加
USE_SID_AS_SERVICE_LISTENER_ORCLCDB = ON
注意:
?1. 我們這里的listene名稱是LISTENER_ORCLCDB,不是默認的LISTENER,所以需要增加的參數(shù)是USE_SID_AS_SERVICE_LISTENER_ORCLCDB = ON .
2. 在RAC 環(huán)境下,我們需要修改grid用戶下的listener.ora文件.
6.2 重啟監(jiān)聽
[oracle@ora19c admin]$ lsnrctl reload LISTENER_ORCLCDB LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-JUL-2022 22:50:32 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1522))) The command completed successfully
6.3 再次連接
C:\Users\admin>sqlplus sxc/sxc@orclpdb1_sid
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 7月 11 15:38:15 2022
Copyright (c) 1982, 2010, Oracle. All rights reserved.
連接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>
?結(jié)論:SID的方式,也可以正常連到PDB 。
綜上:以上就是在 12.1.0.1 以上版本中,需要通過SID 連接PDB數(shù)據(jù)庫的解決辦法。
思考: 如果需要通過SID的方式,連接CDB 也需要修改listener.ora文件嗎? 答案: 不需要。




