欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

Oracle 18c新特性-PDB快照轮播(Snapshot Carousel)

程序员文章站 2024-02-03 11:08:34
oracle 18c推出的一个新功能就是pdb快照轮播，最多可以创建8个pdb的快照，而这8个构成的一组快照就被称作快照轮播。当达到第8个快照限制时，新的快照就会覆盖最旧的...

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），希望对大家有所帮助

上一篇： 栈与队列链式存储结构一货物上架问题

下一篇： C++雾中风景番外篇3：GDB与Valgrind ，调试代码内存的工具

推荐阅读