溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“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 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;
|
下面再看一下自動刷新，該動作需要在快速刷新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. |
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了
感謝各位的閱讀，以上就是“oracle 12c中怎么搭建PDB refresh”的內容了，經過本文的學習后，相信大家對oracle 12c中怎么搭建PDB refresh這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
