本文主要包括以下内容：
1. 搭建refresh PDB
2. 增量同步实验
3. 手动refresh变为自动refresh
4. 自动refresh变为手动refresh
5. refresh PDB转变为普通PDB
PDB Refresh是12C推出的新特性，可以对源端PDB进行增量同步，可以快速实现拷贝一份数据库镜像副本，有点类似运营商喜欢使用的EMC的BCV技术，存储层的复制功能。但是副本只能以read only打开，拷贝速度较慢，刷新走网络流量，实际应用场景较少。
PDB refresh源端与目标端可以在同一个cdb，也可以在不同的cdb中，刷新方式必须通过dblink。
PDB refresh搭建
|
数据库版本 SYS@cdbtest1(CDB$ROOT)> select banner from v$version where rownum=1; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production pdb refresh 有两个基本要求：归档和local undo 检查归档是否开启： SYS@cdbtest1(CDB$ROOT)> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /arch Oldest online log sequence 42 Next log sequence to archive 45 Current log sequence 45 检查undo是否为local模式 SYS@cdbtest1(CDB$ROOT)> col PROPERTY_NAME for a25 SYS@cdbtest1(CDB$ROOT)> col PROPERTY_VALUE for a30 SYS@cdbtest1(CDB$ROOT)> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE ------------------------- ------------------------------ LOCAL_UNDO_ENABLED TRUE 当前pdb信息 SYS@cdbtest1(CDB$ROOT)> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 CLONEMING READ WRITE NO 4 MING READ WRITE NO 快速刷新pdb的数据文件存放位置跟普通pdb有点区别，dba_data_files是查不到的，需要用v$dbfile视图。 select name from v$dbfile; NAME ---------------------------------------------------------------------------------------------------- /oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_system_fxvbwmck_.dbf /oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_sysaux_fxvbwmcp_.dbf /oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_test_fxvbwmcq_.dbf /oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_test_fxvbwmcr_.dbf /oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_undo_1_fxvbwmcr_.dbf 它的数据文件是放在cdb目录下的 以MING为源端pdb，需要创建到该cdb的dblink SYS@cdbtest1(CDB$ROOT)> create database link dblk_pdbming connect to system identified by "oracle" using 'pdbming'; Database link created. create database link dblk_pdbming connect to system identified by "oracle" using 'pdbming'; SYS@cdbtest1(CDB$ROOT)> select sysdate from dual@dblk_pdbming; SYSDATE ------------ 03-NOV-18 创建手动刷新refresh pdb create pluggable database mingdev1 from ming@dblk_pdbming refresh mode manual; |
alert日志
|
create pluggable database mingdev1 from ming@dblk_pdbming refresh mode manual 2018-11-03T22:07:14.174103+08:00 MING(4):Opatch XML is skipped for PDB MING (conid=4) MING(4): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated 2018-11-03T22:07:15.368785+08:00 Opatch validation is skipped for PDB MINGDEV1 (con_id=6) 2018-11-03T22:08:37.761704+08:00 MINGDEV1(6):Endian type of dictionary set to little **************************************************************** Pluggable Database MINGDEV1 with pdb id - 6 is created as UNUSABLE. If any errors are encountered before the pdb is marked as NEW, then the pdb must be dropped local undo-1, localundoscn-0x0000000000a123e2 **************************************************************** 2018-11-03T22:08:44.807546+08:00 Applying media recovery for pdb-4 from SCN 10563994 to SCN 10564176 Remote log information: count-1 thr-1, seq-45, logfile-/arch/parlog_1_45_839db4a4_967464795.arc, los-10555323, nxs-18446744073709551615 MINGDEV1(6):Media Recovery Start 2018-11-03T22:08:44.933181+08:00 MINGDEV1(6):Serial Media Recovery started 2018-11-03T22:08:45.086257+08:00 MINGDEV1(6):Media Recovery Log /arch/parlog_1_45_839db4a4_967464795.arc 2018-11-03T22:08:45.637158+08:00 MINGDEV1(6):Incomplete Recovery applied until change 10564176 time 11/03/2018 22:08:39 2018-11-03T22:08:45.639481+08:00 MINGDEV1(6):Media Recovery Complete (cdbtest1) 2018-11-03T22:08:45.958911+08:00 Completed: create pluggable database mingdev1 from ming@dblk_pdbming refresh mode manual |
|
SYS@cdbtest1(CDB$ROOT)> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 CLONEMING READ WRITE NO 4 MING READ WRITE NO 6 MINGDEV1 MOUNTED SYS@cdbtest1(CDB$ROOT)> alter pluggable database mingdev1 open; alter pluggable database mingdev1 open * ERROR at line 1: ORA-65341: cannot open pluggable database in read/write mode 后来发现报错 ORA-12012: ִ ҵ "SYS"."ORA$AT_OS_OPT_SY_101" ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: SYS.DBMS_STATS", line 47207 ORA-06512: SYS.DBMS_STATS_ADVISOR", line 882 ORA-06512: SYS.DBMS_STATS_INTERNAL", line 20059 ORA-06512: SYS.DBMS_STATS_INTERNAL", line 22201 ORA-06512: SYS.DBMS_STATS", line 47197 虽然不能open read write，但是可以以read only模式打开 SYS@cdbtest1(CDB$ROOT)> alter pluggable database mingdev1 open read only; Pluggable database altered. SYS@cdbtest1(CDB$ROOT)> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 CLONEMING READ WRITE NO 4 MING READ WRITE NO 6 MINGDEV1 READ ONLY NO sys用户登录目标pdb SYS@mingdev1(MINGDEV1)> alter pluggable database mingdev1 refresh; alter pluggable database mingdev1 refresh * ERROR at line 1: ORA-65025: Pluggable database MINGDEV1 is not closed on all instances. 刷新pdb必须将目标pdb先关闭或者mount SYS@mingdev1(MINGDEV1)> alter pluggable database mingdev1 close immediate; Pluggable database altered. SYS@mingdev1(MINGDEV1)> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 6 MINGDEV1 MOUNTED SYS@mingdev1(MINGDEV1)> alter pluggable database mingdev1 refresh; Pluggable database altered. SYS@mingdev1(MINGDEV1)> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 6 MINGDEV1 MOUNTED SYS@mingdev1(MINGDEV1)> alter pluggable database mingdev1 open; alter pluggable database mingdev1 open * ERROR at line 1: ORA-65341: cannot open pluggable database in read/write mode SYS@mingdev1(MINGDEV1)> alter pluggable database mingdev1 open read only; Pluggable database altered. |
实际上刷新的过程就是一个介质恢复的过程
|
MINGDEV1(6):alter pluggable database mingdev1 refresh 2018-11-04T09:05:44.122795+08:00 Applying media recovery for pdb-4 from SCN 10564176 to SCN 10654056 Remote log information: count-4 thr-1, seq-47, logfile-/arch/1_47_967464795.dbf, los-10621887, nxs-10641265 thr-1, seq-46, logfile-/arch/1_46_967464795.dbf, los-10598476, nxs-10621887 thr-1, seq-45, logfile-/arch/1_45_967464795.dbf, los-10555323, nxs-10598476 thr-1, seq-48, logfile-/arch/parlog_1_48_839db4a4_967464795.arc, los-10641265, nxs-18446744073709551615 MINGDEV1(6):Media Recovery Start 2018-11-04T09:05:44.172534+08:00 MINGDEV1(6):Serial Media Recovery started 2018-11-04T09:05:44.440066+08:00 MINGDEV1(6):Media Recovery Log /arch/1_45_967464795.dbf 2018-11-04T09:05:49.927526+08:00 MINGDEV1(6):Media Recovery Log /arch/1_46_967464795.dbf 2018-11-04T09:05:54.341779+08:00 MINGDEV1(6):Media Recovery Log /arch/1_47_967464795.dbf 2018-11-04T09:05:58.832855+08:00 MINGDEV1(6):Media Recovery Log /arch/parlog_1_48_839db4a4_967464795.arc 2018-11-04T09:06:02.365844+08:00 MINGDEV1(6):Incomplete Recovery applied until change 10654056 time 11/04/2018 09:05:38 2018-11-04T09:06:02.367512+08:00 MINGDEV1(6):Media Recovery Complete (cdbtest1) MINGDEV1(6):Completed: alter pluggable database mingdev1 refresh |
增量同步
|
desc ming.tx Name Null? Type ----------------------------------------- -------- ---------------------------- A NUMBER(38) 在源pdb内做一些DDL和DML操作： alter table ming.tx add b int; insert into ming.tx values(1,1); commit; select * from ming.tx; A B ---------- ---------- 1 1 此时refresh pdb内还看不到 sho con_name CON_NAME ------------------------------ MINGDEV1 PDB refresh必须在目标pdb中执行 alter pluggable database mingdev1 refresh; SYS@cdbtest1(CDB$ROOT)> alter pluggable database MINGDEV1 open read only; Pluggable database altered. desc ming.tx Name Null? Type ----------------------------------------- -------- ---------------------------- A NUMBER(38) B NUMBER(38) select * from ming.tx; A B ---------- ---------- 1 1 SYS@cdbtest1(CDB$ROOT)> select index_name from dba_indexes where table_name='TX'; INDEX_NAME -------------------------------------------------------------------------------- TX_IDX_01 |
这种方式的实现方式是通过定时job的方式实现的，如下job详情：
|
begin dbms_scheduler.create_job( job_name =>'MINGDEV1_4131117117_refresh', job_type => 'PLSQL_BLOCK', job_action => 'begin execute immediate ''alter session set container = MINGDEV1''; execute immediate ''alter pluggable database refresh''; end;', start_date => systimestamp, repeat_interval => 'FREQ = MINUTELY; INTERVAL = 1', enabled => TRUE, comments => 'MINGDEV1 refresh'); end; |
手动refresh变为自动refresh
|
下面再看一下自动刷新，该动作需要在快速刷新pdb中执行 SYS@cdbtest1(CDB$ROOT)> @entdb mingdev1 SYS@cdbtest1(MINGDEV1)> ALTER PLUGGABLE DATABASE mingdev1 REFRESH MODE EVERY 1 MINUTES; Pluggable database altered. SELECT pdb_id, pdb_name, refresh_mode, refresh_interval FROM dba_pdbs; 查看： SYS@cdbtest1(CDB$ROOT)> col pdb_name for a30 SYS@cdbtest1(CDB$ROOT)> SELECT pdb_id, pdb_name, refresh_mode, refresh_interval FROM dba_pdbs; PDB_ID PDB_NAME REFRES REFRESH_INTERVAL ---------- ------------------------------ ------ ---------------- 2 PDB$SEED NONE 3 CLONEMING NONE 4 MING NONE 6 MINGDEV1 AUTO 1 源pdb中插入一条数据 insert into ming.tx values(2,2); commit; select * from ming.tx; A B ---------- ---------- 1 1 2 2 |
alert日志中可以发现：
|
MINGDEV1(6):ALTER PLUGGABLE DATABASE mingdev1 REFRESH MODE EVERY 1 MINUTES MINGDEV1(6):Completed: ALTER PLUGGABLE DATABASE mingdev1 REFRESH MODE EVERY 1 MINUTES 2018-12-09T12:03:55.230825+08:00 MINGDEV1(6):alter pluggable database refresh MINGDEV1(6):Completed: alter pluggable database refresh 2018-12-09T12:04:54.144791+08:00 MINGDEV1(6):alter pluggable database refresh MINGDEV1(6):Completed: alter pluggable database refresh 2018-12-09T12:05:54.347415+08:00 MINGDEV1(6):alter pluggable database refresh MINGDEV1(6):Completed: alter pluggable database refresh |
但是refresh pdb中的数据并没有变化
|
SYS@cdbtest1(MINGDEV1)> select * from ming.tx; A B ---------- ---------- 1 1 |
那是因为refresh pdb是read only状态，read only模式下不会自动刷新，需要将pdb关闭或者mount
|
SYS@cdbtest1(MINGDEV1)> shutdown immediate Pluggable Database closed. SYS@cdbtest1(CDB$ROOT)> alter pluggable database MINGDEV1 open read only; Pluggable database altered. 再次查看 SYS@cdbtest1(MINGDEV1)> select * from ming.tx; A B ---------- ---------- 1 1 2 2 |
自动刷新转变为手动刷新
|
SYS@cdbtest1(MINGDEV1)> alter pluggable database MINGDEV1 refresh mode manual; Pluggable database altered. |
refresh PDB转变为普通PDB
|
SYS@cdbtest1(CDB$ROOT)> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 CLONEMING READ WRITE NO 4 MING READ WRITE NO 6 MINGDEV1 READ ONLY NO SYS@cdbtest1(CDB$ROOT)> alter pluggable database MINGDEV1 close immediate; Pluggable database altered. SYS@cdbtest1(MINGDEV1)> alter pluggable database MINGDEV1 refresh mode none; Pluggable database altered. SYS@cdbtest1(CDB$ROOT)> alter pluggable database MINGDEV1 open; Pluggable database altered. |
注意：此过程不可逆，一旦完成转换，就不能再实现refresh了
总结：
1.刷新方式必须通过dblink 2.pdb refresh有两个基本要求：归档和local undo 3.refresh必须以read only模式打开 4.refresh有自动和手动两种模式 5.自动refresh是通过job实现的 6.转变为normal pdb过程不可逆
