Oracle可刷新克隆PDB

PDB定时刷新前提条件：
（1）要创建可刷新PDB，源PDB必须处于归档日志模式和本地UNDO模式。
（2）要创建可刷新PDB，需要dblink，dblink可以指向同一CDB或不同CDB。
（3）可刷新PDB只在关闭状态下执行刷新。

一、创建可刷新克隆PDB（手动方式）

1.创建用户并授权

SQL> create user c##cs identified by admin;
User created.

SQL> grant create session,resource,create any table,unlimited tablespace to c##cs container=all;
Grant succeeded.

SQL> grant create pluggable database to c##cs container=all;
Grant succeeded.

SQL> grant sysoper to c##cs container=all;
Grant succeeded.

2.编辑TNS文件

REFRESH_LINK =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.5)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 86b637b62fdf7a65e053f706e80a27ca)
    )
  )

注：SERVICE_NAME 为根容器GUID。

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID; 
  NAME    CON_ID     DBID      CON_UID              GUID
-------- -------- ------------ -------- ---------------------------------
CDB$ROOT     1     1629592226     1     86B637B62FDF7A65E053F706E80A27CA

3.创建dblink

SQL> create database link pdb_link connect to c##cs identified by admin using 'REFRESH_LINK';
Database link created.

SQL> select * from dual@pdb_link;
D
-
X

4.创建手动可刷新PDB

SQL> create pluggable database <target_pdb> from <source_pdb>@<dblink_name> refresh mode manual;

在OMF模式下创建：
SQL> create pluggable database pdb3 from pdb2@pdb_link refresh mode manual;
Pluggable database created.

查看状态：
SQL> col pdb_name for a10
SQL> col refresh_mode for a15
SQL> select pdb_id,pdb_name,status,refresh_mode,refresh_interval from cdb_pdbs;
  PDB_ID   PDB_NAME    STATUS     REFRESH_MODE   REFRESH_INTERVAL
--------- ---------- ---------- ---------------- ----------------
   3         PDB1      NORMAL        NONE  
   2       PDB$SEED    NORMAL        NONE  
   4         PDB2      NORMAL        NONE  
   6         PDB3    REFRESHING     MANUAL

5.对可刷新PDB，进行手动刷新时，可刷新PDB需要处于关闭状态

SQL> alter pluggable database pdb3 refresh;
Pluggable database altered.
注：手动刷新PDB时，自上次刷新以来对源PDB所做的更改将传输到正在刷新的PDB。

未开启OMF模式下创建：
SQL> create pluggable database pdb3 from pdb2@pdb_link file_name_convert=('/oradata/ORCL/pdb2/system01.dbf','/oradata/ORCL/pdb3/system01.dbf',
'/oradata/ORCL/pdb2/sysaux01.dbf','/oradata/ORCL/pdb3/sysaux01.dbf',
'/oradata/ORCL/pdb2/undotbs01.dbf','/oradata/ORCL/pdb3/undotbs01.dbf',
'/oradata/ORCL/pdb2/users01.dbf','/oradata/ORCL/pdb3/users01.dbf',
'/oradata/ORCL/pdb2/temp01.dbf','/oradata/ORCL/pdb3/temp01.dbf') refresh mode manual;

6.进行数据测试

测试表cs原有12行数据：
SQL> show con_nameCON_NAME
--------  
PDB2

SQL> select * from cs;
ID
----
 3
 4 
 9
 10 
 1 
 2 
 5 
 6 
 7 
 8
 11
 12

插入两行数据进行测试：
SQL> insert into cs values(13);
SQL> insert into cs values(14);
SQL> commit;

进行手动刷新，然后检查数据：
SQL> alter pluggable database pdb3 refresh;
SQL> show con_nameCON_NAME
--------  
PDB3

SQL> select * from test.cs;
ID
----
 3 
 4 
 9
 10 
 1 
 2 
 5 
 6 
 7 
 8
 11
 12
 13
 14
 14 rows selected.至此，手动方式创建可刷新PDB完成。

二、创建可刷新克隆PDB（自动方式）

1.创建自动可刷新克隆PDB（如10分钟自动刷新一次）

SQL> create pluggable database pdb3 from pdb2@pdb_link file_name_convert=('/oradata/ORCL/pdb2/system01.dbf','/oradata/ORCL/pdb3/system01.dbf',
'/oradata/ORCL/pdb2/sysaux01.dbf','/oradata/ORCL/pdb3/sysaux01.dbf',
'/oradata/ORCL/pdb2/undotbs01.dbf','/oradata/ORCL/pdb3/undotbs01.dbf',
'/oradata/ORCL/pdb2/users01.dbf','/oradata/ORCL/pdb3/users01.dbf',
'/oradata/ORCL/pdb2/temp01.dbf','/oradata/ORCL/pdb3/temp01.dbf') refresh mode every 10 minutes;

