PDB Snapshot Carousel 提供了 PDB 手动或自动生成快照，为开发测试用例提供了便捷。
CDB 为 local undo 和归档模式下，指定 max_pdb_snapshots 控制 PDB 最多生成的快照数量，当快照超过最大数量时，会覆盖最旧的快照。

pdb1_prod 为生产 PDB，然后创建 refres PDB pdb1_test_master，让其每天生成快照，当需要测试用例时，可以指定 snapshot 创建 PDB。

数据库按需或自动创建 carousel 中的连续副本。当达到快照限制时（最多8个），数据库将覆盖旧的快照，类似于 Linux 中日志轮转一样。
PDB 快照的文件存放在文件系统上的物理文件中，其不包含数据库归档文件 archive log，默认存储在 PDB 的数据文件相同的目录中。
系统自动生成的 snapshot 以 snap_ 名称开始，唯一标识结尾的命名规则，包含了快照当前的 SCN，并且扩展名为 .PDB

设置 PDB 快照的最大数量

使用 max_pdb_snapshots 参数设置 PDB 快照最大数量。
通过视图 cdb_properties 查询当前 max_pdb_snapshots 值，可以使用 alter pluggable database 更改 max_pdb_snapshots 值。

1
2
3
4
5
6
SQL> set linesize 1500
SQL> col con_id format 99999
SQL> col propery_name format a17
SQL> col pdb_name format a9
SQL> col value format a12
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%' order by property_name;

默认快照最大数量为 8。

将当前 PDB 的快照数量设置为 7 ：

1
2
SQL> alter session set container=PDB;
SQL> alter pluggable database set max_pdb_snapshots=7;

删除所有的快照：

1
SQL> alter pluggable database set max_pdb_snapshots=0;

配置自动生成 PDB 快照

默认都是手动生成快照，可以使用 snapshot mode every 子句自动产生 PDB 快照。

1
2
3
4
5
SQL> select snapshot_mode "s_mode" , snapshot_interval/60 "snap_int_hrs" FROM dba_pdbs;

s_mode snap_int_hrs
------ ------------
MANUAL

进入 PDB，更改自动创建模式为每 24 小时自动生成 PDB 快照：

1
2
3
4
5
6
7
SQL> alter session set container=PDB;
SQL> alter pluggable database snapshot mode every 24 hours;
alter pluggable database snapshot mode every 24 hours
*
ERROR at line 1:
ORA-12754: Feature PDB SNAPSHOT CAROUSEL is disabled due to missing capability
.

报错解决方法：

1
2
3
4
5
6
7
8
SQL> alter session set container=cdb$root;
SQL> alter system set "_exadata_feature_on"=true scope=spfile;
SQL> shutdown immediate;
SQL> startup

SQL> alter pluggable database PDB open;
SQL> alter session set container=PDB;
SQL> alter pluggable database snapshot mode every 24 hours;

确认自动生成快照的时间间隔：

1
2
3
4
5
SQL> select snapshot_mode "s_mode", snapshot_interval/60 "snap_int_hrs" from dba_pdbs;

s_mode snap_int_hrs
------ ------------
AUTO             24

登录 CDB，从 PDB 创建 PDB2 并配置为每 2 小时自动创建快照：

1
2
3
4
conn / as sysdba;
SQL> create pluggable database PDB2 from PDB file_name_convert=('PDB','PDB2') snapshot mode every 120 minutes;
SQL> alter pluggable database PDB2 open;
SQL> alter pluggable database PDB2 save state;

查看创建的快照：

1
SQL> select p.con_id, p.pdb_name,p.snapshot_mode,p.snapshot_interval from cdb_pdbs p order by 1;

更改快照设置：

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> alter session set container=PDB2;
SQL> alter pluggable database PDB2 snapshot mode every 30 minutes;
SQL> select p.con_id, p.pdb_name,p.snapshot_mode,p.snapshot_interval from cdb_pdbs p order by 1;

    CON_ID
----------
PDB_NAME
-------------------------------------------------------------------
SNAPSH SNAPSHOT_INTERVAL
------ -----------------
         4
PDB2
AUTO                  30

更改为小时：

1
2
3
4
5
6
7
8
9
10
11
12
SQL>  alter pluggable database PDB2 snapshot mode every 1 hours;
SQL> select p.con_id, p.pdb_name,p.snapshot_mode,p.snapshot_interval from cdb_pdbs p order by 1;

    CON_ID
----------
PDB_NAME
----------------------------------------------------------------------------
SNAPSH SNAPSHOT_INTERVAL
------ -----------------
         4
PDB2
AUTO                  60

将快照模式设置为手动：

1
2
3
4
5
6
7
8
9
10
11
12
SQL> alter pluggable database snapshot mode manual;
SQL> select p.con_id, p.pdb_name,p.snapshot_mode,p.snapshot_interval from cdb_pdbs p order by 1;

    CON_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
SNAPSH SNAPSHOT_INTERVAL
------ -----------------
         4
PDB2
MANUAL

关闭快照：

1
2
3
4
5
6
7
8
9
10
11
12
SQL> alter pluggable database snapshot mode none;
SQL> select p.con_id, p.pdb_name,p.snapshot_mode,p.snapshot_interval from cdb_pdbs p order by 1;

    CON_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
SNAPSH SNAPSHOT_INTERVAL
------ -----------------
         4
PDB2
NONE

手动创建快照

显示快照信息：

1
2
3
SQL> select con_id,con_name,snapshot_name,snapshot_scn,full_snapshot_path FROM cdb_pdb_snapshots order by con_id,snapshot_scn;

