测试19C的多租户（一）比较全的

创建PDB 一对种子进行克隆

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER lihao IDENTIFIED BY aabb123
FILE_NAME_CONVERT=(’/u01/app/oracle/oradata/CDB1/pdbseed/’,’/u01/app/oracle/oradata/CDB1/pdb2/’);

创建PDB 指定PDB文件名转换

ALTER SESSION SET PDB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/CDB1/pdbseed/’,
‘/u01/app/oracle/oradata/CDB1/pdb3/’;

CREATE PLUGGABLE DATABASE pdb3 ADMIN USER lihao IDENTIFIED BY aabb123;

查看创建的PDB
查看状态为NEW
SELECT pdb_name, status
FROM dba_pdbs
ORDER BY pdb_name;

必须以READ WRITE模式至少打开一次它们，才能完成PDB到CDB的集成
ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;

查看PDB状态
SELECT pdb_name, status FROM dba_pdbs
ORDER BY pdb_name;

查看PDB的打开模式，现在是读写
SELECT name,open_mode from v$pdbs
ORDER by name;

查看创建的PDB文件
ll /u01/app/oracle/oradata/CDB1/

手动删除可插拔数据库

关闭PDB2，查看
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
SELECT name,open_mode from v$pdbs
ORDER by name;

删除PDB2
DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;

删除PDB3
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

查看删除

拔出数据库

拔出pdb2

拔出PDB之前，必须确保已将其关闭
ALTER PLUGGABLE DATABASE pdb2 CLOSE;

要拔出数据库，请使用ALTER PLUGGABLE DATABASE带有UNPLUG INTO子句的命令来指定XML数据文件的位置。
ll /u01/app/oracle/oradata/CDB1/pdb2/

播出PDB2
ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO ‘/u01/app/oracle/oradata/CDB1/pdb2/pdb2.xml’;

查看生成的XML文件

删除PDB，选择将文件保留在文件系统上。
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

查看删除的PDB2
SELECT name,open_mode from v$pdbs
ORDER by name;

查看保留的数据文件
ll /u01/app/oracle/oradata/CDB1/pdb2/

插入数据库

检查兼容性
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;
/

插入数据库，并重命名pdb5
CREATE PLUGGABLE DATABASE pdb5 USING ‘/u01/app/oracle/oradata/CDB1/pdb2/pdb2.xml’
FILE_NAME_CONVERT=(’/u01/app/oracle/oradata/CDB1/pdb2/’,’/u01/app/oracle/oradata/CDB1/pdb5/’);

打开PDB5
ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE;

查看插入的PDB5
SELECT name,open_mode from v$pdbs
ORDER by name;

删除后，在测试

SELECT name,open_mode from v$pdbs
ORDER by name;

插入数据库，不重命名PDB

CREATE PLUGGABLE DATABASE pdb2 USING ‘/u01/app/oracle/oradata/CDB1/pdb2/pdb2.xml’
NOCOPY
TEMPFILE REUSE;

打开PDB2
ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
查看PDB
SELECT name,open_mode from v$pdbs
ORDER by name;

排出数据库，生成pdb格式

查看已经有的PDB
show pdbs

拔出数据库，拔出成pdb文件
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO ‘/tmp/pdb1.pdb’;

删除查看
DROP PLUGGABLE DATABASE pdb1 INCLUDING DATAFILES;
show pdbs

查看 没有数据文件了

查看生成的pdb

unzip -l /tmp/pdb1.pdb

插入拔出数据库

SET SERVEROUTPUT ON
DECLARE
l_result BOOLEAN;
BEGIN
l_result := DBMS_PDB.check_plug_compatibility(
pdb_descr_file => ‘/tmp/pdb1.pdb’,
pdb_name => ‘pdb1’);

IF l_result THEN
DBMS_OUTPUT.PUT_LINE(‘compatible’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘incompatible’);
END IF;
END;
/

插入查看
CREATE PLUGGABLE DATABASE pdb1 USING ‘/tmp/pdb1.pdb’;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ WRITE;
show pdbs

克隆可插入数据库

它需要是只读
SELECT name, open_mode FROM v$pdbs;

把PDB1也修改成只读
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;

创建一个新的PDB，作为现在数据库的副本
CREATE PLUGGABLE DATABASE pdb3 FROM pdb1
FILE_NAME_CONVERT=(’/u01/app/oracle/oradata/CDB1/pdb1/’,
‘/u01/app/oracle/oradata/CDB1/pdb3/’);

查看创建
ALTER PLUGGABLE DATABASE pdb3 OPEN;

在把PDB1修改成读写