Source:

hostname: dbn01

CDB: orcl

PDB: pdb

IP ：192.168.142.132

数据库版本：19.3.0.0.0

Dest:

hostname: dbn02

CDB:test

PDB: pdb_refresh

IP ：192.168.142.133

数据库版本：19.9.0.0.0

前置条件

1 源端与目标端数据库版本需大于等于12.2

2 源端PDB 必须是local undo 模式。

3 源端PDB 必须为归档模式。

4 源端与目标端的字节顺序必须相同

5 Refreshable PDB 和源PDB 必须属于不同的CDB

6 源端PDB 不可以是关闭状态。

7 连接的用户在CDB 中必须拥有“Create Pluggable Database ”的权限。

8 在Refreshable PDB 创建后，如果源端需要创建相应表空间，那么目标端需要配置PDB_FILE_NAME_CONVERT 参数或使用OMF 目录管理方式(OMF 级别高于PDB_FILE_NAME_CONVERT) ，否正会导致目标端PDB 刷新失败。

9 目标端采用OMF 目录管理方式后，Create Pluggable Database 子句中不能指定file_name_convert 做显示目录转换，否则报ORA-01276 错误。

10 在使用Refreshable PDB 与源PDB 同步数据时，是通过dblink 从源PDB 访问redo ，但有些时候，当需要更新刷新副本时，源PDB 或源PDB 所属的CDB 可能无法访问，在这种情况下，设置REMOTE_RECOVERY_FILE_DEST 参数，将尝试从此参数指定的目录中读取归档日志文件。

11 刷新与切换命令均需在目标PDB 中执行。

确认源数据库是否处于归档模式：

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /arch

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

确认源端PDB 状态：

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- -------------------------------------------------------------------------------------

2 PDB$SEED READ ONLY YES

3 PDB READ WRITE YES

确认源端PDB 是否为local undo 模式。

SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

-------------------- --------------------

LOCAL_UNDO_ENABLED TRUE

源端PDB 创建用户并赋予权限

SQL> alter session set container=PDB;

SQL> CREATE USER ora19c IDENTIFIED BY oracle;

SQL> GRANT CREATE SESSION,RESTRICTED SESSION, SYSOPER to ora19c;

源端构造测试数据：

SQL> create table test as select * from dba_objects;

Table created.

SQL> select count(*) from test;

COUNT(*)

----------

72357

目标端创建指向源端的dblink

SQL>

create PUBLIC database link ora19c

connect to ora19c identified by orcale

using '(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.142.132)(PORT = 1521)))

(CONNECT_DATA = (SERVICE_NAME = pdb)))';

目标端验证dblink 是否有效
SQL> select * from dual@ora19c;

D

-

X

目标端设置PDB_FILE_NAME_CONVERT 参数

SQL>

ALTER SYSTEM SET pdb_file_name_convert='/ora11g/app/oradata/ORCL/pdb/','/opt/oracle/oradata/YLR/pdb_refresh/' SCOPE=spfile;

重启数据库生效

目标端CDB 执行创建Refreshable PDB 语句：

CREATE PLUGGABLE DATABASE <PDB_NAME>_refresh FROM <PDB_NAME>@<dblink> refresh mode every <x> minutes;

EG:

SQL> CREATE PLUGGABLE DATABASE pdb_refresh FROM pdb@ora19c refresh mode every 1 minutes;

Pluggable database created.

目标端查看Refreshable PDB 状态（新建好的Refreshable PDB 默认为MOUNTED ）：

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

4 PDB_REFRESH MOUNTED

Alert log ：

目标端定时执行alter pluggable database refresh 命令，通过dblink 查询访问并应用源端归档：

/arch/parlog_1_9_9b620d0f_1082559119.arc

文本描述已自动生成

目标端将Refreshable PDB 开启至read only 模式

SQL> alter pluggable database PDB_REFRESH open read only;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

4 PDB_REFRESH READ ONLY NO

查看数据是否同步过来

SQL> alter session set container=PDB_REFRESH;

Session altered.

SQL> select count(*) from test;

COUNT(*)

----------

72357

目标端查看pdb 自动刷新模式：

SQL> select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;

PDB_ID PDB_NAME STATUS REFRES REFRESH_INTERVAL

---------- -------------------- ---------- ------ ----------------

4 PDB_REFRESH REFRESHING AUTO 1

自动刷新/ 手工刷新模式均无法在open read only 状态下刷新，后台日志会报错报错：

2021-09-06T17:01:18.070527+08:00

PDB_REFRESH(4):alter pluggable database refresh

PDB_REFRESH(4):PDB_REFRESH(4):ERROR:PDB needs to be closed for auto refresh

PDB_REFRESH(4):Completed: alter pluggable database refresh

更改Refreshable PDB 为刷新模式：

手工刷新：

ALTER PLUGGABLE DATABASE PDB_REFRESH REFRESH MODE manual;

自动刷新：

ALTER PLUGGABLE DATABASE PDB_REFRESH REFRESH MODE EVERY 1 MINUTES;

关闭Refreshable PDB ，手工刷新：

SQL> alter pluggable database PDB_REFRESH close immediate;

SQL> alter pluggable database PDB_REFRESH refresh;

SQL> alter pluggable database PDB_REFRESH open read only;

查看数据是否同步，源端新增的test2 表已同步过来：

SQL> select count(*) from test2;

COUNT(*)

----------

72358

Switchover 切换：

连接至cdb 中，检查pdb 状态：

源端：

SQL> ALTER SESSION SET CONTAINER = cdb$root;

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY YES

3 PDB READ WRITE YES

SQL> SELECT pdb_name, status FROM cdb_pdbs;

PDB_NAME STATUS

---------- ----------

PDB NORMAL

PDB$SEED NORMAL

目标端：

SQL> ALTER SESSION SET CONTAINER = cdb$root;

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

4 PDB_REFRESH READ ONLY NO

SQL> SELECT pdb_name, status FROM cdb_pdbs;

PDB_NAME STATUS

-------------------- ----------

PDB$SEED NORMAL

PDB_REFRESH REFRESHING

SQL> ALTER SESSION SET CONTAINER = PDB_REFRESH;

Session altered.

SQL> ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM PDB_REFRESH@ora19c SWITCHOVER;

ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM PDB_REFRESH@ora19c SWITCHOVER

*

ERROR at line 1:

ORA-12754: Feature PDB REFRESH SWITCHOVER is disabled due to missing capability .

12754, 00000, "Feature %s is disabled due to missing capability %s."

// *Document: NO

// *Cause: This feature requires specific capabilities that were not present

// in the current database instance.

// *Action: Contact Oracle Support Services to enable this feature.

夭折~（总结：需要在exadata上面才可以使用此特性）

参考文档：

Administrator’s Guide

Data Guard Impact on Oracle Multitenant Environments (Doc ID 2049127.1)

How to create refreshable PDB (Doc ID 2441949.1)

Switching Over a Refreshable Cloned PDB

https://blog.csdn.net/dev_csdn/article/details/79608412

ITPub博客

19c refresh pdb

最新文章