| 設定PDB快照的最大數量
使用MAX_PDB_SNAPSHOTS引數設定PDB快照最大快照數，首先登入到需要設定的PDB中，透過檢視CDB_PROPERTIES查詢當前MAX_PDB_SNAPSHOT值，可以使用alter pluggabledatabase/alter database更改MAX_PDB_SNAPSHOTS值。
如下為設定PDB snapshot的例子
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SET
LINESIZE 1500
COL CON_ID FORMAT 99999
COL PROPERTY_NAME FORMAT a17
COL PDB_NAME FORMAT a9
COL VALUE FORMAT a12
COL DESCRIPTION FORMAT a90
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%'
ORDER
BY
PROPERTY_NAME;
CON_ID PDB_NAME PROPERTY_NAME VALUE DESCRIPTION
3 ORCLPDB MAX_PDB_SNAPSHOTS 7 maximum number
of
snapshots
for
a given PDB
3 ORCLPDB MAX_PDB_STORAGE UNLIMITED Maximum
Space
Usage
of
Datafiles
and
Local
Tempfiles
in
Container
|
下面語句將當前pdb的快照數設定為7
|
1
2
3
4
5
6
|
進入PDB
YAO.CHONG>
alter
session
set
container = ORCLPDB;
Session altered.
Elapsed: 00:00:00.01
YAO.CHONG>
ALTER
PLUGGABLE
DATABASE
SET
MAX_PDB_SNAPSHOTS=7;
Pluggable
database
altered.
|
想要刪除所有的快照，我們只需要執行下面一條命令即可。
|
1
2
3
4
5
6
7
8
9
10
|
ALTER
PLUGGABLE
DATABASE
SET
MAX_PDB_SNAPSHOTS=0;
SET
LINESIZE 150
COL CON_NAME FORMAT a10
COL SNAPSHOT_NAME FORMAT a25
COL SNAP_SCN FORMAT 9999999
COL FULL_SNAPSHOT_PATH FORMAT a45
SELECT
CON_ID,CON_NAME,SNAPSHOT_NAME,SNAPSHOT_SCN
AS
snap_scn, FULL_SNAPSHOT_PATH
FROM
DBA_PDB_SNAPSHOTS
ORDER
BY
SNAP_SCN;
##都沒了
|
| 配置自動生成PDB快照
預設情況下都是手動生成快照，使用SNAPSHOT MODE EVERY子句自動產生PDB快照。
|
1
2
3
4
|
SELECT
SNAPSHOT_MODE
"S_MODE"
, SNAPSHOT_INTERVAL/60
"SNAP_INT_HRS"
FROM
DBA_PDBS;
S_MODE SNAP_INT_HRS
MANUAL
|
更改自動建立模式為每24小時自動生成PDB快照
|
1
2
3
4
5
6
7
8
9
10
11
12
|
進入PDB裡面去執行
YAO.CHONG >
alter
session
set
container = ORCLPDB;
Session altered.
Elapsed: 00:00:00.01
YAO.CHONG>
ALTER
PLUGGABLE
DATABASE
SNAPSHOT MODE EVERY 24 HOURS;
Pluggable
database
altered.
Elapsed: 00:00:00.17
#確認自動生成快照的時間間隔
SELECT
SNAPSHOT_MODE
"S_MODE"
, SNAPSHOT_INTERVAL/60
"SNAP_INT_HRS"
FROM
DBA_PDBS;
S_MODE SNAP_INT_HRS
AUTO 24
|
下面是每2小時生成快照的例子
首先登入到CDB根目錄。以下語句從名為cdb1_pdb1的現有PDB建立cdb1_pdb3，並將其配置為每2小時自動獲取快照：
|
1
2
3
|
CREATE
PLUGGABLE
DATABASE
cdb1_pdb3
FROM
cdb1_pdb1
FILE_NAME_CONVERT=(
'cdb1_pdb1'
,
'cdb1_pdb3'
)
SNAPSHOT MODE EVERY 120 MINUTES;
|
| 手工建立PDB快照
使用ALTER PLUGGABLE DATABASE/CREATE PLUGGABLE DATABASE手動生成快照。使用ALTER PLUGGABLE DATABASE SNAPSHOT語句需要滿足的要求。
-
CDB必須是LOCAL UNDO模式
-
使用者必須有許可權建立和刪除PDB
|
1
2
3
|
YAO.CHONG>
ALTER
PLUGGABLE
DATABASE
SNAPSHOT test_snapshot;
Pluggable
database
altered.
Elapsed: 00:03:15.66/快照大小481M，3分鐘，
|
下面查詢出當前PDB的快照
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SET
LINESIZE 150
COL CON_NAME FORMAT a10
COL SNAPSHOT_NAME FORMAT a30
COL SNAP_SCN FORMAT 9999999
COL FULL_SNAPSHOT_PATH FORMAT a70
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 CDB1_PDB1 CDB1_PDB1_B4WEDLOAD 2962078 /disk1/oracle/dbs/snap_3489077498_2962078.pdb
3 CDB1_PDB1 CDB1_PDB1_AFWEDLOAD 2962938 /disk1/oracle/dbs/snap_3489077498_2962938.pdb
|
如果你不指定快照名，系統自動生成唯一名稱的快照，下面即為不指定名字建立快照
|
1
2
3
4
5
6
7
8
9
10
11
12
|
ALTER
PLUGGABLE
DATABASE
SNAPSHOT;
SET
LINESIZE 150
COL CON_NAME FORMAT a10
COL SNAPSHOT_NAME FORMAT a25
COL SNAP_SCN FORMAT 9999999
COL FULL_SNAPSHOT_PATH FORMAT a45
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 ORCLPDB SNAP_3034367141_987737800 2995234 /home/oradata/ORCL/orclpdb/snap_3034367141_2995234.pdb
|
| 刪除PDB快照
將MAX_PDB_SNAPSHOTS設定為0，即刪除所有PDB快照，同時也關閉此功能。同樣也可以使用ALTER PLUGGABLE DATABASE刪除當前的快照。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SET
LINESIZE 150
COL CON_NAME FORMAT a10
COL SNAPSHOT_NAME FORMAT a25
COL SNAP_SCN FORMAT 9999999
COL FULL_SNAPSHOT_PATH FORMAT a45
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 ORCLPDB CDB_CNDBA_20180813151200 2970060 /home/oradata/ORCL/orclpdb/snap_3034367141_2970060.pdb
3 ORCLPDB SNAP_3034367141_987737800 2995234 /home/oradata/ORCL/orclpdb/snap_3034367141_2995234.pdb
ALTER
PLUGGABLE
DATABASE
DROP
SNAPSHOT CDB_CNDBA_20180813151200;
再次查詢PDB快照已經被刪除
CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH
3 ORCLPDB SNAP_3034367141_987737800 2995234 /home/oradata/ORCL/orclpdb/snap_3034367141_2995234.pdb
|
| 從snapshot建立PDB
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
alter
system
set
db_create_file_dest =
'/home/oradata/ORCL/orclpdb'
;
conn /
as
sysdba
YAO.CHONG>
create
pluggable
database
pdb_snap_test
from
orclpdb using snapshot test_snapshot;
Pluggable
database
created.
Elapsed: 00:00:39.13
YAO.CHONG> show pdbs;
CON_ID CON_NAME
OPEN
MODE RESTRICTED
2 PDB$SEED
READ
ONLY
NO
3 ORCLPDB
READ
WRITE
NO
4 PDB_SNAP_TEST MOUNTED
YAO.CHONG>
alter
pluggable
database
PDB_SNAP_TEST
open
;
Pluggable
database
altered.
Elapsed: 00:00:09.28
YAO.CHONG>
alter
pluggable
database
PDB_SNAP_TEST
close
;
Pluggable
database
altered.
Elapsed: 00:00:01.11
YAO.CHONG>
drop
pluggable
database
PDB_SNAP_TEST including datafiles;
Pluggable
database
dropped.
Elapsed: 00:00:00.67
|
| 基於SCN或時間點生產快照庫
在閱讀官方文件時，看到生成PDB可以基於SCN，我心中不禁暗喜，當再仔細查閱一番則不然，簡直萬念俱寂、黯然銷魂。提供的基於SCN生成的快照庫只能是基於當前生成快照時間點的SCN，似乎有些雞肋。
|
1
|
create
pluggable
database
pdb_snap_test
from
orclpdb using snapshot
at
SCN 3620951;
|
與recover database until scn指定scn方式開啟資料庫不同，pdb snapshot Carousel指定scn生成的PDB只能是建立snapshot時的固定scn，並且生成快照與snapshot生成pdb的過程中佔據了大量的物理空間與IO資源。
那麼問題來了,說了這麼多關於PDB snapshot Carousel的優勢與好處,是否就說明已經找到了最佳的解決方案呢?
Hold on.
凡事有利也有弊,同樣PDB snapshot Carousel也不能倖免。
快照數量受限
雖然能夠做到Carousel輪轉,但畢竟數量有限,較為死板。在使用者的實際測試環境中條件複雜多變,會限制測試環境的搭建.
佔據大量物理空間與IO資源
對於當前使用者動輒幾十T的資料量，在生成snapshot與snapshot建立pdb的過程中，消耗的時間和佔據的物理資源是巨大的。
拘泥固定時間點
對於PDBsnapshot Carouse而言,只能基於固定時間，即快照點建立，無法根據測試需要靈活指定時間點。面對不斷變化的測試需求，如此死板的模式明顯是不可能滿足要求的。
版本受限
最後是客觀條件的限制,利用新特性需要滿足是在18C的環境下且啟用了PDB功能,但目前很多使用者依然是10G\11G\12C的環境,因此也僅僅只能遠觀而無法上手,對於實際工作很難產生推動作用。
說到這,是否還有更好的解決辦法呢?
答案是必須有!
16 Administering a PDB Snapshot Carousel
您可以為指定的PDB配置一個PDB快照庫，手動或自動建立快照，並設定快照的最大數量。
This section contains the following topics:
Parent topic:
Administering a Multitenant Environment
16.1 About PDB Snapshot Carousel
PDB快照是PDB的時間點副本。您可以使用create PLUGGABLE DATABASE(或ALTER PLUGGABLE DATABASE)的SNAPSHOT子句手動建立快照，也可以使用each interval子句自動建立快照
. A PDB snapshot carousel 快照庫.
This section contains the following topics:
Parent topic:
Administering a PDB Snapshot Carousel
16.1.1 Purpose of PDB Snapshot Carousel
PDB快照庫是維護最近的PDB副本庫以便進行時間點恢復和克隆的一種有用方法。
克隆用於開發和測試的PDBs
在一個典型的開發用例中，您克隆一個產品PDB來進行測試。當CDB處於ARCHIVELOG模式和本地撤銷模式時，源生產PDB可以以讀/寫模式開啟，並在操作過程中充分發揮功能。這種技術被稱為熱克隆。當SCN完成時，熱克隆在事務上與源PDB一致of the
ALTER PLUGGABLE DATABASE ... OPEN
statement.
例如，當名為pdb1_prod的生產PDB處於開啟狀態並正在使用時，您將建立一個名為pdb1_test_master的可重新整理克隆。然後配置pdb1_test_master來每天建立自動快照。當您需要新的PDBs進行測試時，請建立任何快照的完整克隆，然後使用
CREATE PLUGGABLE DATABASE ... SNAPSHOT COPY
.
下圖顯示了從4月5日拍攝的PDB快照建立的克隆pdb1_test_full1。圖中顯示了從pdb1_test_full1建立的三個PDBs快照副本。
Figure 16-1 Automatic Snapshots of a Refreshable Clone PDB
Description of "Figure 16-1 Automatic Snapshots of a Refreshable Clone PDB"
使用PDB快照庫進行時間點恢復
典型的策略是每天在同一時間對PDB進行快照。另一種策略是在資料載入之前手動進行快照。在這兩種情況下，PDB快照庫都允許您使用任何可用快照恢復PDB。
例如，名為pdb1_prod的銷售歷史記錄PDB每天在12:01生成一個自動快照。在4/9週一下午的daily data load中，您意外載入了錯誤的資料，損壞了PDB。您可以基於Monday 4/9快照建立一個新的生產PDB，刪除損壞的PDB，然後重試資料載入。
Figure 16-2 Restore a Production PDB Using a Snapshot
Description of "Figure 16-2 Restore a Production PDB Using a Snapshot"
See Also:
Parent topic:
About PDB Snapshot Carousel
16.1.2 How PDB Snapshot Carousel Works
The carousel 對於特定的PDB，是該PDB的一個迴圈複製庫。
資料庫根據需要或自動地在庫中建立連續的副本。當達到快照限制時，資料庫將重寫最舊的快照。
This section includes the following topics:
Parent topic:
About PDB Snapshot Carousel
16.1.2.1 Contents of a PDB Snapshot
PDB快照的資料檔案駐留在檔案系統上的歸檔檔案中。
不包括存檔的重做日誌檔案。通常，歸檔檔案儲存在與PDB的資料檔案相同的目錄中。對於系統生成的快照名稱，snap_以惟一識別符號為字首，該識別符號包含快照SCN。該歸檔檔案的副檔名是.pdb。
資料庫使用隱式的using snapshot子句獲取快照。在使用建立可插入資料庫快照獲取快照時，不能指定快照複製子句。
Note:
使用USING snapshot子句建立的PDB快照和使用snapshot copy子句建立的快照複製PDB是兩種不同型別的快照，它們不應該混淆。USING SNAPSHOT子句建立一個不需要物化的完整PDB。快照複製子句建立了一個稀疏的PDB，如果您想刪除它所基於的PDB快照，就必須將其物化。
雖然PDB快照carousel中的快照始終是完整的PDB，但是您可以在carousel中獲取快照的快照副本。在這種情況下，快照副本是一個稀疏克隆。
See Also:
"
How a Snapshot Copy PDB Differs from a Standard Clone
"
Parent topic:
How PDB Snapshot Carousel Works
16.1.2.2 Contents of a PDB Snapshot Carousel
PDB快照庫是一個PDB的所有現有快照的集合。
max_pdb_snapshot屬性指定庫中允許的最大快照數量。當前設定在CDB_PROPERTIES檢視中是可見的。
下圖顯示了cdb1_pdb1的庫。在本例中，資料庫每24小時自動獲取一個快照，維護一組8個快照。建立了前8個快照之後，每個新快照都會替換舊快照。例如，星期二4/10快照替換星期一4/2快照;星期三4/11快照取代星期二4/3快照;等等。
Figure 16-3 PDB Snapshot Carousel
Description of "Figure 16-3 PDB Snapshot Carousel"
Parent topic:
How PDB Snapshot Carousel Works
16.1.3 User Interface for PDB Snapshot Carousel
The
SNAPSHOT MODE
clause controls creation of snapshots, and determines whether creation is manual, automatic, or disabled.
CREATE PLUGGABLE DATABASE Statement
要為PDB設定快照模式，請在快照模式中使用以下值之一 clause of
ALTER PLUGGABLE DATABASE
or
CREATE PLUGGABLE DATABASE
:
-
MANUAL
This clause, which is the default, enables the creation of manual snapshots of the PDB. To create a snapshot on demand, specify the
SNAPSHOT snapshot_name
clause in an
ALTER PLUGGABLE DATABASE
or
CREATE PLUGGABLE DATABASE
statement.
-
EVERY snapshot_interval [MINUTES|HOURS]
This clause enables the automatic creation of snapshots after an interval of time. The following restrictions apply to the interval specified:
The database assigns each automatic snapshot a system-generated name. Note that manual snapshots are also supported for the PDB when
EVERY
is specified.
-
NONE
This clause disables snapshot creation for the PDB.
See Also:
MAX_PDB_SNAPSHOTS Database Property
要設定PDB的最大快照數量，請在ALTER PLUGGABLE DATABASE或CREATE PLUGGABLE DATABASE中指定max_pdb_snapshot屬性。屬性的預設值是8，這也是最大值。當建立了允許的最大快照數量時，資料庫將清除最舊的快照。CDB_PROPERTIES檢視顯示了max_pdb_snapshot的設定。
See Also:
Oracle Database SQL Language Reference
for the syntax of the
ALTER PLUGGABLE DATABASE
statement
DBA_PDB_SNAPSHOTS and DBA_PDBS
The following data dictionary views provide snapshot information:
-
The
DBA_PDB_SNAPSHOTS
view metadata about the snapshots, including name, creation SCN, creation time, and file name.
-
The
DBA_PDBS
view has a
SNAPSHOT_MODE
and
SNAPSHOT_INTERVAL
column.
See Also:
Oracle Database Reference to learn about
DBA_PDB_SNAPSHOTS
and
DBA_PDBS
Parent topic:
About PDB Snapshot Carousel
16.2 Setting the Maximum Number of Snapshots in a PDB Snapshot Carousel
You can set the maximum number of PDB snapshots for a PDB.
max_pdb_snapshot資料庫屬性設定PDB快照旋轉木馬中每個PDB的最大快照數量。預設最大值是8。不能將屬性設定為大於8的數字。
Prerequisites
The PDB must be open in read/write mode.
To set the maximum number of PDB snapshots for a PDB:
-
In SQL*Plus, ensure that the current container is the PDB for which you want to set the limit.
-
Optionally, query
CDB_PROPERTIES
for the current setting of the
SET MAX_PDB_SNAPSHOTS
property.
-
Run an
ALTER PLUGGABLE DATABASE
or
ALTER DATABASE
statement with the
SET MAX_PDB_SNAPSHOTS
clause.
Example 16-1 Setting the Maximum Number of PDB Snapshots for a PDB
The following query shows the maximum in the carousel for
cdb1_pdb1
(sample output included):
SET LINESIZE 150
COL CON_ID FORMAT 99999
COL PROPERTY_NAME FORMAT a17
COL PDB_NAME FORMAT a9
COL VALUE FORMAT a3
COL DESCRIPTION FORMAT a43
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%'
ORDER BY PROPERTY_NAME;
CON_ID PDB_NAME PROPERTY_NAME VAL DESCRIPTION
------ --------- ----------------- --- -------------------------------------------
3 CDB1_PDB1 MAX_PDB_SNAPSHOTS 8 maximum number of snapshots for a given PDB
The following SQL statement sets the maximum number of PDB snapshots for the current PDB to 7:
ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=7;
Example 16-2 Dropping All Snapshots in a PDB Snapshot Carousel
To drop all snapshots in a PDB snapshot carousel, set the
MAX_PDB_SNAPSHOTS
database property to
(zero), as shown in the following statement:
ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=0;
This technique is faster than executing
ALTER PLUGGABLE DATABASE ... DROP SNAPSHOTsnapshot_name
for every snapshot.
See Also:
"
About Container Access in a CDB
"
Parent topic:
Administering a PDB Snapshot Carousel
16.3 Configuring Automatic PDB Snapshots
在建立或更改PDB時，透過使用快照模式EVERY子句為自動快照配置PDB。
預設情況下，PDB是為手動快照配置的。
Prerequisites
Note the following prerequisites for the
ALTER PLUGGABLE DATABASE SNAPSHOT
statement:
To configure automatic snapshots when altering a PDB:
-
In SQL*Plus, log in as an administrator to the PDB whose snapshot mode you intend to configure.
-
Optionally, query
DBA_PDBS
to determine the current snapshot mode.
-
Run
ALTER PLUGGABLE DATABASE
with the
SNAPSHOT MODE EVERY interval
clause, specifying either
MINUTES
or
HOURS
.
To configure automatic snapshots when creating a PDB:
-
In SQL*Plus, log in as an administrator to the CDB root or application root.
-
Optionally, query
DBA_PDBS
to determine the current snapshot mode.
-
Run
CREATE PLUGGABLE DATABASE
with the
SNAPSHOT MODE EVERY interval
clause, specifying either
MINUTES
or
HOURS
.
Example 16-3
為現有PDB每天配置一個自動快照
本例假設您已登入到要更改其快照模式的PDB。查詢資料字典，確認PDB目前處於手動模式(包括示例輸出):
SELECT SNAPSHOT_MODE "S_MODE", SNAPSHOT_INTERVAL/60 "SNAP_INT_HRS" FROM DBA_PDBS;
S_MODE SNAP_INT_HRS
------ ------------
MANUAL
Change the snapshot mode to every 24 hours:
ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 24 HOURS;
Confirm the change to automatic mode:
SELECT SNAPSHOT_MODE "S_MODE", SNAPSHOT_INTERVAL/60 "SNAP_INT_HRS" FROM DBA_PDBS;S_MODE SNAP_INT_HRS
------ ------------
AUTO 24
Example 16-4 Creating a PDB That Takes Snapshots Every Two Hours
This example assumes that you are logged in to the CDB root. The following statement creates
cdb1_pdb3
from an existing PDB named
cdb1_pdb1
, and configures it to take snapshots automatically every 2 hours:
CREATE PLUGGABLE DATABASE cdb1_pdb3 FROM cdb1_pdb1FILE_NAME_CONVERT=('cdb1_pdb1','cdb1_pdb3')SNAPSHOT MODE EVERY 120 MINUTES;
See Also:
Parent topic:
Administering a PDB Snapshot Carousel
16.4 Creating PDB Snapshots Manually
To create a PDB snapshot manually, specify the
SNAPSHOT snapshot_name
clause in
ALTER PLUGGABLE DATABASE
or
CREATE PLUGGABLE DATABASE
.
Prerequisites
Note the following prerequisites for the
ALTER PLUGGABLE DATABASE SNAPSHOT
statement:
To create a PDB snapshot:
-
In SQL*Plus, log in as an administrator to the PDB whose snapshot you intend to create.
-
Optionally, query
DBA_PDBS
to confirm that the snapshot mode is not set to
NONE
.
-
Run an
ALTER PLUGGABLE DATABASE
statement with the
SNAPSHOT
clause.
Example 16-5 Creating a Snapshot with a User-Specified Name
以下SQL語句建立兩個cdb1_pdb1的PDB快照，一個在週三資料載入之前，一個在週三資料載入之後:
ALTER PLUGGABLE DATABASE SNAPSHOT cdb1_pdb1_b4wedload;-- data loadALTER PLUGGABLE DATABASE SNAPSHOT cdb1_pdb1_afwedload;
The following query of
DBA_PDB_SNAPSHOTS
shows the locations of two snapshots of the PDB named
cdb1_pdb1
(sample output included):
SET LINESIZE 150
COL CON_NAME FORMAT a10
COL SNAPSHOT_NAME FORMAT a20
COL SNAP_SCN FORMAT 9999999
COL FULL_SNAPSHOT_PATH FORMAT a45
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 CDB1_PDB1 CDB1_PDB1_B4WEDLOAD 2962078 /disk1/oracle/dbs/snap_3489077498_2962078.pdb
3 CDB1_PDB1 CDB1_PDB1_AFWEDLOAD 2962938 /disk1/oracle/dbs/snap_3489077498_2962938.pdb
If you do not specify a PDB snapshot name, then the database generates a unique name.
Example 16-6 建立具有系統指定名稱的快照
下面的SQL語句建立一個快照，但沒有指定名稱:
ALTER PLUGGABLE DATABASE SNAPSHOT;
The following sample query shows that the database assigned the snapshot a name prefixed with
SNAP_
:
SET LINESIZE 150
COL CON_NAME FORMAT a10
COL SNAPSHOT_NAME FORMAT a25
COL SNAP_SCN FORMAT 9999999
COL FULL_SNAPSHOT_PATH FORMAT a45
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 CDB1_PDB1 CDB1_PDB1_B4WEDLOAD 2962078 /disk1/oracle/dbs/snap_3489077498_2962078.pdb
3 CDB1_PDB1 CDB1_PDB1_AFWEDLOAD 2962938 /disk1/oracle/dbs/snap_3489077498_2962938.pdb
3 CDB1_PDB1 SNAP_3489077498_960130367 2993525 /disk1/oracle/dbs/snap_3489077498_2993525.pdb
See Also:
Parent topic:
Administering a PDB Snapshot Carousel
16.5 Dropping a PDB Snapshot
You can drop a PDB snapshot by running an
ALTER PLUGGABLE DATABASE
statement with the
DROP SNAPSHOT
clause.
To drop all PDB snapshots based on a PDB, set the
MAX_PDB_SNAPSHOTS
property in the PDB to
(zero).
To drop a PDB snapshot:
-
In SQL*Plus, ensure that the current container is the PDB from which you created the PDB snapshot.
-
Run an
ALTER PLUGGABLE DATABASE
statement with the
DROP SNAPSHOT
clause.
Example 16-7 Dropping a PDB Snapshot
The following SQL statement drops a PDB snapshot named
sales_snap
:
ALTER PLUGGABLE DATABASE DROP SNAPSHOT sales_snap;
See Also:
"
About Container Access in a CDB
"
Parent topic:
Administering a PDB Snapshot Carousel
ORACLE 18C新特性-PDB快照輪播（SNAPSHOT CAROUSEL）
版權宣告：本文為Buddy Yuan原創文章，未經允許不得轉載。原文地址：
Oracle 18c推出的一個新功能就是PDB快照輪播，最多可以建立8個pdb的快照，而這8個構成的一組快照就被稱作快照輪播。當達到第8個快照限制時，新的快照就會覆蓋最舊的快照，就像redo log一樣。PDB的快照主要作用是當出現了問題可以閃回到快照的時間點，另一個功能是基於快照來查詢歷史資料。
SQL> SELECT r.CON_ID, p.PDB_NAME, PROPERTY_NAME,
2 PROPERTY_VALUE AS value, DESCRIPTION
3 FROM CDB_PROPERTIES r, CDB_PDBS p
4 WHERE r.CON_ID = p.CON_ID
5 AND PROPERTY_NAME LIKE 'MAX_PDB%'
6 AND description like 'maximum%'
7 ORDER BY PROPERTY_NAME;
CON_ID PDB_NAME PROPERTY_NAME VALUE DESCRIPTION
---------- -------------------- -------------------- -------------------- ----------------------------------------------------------------------
3 ORCLPDB1 MAX_PDB_SNAPSHOTS 8 maximum number of snapshots for a given PDB
預設情況下是手動建立快照，我們可以把它改成自動建立。
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.
手動建立快照，可以自己指定名字，也可以使用系統自動生成的名字。透過檢視DBA_PDB_SNAPSHOTS檢視，可以找到快照存放的路徑及建立快照的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引數，當max_pdb_snapshots引數設定成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
演示了上述功能之後，還有一個功能，就是我們可以根據快照建立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.
這樣我們就根據快照建立了一個新的PDB，假設當前PDB出現了資料覆蓋，我們就可以使用這個歷史快照的PDB來恢復資料。
當然還有一個很好用的功能就是我們在建立pdb的過程中把他設定成15分鐘產生一個快照。當然配置這個功能有一個先決條件：CDB必須處於本地undo模式.
SQL> create pluggable database ORCLPDB3 from ORCLPDB1 file_name_convert=('ORCLPDB1','ORCLPDB3') snapshot mode every 5 minutes;
Pluggable database created.
可以看到每隔5分鐘自動建立一個快照
[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
我們可以做個實驗測試一下。在生成快照之前建立一個表插入點資料，這裡上一次快照是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分產生了新的快照，此時我們使用新的快照做一個克隆，登陸到ORCLPDB4資料庫後，我們會發現是有資料的。
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
這個功能對於測試人員非常有用，例如移動電信現在有BCV環境，專門用於經分抽數及測試，而這個BCV環境是底層同步的一個克隆。使用18c資料庫，我們就可以直接建立快照，然後使用快照克隆一個新資料庫形成BCV環境給經分抽數及測試使用了。
