Oracle 12c 新特性之 PDB 级别闪回数据库

时间:2022-02-05 21:34:14
在Oracle Database 12.1中,闪回数据库操作仅限于 CDB ,Oracle Database 12.2支持 CDB 与 PDB 数据库的闪回。

PDB 的还原点种类:
1. normal restore point:          create restore point pdb1_point1;
2. guaranteed restore point:      create restore point pdb1_point1 guarantee flashback database;
-- 在需要设定干净还原点的 PDB 容器下 shutdown immediate , 然后在创建干净还原点。
3.1 clean normal restore point:    create clean restore point cdb1_before_changes;
3.2 clean guaranteed restore point:create clean restore point cdb1_before_changes guarantee flashback database;
补充:PDB 中执行,如果是 CDB 中执行的话,在对应代码后面多添加 for pluggable database pdb_name 指定PDB名称

PDB 中支持的闪回数据库方式:
1.还原点
2.scn
3.timestamp

演示操作:
1、首先,我们开启数据库闪回功能:
[oracle@12c01 ~]$ mkdir -p /home/oracle/fast_recovery_area
SQL>alter system set db_recovery_file_dest_size=10G;
SQL>alter system set db_recovery_file_dest='/home/oracle/fast_recovery_area' scope=both;
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database flashback on;
SQL>alter database open;
SQL>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
闪回日志保留的数量是受参数DB_FLASHBACK_RETENTION_TARGET控制的,该参数表示保留时间(分钟),默认为1天。
-- 查询闪回日志保留天数,如果有必要,就修改保留时间。
SQL>show parameter db_flashback_retention_target
NAME     TYPE VALUE
------------------------------------ ----------- -------------------
db_flashback_retention_target     integer 1440
查看当前数据库实例状态:
show pdbs
    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED  READ ONLY  NO
3 PDB01  READ WRITE NO
SQL>alter session set container=pdb01;
模拟业务
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1628859
SQL> create table c##andy.pdbfla (id int);

Table created.

SQL> insert into c##andy.pdbfla values(1);

1 row created.

SQL> commit;

Commit complete.
SQL>alter session set container=cdb$root;
SQL>create table c##andy.cdbfla (id int);
SQL>insert into c##andy.cdbfla values(1);
SQL>commit;
下面是local undo模式下 PDB 闪回数据库操作步骤:

SQL>alter pluggable database pdb01 close;

SQL> flashback pluggable database pdb01 to scn 1628859;
Flashback complete.
SQL>alter pluggable database pdb01 open resetlogs;
Pluggable database altered.
SQL> select * from c##andy.cdbfla;
ID
----------
1
SQL> alter session set container=pdb01;
SQL> select * from c##andy.pdbfla;
ORA-00942: table or view does not exist
说明:可以看不到 PDB 级别的数据库闪回不影响 CDB 的记录。
_______________________________________
补充:
如果使用的shared undo模式下 PDB 闪回数据库,语法有点不同,就是你需要制定辅助实例的位置。
SQL>flashback pluggable database pdb_name to scn the_scn auxiliary destination '/XXX/auxiliary';
SQL>flashback pluggable database pdb_name to restore point restore_point auxiliary destination '/XXX/auxiliary';