ANBOB™

今天在墨天轮社区看到一个关于PDB Snapshot的问题， 之前在写过一系列PDB新特性，这个功能补充一下，在一些生产容灾环境中有一种定期clone一份数据库的需求，如BCV存储快照技术 ，当然最近几年CDP, CDM技术的出现也是基于存储快照实现数据库历史时间的恢复。Oracle 在18c中同样提供了一种自动生成并清理历史数据库快照的技术PDB Snapshot Carousel，和redo group, undo archive轮训一样，下面附一张官方图一看。

技术特性

1， 可以指定创建快照的频率间隔(minutes, hours)
2,   可以指定最大快照周期个数，配置值为0时，清理所有快照
3， 快照是以轮训的方式清理，达到个数时新快照覆盖历史快照
4，快照可以手动创建也可以自动创建
5， 快照可以批定命名也可以系统自动生成
6， 快照可以在已存在的库指定刷新，也可以在创建PDB时指定刷新频率
7， 快照刷新配置可以在线修改
8， 基于快照可以clone PDB, 但不可以基于快照创建快照
9， 快照文件是一种二进制PDB archive files(unplug database后的datafile和xml描述文件生成的独立的压缩副本文件，以”.pdb”为扩展名)
10,  要求PDB 使得local undo
11,  License 只支持Oracle Database Enterprise Edition以上on Engineered Systems(ODA, EXADATA) 和 on Oracle Database Cloud 

功能演示

note: 我的环境用于测试修改了Exadata的限制参数，（生产环境不建议），使用的19.3 EE On-Premise。

1, 创建PDB时同时指定刷新

SQL> select pdb_name,snapshot_mode,snapshot_interval/60 from dba_pdbs

PDB_NAME             SNAPSH SNAPSHOT_INTERVAL/60
-------------------- ------ --------------------
PDB1                 MANUAL
PDB$SEED             MANUAL

SQL>  create pluggable database pdb1_snp from pdb1 file_name_convert=('pdb1','pdb1_snp') snapshot mode every 1 hours;
 create pluggable database pdb1_snp from pdb1 file_name_convert=('pdb1','pdb1_snp') snapshot mode every 1 hours
*
ERROR at line 1:
ORA-12754: Feature PDB SNAPSHOT CAROUSEL is disabled due to missing capability .

SQL> @pd exadata
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
       482   1E2 _rm_exadata_pdb_cpu_cnt                                  FALSE                          Use PDB CPU cnt for Exadata smart scan
       483   1E3 _rm_exadata_partition_fc                                 FALSE                          Partition flash cache for Exadata
       484   1E4 _rm_exadata_pdb_cpu_cnt_mult                             2                              Multiplication factor for PDB cpu count
       912   390 _exadata_feature_on                                      FALSE                          Exadata Feature On
      1236   4D4 _lm_exadata_fence_type                                   TRUE                           if FALSE disable Exadata fence type
      2062   80E _enable_flash_logging                                    TRUE                           Enable Exadata Smart Flash Logging
      2863   B2F _cell_offload_hybridcolumnar                             TRUE                           Query offloading of hybrid columnar compressed tables to exadata
      4031   FBF _bloom_serial_filter                                     ON                             enable serial bloom filter on exadata
      4828  12DC _asm_offload_all                                         FALSE                          Offload all write operations to Exadata cells, when supported
      5136  1410 _auto_manage_exadata_disks                               TRUE                           Automate Exadata disk management

10 rows selected.

SQL> alter system set "_exadata_feature_on"=true scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1073738488 bytes
Fixed Size                  9143032 bytes
Variable Size             763363328 bytes
Database Buffers          297795584 bytes
Redo Buffers                3436544 bytes
Database mounted.
Database opened.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/19.2.0/db_1/dbs/arch
Oldest online log sequence     12
Current log sequence           14

SQL>  create pluggable database pdb1_snp from pdb1 file_name_convert=('pdb1','pdb1_snp') snapshot mode every 1 hours;
 create pluggable database pdb1_snp from pdb1 file_name_convert=('pdb1','pdb1_snp') snapshot mode every 1 hours
*
ERROR at line 1:
ORA-65036: pluggable database PDB1 not open in required mode


SQL> ho oerr ora 65036
65036, 00000, "pluggable database %s not open in required mode"
// *Cause:  Attempted to perform an operation on a pluggable database in
//          incorrect open mode.
// *Action: Open the pluggable database in the mode required for this operation
//

SQL> alter pluggable database pdb1 open;

Pluggable database altered.


SQL>SELECT property_name, property_value
FROM   database_properties
  3  WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL>  create pluggable database pdb1_snp from pdb1 file_name_convert=('pdb1','pdb1_snp') snapshot mode every 1 hours;

Pluggable database created.


SQL>  select pdb_name,snapshot_mode,snapshot_interval/60 from dba_pdbs;

PDB_NAME             SNAPSH SNAPSHOT_INTERVAL/60
-------------------- ------ --------------------
PDB1                 MANUAL
PDB$SEED             MANUAL
PDB1_SNP             AUTO                      1

2， 现用的PDB创建snapshot

SQL> r
  1  SELECT r.CON_ID, p.PDB_NAME, PROPERTY_NAME,
  2         PROPERTY_VALUE AS value, DESCRIPTION
  3  FROM   CDB_PROPERTIES r, CDB_PDBS p
  4  WHERE  r.CON_ID = p.CON_ID
  5  AND    PROPERTY_NAME LIKE 'MAX_PDB%'
  6* ORDER BY PROPERTY_NAME

CON_ID PDB_NAME  PROPERTY_NAME     VALUE                DESCRIPTION
------ --------- ----------------- -------------------- -------------------------------------------
     3 PDB1      MAX_PDB_SNAPSHOTS 8                    maximum number of snapshots for a given PDB
     3 PDB1      MAX_PDB_STORAGE   UNLIMITED            Maximum Space Usage of Datafiles and Local
                                                        Tempfiles in Container

SQL> ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=4;
ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=4
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database


SQL> @cc pdb1
ALTER SESSION SET container = pdb1;
Session altered.

SQL> ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=4;
Pluggable database altered.
 
SQL> ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 20 minutes;
Pluggable database altered.

SQL>  select pdb_name,snapshot_mode,snapshot_interval/60 from dba_pdbs;

PDB_NAME  SNAPSH SNAPSHOT_INTERVAL/60
--------- ------ --------------------
PDB1      AUTO             .333333333

Tip：
Creating a Snapshot with a User-Specified Name

ALTER PLUGGABLE DATABASE SNAPSHOT cdb1_pdb1_b4wedload;

Creating a Snapshot with a System-Specified Name

ALTER PLUGGABLE DATABASE SNAPSHOT;

3, 查询快照

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;

因为虚机存储满，不再演示生成的文件，自己可以测试都是.pdb文件， 目前是不支持ASM中的，可以考虑ACFS。

— enjoy

打赏

微信扫一扫，打赏作者吧～