Oracle 12.2 新特性：線上PDB資料庫克隆(Pluggable Hot Clone)
Oracle 12.2 新特性：線上PDB資料庫克隆(Pluggable Hot Clone)
在Oracle 12c的多租戶（Multitenant）特性中，可以透過靈活的克隆複製來建立PDB（Pluggable Database），使得運維的工作大大簡化。但是在12.1中，進行資料庫Clone時，源資料庫需要 ，即影響源資料庫的使用，又會帶來時間視窗的不便。
在12.2中，Oracle引入了 Hot Clone 技術，可以透過線上的方式，對源庫進行復制。大大簡化的運維的複雜性。
以下測試向大家展示了這一新特性的用法和功能。首先在PDB1執行一個資料表的連續插入動作，維持活動事務（注意：最後中斷是因為克隆已經完成了）:
SQL> connect eygle/eygle@pdb1 Connected. SQL> create table enmotech ( id number, dt timestamp ); Table created. Elapsed: 00:00:01.17 SQL> begin 2 for i in 1 .. 600 loop 3 insert into enmotech values (i, systimestamp ); 4 commit; 5 dbms_lock.sleep(2); 6 end loop; 7 end; 8 / ^Cbegin * ERROR at line 1: ORA-01013: user requested cancel of current operation ORA-06512: at "SYS.DBMS_LOCK", line 205 ORA-06512: at line 5 Elapsed: 00:04:20.96
選擇PDB1作為克隆複製的源資料庫，在源資料庫保持活動的狀態下，進行Clone複製：
[oracle@pg1-enmotech-com ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 1 03:22:50 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select con_id,name from v$datafile where con_id=3; CON_ID ---------- NAME -------------------------------------------------------------------------------- 3 /u01/app/oracle/oradata/ORCL/3F605C8D8A4206B6E0536838A8C04ACF/datafile/o1_mf_system_d0n37tyk_.dbf 3 /u01/app/oracle/oradata/ORCL/3F605C8D8A4206B6E0536838A8C04ACF/datafile/o1_mf_sysaux_d0n37tyx_.dbf 3 CON_ID ---------- NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ORCL/3F605C8D8A4206B6E0536838A8C04ACF/datafile/o1_mf_undotbs1_d0n37tyy_.dbf 3 /u01/app/oracle/oradata/ORCL/3F605C8D8A4206B6E0536838A8C04ACF/datafile/o1_mf_users_d0n38fdr_.dbf
接下來執行資料庫克隆的操作，無需對源庫執行任何操作：
SQL> ! mkdir /u01/app/oracle/oradata/ORCL/enmotech SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/ORCL/enmotech'; System altered. SQL> create pluggable database yhem from pdb1 ;Pluggable database created.
克隆複製完成之後，可以開啟資料庫，檢視資料，之前的事務資料已經被部分的複製過來：
SQL> alter pluggable database yhem open; Pluggable database altered. SQL> alter session set container=YHEM; Session altered. SQL> alter session set current_schema=EYGLE; Session altered. SQL> select count(*) from enmotech; COUNT(*) ---------- 10 SQL> select * from enmotech; ID DT ---------- ------------------------------------------------------ 1 01-DEC-16 03.37.06.539694 AM 2 01-DEC-16 03.37.08.556754 AM 3 01-DEC-16 03.37.10.558066 AM 4 01-DEC-16 03.37.12.563268 AM 5 01-DEC-16 03.37.14.564449 AM 6 01-DEC-16 03.37.16.570639 AM 7 01-DEC-16 03.37.18.571286 AM 8 01-DEC-16 03.37.20.571668 AM 9 01-DEC-16 03.37.22.903123 AM 10 01-DEC-16 03.37.24.902948 AM 10 rows selected.
透過告警日誌記錄的資訊，可以看到整個動作的核心步驟，實際上Oracle做了不完全恢復，最後在指定的SCN開啟了資料庫：
2016-12-01T03:35:43.207319+08:00 ALTER SYSTEM SET db_create_file_dest='/u01/app/oracle/oradata/ORCL/enmotech' SCOPE=BOTH; 2016-12-01T03:37:08.934023+08:00 create pluggable database yhem from pdb1 2016-12-01T03:37:08.989913+08:00 PDB1(3): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated 2016-12-01T03:37:29.640510+08:00 YHEM(5):Endian type of dictionary set to little **************************************************************** Pluggable Database YHEM with pdb id - 5 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-0x00000000000000e0 **************************************************************** YHEM(5):Media Recovery Start 2016-12-01T03:37:30.026018+08:00 YHEM(5):Serial Media Recovery started 2016-12-01T03:37:30.154319+08:00 YHEM(5):Recovery of Online Redo Log: Thread 1 Group 2 Seq 68 Reading mem 0 YHEM(5): Mem# 0: /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_d0n2yzro_.log YHEM(5): Mem# 1: /u01/app/oracle/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_2_d0n2z0vm_.log 2016-12-01T03:37:30.346568+08:00 YHEM(5):Incomplete Recovery applied until change 2198639 time 12/01/2016 03:37:26 2016-12-01T03:37:30.353390+08:00 YHEM(5):Media Recovery Complete (orcl)YHEM(5):Autotune of undo retention is turned on. 2016-12-01T03:37:36.701219+08:00 YHEM(5):[15846] Successfully onlined Undo Tablespace 2. YHEM(5):Undo initialization finished serial:0 start:139692076 end:139692182 diff:106 ms (0.1 seconds) YHEM(5):Database Characterset for YHEM is AL32UTF8 YHEM(5):JIT: pid 15846 requesting stop Completed: create pluggable database yhem from pdb1 2016-12-01T03:38:32.727311+08:00 alter pluggable database yhem open YHEM(5):Autotune of undo retention is turned on. 2016-12-01T03:38:32.850162+08:00 YHEM(5):Endian type of dictionary set to little YHEM(5):[15846] Successfully onlined Undo Tablespace 2. YHEM(5):Undo initialization finished serial:0 start:139748579 end:139748829 diff:250 ms (0.2 seconds) YHEM(5):Deleting old file#9 from file$ YHEM(5):Deleting old file#10 from file$ YHEM(5):Deleting old file#11 from file$ YHEM(5):Deleting old file#12 from file$ YHEM(5):Deleting old file#13 from file$ YHEM(5):Adding new file#18 to file$(old file#9) YHEM(5):Adding new file#19 to file$(old file#10) YHEM(5):Adding new file#20 to file$(old file#11) YHEM(5):Adding new file#21 to file$(old file#12) YHEM(5):Successfully created internal service yhem at open ****************************************************************Post plug operations are now complete.Pluggable database YHEM with pdb id - 5 is now marked as NEW.**************************************************************** YHEM(5):Database Characterset for YHEM is AL32UTF8 2016-12-01T03:38:33.998091+08:00 YHEM(5):Opatch validation is skipped for PDB YHEM (con_id=0) 2016-12-01T03:39:01.596335+08:00 YHEM(5):Opening pdb with no Resource Manager plan active 2016-12-01T03:39:12.433441+08:00 Pluggable database YHEM opened read write Completed: alter pluggable database yhem open 2016-12-01T03:46:45.511090+08:00 Starting control autobackup Control autobackup written to DISK device handle '/u01/app/oracle/fast_recovery_area/orcl/PG1/autobackup/2016_12_01/o1_mf_s_929418405_d3yc55s5_.bkp'
這就是Oracle 12.2的重要增強之一，關於PDB的持續改進。
在12.1版本中Clone一份PDB源庫需要開啟在read only只讀模式, 在12.2版本中引入了local undo mode, 源PDB在read/write 讀寫模式也可以Clone. 這樣可以在複製一份測試環境時對源庫影響降到最低, 同時local undo mode也是ORACLE推薦的.
12.1 源PDB在read/write模式clone是不允許的.以前的
ORA-65081: database or pluggable database is not open in read only mode
下面會演示一下12.2 的PDB HOT Clone, 開始前先補充一點local undo的知識.
UNDO模式
undo 在12C R1版本中只支援Global Shared Undo模式, 所有container共享一個UNDO表空間, 目前保留這種模式只是為了升級過渡, 在12C R2引入了PDB Local UNDO模式,每個container都有自己的UNDO 表空間, 對於RAC是每個例項每個container都有自己的UNDO表空間, 這也正是推薦的. 無論是Shared undo還是Local undo模式,都是CDB的屬性,所以在修改時當前的container都要在cdb$root下. 12.2只要配置了LOCAL undo才支援下面的新特性:Hot Clone, Refresh PDB, PDB Relocate, Flashback PDB
如何檢查當前UNDO模式?
SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPE DESCRIPTION -------------------- ----- ----------------------------------- LOCAL_UNDO_ENABLED TRUE true if local undo is enabled
UNDO模式轉換
How to convert CDB from Local to Shared Undo Mode
When a CDB is in local undo mode, you can change it to use shared undo mode by issuing an ALTER DATABASE LOCAL UNDO OFF statement and restarting the database. When it is in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete these local undo tablespaces. startup upgrade ALTER DATABASE LOCAL UNDO off; shutdown immediate startup ALTER PLUGGABLE DATABASE xxx OPEN READ WRITE; ALTER SESSION SET CONTAINER=xxx; select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; drop tablespace UNDOTBS1 including contents and datafiles;
How to convert CDB from Shared to Local Undo Mode
startup upgrade container set cdb$root ,if not :alter session set container=cdb$root; ALTER DATABASE LOCAL UNDO ON; shutdown immediate; startup -- When a CDB is changed from shared undo mode to local undo mode, -- Oracle Database creates the required undo tablespaces automatically.alter pluggable database xxx open;
Hot Clone Local PDB
因為我當前的測試環境12.2只有一套,先測試Clone LOCAL Pdb, 環境oracle 12.2.0.1 ee on OEL 6, HOT Clone的內部原理:
Source PDB remains open for read and write
Read and copy in parallel
On-going operations imply a “dirty read”
Some data changes not included in initial file copy
Ship and apply redo to catch up with source
Apply undo to rollback uncommitted transactions
所以在較長或較大PDB Clone期間要保證REDO 日誌存在, 最好源庫開起archivelog mode.
[oracle@anbob ~]$ ora SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 6 17:11:54 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBANBOB MOUNTED SQL> alter pluggable database pdbanbob open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBANBOB READ WRITE NO SQL> create pluggable database pdbweejar from pdbanbpb; create pluggable database pdbweejar from pdbanbpb ERROR at line 1: ORA-65016: FILE_NAME_CONVERT must be specified SQL> alter session set pdb_file_name_convert='anbob','weejar'; Session altered. -- Multi-threaded based on cpu count by default -- File copy progress can be monitored in v$session_longops:SQL> create pluggable database pdbweejar from pdbanbob; Pluggable database created. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBANBOB READ WRITE NO 6 PDBWEEJAR MOUNTED SQL> alter pluggable database pdbweejar open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBANBOB READ WRITE NO 6 PDBWEEJAR READ WRITE NO SQL> alter session set container=pdbweejar; Session altered. SQL> select * from v$dbfile FILE# NAME CON_ID ---------- -------------------------------------------------------------------- ---------- 14 /u02/app/oracle/oradata/weejar/pdbweejar/system01.dbf 6 15 /u02/app/oracle/oradata/weejar/pdbweejar/sysaux01.dbf 6 16 /u02/app/oracle/oradata/weejar/pdbweejar/undotbs01.dbf 6 17 /u02/app/oracle/oradata/weejar/pdbweejar/users01.dbf 6 18 /u02/app/oracle/oradata/weejar/pdbweejar/lower01.dbf 6 SQL> alter session set container=pdbanbob; Session altered. SQL> set lines 300 SQL> select * from v$dbfile; FILE# NAME CON_ID ---------- -------------------------------------------------------------------------------- ---------- 9 /u02/app/oracle/oradata/anbob/pdbanbob/system01.dbf 3 10 /u02/app/oracle/oradata/anbob/pdbanbob/sysaux01.dbf 3 11 /u02/app/oracle/oradata/anbob/pdbanbob/undotbs01.dbf 3 12 /u02/app/oracle/oradata/anbob/pdbanbob/users01.dbf 3 13 /u02/app/oracle/oradata/anbob/pdbanbob/lower01.dbf 3 SQL> select * from database_properties; PROPERTY_NAME PROPERTY_VALUE DESCRIPTION --------------------------- -------------------- ------------------------------------------------------------ CON_VSN 5 version number for the CDB or the Application Container DICTIONARY_ENDIAN_TYPE LITTLE Endian type of the data dictionary LOCAL_UNDO_ENABLED TRUE true if local undo is enabled OLS_OID_STATUS 0 OLS OID Status used for Label Security GLOBAL_DB_NAME ANBOB.COM Global database name
Hot Clone REMOTE PDB
ENABLE ARCHIVELOG MODE IN CDB$ROOT CDB$ROOT-SQL> startup mount CDB$ROOT-SQL> alter database archivelog; CDB$ROOT-SQL> alter database open; ENABLE LOCAL UNDO IN CDB$ROOT CDB$ROOT-SQL> startup mount CDB$ROOT-SQL> alter database open upgrade; CDB$ROOT-SQL> alter database local undo on; CREATE COMMON USER IN CDB$ROOT CDB$ROOT-SQL> create user c##admin identified by <password> container=all; CDB$ROOT-SQL> grant sysoper to c##admin container=all; CREATE PUBLIC DATABASE LINK IN CDB$ROOT CDB$ROOT-SQL> create public database link cdb1_link connect to c##admin identified by <password> using ‘<tns alias>’; CREATE PLUGGABLE DATABASE PDB_DEV FROM PDB@DBLINK;
Summary:
12.2引入了local undo解決了12.1時clone pdb時源PDB需要READ-ONLY的前提. 減少了clone
pdb時對源庫的影響. 12.2 可以在shared undo和local undo之前來回的轉換, 事務可以透明的轉移, 從LOCAL 轉到
SHARED裡,第一次開啟PDB時會應用local undo接著使用shared undo, 後就不再需要local undo, 但是local undo
mode是ORACLE建議的配置. 這樣才可以使用HOT CLONE和Refresh PDB及快速的閃回PDB時使用.
References:
Undo Modes in 12.2 Multitenant Databases – Local and Shared
Modes (文件 ID 2169828.1)
About Me
|
........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（ http://blog.itpub.net/26736162 ）、部落格園（ http://www.cnblogs.com/lhrbest ）和個人weixin公眾號（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ........................................................................................................................ ● QQ群號： 230161599 （滿） 、618766405 ● weixin群：可加我weixin，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ） ，註明新增緣由 ● 於 2018-11-01 06:00 ~ 2018-11-31 24:00 在魔都完成 ● 最新修改時間：2018-11-01 06:00 ~ 2018-11-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店 ： ● 小麥苗出版的資料庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 weixin客戶端 掃描下面的二維碼來關注小麥苗的weixin公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）、新增小麥苗weixin， 學習最實用的資料庫技術。
........................................................................................................................ |
|
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2222155/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12.2 新特性: Online PDB relocate (PDB hot move)Oracle
- Oracle 12.2新特性: PDB級閃回資料庫（Flashback PDB）Oracle資料庫
- oracle12c新特點之可插拔資料庫（Pluggable Database,PDB）Oracle資料庫Database
- Oracle 12.2的新特性之PDB級別閃回Oracle
- Oracle 12C 新特性之 PDB熱克隆（本地克隆、遠端異機克隆）Oracle
- 【BUILD_ORACLE】使用Oracle資料泵線上不停機克隆/遷移PDBUIOracle
- Oracle 12.2 新特性 | PDB不同字符集變更深入解析Oracle
- Oracle 12c 新特性之 PDB 級別閃回資料庫Oracle資料庫
- 克隆(clone) ORACLE HOMEOracle
- Clone (克隆) ORACLE HOMEOracle
- 【PDB】Oracle 建立pdb說明(create pluggable database)OracleDatabase
- clone oracle 12c pluggable databasesOracleDatabase
- 克隆 clone Oracle 軟體Oracle
- clone oracle資料庫的方法Oracle資料庫
- Oracle 建立PDB-本地克隆Oracle
- 【新炬網路名師大講堂】clone oracle 12c pluggable databasesOracleDatabase
- 【PDB】DBA常用命令之Oracle12.2+ cdb資料庫統一檢查Oracle資料庫
- Oracle12cr1新特性之容器資料庫(CDB)和可插拔資料庫(PDB) 的啟動和關閉Oracle資料庫
- ORACLE 12C新特性——CDB與PDBOracle
- Oracle 建立PDB-遠端克隆Oracle
- 靜默建立oracle資料庫及克隆資料庫Oracle資料庫
- Oracle Database 12.2新特性詳解 --該國強OracleDatabase
- ZT：資料庫Oracle11g新特性RMAN資料庫Oracle
- Oracle 12C 新特性之資料檔案線上遷移Oracle
- 詳談Oracle12c新特點容器資料庫&可插拔資料庫（CDB&PDB）Oracle資料庫
- Oracle 18C新特性之PDB snapshot Carousel--PDB快照輪播Oracle
- oracle 12c使用dblink克隆pdbOracle
- MySQL 8 新特性之Clone PluginMySqlPlugin
- Oracle 12c多租戶特性詳解：PDB 的建立、克隆與維護Oracle
- 靜默安裝Oracle12.2資料庫Oracle資料庫
- Oracle 資料庫12c新特性總結（一）Oracle資料庫
- js deep clone 深克隆JS
- Rman 實現oracle11g資料庫cloneOracle資料庫
- Oracle 18C新特性之PDB snapshot(快照) CarouselOracle
- JAVA 基礎 – clone淺克隆與深克隆Java
- JAVA 基礎 - clone淺克隆與深克隆Java
- 【PDB】Oracle資料庫如何檢查和設定pdb最大儲存大小Oracle資料庫
- oracle 12c 新特性之一：線上重新命名資料檔案Oracle