Oracle 18c新特性-PDB快照輪播(Snapshot Carousel)
更新時間：2018年10月09日 11:45:26 投稿：mrr
Oracle 18c推出的一個新功能就是PDB快照輪播，最多可以創(chuàng)建8個pdb的快照，而這8個構(gòu)成的一組快照就被稱作快照輪播。這篇文章主要介紹了Oracle 18c新特性-PDB快照輪播（Snapshot Carousel）,需要的朋友可以參考下
Oracle 18c推出的一個新功能就是PDB快照輪播，最多可以創(chuàng)建8個pdb的快照，而這8個構(gòu)成的一組快照就被稱作快照輪播。當(dāng)達到第8個快照限制時，新的快照就會覆蓋最舊的快照，就像redo log一樣。PDB的快照主要作用是當(dāng)出現(xiàn)了問題可以閃回到快照的時間點，另一個功能是基于快照來查詢歷史數(shù)據(jù)。
SQL> SELECT r.CON_ID, p.PDB_NAME, PROPERTY_NAME,
PROPERTY_VALUE AS value, DESCRIPTION
FROM CDB_PROPERTIES r, CDB_PDBS p
WHERE r.CON_ID = p.CON_ID
AND PROPERTY_NAME LIKE 'MAX_PDB%'
AND description like 'maximum%'
ORDER BY PROPERTY_NAME;
CON_ID PDB_NAME PROPERTY_NAME VALUE DESCRIPTION
---------- -------------------- -------------------- -------------------- ----------------------------------------------------------------------
3 ORCLPDB1 MAX_PDB_SNAPSHOTS
maximum number of snapshots for a given PDB
默認(rèn)情況下是手動創(chuàng)建快照，我們可以把它改成自動創(chuàng)建。
SQL> select snapshot_mode,snapshot_interval/60 from dba_pdbs; SNAPSH SNAPSHOT_INTERVAL/60 ------ -------------------- MANUAL SQL> alter pluggable database snapshot mode every 4 hours; Pluggable database altered. SQL> select snapshot_mode,snapshot_interval/60 from dba_pdbs; SNAPSH SNAPSHOT_INTERVAL/60 ------ -------------------- AUTO 4
要返回手動模式，輸入mode manual即可。
SQL> alter pluggable database snapshot mode manual; Pluggable database altered. SQL> alter pluggable database snapshot; Pluggable database altered.
手動創(chuàng)建快照，可以自己指定名字，也可以使用系統(tǒng)自動生成的名字。通過查看DBA_PDB_SNAPSHOTS視圖，可以找到快照存放的路徑及創(chuàng)建快照的SCN號。
SQL> SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN;
CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH
---------- -------------------- ------------------------- ---------- ---------------------------------------------------------------------------
3 ORCLPDB1 SNAP_2953839490_989014667 2821702 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2821702.pdb
3 ORCLPDB1 PDB_SNAP 2823303 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2823303.pdb
3 ORCLPDB1 SNAP_2953839490_989015970 2823679 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2823679.pdb
[oracle@oracle-18c-vagrant ORCLPDB1]$ ls -lsh
total 1.3G
4.0K drwxr-x---. 8 oracle oinstall 4.0K Oct 8 22:39 ORCLCDB
171M -rw-r--r--. 1 oracle oinstall 171M Oct 8 22:18 snap_2953839490_2821702.pdb
171M -rw-r--r--. 1 oracle oinstall 171M Oct 8 22:38 snap_2953839490_2823303.pdb
171M -rw-r--r--. 1 oracle oinstall 171M Oct 8 22:40 snap_2953839490_2823679.pdb
371M -rw-r-----. 1 oracle oinstall 371M Oct 8 22:39 sysaux01.dbf
271M -rw-r-----. 1 oracle oinstall 271M Oct 8 22:39 system01.dbf
56K -rw-r-----. 1 oracle oinstall 63M Oct 1 23:21 temp01.dbf
101M -rw-r-----. 1 oracle oinstall 101M Oct 8 22:39 undotbs01.dbf
5.1M -rw-r-----. 1 oracle oinstall 5.1M Oct 8 22:39 users01.dbf
如果要刪除快照，則有兩種方法，第一種就是使用alter pluggable database刪除，第二種是修改max_pdb_snapshots參數(shù)，當(dāng)max_pdb_snapshots參數(shù)設(shè)置成0時，將刪除全部快照。
SQL> alter pluggable database drop snapshot SNAP_2953839490_989015970;
Pluggable database altered.
SQL> SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN;
CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH
---------- -------------------- ------------------------- ---------- ------------- ---------------------------------------------------------------------------
3 ORCLPDB1 SNAP_2953839490_989014667 2821702 1539008272 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2821702.pdb
3 ORCLPDB1 PDB_SNAP 2823303 1539009503 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2823303.pdb
SQL> alter pluggable database set max_pdb_snapshots=0;
Pluggable database altered.
SQL> SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN;
no rows selected
演示了上述功能之后，還有一個功能，就是我們可以根據(jù)快照創(chuàng)建pdb。
SQL> alter pluggable database set max_pdb_snapshots=8;
Pluggable database altered
SQL> alter pluggable database snapshot pdb_snap;
Pluggable database altered.
SQL> SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN;
CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH
---------- -------------------- ------------------------- ---------- ------------- ---------------------------------------------------------------------------
3 ORCLPDB1 PDB_SNAP 2825283 1539010328 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2825283.pdb
SQL> !
[oracle@oracle-18c-vagrant ~]$ mkdir -p /opt/oracle/oradata/ORCLCDB/ORCLPDB2
SQL> create pluggable database ORCLPDB2 from ORCLPDB1 using snapshot PDB_SNAP create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2';
create pluggable database ORCLPDB2 from ORCLPDB1 using snapshot PDB_SNAP create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2'
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> create pluggable database ORCLPDB2 from ORCLPDB1 using snapshot PDB_SNAP create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2';
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
5 ORCLPDB2 MOUNTED
SQL> alter pluggable database ORCLPDB2 open;
Pluggable database altered.
這樣我們就根據(jù)快照創(chuàng)建了一個新的PDB，假設(shè)當(dāng)前PDB出現(xiàn)了數(shù)據(jù)覆蓋，我們就可以使用這個歷史快照的PDB來恢復(fù)數(shù)據(jù)。
當(dāng)然還有一個很好用的功能就是我們在創(chuàng)建pdb的過程中把他設(shè)置成15分鐘產(chǎn)生一個快照。當(dāng)然配置這個功能有一個先決條件：CDB必須處于本地undo模式.
SQL> create pluggable database ORCLPDB3 from ORCLPDB1 file_name_convert=('ORCLPDB1','ORCLPDB3') snapshot mode every 5 minutes;
Pluggable database created.
可以看到每隔5分鐘自動創(chuàng)建一個快照
[oracle@oracle-18c-vagrant ORCLPDB3]$ ls -lrt snap* -rw-r--r--. 1 oracle oinstall 179134736 Oct 8 23:13 snap_2091710291_2830531.pdb -rw-r--r--. 1 oracle oinstall 179167158 Oct 8 23:18 snap_2091710291_2832545.pdb -rw-r--r--. 1 oracle oinstall 179285979 Oct 8 23:23 snap_2091710291_2833104.pdb -rw-r--r--. 1 oracle oinstall 179284365 Oct 8 23:28 snap_2091710291_2833687.pdb -rw-r--r--. 1 oracle oinstall 179302205 Oct 8 23:33 snap_2091710291_2834965.pdb -rw-r--r--. 1 oracle oinstall 179315484 Oct 8 23:38 snap_2091710291_2836695.pdb -rw-r--r--. 1 oracle oinstall 115724288 Oct 8 23:43 snap_2091710291_2837222.pdb
我們可以做個實驗測試一下。在生成快照之前創(chuàng)建一個表插入點數(shù)據(jù)，這里上一次快照是23:43分。
SQL> alter session set container=ORCLPDB3;
Session altered.
SQL> create table a1 as select * from dba_objects;
Table created.
SQL> select count(1) from a1;
COUNT(1)
----------
72897
[oracle@oracle-18c-vagrant ORCLPDB3]$ ls -lrt snap*
-rw-r--r--. 1 oracle oinstall 179134736 Oct 8 23:13 snap_2091710291_2830531.pdb
-rw-r--r--. 1 oracle oinstall 179167158 Oct 8 23:18 snap_2091710291_2832545.pdb
-rw-r--r--. 1 oracle oinstall 179285979 Oct 8 23:23 snap_2091710291_2833104.pdb
-rw-r--r--. 1 oracle oinstall 179284365 Oct 8 23:28 snap_2091710291_2833687.pdb
-rw-r--r--. 1 oracle oinstall 179302205 Oct 8 23:33 snap_2091710291_2834965.pdb
-rw-r--r--. 1 oracle oinstall 179315484 Oct 8 23:38 snap_2091710291_2836695.pdb
-rw-r--r--. 1 oracle oinstall 179353508 Oct 8 23:43 snap_2091710291_2837222.pdb
-rw-r--r--. 1 oracle oinstall 5767168 Oct 8 23:48 snap_2091710291_2839217.pdb
SQL> SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN;
CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH
---------- -------------------- ------------------------- ---------- ------------- ---------------------------------------------------------------------------
7 ORCLPDB3 SNAP_2091710291_989017984 2830531 1539011592 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2830531.pdb
7 ORCLPDB3 SNAP_2091710291_989018284 2832545 1539011887 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2832545.pdb
7 ORCLPDB3 SNAP_2091710291_989018584 2833104 1539012188 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2833104.pdb
7 ORCLPDB3 SNAP_2091710291_989018884 2833687 1539012489 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2833687.pdb
7 ORCLPDB3 SNAP_2091710291_989019184 2834965 1539012787 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2834965.pdb
7 ORCLPDB3 SNAP_2091710291_989019484 2836695 1539013087 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2836695.pdb
7 ORCLPDB3 SNAP_2091710291_989019784 2837222 1539013386 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2837222.pdb
7 ORCLPDB3 SNAP_2091710291_989020084 2839217 1539013686 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2839217.pdb
23點48分產(chǎn)生了新的快照，此時我們使用新的快照做一個克隆，登陸到ORCLPDB4數(shù)據(jù)庫后，我們會發(fā)現(xiàn)是有數(shù)據(jù)的。
SQL> alter session set container=CDB$ROOT; Session altered. SQL> create pluggable database ORCLPDB4 from ORCLPDB3 using snapshot SNAP_2091710291_989020084 create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB4'; Pluggable database created SQL> alter pluggable database ORCLPDB4 open; Pluggable database altered. SQL> alter session set container=ORCLPDB4; Session altered. SQL> select count(1) from a1; COUNT(1) ---------- 72897
這個功能對于測試人員非常有用，例如移動電信現(xiàn)在有BCV環(huán)境，專門用于經(jīng)分抽數(shù)及測試，而這個BCV環(huán)境是底層同步的一個克隆。使用18c數(shù)據(jù)庫，我們就可以直接創(chuàng)建快照，然后使用快照克隆一個新數(shù)據(jù)庫形成BCV環(huán)境給經(jīng)分抽數(shù)及測試使用了。
總結(jié)
以上所述是小編給大家介紹的Oracle 18c新特性-PDB快照輪播（Snapshot Carousel），希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！
相關(guān)文章
關(guān)于SQL執(zhí)行計劃錯誤導(dǎo)致臨時表空間不足的問題
故障現(xiàn)象：臨時表空間不足的問題已經(jīng)報錯過3次，客戶也煩了，前兩次都是同事添加5G的數(shù)據(jù)文件，目前已經(jīng)達到40G，占用臨時表空間主要是distinct 和group by 以及Union all 表數(shù)據(jù)量在200W左右，也不至于把40G的臨時表空間撐爆。2013-04-04
這篇文章主要介紹了oracle查詢不含括號及不含指定字符的方法,需要的朋友可以參考下2014-07-07
ORACLE數(shù)據(jù)庫應(yīng)用開發(fā)常見問題及排除
ORACLE數(shù)據(jù)庫應(yīng)用開發(fā)常見問題及排除...2007-03-03
Oracle解鎖表、包、用戶、殺會話、停job的方法實現(xiàn)
本文主要介紹了Oracle解鎖表、包、用戶、殺會話、停job的方法實現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-12-12
本篇文章主要介紹了Oracle修改字段類型方法總結(jié)，小編覺得不錯，非常具有實用價值，有興趣的可以了解一下。2016-12-12
oracle數(shù)據(jù)庫中選擇桌面類和服務(wù)器類的區(qū)別詳解
oracle就是ORACLE公司的數(shù)據(jù)庫產(chǎn)品,以高性能著稱,下面這篇文章主要給大家介紹了關(guān)于oracle數(shù)據(jù)庫中選擇桌面類和服務(wù)器類區(qū)別的相關(guān)資料,文中介紹的非常詳細(xì),需要的朋友可以參考下2024-05-05
Oracle用PLSQL導(dǎo)出導(dǎo)入dmp表詳細(xì)圖解
dmp是二進制文件,可跨平臺還能包含權(quán)限,效率好,這篇文章主要給大家介紹了關(guān)于Oracle用PLSQL導(dǎo)出導(dǎo)入dmp表的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-11-11
我們經(jīng)常在設(shè)計數(shù)據(jù)庫的時候用一個系統(tǒng)自動分配的ID來作為我們的主鍵,但是在Oracle沒有自增主鍵的設(shè)置,我們可以通過這篇文章介紹的方法實現(xiàn)，有需要的朋友們可以參考借鑒，下面來一起看看吧。2016-10-10
oracle初始化參數(shù)設(shè)置...2007-03-03
如果你是初學(xué)者,你有必要看看這篇文章。2009-06-06