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。数据库版本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 Productionpdb refresh有两个基本要求：归档和local undo检查归档是否开启：SYS@cdbtest1(CDB$ROOT)> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /archOldest online log sequence 42Next log sequence to archive 45Current log sequence 45检查undo是否为local模式SYS@cdbtest1(CDB$ROOT)> col PROPERTY_NAME for a25SYS@cdbtest1(CDB$ROOT)> col PROPERTY_VALUE for a30SYS@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的dblinkSYS@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 pdbcreate pluggable database mingdev1 from ming@dblk_pdbming refresh mode manual;alert日志create pluggable database mingdev1 from ming@dblk_pdbming refresh mode manual2018-11-03T22:07:14.174103+08:00MING(4):Opatch XML is skipped for PDB MING (conid=4)MING(4): AUDSYS.AUD$UNIFIED (SQL_TEXT) – CLOB populated2018-11-03T22:07:15.368785+08:00Opatch validation is skipped for PDB MINGDEV1 (con_id=6)2018-11-03T22:08:37.761704+08:00MINGDEV1(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 droppedlocal undo-1, localundoscn-0x0000000000a123e2****************************************************************2018-11-03T22:08:44.807546+08:00Applying media recovery for pdb-4 from SCN 10563994 to SCN 10564176Remote log information: count-1thr-1, seq-45, logfile-/arch/parlog_1_45_839db4a4_967464795.arc, los-10555323, nxs-18446744073709551615MINGDEV1(6):Media Recovery Start2018-11-03T22:08:44.933181+08:00MINGDEV1(6):Serial Media Recovery started2018-11-03T22:08:45.086257+08:00MINGDEV1(6):Media Recovery Log /arch/parlog_1_45_839db4a4_967464795.arc2018-11-03T22:08:45.637158+08:00MINGDEV1(6):Incomplete Recovery applied until change 10564176 time 11/03/2018 22:08:392018-11-03T22:08:45.639481+08:00MINGDEV1(6):Media Recovery Complete (cdbtest1)2018-11-03T22:08:45.958911+08:00Completed: create pluggable database mingdev1 from ming@dblk_pdbming refresh mode manualSYS@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 MOUNTEDSYS@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 userORA-06512: SYS.DBMS_STATS”, line 47207ORA-06512: SYS.DBMS_STATS_ADVISOR”, line 882ORA-06512: SYS.DBMS_STATS_INTERNAL”, line 20059ORA-06512: SYS.DBMS_STATS_INTERNAL”, line 22201ORA-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 NOsys用户登录目标pdbSYS@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先关闭或者mountSYS@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 MOUNTEDSYS@mingdev1(MINGDEV1)> alter pluggable database mingdev1 refresh;Pluggable database altered.SYS@mingdev1(MINGDEV1)> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———- 6 MINGDE免费主机域名V1 MOUNTEDSYS@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 modeSYS@mingdev1(MINGDEV1)> alter pluggable database mingdev1 open read only;Pluggable database altered.实际上刷新的过程就是一个介质恢复的过程MINGDEV1(6):alter pluggable database mingdev1 refresh2018-11-04T09:05:44.122795+08:00Applying media recovery for pdb-4 from SCN 10564176 to SCN 10654056Remote log information: count-4thr-1, seq-47, logfile-/arch/1_47_967464795.dbf, los-10621887, nxs-10641265thr-1, seq-46, logfile-/arch/1_46_967464795.dbf, los-10598476, nxs-10621887thr-1, seq-45, logfile-/arch/1_45_967464795.dbf, los-10555323, nxs-10598476thr-1, seq-48, logfile-/arch/parlog_1_48_839db4a4_967464795.arc, los-10641265, nxs-18446744073709551615MINGDEV1(6):Media Recovery Start2018-11-04T09:05:44.172534+08:00MINGDEV1(6):Serial Media Recovery started2018-11-04T09:05:44.440066+08:00MINGDEV1(6):Media Recovery Log /arch/1_45_967464795.dbf2018-11-04T09:05:49.927526+08:00MINGDEV1(6):Media Recovery Log /arch/1_46_967464795.dbf2018-11-04T09:05:54.341779+08:00MINGDEV1(6):Media Recovery Log /arch/1_47_967464795.dbf2018-11-04T09:05:58.832855+08:00MINGDEV1(6):Media Recovery Log /arch/parlog_1_48_839db4a4_967464795.arc2018-11-04T09:06:02.365844+08:00MINGDEV1(6):Incomplete Recovery applied until change 10654056 time 11/04/2018 09:05:382018-11-04T09:06:02.367512+08:00MINGDEV1(6):Media Recovery Complete (cdbtest1)MINGDEV1(6):Completed: alter pluggable database mingdev1 refreshdesc ming.txName Null? Type—————————————– ——– —————————-A N免费主机域名UMBER(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_nameCON_NAME——————————MINGDEV1PDB refresh必须在目标pdb中执行alter pluggable database mingdev1 refresh;SYS@cdbtest1(CDB$ROOT)> alter pluggable database MINGDEV1 open read only;Pluggable database altered.desc ming.txName Null? Type—————————————– ——– —————————-A NUMBER(38)B NUMBER(38)select * from ming.tx; A B———- ———- 1 1SYS@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;下面再看一下自动刷新，该动作需要在快速刷新pdb中执行SYS@cdbtest1(CDB$ROOT)> @entdb mingdev1SYS@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 a30SYS@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 2alert日志中可以发现：MINGDEV1(6):ALTER PLUGGABLE DATABASE mingdev1 REFRESH MODE EVERY 1 MINUTESMINGDEV1(6):Completed: ALTER PLUGGABLE DATABASE mingdev1 REFRESH MODE EVERY 1 MINUTES2018-12-09T12:03:55.230825+08:00MINGDEV1(6):alter pluggable database refreshMINGDEV1(6):Completed: alter pluggable database refresh2018-12-09T12:04:54.144791+08:00MINGDEV1(6):alter pluggable database refreshMINGDEV1(6):Completed: alter pluggable database refresh2018-12-09T12:05:54.347415+08:00MINGDEV1(6):alter pluggable database refreshMINGDEV1(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关闭或者mountSYS@cdbtest1(MINGDEV1)> shutdown immediatePluggable 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 2SYS@cdbtest1(MINGDEV1)>
alter pluggable database MINGDEV1 refresh mode manual;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 NOSYS@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这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是云技术，小编将为大家推送更多相关知识点的文章，欢迎关注！

相关推荐: 怎么KILL SESSION时保证不中断正在进行的事务

小编给大家分享一下怎么KILL SESSION时保证不中断正在进行的事务免费主机域名，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 通过在ALTER SYSTEM DISCONNEC…