Oracle中建立到CDB和PDB的连接

原创 Xiaofei Huangfu 3天前

130

适用范围
适用于Oracle 19c CDB架构
方案概述
在CDB架构下，我们要对CDB和PDB进行管理维护，首先要连接进来。
一、默认服务
CDB成功创建后，与非CDB架构相比会创建一些新的对象和组件。

两个容器：根容器和种子 PDB
若干服务（数量和容器数量相同）：根容器服务名是在创建 CDB 时给定的 CDB 名称，该名称会与域名连接在一起。每个新 PDB 都会分配一个服务名：服务名是在创建 PDB 时给定的 PDB 名称，该名称会与域名连接在一起。如果创建或插入PDB，其服务名为 PDB的名字，该名称会与域名连接在一起。可以在 CDB_SERVICES 视图中查找在 CDB 中维护的所有服务名。
公用用户：在所有容器、根和种子中会创建 SYS、SYSTEM 向所有容器中的所有用户授予公用权限
所有容器、根和种子中的预定义公用角色
表空间：与每个容器关联的SYSTEM、SYSAUX、TEMP

--连接到CDB中检查service
SELECT name, pdb FROM cdb_services;
SYS@cdb19c(CDB$ROOT)> show con_name

CON_NAME
------------------------------
CDB$ROOT
SYS@cdb19c(CDB$ROOT)>  SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES WHERE PDB IS NOT NULL ORDER BY PDB;

PDB             NETWORK_NAME             CON_ID
--------------- -------------------- ----------
CDB$ROOT                                      1
CDB$ROOT                                      1
CDB$ROOT        cdb19cXDB                     1
CDB$ROOT        cdb19c                        1
HRPDB           hrpdb                         3
RCATPDB         RCATPDB                       6

6 rows selected.

SYS@cdb19c(CDB$ROOT)>

SYS@cdb19c(CDB$ROOT)> SELECT PDB, NETWORK_NAME, CON_ID FROM V$SERVICES  WHERE PDB IS NOT NULL ORDER BY PDB;

PDB             NETWORK_NAME             CON_ID
--------------- -------------------- ----------
CDB$ROOT                                      1
CDB$ROOT        cdb19cXDB                     1
CDB$ROOT        cdb19c                        1
CDB$ROOT                                      1
HRPDB           hrpdb                         3
RCATPDB         rcatpdb                       6

6 rows selected.

SYS@cdb19c(CDB$ROOT)>

--切换到PDB中检查service

SYS@cdb19c(CDB$ROOT)> SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES WHERE PDB IS NOT NULL ORDER BY PDB;

PDB             NETWORK_NAME             CON_ID
--------------- -------------------- ----------
HRPDB           hrpdb                         3

SYS@cdb19c(CDB$ROOT)> SELECT PDB, NETWORK_NAME, CON_ID FROM V$SERVICES  WHERE PDB IS NOT NULL ORDER BY PDB;

PDB             NETWORK_NAME             CON_ID
--------------- -------------------- ----------
HRPDB           hrpdb                         3

SYS@cdb19c(CDB$ROOT)>

SYS@cdb19c(CDB$ROOT)>
关闭PDB service 在cdb_service查询不到该PDB对应的服务名了，v$service中还可以查到。
SYS@cdb19c(CDB$ROOT)> SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES WHERE PDB IS NOT NULL ORDER BY PDB;
SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES WHERE PDB IS NOT NULL ORDER BY PDB
                                      *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only


SYS@cdb19c(CDB$ROOT)> SELECT PDB, NETWORK_NAME, CON_ID FROM V$SERVICES  WHERE PDB IS NOT NULL ORDER BY PDB;

PDB             NETWORK_NAME             CON_ID
--------------- -------------------- ----------
HRPDB           hrpdb                         3

SYS@cdb19c(CDB$ROOT)>

SYS@cdb19c(CDB$ROOT)> conn / as sysdba
Connected.

Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CDB$ROOT

SYS@cdb19c(CDB$ROOT)> SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES WHERE PDB IS NOT NULL ORDER BY PDB;

PDB             NETWORK_NAME             CON_ID
--------------- -------------------- ----------
CDB$ROOT                                      1
CDB$ROOT                                      1
CDB$ROOT        cdb19cXDB                     1
CDB$ROOT        cdb19c                        1
RCATPDB         RCATPDB                       6

SYS@cdb19c(CDB$ROOT)>  SELECT PDB, NETWORK_NAME, CON_ID FROM V$SERVICES  WHERE PDB IS NOT NULL ORDER BY PDB;

PDB             NETWORK_NAME             CON_ID
--------------- -------------------- ----------
CDB$ROOT                                      1
CDB$ROOT        cdb19cXDB                     1
CDB$ROOT        cdb19c                        1
CDB$ROOT                                      1
HRPDB           hrpdb                         3
RCATPDB         rcatpdb                       6

6 rows selected.

SYS@cdb19c(CDB$ROOT)>

每个 PDB 都有一个默认服务。服务名必须在各个 CDB 中都是唯一的。当PDB关闭时cdb_services中将不能查询到该PDB的service。
二、创建服务
创建服务
如果数据库不是由 Oracle Restart 或 Oracle Clusterware 管理，可以使用 DBMS_SERVICE 程序包为每个 PDB 创建或修改服务。连接到当前执行操作的PDB中。

