oracle 12c中怎么搭建PDB refresh
这篇文章主要讲解了“oracle 12c中怎么搭建PDB refresh”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“oracle 12c中怎么搭建PDB refresh”吧！
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转变为普通PDBSYS@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了
感谢各位的阅读，以上就是“oracle 12c中怎么搭建PDB refresh”的内容了，经过本文的学习后，相信大家对oracle 12c中怎么搭建PDB refresh这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
