刷新克隆PDB

可刷新的克隆pdb是指一个可以定期从源pdb同步的只读克隆库，可以用来当做报表库或者准生产环境来做测试用途。

刷新pdb

只有在创建的时候指明了是可刷新的克隆pdb，你才可以进行刷新操作。

当手动刷新pdb时，源端pdb自从上次刷新时间之后所有的变化都会同步到被刷新pdb当中来，你也可以手动刷新那些配置成自动刷新的pdb。

所以在创建pdb时，你必须要通过REFRESH MODE MANUAL或REFRESH MODE EVERY minutes语句。

创建可刷新的克隆pdb

1
2
3
sys@ORA12C> create pluggable database pdb3new from pdb3@to_remote refresh mode manual;

Pluggable database created.

打开新建的pdb，这里可刷新的pdb只能打开成read-only模式

1
2
3
4
5
6
7
8
9
10
sys@ORA12C> alter pluggable database pdb3new open;
alter pluggable database pdb3new open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode


sys@ORA12C> alter pluggable database pdb3new open read only;

Pluggable database altered.

在源pdb上创建点数据

1
2
3
4
5
6
7
8
9
10
create tablespace tbs1 datafile size 10m autoextend on ;

create user xb identified by xb default tablespace tbs1;

grant dba to xb;

create table xb.t1 (id number,name varchar2(10));

insert into xb.t1 values(1,'xb');
commit;

手动刷新pdb，这里要注意被刷新的pdb要先关闭

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
sys@ORA12C> alter session set container=pdb3new;

Session altered.

sys@ORA12C> select * from xb.t1;
select * from xb.t1
                 *
ERROR at line 1:
ORA-00942: table or view does not exist


sys@ORA12C> alter pluggable Database close immediate;

Pluggable database altered.

sys@ORA12C> alter pluggable Database refresh;

Pluggable database altered.

sys@ORA12C> alter pluggable Database open read only;

Pluggable database altered.

sys@ORA12C> select * from xb.t1;

        ID NAME
---------- ----------
         1 xb

表空间也同步过来了

1
2
3
4
5
6
7
8
9
sys@ORA12C> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
TBS1

刷新模式，总共包含三种

  • REFRESH MODE MANUAL 手动刷新
  • REFRESH MODE EVERY 60 MINUTES 每60分钟自动刷新
  • REFRESH MODE NONE 不刷新

也可以通过ALTER的方式来调整刷新模式。

swithover克隆pdb

可以切换源pdb和可刷新pdb的角色

1
ALTER PLUGGABLE DATABASE refresh_mode FROM clonepdb@dblink SWITCHOVER;

这里的refresh_mode不允许为NONE模式，切换完毕后，源pdb就变成了只读的可刷新pdb

前提条件：

  • 必须连到源pdb去执行命令
  • 如果源和目标端是两个不同的CDB，则数据链里指定的用户需要在源端和目标端pdb有相同的用户名和密码

源端也建一个同样的数据链

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 建用户
GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c#xb CONTAINER=ALL;
GRANT CREATE PLUGGABLE DATABASE TO c#xb CONTAINER=ALL;
GRANT SYSOPER TO c#xb CONTAINER=ALL;

# 数据链
drop public database link to_test;

create public database link to_test connect to c#xb identified by xb using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = testyum)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora12c)
    )
  )';

做swithover

1
2
3
4
5
6
7
sys@CDB1> alter session set container=pdb3;

Session altered.

sys@CDB1> alter pluggable database refresh mode manual from pdb3new@to_test switchover;

Pluggable database altered.

打开之前的源库，检查数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
sys@CDB1> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB3                           MOUNTED
sys@CDB1> alter pluggable database open ;
alter pluggable database open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode


sys@CDB1> alter pluggable database open read only;

Pluggable database altered.

sys@CDB1> select * from xb.t1;

        ID NAME
---------- ----------
         1 xb

在老的克隆pdb上插入新的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
sys@ORA12C> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB12C                         READ WRITE NO
         4 PDB4                           READ WRITE NO
         6 PDB3NEW                        READ WRITE NO
sys@ORA12C> alter session set container=pdb3new;

Session altered.

sys@ORA12C> insert into xb.t1 values(2,'test');

1 row created.

sys@ORA12C> commit;

Commit complete.

sys@ORA12C> select * from xb.t1;

        ID NAME
---------- ----------
         1 xb
         2 test

刷新源pdb库

1
2
3
4
5
6
7
8
9
10
11
12
13
sys@CDB1> alter session set container=pdb3;

Session altered.

ALTER PLUGGABLE DATABASE REFRESH;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;

sys@CDB1> select * from xb.t1;

        ID NAME
---------- ----------
         1 xb
         2 test
Oracle
刷新克隆PDB
https://www.xbdba.com/2019/06/14/refresh-clone-pdb/
作者
xbdba
发布于
2019年6月14日
许可协议