共计 9058 个字符，预计需要花费 23 分钟才能阅读完成。
这篇文章给大家分享的是有关 Oracle 18c 新特性 -PDB 快照轮播的示例分析的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。
Oracle 18c 推出的一个新功能就是 PDB 快照轮播，最多可以创建 8 个 pdb 的快照，而这 8 个构成的一组快照就被称作快照轮播。当达到第 8 个快照限制时，新的快照就会覆盖最旧的快照，就像 redo log 一样。PDB 的快照主要作用是当出现了问题可以闪回到快照的时间点，另一个功能是基于快照来查询历史数据。
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默认情况下是手动创建快照，我们可以把它改成自动创建。
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.pdb23 点 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 环境给经分抽数及测试使用了。
感谢各位的阅读！关于“Oracle 18c 新特性 -PDB 快照轮播的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
正文完