SQL> CONNECT system@hrpdb 
SQL> EXECDBMS_SERVICE.CREATE_SERVICE('hrpdb','hrpdb')
SQL> EXEC DBMS_SERVICE.START_SERVICE
要为每个 PDB 创建或修改服务，还可以使用 Server Control 实用程序和 srvctl service 命令通过额外的属性 –pdb 指定 PDB 名：
$ srvctl add service -db mycdb -service hrpdb -pdb hrpdb
$ srvctl start service -db mycdb-service hrpdb

生产RAC环境如需要创建service，建议优先使用srvctl add service方式。
三、连接到CDB和PDB
1、基于OS认证的方式
–确认$ORACLE_SID环境变量

[oracle@19cdb01 ~]$ ps -ef|grep smon
oracle   12645     1  0 Jun18 ?        00:00:00 ora_smon_cdb19c
oracle   30959 29011  0 03:49 pts/0    00:00:00 grep --color=auto smon
[oracle@19cdb01 ~]$ . oraenv
ORACLE_SID = [cdb19c] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@19cdb01 ~]$ echo $ORACLE_SID
cdb19c
[oracle@19cdb01 ~]$

–以sysdba身份连接CDB

[oracle@19cdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 03:50:51 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0


Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------
CDB$ROOT

SYS@cdb19c(CDB$ROOT)> show con_name

CON_NAME
------------------------------
CDB$ROOT
SYS@cdb19c(CDB$ROOT)>
--以sysdba身份连接到PDB
设置环境变量ORACLE_PDB_SID
[oracle@19cdb01 ~]$ export ORACLE_PDB_SID=hrpdb
[oracle@19cdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 04:12:05 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0


Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------
HRPDB

SYS@cdb19c(HRPDB)>

2、EasyConnect方式
sqlplus username@hostname:portnumber/service_name

--检查hostname
[oracle@19cdb01 ~]$ hostname
19cdb01
[oracle@19cdb01 ~]$
--使用sys用户以sysdba身份连接连接到CDB
[oracle@19cdb01 ~]$ sqlplus sys/<password>@19cdb01:1525/cdb19c as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 03:53:55 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0


Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------
CDB$ROOT

SYS@19cdb01:1525/cdb19c(CDB$ROOT)> show con_name

CON_NAME
------------------------------
CDB$ROOT
SYS@19cdb01:1525/cdb19c(CDB$ROOT)>

--使用sys用户以sysdba身份连接连接到PDB
[oracle@19cdb01 ~]$ sqlplus sys/<password>@19cdb01:1525/hrpdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 03:58:37 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0


Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------
HRPDB

SYS@19cdb01:1525/hrpdb(HRPDB)> show con_name

CON_NAME
------------------------------
HRPDB
SYS@19cdb01:1525/hrpdb(HRPDB)>

3、 tnsnames.ora 中的别名连接
检查tnsnames.ora文件

[oracle@19cdb01 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDB19C =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19cdb01)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cdb19c)
    )
  )
HRPDB=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19cdb01)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hrpdb)
    )
  )
--连接到CDB
[oracle@19cdb01 ~]$ sqlplus sys/<password>@cdb19c as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 03:56:17 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0


Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------
CDB$ROOT

SYS@cdb19c(CDB$ROOT)> show con_name

CON_NAME
------------------------------
CDB$ROOT
SYS@cdb19c(CDB$ROOT)>
--连接到PDB
[oracle@19cdb01 ~]$ sqlplus sys/<password>@hrpdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 03:57:40 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0


Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------
HRPDB

SYS@hrpdb(HRPDB)> show con_name

CON_NAME
------------------------------
HRPDB
SYS@hrpdb(HRPDB)>

四、切换连接

CDB 管理员可以使用重新连接 CONNECT 或 ALTER SESSION SET CONTAINER 在容器之间切换，从而连接到 CDB 中的任意容器。例如，CDB 管理员可以在一个会话中连接到根，然后在同一会话中切换到 PDBHR 容器。这要求用户是这两个容器的已知公用用户，并且拥有系统权限 SET CONTAINER。使用 CONNECT 命令时，能以公用或本地用户身份进行连接。使用 ALTER SESSION SET CONTAINER 时，只能以授予新系统权限 SET CONTAINER 的公用用户身份进行连接。

--通过connect连接到PDB
[oracle@19cdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 04:05:20 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0


Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------
CDB$ROOT

SYS@cdb19c(CDB$ROOT)> connect hr@hrpdb
Enter password:
--通过ALTER SESSION SET CONTAINER切换到PDB
[oracle@19cdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 04:06:07 2026
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0


Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------
CDB$ROOT

SYS@cdb19c(CDB$ROOT)> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HRPDB                          MOUNTED
         6 RCATPDB                        READ WRITE NO
SYS@cdb19c(CDB$ROOT)> alter session set container=hrpdb;

Session altered.

SYS@cdb19c(CDB$ROOT)> show con_name

CON_NAME
------------------------------
HRPDB
--通过CONNECT连接到CDB
SYS@cdb19c(CDB$ROOT)> conn / as sysdba
Connected.

Hello
------------------------------------------------
Welcome! you are connected to CDB19C database


CONNAME
--------------------------------------------------------------------------------
CDB$ROOT

SYS@cdb19c(CDB$ROOT)>

-the end-

墨力计划 oracle

最后修改时间：2026-08-12 10:02:38

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle中建立到CDB和PDB的连接

评论