分类: Oracle

2018-03-28 10:18:34

沃趣科技数据库技术专家 杨禹航




PDB Refresh12c推出的特性,具有对源端PDB进行增量同步的功能,每次刷新会将源端PDB中的任何更改同步到目标PDB(在此环境中目标PDB被称作Refreshable PDB)中,目前增量同步方式有两种:手动方式自动方式


REFRESH MODE语句:

在使用CREATE PLUGGABLE DATABASE创建PDB时指定REFRESH MODE子句用来控制是否开启刷新机制。

该子句目前只在"CREATE PLUGGABLE DATABASE ... FROM"中支持,可以使用此语句指定如下选项:

1.指定REFRESH MODE NONE,这是默认方式,在创建PDB时不开启刷机机制。

2.指定REFRESH MODE MANUAL子句,创建手动刷新的PDB

3.指定REFRESH MODE EVERY number_of_minutes MINUTES子句,创建以周期性刷新的PDB

另外值得一提的是Oracle 18cRefreshable PDB进行了进一步的增强,增强为Refreshable PDB Switchover(可刷新PDB的切换),在12.2.1中源PDB只能作为源端PDB,而在18c中可对源PDB和目标PDB进行角色转换,该功能类似Active Dataguard的主备切换,只不过OraclePDB Refresh中再次实现了该功能。

目前refreshable PDB只能在只读模式下打开,当Refreshable PDB进行刷新时,Refreshable PDB必须处于关闭状态,只读模式打开时会停止刷新,当再次关闭Refreshable PDB时继续上次的刷新,也可以将Refreshable PDB更改为REFRESH MODE NONE模式,更改之后你无法在将PDB改为Refreshable PDB

另外也可以使用ALTER PLUGGABLE DATABASE命令将Refreshable PDB从手动刷新更改为自动刷新,也可以从自动刷新更改为手动刷新,同样通过语句ALTER PLUGGABLE DATABASE + REFRESH MODE更改为非Refreshable PDB,但是非Refreshable PDB不可以被更改为Refreshable PDB

同时需要注意的是Refreshable PDB和源PDB必须属于不同的CDB,因此Refreshable PDB需要使用远端的CDB中的PDB来创建,在创建期间必须使用dblink,但实际情况是Source PDBRefreshable PDB可以属于同一个CDB,只不过在创建Refreshable PDB时必须指定dblinkdblink指向自己所在的CDB,否则语法无法通过。

Refreshable PDB应用场景可以用在开发和测试环境的搭建过程中, 因为数据是采用增量同步的方式,这样就减少了对源数据库的影响,也可以作为online备份库。

Clone源端的PDB前期注意事项:

1.如果PDBClone的到的CDB的字符集不是AL32UTF8,那么源与目标字符集必须兼容。

2.源端与目标端的字节顺序必须相同。

3.连接的用户在CDB中必须拥有'CREATE PLUGGABLE DATABASE'的权限。

4.PDB不可以是关闭状态。

5.源端PDB必须为归档模式。

6.源端PDB必须是local undo模式。

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

8.目标端采用OMF目录管理方式后,CREATE PLUGGABLE DATABASE子句中不能指定file_name_convert做显示目录转换,否正报ORA-01276错误。

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

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

PDB Refresh测试:

如下将源端PDBwoqupdb通过refresh方式克隆到本地,且命名为qdatapdb

(1)创建目标端到源端库的DBLINKorcl12c

sys. ora12c>CREATE DATABASE LINK orcl12c CONNECT TO system IDENTIFIED BY oracle USING 'orcl12c';

Database link created.

Elapsed: 00:00:00.15

sys. ora12c>


克隆源端WOQUPDB

sys. orcl12c>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         4 WOQUPDB                        READ WRITE NO

sys. orcl12c>


(2)
目标端配置OMF目录管理方式

sys. ora12c>alter system set db_create_file_dest='/u01/app/oracle/oradata';

System altered.

Elapsed: 00:00:00.04

sys. ora12c>


(3)
在目标端使用CREATE PLUGGABLE DATABASE + REFRESH MODE MANUAL语句创建qdatapdb

sys. ora12c>CREATE PLUGGABLE DATABASE qdatapdb FROM woqupdb@orcl12c REFRESH MODE MANUAL;

Pluggable database created.

Elapsed: 00:00:14.20

sys. ora12c>


(4)
检查克隆完成的QDATAPDB

sys. ora12c>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         4 QDATAPDB                       MOUNTED

sys. ora12c>select pdb_id, pdb_name, status, refresh_mode, refresh_interval from cdb_pdbs;

    PDB_ID PDB_NAME                       STATUS     REFRES REFRESH_INTERVAL

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

         2 PDB$SEED                       NORMAL     NONE

         4 QDATAPDB                       REFRESHING MANUAL

Elapsed: 00:00:00.07

sys. ora12c>