no rows selected

没有任何信息，现在生成快照，一个系统，一个自定义快照：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> show con_name;

CON_NAME
------------------------------
PDB2

SQL> show user;
USER is "SYS"

SQL> alter pluggable database snapshot;
alter pluggable database snapshot
*
ERROR at line 1:
ORA-65363: pluggable database PDB2 not enabled for snapshots

SQL> alter pluggable database snapshot test_snapshot;
alter pluggable database snapshot test_snapshot
*
ERROR at line 1:
ORA-65363: pluggable database PDB2 not enabled for snapshots

CDB 必须时 LOCAL UNDO 模式，用户必须有权限创建和删除 PDB。
如果不指定快照名，系统自动生成唯一名称的快照：

1
2
3
4
5
6
SQL> alter pluggable database snapshot mode manual;
SQL> alter pluggable database snapshot test_snapshot;
alter pluggable database snapshot test_snapshot
*
ERROR at line 1:
ORA-64762: cannot create snapshot

此时失败的原因是快照最大数设置成了 0：

1
2
3
4
5
[oracle@oel7-host1 ~]$ oerr ORA 64762
64762, 0000, "cannot create snapshot"
// *Cause:    An attempt was made to create a snapshot while 'max_pdb_snapshots'
//            was set to 0.
// *Action:   max_pdb_snapshots should be set greater than 0 to take pdb snapshots.

修改设置并创建快照：

1
2
3
4
5
SQL> alter session set container=PDB2;
SQL> alter pluggable database set max_pdb_snapshots=8;

SQL> alter pluggable database snapshot;
SQL> alter pluggable database snapshot test_snapshot;

查看生成的快照：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL> col snapshot_name format a30
SQL> col full_snapshot_path format a80
SQL> select con_id,con_name,snapshot_name,snapshot_scn,full_snapshot_path FROM cdb_pdb_snapshots order by con_id,snapshot_scn;

    CON_ID
----------
CON_NAME
--------------------------------------------------------------------------------
SNAPSHOT_NAME                  SNAPSHOT_SCN
------------------------------ ------------
FULL_SNAPSHOT_PATH
--------------------------------------------------------------------------------
         4
PDB2
SNAP_3318643222_1190547411          2433997
/u01/app/oracle/oradata/ORCL/PDB2/snap_3318643222_2433997.pdb

    CON_ID
----------
CON_NAME
--------------------------------------------------------------------------------
SNAPSHOT_NAME                  SNAPSHOT_SCN
------------------------------ ------------
FULL_SNAPSHOT_PATH
--------------------------------------------------------------------------------
         4
PDB2
TEST_SNAPSHOT                       2434524
/u01/app/oracle/oradata/ORCL/PDB2/snap_3318643222_2434524.pdb

删除快照

可以将 max_pdb_snapshots 设置为 0 ，即删除所有 PDB 快照，同时也关闭快照功能。
也可以使用 alter pluggable database 删除当前的快照：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> alter pluggable database drop snapshot SNAP_3318643222_1190547411;

SQL> select con_id,con_name,snapshot_name,snapshot_scn,full_snapshot_path FROM cdb_pdb_snapshots order by con_id,snapshot_scn;

    CON_ID
----------
CON_NAME
--------------------------------------------------------------------------------
SNAPSHOT_NAME                  SNAPSHOT_SCN
------------------------------ ------------
FULL_SNAPSHOT_PATH
--------------------------------------------------------------------------------
         4
PDB2
TEST_SNAPSHOT                       2434524
/u01/app/oracle/oradata/ORCL/PDB2/snap_3318643222_2434524.pdb

从 snapshot 创建 PDB

首先需要在系统中创建 PDB 目录：

1
[oracle@oel7-host1 ~]$ mkdir /u01/app/oracle/oradata/ORCL/PDB_snap_test

基于快照创建 PDB：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/ORCL/PDB_snap_test';

SQL> create pluggable database PDB_snap_test from PDB2 using snapshot test_snapshot;

SQL> alter pluggable database PDB_SNAP_TEST open;
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB_SNAP_TEST                  READ WRITE NO

SQL> alter pluggable database PDB_SNAP_TEST close;
SQL> drop pluggable database PDB_SNAP_TEST including datafiles;

基于 SCN 或时间点来创建 PDB：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SQL> select con_id,con_name,snapshot_name,snapshot_scn,full_snapshot_path FROM cdb_pdb_snapshots order by con_id,snapshot_scn;

    CON_ID
----------
CON_NAME
--------------------------------------------------------------------------------
SNAPSHOT_NAME
--------------------------------------------------------------------------------
SNAPSHOT_SCN
------------
FULL_SNAPSHOT_PATH
--------------------------------------------------------------------------------
         4
PDB2
TEST_SNAPSHOT

    CON_ID
----------
CON_NAME
--------------------------------------------------------------------------------
SNAPSHOT_NAME
--------------------------------------------------------------------------------
SNAPSHOT_SCN
------------
FULL_SNAPSHOT_PATH
--------------------------------------------------------------------------------
     2434524
/u01/app/oracle/oradata/ORCL/PDB2/snap_3318643222_2434524.pdb

根据返回的 2434524 SCN 点来创建 PDB：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> create pluggable database PDB_snap_test from PDB2 using snapshot at SCN 2434524;
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
         4 PDB2                           READ WRITE NO
         6 PDB_SNAP_TEST                  MOUNTED

注意：在生成 Snapshot 与通过 Snapshot 创建 PDB 过程中，消耗的时间和占据物理资源会很大。