DB / Oracle / 底层与基础

Oracle PDB 操作与管理

2026.09.06

Oracle PDB 操作与管理

适用版本：Oracle Database 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

PDB 日常管理包括：状态切换、参数修改、表空间、用户、监控等[1]。

2. PDB 状态管理

2.1 打开 PDB

-- 打开单个 PDB
ALTER PLUGGABLE DATABASE hrpdb OPEN;
ALTER PLUGGABLE DATABASE hrpdb OPEN READ ONLY;
ALTER PLUGGABLE DATABASE hrpdb OPEN UPGRADE;
ALTER PLUGGABLE DATABASE hrpdb OPEN RESTRICTED;

-- 打开所有 PDB
ALTER PLUGGABLE DATABASE ALL OPEN;

-- 打开除指定外的 PDB
ALTER PLUGGABLE DATABASE ALL EXCEPT hrpdb OPEN;

2.2 关闭 PDB

-- 关闭
ALTER PLUGGABLE DATABASE hrpdb CLOSE;
ALTER PLUGGABLE DATABASE hrpdb CLOSE IMMEDIATE;

-- 关闭所有
ALTER PLUGGABLE DATABASE ALL CLOSE;

2.3 保存状态

-- 保存当前状态（重启后自动恢复）
ALTER PLUGGABLE DATABASE hrpdb SAVE STATE;

-- 取消保存
ALTER PLUGGABLE DATABASE hrpdb DISCARD STATE;

-- 查看
SELECT con_id, name, open_mode, restricted 
FROM v$pdbs;

-- 查看保存状态
SELECT con_id, state 
FROM dba_pdb_saved_states;

2.4 启动顺序

CDB 启动 → PDB$SEED 自动打开（READ ONLY）
          → 用户 PDB 默认 MOUNT（除非 SAVE STATE）
          → SAVE STATE 的 PDB 自动打开

3. PDB 切换

3.1 切换容器

-- 切换到 PDB
ALTER SESSION SET CONTAINER = hrpdb;

-- 切换到 CDB ROOT
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- 切换到 Application Root
ALTER SESSION SET CONTAINER = approot;

3.2 查看当前容器

SELECT 
  sys_context('USERENV','CON_ID') AS con_id,
  sys_context('USERENV','CON_NAME') AS con_name,
  sys_context('USERENV','CON_UID') AS con_uid
FROM dual;

3.3 连接到 PDB

# 通过服务名连接
sqlplus system/pwd@dbhost:1521/hrpdb

# 通过 tnsnames
HRPDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hrpdb)))
sqlplus system/pwd@hrpdb

4. PDB 参数管理

4.1 查看参数

-- 查看所有参数
SELECT name, value, con_id 
FROM v$system_parameter 
WHERE con_id = 0  -- CDB 级
ORDER BY name;

-- 当前 PDB 的参数
SELECT name, value, ispdb_modifiable
FROM v$system_parameter 
WHERE ispdb_modifiable='TRUE'
ORDER BY name;

4.2 修改 PDB 参数

-- 切换到 PDB
ALTER SESSION SET CONTAINER = hrpdb;

-- 修改 PDB 参数
ALTER SYSTEM SET open_cursors=500 SCOPE=BOTH;
ALTER SYSTEM SET sga_target=2G SCOPE=BOTH;
ALTER SYSTEM SET cpu_count=8 SCOPE=BOTH;

-- 重置
ALTER SYSTEM RESET open_cursors;

4.3 19c PDB 级参数

-- 19c 支持的 PDB 参数（部分）
-- sga_min_size, sga_target
-- pga_aggregate_limit, pga_aggregate_target
-- cpu_count, parallel_threads_per_cpu
-- open_cursors, sessions
-- processes

5. PDB 表空间

5.1 创建表空间

ALTER SESSION SET CONTAINER = hrpdb;

-- 创建数据表空间
CREATE TABLESPACE users 
  DATAFILE '/u01/oradata/orcl/hrpdb/users01.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

-- 创建临时表空间
CREATE TEMPORARY TABLESPACE temp 
  TEMPFILE '/u01/oradata/orcl/hrpdb/temp01.dbf' SIZE 500M;