通过视图我们可以看到状态为REFRESHING,刷新模式为MANUAL手工刷新。


(5)
在源PDB创建表空间tbs1,及测试表t

sys. orcl12c>create tablespace tbs1 datafile size 100M;

Tablespace created.

Elapsed: 00:00:00.30

sys. orcl12c>create user yyh identified by yyh;

User created.

Elapsed: 00:00:00.09

sys. orcl12c>drop user yyh ;

User dropped.

Elapsed: 00:00:01.34

sys. orcl12c>create user yyh default tablespace tbs1 identified by yyh;

User created.

Elapsed: 00:00:00.05

sys. orcl12c>grant dba to yyh;

Grant succeeded.

Elapsed: 00:00:00.04

sys. orcl12c>conn yyh/yyh@woqupdb

Connected.

yyh. woqupdb>create table t as select * from dba_objects;

Table created.

Elapsed: 00:00:02.18

yyh. woqupdb>select count(*) from t;

  COUNT(*)

----------

     72714

Elapsed: 00:00:00.03

yyh. woqupdb>


  (6)
在目标端刷新qdatapdb

sys. ora12c>alter pluggable database qdatapdb refresh;

alter pluggable database qdatapdb refresh

*

ERROR at line 1:

ORA-65118: operation affecting a pluggable database cannot be performed from another pluggable database

Elapsed: 00:00:00.04

sys. ora12c>conn sys/oracle@qdatapdb as sysdba

Connected.

21:55:00 sys. qdatapdb>set time off

sys. qdatapdb>alter pluggable database qdatapdb refresh;

Pluggable database altered.

Elapsed: 00:00:01.84

sys. qdatapdb>


日志信息:

2018-03-10T21:55:06.187494-05:00

QDATAPDB(4):alter pluggable database qdatapdb refresh

2018-03-10T21:55:06.975492-05:00

Applying media recovery for pdb-4099 from SCN 2800296 to SCN 2802347

Remote log information: count-1

thr-1, seq-5, logfile-/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/foreign_archivelog/WOQUPDB/2018_03_10/o1_mf_1_5_1212300924_.arc, los-2791634, nxs-18446744073709551615

QDATAPDB(4):Media Recovery Start

2018-03-10T21:55:06.977285-05:00

QDATAPDB(4):Serial Media Recovery started

2018-03-10T21:55:07.061801-05:00

QDATAPDB(4):Media Recovery Log /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/foreign_archivelog/WOQUPDB/2018_03_10/o1_mf_1_5_1212300924_.arc

2018-03-10T21:55:07.503582-05:00

QDATAPDB(4):Successfully added datafile 51 to media recovery

QDATAPDB(4):Datafile #51: '/u01/app/oracle/oradata/ORA12C/671B0AC55A3F2870E0535138A8C0026E/datafile/o1_mf_tbs1_fb96ncby_.dbf'

2018-03-10T21:55:07.998822-05:00

QDATAPDB(4):Incomplete Recovery applied until change 2802347 time 03/10/2018 21:55:06

2018-03-10T21:55:08.006177-05:00

QDATAPDB(4):Media Recovery Complete (ora12c)

QDATAPDB(4):Completed: alter pluggable database qdatapdb refresh

(7)目标端打开qdatapdb, 检查刷新完成后的数据

sys. qdatapdb>alter database open read only;

Database altered.

Elapsed: 00:00:01.54

sys. qdatapdb>select count(*) from yyh.t;

  COUNT(*)

----------

     72714

Elapsed: 00:00:00.04

sys. qdatapdb>select t.name tablespace_name, d.name from v$tablespace t, v$datafile d where d.ts#=t.ts#;

TABLESPACE_NAME NAME

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

SYSTEM          /u01/app/oracle/oradata/ORA12C/671B/datafile/o1_mf_system_fbjv_.dbf

SYSAUX          /u01/app/oracle/oradata/ORA12C/671B/datafile/o1_mf_sysaux_fbk6_.dbf

UNDOTBS1        /u01/app/oracle/oradata/ORA12C/671B/datafile/o1_mf_undotbs1_fbhk7_.dbf

TBS1            /u01/app/oracle/oradata/ORA12C/671B/datafile/o1_mf_tbs1_fbcby_.dbf

Elapsed: 00:00:00.01

sys. qdatapdb>

这里已经刷新完毕,数据同步到本地目标qdatapdb中。


(8)
目标库修改为自动刷新模式,每分钟刷新一次

sys. qdatapdb>ALTER PLUGGABLE DATABASE qdatapdb REFRESH MODE EVERY 1 MINUTES;

Pluggable database altered.

Elapsed: 00:00:00.09

sys. qdatapdb>select pdb_id, pdb_name, status, refresh_mode, refresh_interval from cdb_pdbs;

    PDB_ID PDB_NAME                       STATUS     REFRES REFRESH_INTERVAL

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

         4 QDATAPDB                       REFRESHING AUTO                  1

