溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“oracle 12c中怎么搭建PDB refresh”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“oracle 12c中怎么搭建PDB refresh”吧！
PDB Refresh是12C推出的新特性，可以對(duì)源端PDB進(jìn)行增量同步，可以快速實(shí)現(xiàn)拷貝一份數(shù)據(jù)庫(kù)鏡像副本，有點(diǎn)類似運(yùn)營(yíng)商喜歡使用的EMC的BCV技術(shù)，存儲(chǔ)層的復(fù)制功能。但是副本只能以read only打開(kāi)，拷貝速度較慢，刷新走網(wǎng)絡(luò)流量，實(shí)際應(yīng)用場(chǎng)景較少。
PDB refresh源端與目標(biāo)端可以在同一個(gè)cdb，也可以在不同的cdb中，刷新方式必須通過(guò)dblink。
數(shù)據(jù)庫(kù)版本 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有兩個(gè)基本要求：歸檔和local undo 檢查歸檔是否開(kāi)啟： 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
當(dāng)前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的數(shù)據(jù)文件存放位置跟普通pdb有點(diǎn)區(qū)別，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 它的數(shù)據(jù)文件是放在cdb目錄下的
以MING為源端pdb，需要?jiǎng)?chuàng)建到該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
創(chuàng)建手動(dòng)刷新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
后來(lái)發(fā)現(xiàn)報(bào)錯(cuò) 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模式打開(kāi) 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用戶登錄目標(biāo)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必須將目標(biāo)pdb先關(guān)閉或者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.
|
實(shí)際上刷新的過(guò)程就是一個(gè)介質(zhì)恢復(fù)的過(guò)程
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內(nèi)做一些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
此時(shí)refresh pdb內(nèi)還看不到 sho con_name CON_NAME ------------------------------ MINGDEV1 PDB refresh必須在目標(biāo)pdb中執(zhí)行 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
|
這種方式的實(shí)現(xiàn)方式是通過(guò)定時(shí)job的方式實(shí)現(xiàn)的，如下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;
|
下面再看一下自動(dòng)刷新，該動(dòng)作需要在快速刷新pdb中執(zhí)行 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中插入一條數(shù)據(jù) insert into ming.tx values(2,2); commit; select * from ming.tx;
A B ---------- ---------- 1 1 2 2 |
alert日志中可以發(fā)現(xiàn)：
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中的數(shù)據(jù)并沒(méi)有變化
SYS@cdbtest1(MINGDEV1)> select * from ming.tx;
A B ---------- ---------- 1 1
|
那是因?yàn)閞efresh pdb是read only狀態(tài)，read only模式下不會(huì)自動(dòng)刷新，需要將pdb關(guān)閉或者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. |
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.
|
注意：此過(guò)程不可逆，一旦完成轉(zhuǎn)換，就不能再實(shí)現(xiàn)refresh了
感謝各位的閱讀，以上就是“oracle 12c中怎么搭建PDB refresh”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)oracle 12c中怎么搭建PDB refresh這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