5.2 默认表空间

ALTER DATABASE DEFAULT TABLESPACE users;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

5.3 查看表空间

SELECT 
  tablespace_name, 
  contents, 
  status
FROM dba_tablespaces;

6. PDB 用户管理

6.1 Local User

ALTER SESSION SET CONTAINER = hrpdb;

-- 创建 Local User
CREATE USER hr_user IDENTIFIED BY ****** 
  DEFAULT TABLESPACE users 
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION TO hr_user;
GRANT CREATE TABLE TO hr_user;

6.2 Common User

-- 在 CDB$ROOT 创建
ALTER SESSION SET CONTAINER = CDB$ROOT;

CREATE USER c##admin IDENTIFIED BY ****** 
  CONTAINER=ALL;

GRANT CREATE SESSION TO c##admin CONTAINER=ALL;

详细内容见：Oracle Common User 与 Local User。

7. PDB 服务管理

7.1 创建服务

-- 单实例
ALTER SESSION SET CONTAINER = hrpdb;
EXEC DBMS_SERVICE.CREATE_SERVICE('hrpdb', 'hrpdb');
EXEC DBMS_SERVICE.START_SERVICE('hrpdb');

-- RAC
srvctl add service -db orcl -service hrpdb 
  -preferred orcl1,orcl2
srvctl start service -db orcl -service hrpdb

7.2 查看服务

SELECT 
  name, 
  network_name, 
  con_id, 
  enabled
FROM v$active_services
WHERE con_id = (SELECT con_id FROM v$pdbs WHERE name='HRPDB');

8. PDB 资源管理

8.1 资源计划

-- 创建 PDB 资源计划
EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN('cdb_plan','CDB plan');

EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
  plan => 'cdb_plan',
  pluggable_database => 'hrpdb',
  shares => 2,
  utilization_limit => 50
);

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

-- 启用
ALTER SYSTEM SET resource_manager_plan='cdb_plan' SCOPE=BOTH;

8.2 内存限制

ALTER SESSION SET CONTAINER = hrpdb;
ALTER SYSTEM SET sga_min_size=1G SCOPE=BOTH;
ALTER SYSTEM SET sga_target=4G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_limit=2G SCOPE=BOTH;

9. PDB 监控

9.1 PDB 列表

SELECT 
  con_id, 
  name, 
  open_mode, 
  restricted, 
  open_time
FROM v$pdbs;

9.2 PDB 资源使用

SELECT 
  p.name AS pdb_name,
  r.cpu_consumed_time,
  r.cpu_wait_time,
  r.memory_used
FROM v$rsrc_pdb r, v$pdbs p
WHERE r.con_id = p.con_id;

9.3 PDB 大小

SELECT 
  p.name,
  sum(d.bytes)/1024/1024/1024 AS size_gb
FROM v$datafile d, v$pdbs p
WHERE d.con_id = p.con_id
GROUP BY p.name;

9.4 PDB 会话

SELECT 
  s.con_id,
  p.name AS pdb_name,
  count(*) AS sessions
FROM v$session s, v$pdbs p
WHERE s.con_id = p.con_id
GROUP BY s.con_id, p.name;

10. PDB 备份恢复

10.1 RMAN 备份

-- 备份单个 PDB
RMAN> BACKUP PLUGGABLE DATABASE hrpdb PLUS ARCHIVELOG;

-- 备份所有 PDB
RMAN> BACKUP PLUGGABLE DATABASE ALL PLUS ARCHIVELOG;

10.2 PDB 时间点恢复

RMAN> ALTER PLUGGABLE DATABASE hrpdb CLOSE;

RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2026-07-21 14:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE PLUGGABLE DATABASE hrpdb;
  RECOVER PLUGGABLE DATABASE hrpdb;
}

RMAN> ALTER PLUGGABLE DATABASE hrpdb OPEN RESETLOGS;

详细内容见：Oracle RMAN 备份与恢复全流程。

11. PDB 迁移

11.1 拔出/插入