注：自动刷新时，可刷新PDB也要处于关闭状态。如果在自动刷新时未关闭PDB，则自动刷新将延迟到下一次计划刷新。
可刷新PDB必须保持只读模式（也只能以只读模式打开），以防止在源PDB上发生的不同步更改。
可刷新PDB旨在用作克隆主服务器，因此必须在刷新的时间点准确反映源PDB。

2.进行数据测试

SQL> show con_name
CON_NAME
--------
PDB2

SQL> insert into cs values(15);
SQL> insert into cs values(16);
SQL> commit;

3.等待PDB刷新时间过去，以只读模式打开PDB，检查表数据行数

SQL> show con_name
CON_NAME
--------
PDB3

SQL> select * from test.cs;
ID
---
 3 
 4
 15
 16 
 9
 10 
 1 
 2 
 5 
 6 
 7 
 8
 11
 12
 13
 14
 16 rows selected.

同样的删除某行数据，也会通过定时刷新进行同步。

4.测试创建新用户是否能刷新至目标端（可刷新克隆PDB）

源端添加新用户：
SQL> show con_name
CON_NAME
--------
PDB2

SQL> create user t1 identified by admin;
User created.

定时自动刷新后，目标端查看：
SQL> show con_name
CON_NAME
--------
PDB3

SQL> set pagesize 99
SQL> select username from dba_users;
USERNAME
--------
SYS
SYSTEM
T1  ----通过刷新，正常同步至目标端

用户登录测试：
SQL> conn t1/admin@pdb3
Connected.
SQL> show user
USER is "T1"

5.测试创建表空间、添加数据文件是否可以刷新至目标端

源PDB中添加数据文件，则必须在CDB中设置 pdb_file_name_convert 参数：
SQL> alter system set pdb_file_name_convert='/oradata/ORCL/pdb2','/oradata/ORCL/pdb3';
System altered.

源端创建表空间：
SQL> create tablespace t2 datafile'/oradata/ORCL/pdb2/t201.dbf' size 20M;
Tablespace created.

目标端查看：
SQL> alter session set container=pdb3;
Session altered.

SQL> select name from v$datafile;
NAME
---------------------------
/oradata/ORCL/pdb3/t201.dbf

SQL> select name from v$tablespace;
NAME
-----
T2

源端添加数据文件：
SQL> alter tablespace t2 add datafile'/oradata/ORCL/pdb2/t202.dbf' size 20M;
Tablespace altered.

目标端查看：
SQL> select a.name,b.name from v$datafile a,v$tablespace b where a.ts#=b.ts# and b.name='T2';
NAME                            NAME
---------------------------- -----------
/oradata/ORCL/pdb3/t201.dbf      T2
/oradata/ORCL/pdb3/t202.dbf      T2

6.切换源端和目标端可刷新角色

前提条件：
（1）切换命令必须在源PDB中执行。
（2）源库和可刷新库不在同一个CDB中时，两个CDB中DBLink指定的用户和密码必须完全一致。
（3）DBLink中的字符串必须指向PDB所在的根容器中。

SQL> col pdb_name for a10
SQL> col refresh_mode for a15
SQL> select pdb_id,pdb_name,status,refresh_mode,refresh_interval from cdb_pdbs;
PDB_ID    PDB_NAME     STATUS     REFRESH_MODE  REFRESH_INTERVAL
------ ------------- ----------- -------------- ----------------
  3        PDB1        NORMAL         NONE  
  2      PDB$SEED      NORMAL         NONE  
  4        PDB2        NORMAL         NONE  
  5        PDB3      REFRESHING       AUTO           10

SQL> alter session set container=pdb2;
Session altered.

SQL> ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdb3@pdb_link SWITCHOVER;
Pluggable database altered.

SQL> select pdb_id,pdb_name,status,refresh_mode,refresh_interval from cdb_pdbs;
PDB_ID  PDB_NAME     STATUS     REFRESH_MODE  REFRESH_INTERVAL
------ ---------- ----------- --------------- ----------------
  3      PDB1        NORMAL        NONE  
  2    PDB$SEED      NORMAL        NONE  
  4      PDB2      REFRESHING      NONE            10  
  5      PDB3        NORMAL        AUTO

7.将可刷新PDB转换为普通PDB

SQL> alter pluggable database <pdb_name> refresh mode none;

注：转为普通PDB后，以读写模式打开，无法再次将普通PDB转换为可刷新PDB。

SQL> select pdb_id,pdb_name,status,refresh_mode,refresh_interval from cdb_pdbs;
PDB_ID   PDB_NAME   STATUS   REFRESH_MODE REFRESH_INTERVAL
------ ----------- --------- ------------ ----------------
  3       PDB1       NORMAL      NONE  
  2     PDB$SEED     NORMAL      NONE  
  4       PDB2        NEW        NONE  
  5       PDB3       NORMAL      AUTO