Bo's Oracle Station › 博客首页 ›Oracle技术› 开发和基础管理 › 查看内容

【博客文章2024】为基于ASM存储的PDB制作快照Carousel，并基于快照Carousel中的快照创建新的PDB

2024-6-18 14:27| 发布者: admin| 查看: 45| 评论: 0|原作者: Bo Tang

摘要: 本博客为基于ASM存储的PDB制作快照Carousel，并基于快照Carousel中的快照创建新的PDB。文中对创建过程的各种错误进行排错，内容详实可重现。最后进行了PDB的快照Carousel、PDB的clean flashback restore point/flashback restore point和PDB全备份的应用场景对比。

Author: Bo Tang

PDB快照是PDB在特定时间点的拷贝。 PDB快照把整个PDB备份为扩展名为“.pdb”的单个文件。手动PDB快照是DBA手动制作的，而自动PDB快照是系统按照时间间隔自动制作的。每一个PDB最多轮转8个PDB快照（保留最新的8个），这一系列快照被称作快照Carousel。PDB快照Carousel可以用于创建新的PDB。

1. 开启PDB制作快照Carousel功能：

根容器cdb1和插件数据库pdb1_1，版本为19c，实验环境如下：

[oracle@db ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 10:28:37 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> select p.pdb_name,p.snapshot_interval,p.snapshot_mode from dba_pdbs p;

PDB_NAME SNAPSHOT_INTERVAL SNAPSHOT_MODE
------------ -------------------------- -------------------
PDB$SEED MANUAL
PDB1_1 MANUAL

开启PDB制作快照Carousel功能会报告ORA-12754错误：

[oracle@db ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 10:28:37 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> ALTER PLUGGABLE DATABASE pdb1_1 snapshot mode every 1 hours;
Error report:
Error starting at line 11 in command:
ALTER PLUGGABLE DATABASE pdb1_1 snapshot mode every 1 hours
Error report:
SQL Error: ORA-12754: Feature PDB SNAPSHOT CAROUSEL is disabled due to missing capability .

以上报错说明PDB SNAPSHOT CAROUSEL功能（exadata专属）没有开启，以下通过设置隐含参数“_exadata_feature_on”开启该功能：

System altered.

SQL> shutdown immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4294963960 bytes
Fixed Size            9143032 bytes
Variable Size         3388997632 bytes
Database Buffers      872415232 bytes
Redo Buffers           24408064 bytes
Database mounted.
Database opened.
SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDB1_1              READ WRITE NO

SQL> alter pluggable database all open;

2. 自动制作PDB的快照Carousel：

必需在插件数据库内制作PDB快照Carousel，否则会报告ORA-65118的错误：

[oracle@db ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 10:28:37 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> ALTER PLUGGABLE DATABASE pdb1_1 snapshot mode every 1 hours;

Error report:
SQL Error: ORA-65118: operation affecting a pluggable database cannot be performed from another pluggable database

转入插件数据库内执行自动制作PDB的快照Carousel的命令：

SQL> alter session set container=pdb1_1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE pdb1_1 snapshot mode every 1 hours ;

Pluggable database altered.

[oracle@db ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 10:28:37 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> select p.pdb_name,p.snapshot_interval,p.snapshot_mode   from dba_pdbs p;

PDB_NAME  SNAPSHOT_INTERVAL SNAPSHOT_MODE
------------ -------------------------- -------------------

PDB$SEED                                    MANUAL
PDB1_1        60                             AUTO

3. 手动制作PDB的快照Carousel：

与自动制作快照Carousel时一样（见本文第2节），必需在插件数据库内制作PDB快照Carousel，否则会报告ORA-65118的错误：

[oracle@db ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 10:28:37 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb1_1 snapshot s1;

Error report:
SQL Error: ORA-65118: operation affecting a pluggable database cannot be performed from another pluggable database

转入插件数据库内执行手动制作PDB的快照Carousel的命令，报了ORA-00604/ORA-65169/ORA-17517错误：

SQL> alter session set container=pdb1_1;

Session altered.

SQL> alter pluggable database pdb1_1  snapshot s1; 
alter pluggable database pdb1_1  snapshot s1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-65169: error encountered while attempting to copy file
+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/DATAFILE/undotbs1.287.1169814539
ORA-17517: Database cloning using storage snapshot failed on file
8:+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/DATAFILE/undotbs1.287.1169814539

在该版本上，这是由于插件数据库的数据文件和临时文件位于+ASM存储上，同时根容器的db_create_file_dest设定成+ASM路径所致。
4. 手动制作PDB的快照Carousel的错误处理：
利用19c的特性，将插件数据库的数据文件和临时文件在线移动到文件系统之上：

[oracle@db ~]$ mkdir  -p /u01/app/oracle/oradata/CDB1/PDB1_1/

[oracle@db ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 10:28:37 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.

SQL>alter session set container=pdb1_1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1_1

SQL> select  name from v$datafile;


NAME
--------------------------------------------------------------------------------
+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/DATAFILE/system.288.1169814539
+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/DATAFILE/sysaux.286.1169814537
+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/DATAFILE/undotbs1.287.1169814539
+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/DATAFILE/users.285.1169814537

SQL> alter database move datafile '+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/DATAFILE/system.288.1169814539' to '/u01/app/oracle/oradata/CDB1/PDB1_1/system01.dbf'; 

Database altered.

SQL> alter database move datafile '+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/DATAFILE/sysaux.286.1169814537' to '/u01/app/oracle/oradata/CDB1/PDB1_1/sysaux01.dbf';

Database altered.

SQL>  alter database move datafile '+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/DATAFILE/undotbs1.287.1169814539' to '/u01/app/oracle/oradata/CDB1/PDB1_1/undotbs1.dbf'; 

Database altered.

SQL> alter database move datafile '+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/DATAFILE/users.285.1169814537' to '/u01/app/oracle/oradata/CDB1/PDB1_1/users01.dbf'; 

Database altered.

SQL> select  name from v$datafile; 

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB1/PDB1_1/system01.dbf
/u01/app/oracle/oradata/CDB1/PDB1_1/sysaux01.dbf
/u01/app/oracle/oradata/CDB1/PDB1_1/undotbs1.dbf
/u01/app/oracle/oradata/CDB1/PDB1_1/users01.dbf

SQL>  alter tablespace temp add tempfile '/u01/app/oracle/oradata/CDB1/PDB1_1/temp01.dbf' size 30M ;

Tablespace altered.

SQL> select  name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/TEMPFILE/temp.289.1169814547
/u01/app/oracle/oradata/CDB1/PDB1_1/temp01.dbf

SQL> alter tablespace  temp drop tempfile '+DATA/CDB1/19367110B1E107C6E0635CFA19AC35D7/TEMPFILE/temp.289.1169814547';

Tablespace altered.
SQL> select  name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB1/PDB1_1/temp01.dbf

转入插件数据库内执行手动制作PDB的快照Carousel的命令，仍然报错：

SQL> show con_name

CON_NAME
------------------------------
PDB1_1
SQL> alter pluggable database pdb1_1  snapshot s1; 
alter pluggable database pdb1_1  snapshot s1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-65169: error encountered while attempting to copy file
/u01/app/oracle/oradata/CDB1/PDB1_1/undotbs1.dbf
ORA-17517: Database cloning using storage snapshot failed on file
8:/u01/app/oracle/oradata/CDB1/PDB1_1/undotbs1.dbf

更改根容器的db_create_file_dest设定成文件系统路径。自动快照和手动快照都制作成功：

SQL>show con_nameCON_NAME------------------------------PDB1_1SQL> alter system set db_create_file_dest='/home/oracle';
System altered.SQL> alter pluggable database pdb1_1 snapshot s1; Pluggable database altered.SQL> col CON_NAME format a10SQL> col SNAPSHOT_NAME format a30SQL> col FULL_SNAPSHOT_PATH format a50SQL> set linesize 1000 SQL> select CON_NAME, SNAPSHOT_NAME, SNAPSHOT_TIME , PREVIOUS_SNAPSHOT_TIME , FULL_SNAPSHOT_PATH from dba_pdb_snapshots; CON_NAME SNAPSHOT_NAME SNAPSHOT_TIME PREVIOUS_SNAPSHOT_TIME FULL_SNAPSHOT_PATH ---------- ------------------------------ ------------- ---------------------- -------------------------------------------PDB1_1 SNAP_2142865052_1172040564 1718794163 0 /home/oracle/s1.pdbPDB1_1 SNAP_2142865052_1172124748 1718878379 0 /home/oracle/snap_2142865052_7928736.pdbPDB1_1 SNAP_2142865052_1172128349 1718881981 0 /home/oracle/snap_2142865052_7933667.pdb PDB1_1 SNAP_2142865052_1172131948 1718885545 0 /home/oracle/snap_2142865052_7938493.pdb PDB1_1 SNAP_2142865052_1172135548 1718889179 0 /home/oracle/snap_2142865052_7943091.pdb PDB1_1 SNAP_2142865052_1172139148 1718892747 0 /home/oracle/snap_2142865052_7947602.pdbPDB1_1 SNAP_2142865052_1172142748 1718896348 0 /home/oracle/snap_2142865052_7952133.pdb......

自动快照的名字是系统定名的，包含SCN和时间戳，如上面的“/home/oracle/snap_2142865052_7928736.pdb”。

5. 基于PDB的快照Carousel中的某个快照创建新的PDB：

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter system  set db_create_file_dest='+DATA';                  

System altered.

SQL> show parameter db_create_file_dest

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest             string     +DATA

SQL>  create pluggable database pdb1_2 from pdb1_1  using snapshot  s1;

Pluggable database created.

SQL> alter session set container=pdb1_2; 

Session altered.

SQL> select  name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/CDB1/1B3CF163EB28F74FE0635CFA19AC48CB/DATAFILE/system.286.1172042111
+DATA/CDB1/1B3CF163EB28F74FE0635CFA19AC48CB/DATAFILE/sysaux.288.1172042111
+DATA/CDB1/1B3CF163EB28F74FE0635CFA19AC48CB/DATAFILE/undotbs1.293.1172042111
+DATA/CDB1/1B3CF163EB28F74FE0635CFA19AC48CB/DATAFILE/users.287.1172042111

创建的新的PDB里的数据和PDB快照里的数据一模一样。可以在创建的新的PDB里执行报表查询来从容地分析PDB快照那个时刻的数据。创建的PDB的数据文件存放的位置可以根据根容器上db_create_file_dest的设定存放于+ASM中。

6. PDB的快照Carousel、PDB的clean flashback restore point/flashback restore point和PDB全备份的应用场景对比：

PDB的快照Carousel和PDB全备份的应用场景相同。都是离线的，对后续数据库的性能没有影响。

PDB的clean flashback restore point/flashback restore point可用于迅速回退PDB到过去的时间点。clean flashback restore point用于shared undo模式，而普通的flashback restore point适用于所有的undo模式。这两种flashback restore point都需要实例的rvwr进程进行后续的flashback database log的维护，对后续数据库性能是有影响的。

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：【博客文章2024】Oracle 19c创建虚拟专用恢复目录（基于VPD，不同于之前版本的实施方式）