客服微信
本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。
对于此处列出的所有操作，您必须连接到 CDB，并将容器设置为根（默认）。通常，您将连接到具有 SYSDBA 或 SYSOPER 权限的普通用户。创建新的可插入数据库时，用户必须具有 CREATE PLUGGABLE DATABASE 系统权限。
要从种子数据库创建新的可插入数据库，我们所要做的就是告诉 Oracle 文件应该放置在哪里。
ALTER SYSTEM SET db_create_file_dest = '/opt/oracle/oradata/ORCLCDB';
从 12.1.0.2 开始，有一个使用 CREATE_FILE_DEST 子句的内联变体。本节中设置的路径将用作新PDB 的 OMF 位置。
CONN / AS SYSDBA host mkdir -p /opt/oracle/oradata/ORCLCDB/ORCLPDB2 CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1 CREATE_FILE_DEST='/opt/oracle/oradata/ORCLCDB/ORCLPDB2';
第二种方法使用 CREATE PLUGGABLE DATABASE 语句中的 FILE_NAME_CONVERT 子句。
CONN / AS SYSDBA
CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=
('/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/pd
b3/');
或者，我们可以在调用命令之前指定 PDB_FILE_NAME_CONVERT 初始化参数，而不使用FILE_NAME_CONVERT 子句。
CONN / AS SYSDBA ALTER SESSION SET PDB_FILE_NAME_CONVERT='/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/pdb4/'; CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb_adm IDENTIFIED BY Password1;
我们可以通过查询 DBA_PDBS 和 V$PDBS 视图来查看 PDB 是否存在。
COLUMN pdb_name FORMAT A20 SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name; PDB_NAME STATUS -------------------- ------------- PDB$SEED NORMAL PDB1 NORMAL PDB2 NEW PDB3 NEW SQL> COLUMN name FORMAT A20 SELECT name, open_mode FROM v$pdbs ORDER BY name; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 MOUNTED PDB2 MOUNTED PDB3 MOUNTED SQL>
您还可以使用 SQL*Plus 中的 SHOW PDBS 命令。
SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 PDB3 MOUNTED
SQL>
创建的 PDB 状态为“NEW”。它们必须以读写模式至少打开一次，才能完成 PDB 到 CDB 的集成。
ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE; ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE; SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name; PDB_NAME STATUS -------------------- ------------- PDB$SEED NORMAL PDB1 NORMAL PDB2 NORMAL PDB3 NORMAL SQL> SELECT name, open_mode FROM v$pdbs ORDER BY name; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 MOUNTED PDB2 READ WRITE PDB3 READ WRITE
根据所使用的语法，您可能需要将 PDB_DBA 角色授予 PDB 的本地管理员用户。
在尝试拔下 PDB 之前，必须确保其已关闭。要拔出数据库，请使用 ALTER PLUGGABLE DATABASE 命令和 UNPLUG INTO 子句来指定 XML 元数据文件的位置。
ALTER PLUGGABLE DATABASE pdb2 CLOSE; ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml';
可插入数据库仍然存在，但在元数据文件和所有数据文件复制到安全的地方之前，您不应该打开它。
SELECT name, open_mode FROM v$pdbs ORDER BY name; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 MOUNTED PDB2 MOUNTED PDB3 READ WRITE
您可以删除 PDB，选择将文件保留在文件系统上。
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES; SELECT name, open_mode FROM v$pdbs ORDER BY name; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 MOUNTED PDB3 READ WRITE
将 PDB 插入 CDB 类似于创建新的 PDB。首先通过调用 DBMS_PDB.CHECK_PLUG_COMPATIBILITY 函数检查 PBD 是否与 CDB 兼容，并传入 XML 元数据文件和要使用它创建的 PDB 的名称。
SET SERVEROUTPUT ON
DECLARE
l_result BOOLEAN;
BEGIN
l_result := DBMS_PDB.check_plug_compatibility(
pdb_descr_file =>
'/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml',
pdb_name => 'pdb2');
IF l_result THEN
DBMS_OUTPUT.PUT_LINE('compatible');
ELSE
DBMS_OUTPUT.PUT_LINE('incompatible');
END IF;
END;
/
compatible
PL/SQL procedure successfully completed.
SQL>
如果 PDB 不兼容，则会在 PDB_PLUG_IN_VIOLATIONS 视图中列出违规行为。如果 PDB 兼容，请使用它作为源创建一个新的 PDB。如果我们用新名称创建它，我们可能会这样做。
--FILE_NAME_CONVERT不支持OMF管理的PDB
CREATE PLUGGABLE DATABASE pdb5 AS CLONE USING
'/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml'
FILE_NAME_CONVERT=
('/u01/app/oracle/oradata/cdb1/pdb2/','/u01/app/oracle/oradata/cdb1/pdb
5/')
相反，我们希望将数据库插入到相同的容器中，因此不需要复制文件或重新创建临时文件，因此可以执行以下操作。
--保留数据文件删除pdb2 drop pluggable database pdb2 KEEP DATAFILES;; --将pdb2重新插入CDB中 CREATE PLUGGABLE DATABASE pdb2 as clone USING '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml' NOCOPY TEMPFILE REUSE; ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE; SELECT name, open_mode FROM v$pdbs ORDER BY name; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 MOUNTED PDB2 READ WRITE PDB3 READ WRITE
如果您使用的是 12.1 或 12.2，没有本地撤消模式，请确保源 PDB 以只读模式打开。
ALTER PLUGGABLE DATABASE pdb3 OPEN ;
CREATE PLUGGABLE DATABASE pdb4 FROM pdb3
FILE_NAME_CONVERT=
('/u01/app/oracle/oradata/cdb1/pdb3/','/u01/app/oracle/oradata/cdb1/pdb
4/');
ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;
克隆语法还允许使用本地 CBD 中的数据库链接从远程数据库进行克隆。此功能有一些相关限制。
OMF转换示例
select name from v$datafile where con_id=5;
select name from v$tempfile where con_id=5;
CREATE PLUGGABLE DATABASE pdb4 FROM pdb2
FILE_NAME_CONVERT=
('/u01/app/oracle/oradata/ORCL/pdb2/','/u01/app/oracle/oradata/ORCL/pdb
4/','/u01/app/oracle/oradata/ORCL/pdb2/ORCL/03DE94353886972BE0638274A8C
0D742/datafile/o1_mf_system_lgog9f0p_.dbf','/u01/app/oracle/oradata/ORC
L/pdb4/system01.dbf','/u01/app/oracle/oradata/ORCL/pdb2/ORCL/03DE943538
86972BE0638274A8C0D742/datafile/o1_mf_sysaux_lgog9f0q_.dbf','/u01/app/o
racle/oradata/ORCL/pdb4/sysaux01.dbf','/u01/app/oracle/oradata/ORCL/pdb
2/ORCL/03DE94353886972BE0638274A8C0D742/datafile/o1_mf_undotbs1_lgog9f0
r_.dbf','/u01/app/oracle/oradata/ORCL/pdb4/undo01.dbf','/u01/app/oracle
/oradata/ORCL/pdb2/ORCL/03DE94353886972BE0638274A8C0D742/datafile/o1_mf
_temp_lgog9f0r_.dbf','/u01/app/oracle/oradata/ORCL/pdb4/temp01.dbf')
CONN / AS SYSDBA
CREATE PLUGGABLE DATABASE pdb10 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=
('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/
pdb10/');
ALTER PLUGGABLE DATABASE pdb10 OPEN;
ALTER SESSION SET CONTAINER = pdb10;
alter tablespace temp add tempfile
'/opt/oracle/oradata/ORCLCDB/pdb10/temp01.dbf' size 10m;
CREATE TABLESPACE users
DATAFILE '/opt/oracle/oradata/ORCLCDB/pdb10/users01.dbf'
SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE USER test IDENTIFIED BY test
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
CREATE TABLE test.t1 (
id NUMBER
);
INSERT INTO test.t1 VALUES (1);
COMMIT;
SELECT COUNT(*) FROM test.t1;
COUNT(*)
----------
1
使用 NO DATA 子句执行 PDB 的仅元数据克隆。
CONN / AS SYSDBA
CREATE PLUGGABLE DATABASE pdb11 FROM pdb10
FILE_NAME_CONVERT=
('/u01/app/oracle/oradata/cdb1/pdb10/','/u01/app/oracle/oradata/cdb1/pdb11/')
NO DATA;
--如果pdb10中包括OMF管理的数据文件，需要转换
--否则会报ORA-65005: missing or invalid file name pattern for file
-/opt/oracle/oradata/ORCLCDB/031DC5CB31DD3CABE063824AA8C065C2/datafile/o1_mf_users_lfw20flv_.dbf
CREATE PLUGGABLE DATABASE pdb11 FROM pdb10
FILE_NAME_CONVERT=
('/opt/oracle/oradata/ORCLCDB/pdb10/','/opt/oracle/oradata/ORCLCDB/pdb11/','/opt/oracle/oradata/ORCLCDB/031DC5CB31DD3CABE063824AA8C065C2/datafile/o1_mf_users_lfw20flv_.dbf','/opt/oracle/oradata/ORCLCDB/pdb11/user01.dbf')
NO DATA;
ALTER PLUGGABLE DATABASE pdb11 OPEN READ WRITE;
检查新 PDB 中测试表的内容显示该表存在，但它是空的。
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb11;
SELECT COUNT(*) FROM test.t1;
COUNT(*)
----------
0
·表集群
如果是这样，您将收到以下类型的错误。
CREATE TABLE locations
(id NUMBER(10),
description VARCHAR2(50) NOT NULL,
map BLOB,
CONSTRAINT pk_locations PRIMARY KEY (id)
)
ORGANIZATION INDEX
TABLESPACE iot_tablespace
PCTTHRESHOLD 20
INCLUDING description
OVERFLOW TABLESPACE overflow_tablespace;
SQL> CREATE PLUGGABLE DATABASE pdb11 FROM pdb1
FILE_NAME_CONVERT=
('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb1/pdb11/')
NO DATA;
CREATE PLUGGABLE DATABASE pdb11 FROM pdb1
*
ERROR at line 1:
ORA-65161: Unable to create pluggable database with no data
ALTER PLUGGABLE DATABASE pdb2 CLOSE; DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES; ALTER PLUGGABLE DATABASE pdb3 CLOSE; DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES; ALTER PLUGGABLE DATABASE pdb4 CLOSE; DROP PLUGGABLE DATABASE pdb4 INCLUDING DATAFILES; SELECT name, open_mode FROM v$pdbs ORDER BY name; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 MOUNTED
