oracle如何通过sid的方式连接pdb

目前行内的数据库已经迁移到了19c的pdb rac环境，但是由于部分其他单位要的数据是通过工具进行抽取，并且抽取工具只支持使用sid进行连接， 因此我们通过下述办法进行相关测试 ## 1 查看测试环境配置 1.1 数据库情况： [oracle@single19c oradata]$ export ORACLE_SID=pdb [oracle@single19c oradata]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 22 14:57:00 2024 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 SQL> show pdbs;     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   READ ONLY  NO SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 1.2 监听情况： [oracle@single19c oradata]$ lsnrctl  status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JUL-2024 14:57:25 Copyright (c) 1991, 2019, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=single19c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date                19-JUL-2024 00:10:41 Uptime                    3 days 14 hr. 46 min. 43 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/diag/tnslsnr/single19c/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=single19c)(PORT=1521))) Services Summary... Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).   Instance "pdb", status READY, has 1 handler(s) for this service... Service "pdb" has 1 instance(s).   Instance "pdb", status READY, has 1 handler(s) for this service... Service "pdbXDB" has 1 instance(s).   Instance "pdb", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@single19c oradata]$  [oracle@single19c oradata]$  ## 2创建测试的pdb [oracle@single19c oradata]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 22 14:57:59 2024 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 SQL> show parameter create NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ create_bitmap_area_size      integer 8388608 create_stored_outlines      string db_create_file_dest      string db_create_online_log_dest_1      string db_create_online_log_dest_2      string db_create_online_log_dest_3      string db_create_online_log_dest_4      string db_create_online_log_dest_5      string SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/PDB/system01.dbf /u01/app/oracle/oradata/PDB/sysaux01.dbf /u01/app/oracle/oradata/PDB/undotbs01.dbf /u01/app/oracle/oradata/PDB/pdbseed/system01.dbf /u01/app/oracle/oradata/PDB/pdbseed/sysaux01.dbf /u01/app/oracle/oradata/PDB/users01.dbf /u01/app/oracle/oradata/PDB/pdbseed/undotbs01.dbf 7 rows selected. SQL> alter system  set db_create_file_dest='/u01/app/oracle/oradata'; System altered. create pluggable database test admin user zc identified by 123456; SQL> create pluggable database test admin user zc identified by 123456; Pluggable database created. SQL> show pdbs;     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   READ ONLY  NO 3 TEST   MOUNTED SQL> alter pluggable database test open; Pluggable database altered. SQL> show pdbs;     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   READ ONLY  NO 3 TEST   READ WRITE NO SQL> quit lsDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@single19c oradata]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JUL-2024 15:07:11 Copyright (c) 1991, 2019, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=single19c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date                19-JUL-2024 00:10:41 Uptime                    3 days 14 hr. 56 min. 29 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/diag/tnslsnr/single19c/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=single19c)(PORT=1521))) Services Summary... Service "1dd1ad9f574cb2d5e0630afea8c0dcb2" has 1 instance(s).   Instance "pdb", status READY, has 1 handler(s) for this service... Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).   Instance "pdb", status READY, has 1 handler(s) for this service... Service "pdb" has 1 instance(s).   Instance "pdb", status READY, has 1 handler(s) for this service... Service "pdbXDB" has 1 instance(s).   Instance "pdb", status READY, has 1 handler(s) for this service... Service "test" has 1 instance(s).   Instance "pdb", status READY, has 1 handler(s) for this service... The command completed successfully ## 3使用pdb service登录测试 [oracle@single19c oradata]$ sqlplus  zc/123456@192.168.50.68:1521/test SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 22 15:15:00 2024 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 SQL> quit 修改配置文件 tnsnames.ora 添加sid的登录条目 ser_t =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.68)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = test)     )   ) sid_t =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST =  192.168.50.68)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SID = test)     )   ) sid登录测试： [oracle@single19c admin]$ sqlplus zc/123456@sid_t SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 22 15:19:20 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle.  All rights reserved. ERROR: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor ## 4调整监听文件 根据mos文档修改listener.ora文件配置。 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 USE_SID_AS_SERVICE_LISTENER  = ON [oracle@single19c admin]$ vi listener.ora  [oracle@single19c admin]$ cat listener.ora  LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = single19c)(PORT = 1521))     )   ) USE_SID_AS_SERVICE_LISTENER  = ON 再次进行sid登录测试 [oracle@single19c admin]$ sqlplus zc/123456@sid_t SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 22 15:20:46 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle.  All rights reserved. ERROR: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor Enter user-name:  ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus --》这里没有登录成功是因为监听没有重启或者reload，reload后再次测试 [oracle@single19c admin]$  [oracle@single19c admin]$ lsnrctl reload LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JUL-2024 15:21:03 Copyright (c) 1991, 2019, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=single19c)(PORT=1521))) The command completed successfully 再次测试： [oracle@single19c admin]$ sqlplus zc/123456@sid_t SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 22 15:21:07 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle.  All rights reserved. Last Successful login time: Mon Jul 22 2024 15:19:06 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@single19c admin]$ sqlplus system/oracle@sid_t SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 22 15:21:58 2024 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 SQL> SQL> SELECT PDB_NAME, STATUS FROM DBA_PDBS   2   SQL>  SQL> SELECT PDB_NAME, STATUS FROM DBA_PDBS; PDB_NAME --------------- STATUS ---------- TEST NORMAL SQL>  set line 232  col os_pid for a7  col tracefile for a80   col username for a15  col con_name for a10  col schemaname for a10  SELECT distinct s.con_id           , c.con_name           , s.username           , s.user#           , s.sid           , s.serial#SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6             , s.prev_hash_value           , schemaname           , p.spid os_pid      FROM V$SESSION S, v$process p, v$active_services c,      (SELECT sid FROM v$mystat WHERE rownum=1) sid       WHERE audsid = SYS_CONTEXT('userenv','sessionid')       and p.  7    8    9   10   11   12   13  addr = s.paddr       and sid.sid = s.sid       and s.username is not null      and s.con_id=c.con_id      and s.con_id=p.con_id;       14   15   16   17       CON_ID CON_NAME   USERNAME    USER# SID    SERIAL# PREV_HASH_VALUE SCHEMANAME OS_PID ---------- ---------- --------------- ---------- ---------- ---------- --------------- ---------- ------- 3 TEST       SYSTEM        9 35 55270     1034145063 SYSTEM   47290 可以看到通过sid顺利登录了数据库