Oracle 18c新特性-PDB快照轮播(Snapshot Carousel)
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.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),希望对大家有所帮助
上一篇: rabbitmq安装(windows)
下一篇: 关于expdp任务异常的处理案例详析