Elapsed: 00:00:00.05

sys. qdatapdb>

通过视图我们可以看到刷新模式已经被更改为AUTO,间隔时间为1分钟。


(9)
PDB删除测试表

yyh. woqupdb>drop table t;

Table dropped.

Elapsed: 00:00:00.31

yyh. woqupdb>


日志:

2018-03-10T22:01:49.537240-05:00

QDATAPDB(4):ALTER PLUGGABLE DATABASE pdb1 REFRESH MODE EVERY 1 MINUTES

QDATAPDB(4):ORA-65118 signalled during: ALTER PLUGGABLE DATABASE pdb1 REFRESH MODE EVERY 1 MINUTES...

2018-03-10T22:02:21.179776-05:00

QDATAPDB(4):ALTER PLUGGABLE DATABASE qdatapdb REFRESH MODE EVERY 1 MINUTES

QDATAPDB(4):Completed: ALTER PLUGGABLE DATABASE qdatapdb REFRESH MODE EVERY 1 MINUTES

2018-03-10T22:02:21.357524-05:00

QDATAPDB(4):alter pluggable database refresh

QDATAPDB(4):Completed: alter pluggable database refresh

2018-03-10T22:03:21.502428-05:00

QDATAPDB(4):alter pluggable database refresh

QDATAPDB(4):Completed: alter pluggable database refresh

2018-03-10T22:04:21.680468-05:00

QDATAPDB(4):alter pluggable database refresh

QDATAPDB(4):Completed: alter pluggable database refresh

通过日志可以观察到目标端每分钟自动刷新一次。


(10)
检查目标端数据同步情况

sys. qdatapdb>select count(*) from yyh.t;

  COUNT(*)

----------

     72714

Elapsed: 00:00:00.03

sys. qdatapdb>select open_mode from v$database;

OPEN_MODE

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

READ ONLY

Elapsed: 00:00:00.03

sys. qdatapdb>shutdown immediate

Pluggable Database closed.

sys. qdatapdb>

因目标库在OPEN状态下,虽然日志中显示每分钟刷新,但是数据实际并未正真同步。


(11)1
分钟后再次检查数据同步情况

sys. qdatapdb>alter database open read only;

Database altered.

Elapsed: 00:00:01.89

sys. qdatapdb>select count(*) from yyh.t;

select count(*) from yyh.t

                         *

ERROR at line 1:

ORA-00942: table or view does not exist

Elapsed: 00:00:00.01

sys. qdatapdb>

数据已经同步完成。


(12)
转换为目标PDB:qdatapdb为非none PDB

sys. ora12c>select pdb_id, pdb_name, status, refresh_mode, refresh_interval from cdb_pdbs;

    PDB_ID PDB_NAME                       STATUS     REFRES REFRESH_INTERVAL

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

         2 PDB$SEED                       NORMAL     NONE

         4 QDATAPDB                       REFRESHING AUTO                  1

Elapsed: 00:00:00.03

sys. ora12c>alter pluggable database qdatapdb close;

Pluggable database altered.

Elapsed: 00:00:00.19

sys. ora12c>alter session set container=qdatapdb;

Session altered.

Elapsed: 00:00:00.00

sys. qdatapdb>ALTER PLUGGABLE DATABASE qdatapdb REFRESH MODE NONE;

Pluggable database altered.

Elapsed: 00:00:04.01

sys. ora12c>ALTER PLUGGABLE DATABASE qdatapdb REFRESH MODE manual;

ALTER PLUGGABLE DATABASE qdatapdb REFRESH MODE manual

*

ERROR at line 1:

ORA-65261: pluggable database QDATAPDB not enabled for refresh

Elapsed: 00:00:00.00

sys. ora12c>

Note:

 禁用PDB refresh功能后,就不能再转换为refresh pdb


(13)
检查qdatapdb状态

sys. ora12c>conn /as sysdba

Connected.

sys. ora12c>select pdb_id, pdb_name, status, refresh_mode, refresh_interval from cdb_pdbs;

    PDB_ID PDB_NAME                       STATUS     REFRES REFRESH_INTERVAL

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

         2 PDB$SEED                       NORMAL     NONE

         4 QDATAPDB                       NEW        NONE

Elapsed: 00:00:00.00

sys. ora12c>alter pluggable database qdatapdb open;

Pluggable database altered.

Elapsed: 00:00:05.02

sys. ora12c>select pdb_id, pdb_name, status, refresh_mode, refresh_interval from cdb_pdbs;

    PDB_ID PDB_NAME                       STATUS     REFRES REFRESH_INTERVAL

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

         2 PDB$SEED                       NORMAL     NONE

         4 QDATAPDB                       NORMAL     NONE

Elapsed: 00:00:00.00

sys. ora12c>

QDATAPDB的状态已经由自动刷新转换成为了普通的PDB


阅读(3198) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册