Oracle 18c新特性-PDB快照轮播(Snapshot Carousel)
数据库  /  管理员 发布于 6年前   330
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 -lshtotal 1.3G4.0K
drwxr-x---. 8 oracle oinstall
4.0K Oct 8 22:39 ORCLCDB171M -rw-r--r--. 1 oracle oinstall
171M Oct 8 22:18 snap_2953839490_2821702.pdb171M -rw-r--r--. 1 oracle oinstall
171M Oct 8 22:38 snap_2953839490_2823303.pdb171M -rw-r--r--. 1 oracle oinstall
171M Oct 8 22:40 snap_2953839490_2823679.pdb371M -rw-r-----. 1 oracle oinstall
371M Oct 8 22:39 sysaux01.dbf271M -rw-r-----. 1 oracle oinstall
271M Oct 8 22:39 system01.dbf 56K -rw-r-----. 1 oracle oinstall
63M Oct 1 23:21 temp01.dbf101M -rw-r-----. 1 oracle oinstall
101M Oct 8 22:39 undotbs01.dbf5.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/ORCLPDB2SQL> 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_NAMEOPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEEDREAD ONLY NO
3 ORCLPDB1READ WRITE NO
5 ORCLPDB2MOUNTED
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环境给经分抽数及测试使用了。
总结
以上所述是小编给大家介绍的Oracle 18c新特性-PDB快照轮播(Snapshot Carousel),希望对大家有所帮助.
122 在
学历:一种延缓就业设计,生活需求下的权衡之选中评论 工作几年后,报名考研了,到现在还没认真学习备考,迷茫中。作为一名北漂互联网打工人..123 在
Clash for Windows作者删库跑路了,github已404中评论 按理说只要你在国内,所有的流量进出都在监控范围内,不管你怎么隐藏也没用,想搞你分..原梓番博客 在
在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了,今天刚看,已经添加。..博主 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了,可以看看近期评论的其他文章..1111 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开,博主百忙中能否发个APP下载链接,佛跳墙或极光..
Copyright·© 2019 侯体宗版权所有·
粤ICP备20027696号