Oracle 18c新特性-PDB快照轮播(Snapshot Carousel)
Oracle18c推出的一个新功能就是PDB快照轮播,最多可以创建8个pdb的快照,而这8个构成的一组快照就被称作快照轮播。当达到第8个快照限制时,新的快照就会覆盖最旧的快照,就像redolog一样。PDB的快照主要作用是当出现了问题可以闪回到快照的时间点,另一个功能是基于快照来查询历史数据。
SQL>SELECTr.CON_ID,p.PDB_NAME,PROPERTY_NAME, PROPERTY_VALUEASvalue,DESCRIPTION FROMCDB_PROPERTIESr,CDB_PDBSp WHEREr.CON_ID=p.CON_ID ANDPROPERTY_NAMELIKE'MAX_PDB%' ANDdescriptionlike'maximum%' ORDERBYPROPERTY_NAME; CON_IDPDB_NAMEPROPERTY_NAMEVALUEDESCRIPTION -------------------------------------------------------------------------------------------------------------------------------------------- 3ORCLPDB1MAX_PDB_SNAPSHOTS maximumnumberofsnapshotsforagivenPDB
默认情况下是手动创建快照,我们可以把它改成自动创建。
SQL>selectsnapshot_mode,snapshot_interval/60fromdba_pdbs; SNAPSHSNAPSHOT_INTERVAL/60 -------------------------- MANUAL SQL>alterpluggabledatabasesnapshotmodeevery4hours; Pluggabledatabasealtered. SQL>selectsnapshot_mode,snapshot_interval/60fromdba_pdbs; SNAPSHSNAPSHOT_INTERVAL/60 -------------------------- AUTO 4
要返回手动模式,输入modemanual即可。
SQL>alterpluggabledatabasesnapshotmodemanual; Pluggabledatabasealtered. SQL>alterpluggabledatabasesnapshot; Pluggabledatabasealtered.
手动创建快照,可以自己指定名字,也可以使用系统自动生成的名字。通过查看DBA_PDB_SNAPSHOTS视图,可以找到快照存放的路径及创建快照的SCN号。
SQL>SELECTCON_ID,CON_NAME,SNAPSHOT_NAME,SNAPSHOT_SCNASsnap_scn,FULL_SNAPSHOT_PATHFROMDBA_PDB_SNAPSHOTSORDERBYSNAP_SCN; CON_IDCON_NAMESNAPSHOT_NAMESNAP_SCNFULL_SNAPSHOT_PATH -------------------------------------------------------------------------------------------------------------------------------------------- 3ORCLPDB1SNAP_2953839490_9890146672821702/opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2821702.pdb 3ORCLPDB1PDB_SNAP2823303/opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2823303.pdb 3ORCLPDB1SNAP_2953839490_9890159702823679/opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2823679.pdb [oracle@oracle-18c-vagrantORCLPDB1]$ls-lsh total1.3G 4.0Kdrwxr-x---.8oracleoinstall4.0KOct822:39ORCLCDB 171M-rw-r--r--.1oracleoinstall171MOct822:18snap_2953839490_2821702.pdb 171M-rw-r--r--.1oracleoinstall171MOct822:38snap_2953839490_2823303.pdb 171M-rw-r--r--.1oracleoinstall171MOct822:40snap_2953839490_2823679.pdb 371M-rw-r-----.1oracleoinstall371MOct822:39sysaux01.dbf 271M-rw-r-----.1oracleoinstall271MOct822:39system01.dbf 56K-rw-r-----.1oracleoinstall63MOct123:21temp01.dbf 101M-rw-r-----.1oracleoinstall101MOct822:39undotbs01.dbf 5.1M-rw-r-----.1oracleoinstall5.1MOct822:39users01.dbf
如果要删除快照,则有两种方法,第一种就是使用alterpluggabledatabase删除,第二种是修改max_pdb_snapshots参数,当max_pdb_snapshots参数设置成0时,将删除全部快照。
SQL>alterpluggabledatabasedropsnapshotSNAP_2953839490_989015970; Pluggabledatabasealtered. SQL>SELECTCON_ID,CON_NAME,SNAPSHOT_NAME,SNAPSHOT_SCNASsnap_scn,SNAPSHOT_TIME,FULL_SNAPSHOT_PATHFROMDBA_PDB_SNAPSHOTSORDERBYSNAP_SCN; CON_IDCON_NAMESNAPSHOT_NAMESNAP_SCNSNAPSHOT_TIMEFULL_SNAPSHOT_PATH --------------------------------------------------------------------------------------------------------------------------------------------------------- 3ORCLPDB1SNAP_2953839490_98901466728217021539008272/opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2821702.pdb 3ORCLPDB1PDB_SNAP28233031539009503/opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2823303.pdb SQL>alterpluggabledatabasesetmax_pdb_snapshots=0; Pluggabledatabasealtered. SQL>SELECTCON_ID,CON_NAME,SNAPSHOT_NAME,SNAPSHOT_SCNASsnap_scn,SNAPSHOT_TIME,FULL_SNAPSHOT_PATHFROMDBA_PDB_SNAPSHOTSORDERBYSNAP_SCN; norowsselected
演示了上述功能之后,还有一个功能,就是我们可以根据快照创建pdb。
SQL>alterpluggabledatabasesetmax_pdb_snapshots=8; Pluggabledatabasealtered SQL>alterpluggabledatabasesnapshotpdb_snap; Pluggabledatabasealtered. SQL>SELECTCON_ID,CON_NAME,SNAPSHOT_NAME,SNAPSHOT_SCNASsnap_scn,SNAPSHOT_TIME,FULL_SNAPSHOT_PATHFROMDBA_PDB_SNAPSHOTSORDERBYSNAP_SCN; CON_IDCON_NAMESNAPSHOT_NAMESNAP_SCNSNAPSHOT_TIMEFULL_SNAPSHOT_PATH --------------------------------------------------------------------------------------------------------------------------------------------------------- 3ORCLPDB1PDB_SNAP28252831539010328/opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2825283.pdb SQL>! [oracle@oracle-18c-vagrant~]$mkdir-p/opt/oracle/oradata/ORCLCDB/ORCLPDB2 SQL>createpluggabledatabaseORCLPDB2fromORCLPDB1usingsnapshotPDB_SNAPcreate_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2'; createpluggabledatabaseORCLPDB2fromORCLPDB1usingsnapshotPDB_SNAPcreate_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2' * ERRORatline1: ORA-65040:operationnotallowedfromwithinapluggabledatabase SQL>altersessionsetcontainer=CDB$ROOT; Sessionaltered. SQL>createpluggabledatabaseORCLPDB2fromORCLPDB1usingsnapshotPDB_SNAPcreate_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2'; Pluggabledatabasecreated. SQL>showpdbs; CON_IDCON_NAMEOPENMODERESTRICTED ------------------------------------------------------------ 2PDB$SEEDREADONLYNO 3ORCLPDB1READWRITENO 5ORCLPDB2MOUNTED SQL>alterpluggabledatabaseORCLPDB2open; Pluggabledatabasealtered.
这样我们就根据快照创建了一个新的PDB,假设当前PDB出现了数据覆盖,我们就可以使用这个历史快照的PDB来恢复数据。
当然还有一个很好用的功能就是我们在创建pdb的过程中把他设置成15分钟产生一个快照。当然配置这个功能有一个先决条件:CDB必须处于本地undo模式.
SQL>createpluggabledatabaseORCLPDB3fromORCLPDB1file_name_convert=('ORCLPDB1','ORCLPDB3')snapshotmodeevery5minutes; Pluggabledatabasecreated.
可以看到每隔5分钟自动创建一个快照
[oracle@oracle-18c-vagrantORCLPDB3]$ls-lrtsnap* -rw-r--r--.1oracleoinstall179134736Oct823:13snap_2091710291_2830531.pdb -rw-r--r--.1oracleoinstall179167158Oct823:18snap_2091710291_2832545.pdb -rw-r--r--.1oracleoinstall179285979Oct823:23snap_2091710291_2833104.pdb -rw-r--r--.1oracleoinstall179284365Oct823:28snap_2091710291_2833687.pdb -rw-r--r--.1oracleoinstall179302205Oct823:33snap_2091710291_2834965.pdb -rw-r--r--.1oracleoinstall179315484Oct823:38snap_2091710291_2836695.pdb -rw-r--r--.1oracleoinstall115724288Oct823:43snap_2091710291_2837222.pdb
我们可以做个实验测试一下。在生成快照之前创建一个表插入点数据,这里上一次快照是23:43分。
SQL>altersessionsetcontainer=ORCLPDB3; Sessionaltered. SQL>createtablea1asselect*fromdba_objects; Tablecreated. SQL>selectcount(1)froma1; COUNT(1) ---------- 72897 [oracle@oracle-18c-vagrantORCLPDB3]$ls-lrtsnap* -rw-r--r--.1oracleoinstall179134736Oct823:13snap_2091710291_2830531.pdb -rw-r--r--.1oracleoinstall179167158Oct823:18snap_2091710291_2832545.pdb -rw-r--r--.1oracleoinstall179285979Oct823:23snap_2091710291_2833104.pdb -rw-r--r--.1oracleoinstall179284365Oct823:28snap_2091710291_2833687.pdb -rw-r--r--.1oracleoinstall179302205Oct823:33snap_2091710291_2834965.pdb -rw-r--r--.1oracleoinstall179315484Oct823:38snap_2091710291_2836695.pdb -rw-r--r--.1oracleoinstall179353508Oct823:43snap_2091710291_2837222.pdb -rw-r--r--.1oracleoinstall5767168Oct823:48snap_2091710291_2839217.pdb SQL>SELECTCON_ID,CON_NAME,SNAPSHOT_NAME,SNAPSHOT_SCNASsnap_scn,SNAPSHOT_TIME,FULL_SNAPSHOT_PATHFROMDBA_PDB_SNAPSHOTSORDERBYSNAP_SCN; CON_IDCON_NAMESNAPSHOT_NAMESNAP_SCNSNAPSHOT_TIMEFULL_SNAPSHOT_PATH --------------------------------------------------------------------------------------------------------------------------------------------------------- 7ORCLPDB3SNAP_2091710291_98901798428305311539011592/opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2830531.pdb 7ORCLPDB3SNAP_2091710291_98901828428325451539011887/opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2832545.pdb 7ORCLPDB3SNAP_2091710291_98901858428331041539012188/opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2833104.pdb 7ORCLPDB3SNAP_2091710291_98901888428336871539012489/opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2833687.pdb 7ORCLPDB3SNAP_2091710291_98901918428349651539012787/opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2834965.pdb 7ORCLPDB3SNAP_2091710291_98901948428366951539013087/opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2836695.pdb 7ORCLPDB3SNAP_2091710291_98901978428372221539013386/opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2837222.pdb 7ORCLPDB3SNAP_2091710291_98902008428392171539013686/opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2839217.pdb
23点48分产生了新的快照,此时我们使用新的快照做一个克隆,登陆到ORCLPDB4数据库后,我们会发现是有数据的。
SQL>altersessionsetcontainer=CDB$ROOT; Sessionaltered. SQL>createpluggabledatabaseORCLPDB4fromORCLPDB3usingsnapshotSNAP_2091710291_989020084create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB4'; Pluggabledatabasecreated SQL>alterpluggabledatabaseORCLPDB4open; Pluggabledatabasealtered. SQL>altersessionsetcontainer=ORCLPDB4; Sessionaltered. SQL>selectcount(1)froma1; COUNT(1) ---------- 72897
这个功能对于测试人员非常有用,例如移动电信现在有BCV环境,专门用于经分抽数及测试,而这个BCV环境是底层同步的一个克隆。使用18c数据库,我们就可以直接创建快照,然后使用快照克隆一个新数据库形成BCV环境给经分抽数及测试使用了。
总结
以上所述是小编给大家介绍的Oracle18c新特性-PDB快照轮播(SnapshotCarousel),希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持!