目前行内的数据库已经迁移到了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 © 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 © 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 © 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 © 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 © 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 © 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 © 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 © 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 © 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 © 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顺利登录了数据库
