一台数据库服务器部署两个实例均为CDB模式。CDB1及CDB2通过两个环境执行相关实验。

数据库版本19.3

创建PDB

[oracle@gpfs-s02 ~]$ cat who**i.sql

select
'DB N**e: ' ||Sys_Context('Userenv', 'DB_N**e')||
' / CDB?: ' ||case
when Sys_Context('Userenv', 'CDB_N**e') is not null
then 'YES'
else 'NO'
end||
' / Auth-ID: ' ||Sys_Context('Userenv',
'Authenticated_Identity')||
' / Sessn-User: '||Sys_Context('Userenv',
'Session_User')||
' / Container: ' ||Nvl(Sys_Context('Userenv',
'Con_N**e'), 'n/a')
"Who ** I?"
from Dual

**L> create pluggable database pdb2 admin user admin identified by oracle file_n**e_convert=('pdbseed','pdb2');
Pluggable database created.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 PDB2 MOUNTED
**L> select * from v$dbfile;
FILE# NAME CON_ID
---------- -------------------------------------------------- ----------
7 /u02/app/oracle/oradata/CDB1/users01.dbf 1
4 /u02/app/oracle/oradata/CDB1/undotbs01.dbf 1
1 /u02/app/oracle/oradata/CDB1/system01.dbf 1
3 /u02/app/oracle/oradata/CDB1/sysaux01.dbf 1
5 /u02/app/oracle/oradata/CDB1/pdbseed/system01.dbf 2
6 /u02/app/oracle/oradata/CDB1/pdbseed/sysaux01.dbf 2
8 /u02/app/oracle/oradata/CDB1/pdbseed/undotbs01.dbf 2
9 /u02/app/oracle/oradata/CDB1/pdb/system01.dbf 3
10 /u02/app/oracle/oradata/CDB1/pdb/sysaux01.dbf 3
11 /u02/app/oracle/oradata/CDB1/pdb/undotbs01.dbf 3
12 /u02/app/oracle/oradata/CDB1/pdb/users01.dbf 3
FILE# NAME CON_ID
---------- -------------------------------------------------- ----------
13 /u02/app/oracle/oradata/CDB1/pdb2/system01.dbf 4
14 /u02/app/oracle/oradata/CDB1/pdb2/sysaux01.dbf 4
15 /u02/app/oracle/oradata/CDB1/pdb2/undotbs01.dbf 4
14 rows selected.
创建用户及表空间
**L> alter pluggable database pdb2 open;
Pluggable database altered.
**L> alter session set container=pdb2;
Session altered.
**L> grant sysdba to admin;
Grant succeeded
**L> create tablespace users datafile '/u02/app/oracle/oradata/CDB1/pdb2/user01.dbf' size 10M autoextend on
next 1M **xsize unlimited segment space **nagement auto; 2
Tablespace created.
**L> alter database default tablespace users;
Database altered.
**L> grant create table,unlimited tablespace to admin;
Grant succeeded.
**L> conn admin/oracle@localhost:1522/pdb2;
Connected.
**L> show user;
USER is "ADMIN"
创建测试表

**PDB

从pdb2**新的pdb3

**L> create pluggable database pdb3 from pdb2 file_n**e_convert=('pdb2','pdb3');
Pluggable database created.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 PDB2 READ ONLY NO
5 PDB3 MOUNTED
**L> alter pluggable database pdb3 open;
Pluggable database altered.
**L> alter pluggable database PDB2 open read write force;
Pluggable database altered.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO
**L> conn admin/oracle@localhost:1522/pdb3
Connected.
**L> select * from my_tab;
MY_COL
----------
1

卸载PDB（unplug）

**L> conn sys/oracle@localhost:1522/pdb3 as sysdba
Connected.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PDB3 READ WRITE NO
**L> alter pluggable database pdb3 close immediate;
Pluggable database altered.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PDB3 MOUNTED
**L> conn / as sysdba
Connected.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 MOUNTED
**L> alter pluggable database pdb3 unplug into '/tmp/cdb1_pdb3.xml';
Pluggable database altered.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 MOUNTED
**L> drop pluggable database pdb3 keep datafiles;
Pluggable database dropped.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 PDB2 READ WRITE NO

查看xml文件

Plug pdb

从cdb1中卸载的pdb加载到cdb2中。并使用路径转换

目标cdb执行兼容性检查

SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/tmp/cdb1_pdb3.xml',
pdb_n**e => 'SALESPDB')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

**L> select CAUSE,CON_ID,MESSAGE from pdb_plug_in_violations;

执行插入pdb

CREATE PLUGGABLE DATABASE salespdb USING '/tmp/cdb1_pdb3.xml'
COPY
PATH_PREFIX = '/u02/app/oracle/oradata/CDB2'
FILE_NAME_CONVERT = ('/u02/app/oracle/oradata/CDB1/pdb3/', '/u02/app/oracle/oradata/CDB2/salespdb/');

**L> select * from admin.my_tab;
MY_COL
----------
1

删除pdb

**L> alter pluggable database salespdb close immediate;
Pluggable database altered.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 SALESPDB MOUNTED
**L> drop pluggable database salespdb including datafiles;
Pluggable database dropped.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
**L>

Clone database-1

