ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > oracle如何通过sid的方式连接pdb

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

原创 Oracle 作者：oracle11111 时间：2025-01-21 17:48:37 0 删除 编辑

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


 

 


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69924215/viewspace-3070156/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： oracle 等待事件enq: TM – contention 处理
下一篇： Oracle使用psqlODBC访问PostgreSQL
请登录后发表评论 登录
全部评论
oracle11111

注册时间：2019-05-19

  • 博文量
    81
  • 访问量
    90371

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593