-- 拔出
ALTER PLUGGABLE DATABASE hrpdb UNPLUG INTO '/tmp/hrpdb.xml';

-- 关闭并删除
ALTER PLUGGABLE DATABASE hrpdb CLOSE;
DROP PLUGGABLE DATABASE hrpdb KEEP DATAFILES;

-- 在新 CDB 插入
CREATE PLUGGABLE DATABASE hrpdb USING '/tmp/hrpdb.xml' NOCOPY;

11.2 19c PDB Relocate（零停机）

-- 创建可重定位 PDB（不停机）
CREATE PLUGGABLE DATABASE hrpdb FROM hrpdb@src_link 
  RELOCATE 
  AVAILABILITY MAX;

-- 完成后自动切换

12. PDB 升级

12.1 升级方式

-- 1. 关闭 PDB
ALTER PLUGGABLE DATABASE hrpdb CLOSE;

-- 2. 升级模式打开
ALTER PLUGGABLE DATABASE hrpdb OPEN UPGRADE;

-- 3. 运行升级脚本
ALTER SESSION SET CONTAINER = hrpdb;
@?/rdbms/admin/catctl.sql catupgrd.sql

-- 4. 编译无效对象
@?/rdbms/admin/utlrp.sql

-- 5. 正常打开
ALTER PLUGGABLE DATABASE hrpdb CLOSE;
ALTER PLUGGABLE DATABASE hrpdb OPEN;

13. 常见坑与排错

13.1 ORA-65052: PDB 与 CDB 不兼容

现象：插入 PDB 时报错。

修复：

-- 检查兼容性
SELECT message, status FROM pdb_plug_in_violations 
WHERE name='HRPDB';

-- 解决违规项后重试

13.2 PDB 无法打开 ORA-65122

现象：PDB 操作报错。

修复：

-- 1. 检查 PDB 状态
SELECT name, open_mode FROM v$pdbs;

-- 2. 切换到 CDB$ROOT
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- 3. 检查是否有违规
SELECT message FROM pdb_plug_in_violations WHERE name='HRPDB';

13.3 Common User 无法登录 PDB

现象：c##admin 在 PDB 中无法使用。

修复：

-- 1. 检查权限
SELECT * FROM dba_sys_privs WHERE grantee='C##ADMIN';

-- 2. 授予 ALL 容器权限
GRANT CREATE SESSION TO c##admin CONTAINER=ALL;

13.4 PDB 参数修改失败

现象：ALTER SYSTEM SET 报 ORA-02096。

修复：

-- 1. 检查参数是否 PDB 可修改
SELECT name, ispdb_modifiable 
FROM v$system_parameter 
WHERE name='open_cursors';

-- 2. 仅 PDB 可修改的参数在 PDB 中修改
ALTER SESSION SET CONTAINER = hrpdb;
ALTER SYSTEM SET open_cursors=500 SCOPE=BOTH;

13.5 PDB 服务不存在

现象：连接 hrpdb 服务失败。

修复：

-- 1. 检查服务
SELECT name FROM v$active_services;

-- 2. 创建服务
ALTER SESSION SET CONTAINER = hrpdb;
EXEC DBMS_SERVICE.CREATE_SERVICE('hrpdb', 'hrpdb');
EXEC DBMS_SERVICE.START_SERVICE('hrpdb');

-- 3. 强制注册
ALTER SYSTEM REGISTER;

14. 最佳实践

  1. PDB SAVE STATE：启动自动打开
  2. 服务名与 PDB 对应：清晰标识
  3. 每个 PDB 独立服务：故障隔离
  4. 资源计划限制：避免争抢
  5. PDB 独立备份：RMAN 单独备份
  6. PDB 独立参数：精细控制
  7. 19c+ 本地 UNDO：性能更好
  8. Application Container：SaaS 多租户
  9. 监控 PDB 资源：v$rsrc_pdb
  10. 定期检查违规：pdb_plug_in_violations

15. 参考资料

[1] Oracle Multitenant Administrator’s Guide 19c, “Administering PDBs” https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/administering-pdbs.html

[2] Oracle Database Administrator’s Guide 19c, “Altering a PDB” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/