**L> alter pluggable database pdb3 unplug into '/tmp/cdb1_pdb3.xml';
Pluggable database altered.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 MOUNTED
**L> drop pluggable database pdb3 keep datafiles;
Pluggable database dropped.
**L> create pluggable database copypdb3 as clone using '/tmp/cdb1_pdb3.xml' storage (**xsize unlimited **x_shared_temp_size unlimited) copy FILE_NAME_CONVERT = ('/u02/app/oracle/oradata/CDB1/pdb3/', '/u02/app/oracle/oradata/CDB2/copypdb3/');

Clone database-2

不拷贝数据文件使用源路径。

CREATE PLUGGABLE DATABASE copypdb4 USING '/tmp/cdb1_pdb3.xml'
NOCOPY
TEMPFILE REUSE;

PDB热**

12.2开始支持热**。源库处于读写状态下。

目标数据库创建dblink连接到源端pdb

**L> create public database link to_cdb1_pdb2 connect to system identified by oracle using 'CDB1PDB2';
Database link created.
**L> select sysdate from dual@to_cdb1_pdb2;
SYSDATE
---------
13-JUN-22
源端数据库授权用户
**L> alter session set container=pdb2;
Session altered.
**L> grant create pluggable database to system;
Grant succeeded.
目标cdb执行hotclone命令
**L> create pluggable database hotpdb2 from pdb2@to_cdb1_pdb2 FILE_NAME_CONVERT = ('/u02/app/oracle/oradata/CDB1/pdb2/', '/u02/app/oracle/oradata/CDB2/hostpdb2/');
Pluggable database created.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 HOTPDB2 MOUNTED
5 COPYPDB3 READ WRITE NO
**L> alter pluggable database hotpdb2 open;
Pluggable database altered.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 HOTPDB2 READ WRITE NO
5 COPYPDB3 READ WRITE NO

PDB refreh

创建为自动刷新

create pluggable database refpdb2 from pdb2@to_cdb1_pdb2 create_file_dest='/u02/app/oracle/oradata/CDB2/refpdb2' refresh mode every 1 minutes;

创建为手动刷新

**L> create pluggable database refpdb2 from pdb2@to_cdb1_pdb2 create_file_dest='/u02/app/oracle/oradata/CDB2/refpdb2' refresh mode **nual;
Pluggable database created.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 HOTPDB2 READ WRITE NO
5 COPYPDB3 READ WRITE NO
6 REFPDB2 MOUNTED
**L> alter pluggable database refpdb2 open read only;
Pluggable database altered.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 HOTPDB2 READ WRITE NO
5 COPYPDB3 READ WRITE NO
6 REFPDB2 READ ONLY NO
**L> alter session set container=refpdb2;
Session altered.
**L> select * from admin.my_tab;
MY_COL
----------
1

源库模拟数据

**L> conn admin/oracle@localhost:1522/pdb2;
Connected.
**L> insert into my_tab values(2);
1 row created.
**L> commit;
Commit complete.

目标库执行刷新-手动刷新

**L> alter pluggable database refpdb2 close immediate;
Pluggable database altered.
**L> alter pluggable database refpdb2 refresh;
Pluggable database altered.
**L> alter pluggable database refpdb2 open read only;
Pluggable database altered.
**L> select * from admin.my_tab;
MY_COL
----------
1
2 数值已更新。

转换为自动刷新1分钟同步一次

**L> alter pluggable database refpdb2 refresh mode every 1 minutes;
Pluggable database altered.
**L> select pdb_n**e,status,refresh_mode,refresh_interval from cdb_pdbs;
PDB_NAME STATUS REFRES REFRESH_INTERVAL
-------------------- ---------- ------ ----------------
PDB NORMAL NONE
PDB$SEED NORMAL NONE
COPYPDB3 NORMAL NONE
REFPDB2 REFRESHING AUTO 1
HOTPDB2 NORMAL NONE

同样自动刷新目标pdb需要处于close的状态否则无法刷新

关闭refpdb后，后台自动刷新同步数据

转换为non模式，停止刷新同步

关闭目标pdb
**L> alter pluggable database close immediate;
Pluggable database altered.
**L> alter pluggable database refpdb2 refresh mode none;
Pluggable database altered.
**L> select pdb_n**e,status,refresh_mode,refresh_interval from cdb_pdbs;
PDB_NAME STATUS REFRES REFRESH_INTERVAL
-------------------- ---------- ------ ----------------
PDB NORMAL NONE
PDB$SEED NORMAL NONE
COPYPDB3 NORMAL NONE
REFPDB2 NEW NONE
HOTPDB2 NORMAL NONE
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 HOTPDB2 READ WRITE NO
5 COPYPDB3 READ WRITE NO
6 REFPDB2 MOUNTED
**L> alter pluggable database refpdb2 open;
Pluggable database altered.
**L> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 HOTPDB2 READ WRITE NO
5 COPYPDB3 READ WRITE NO
6 REFPDB2 READ WRITE NO
**L> select pdb_n**e,status,refresh_mode,refresh_interval from cdb_pdbs;
PDB_NAME STATUS REFRES REFRESH_INTERVAL
-------------------- ---------- ------ ----------------
PDB NORMAL NONE
PDB$SEED NORMAL NONE
COPYPDB3 NORMAL NONE
REFPDB2 NORMAL NONE
HOTPDB2 NORMAL NONE