Oracle 12c 数据库支持闪回库功能， r1 仅支持 cdb 级别的闪回库， r2 可以支持到 pdb 级别的闪回库，闪回到闪回点，时间点， scn 几种方式。当然在闪回库操作时， share undo 和 local undo 模式使用的语句是不同的。（ share undo 模式需要使用到辅助实例） 一、采用 local undo 模式 SYS@orcl>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'; ------------------------------------------------------------ ----- LOCAL_UNDO_ENABLED TRUE 数据库版本 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 数据库 scott.it 输入数据，记录 scn ； 16:55:24 SYS@orcl>conn scott/oracle@pdb1 Connected. 16:55:40 SCOTT@pdb1>select * from it; ID ---------- 1 2 16:55:46 SCOTT@pdb1>insert into it values(100); 1 row created. 16:55:57 SCOTT@pdb1>commit; Commit complete. 16:55:59 SCOTT@pdb1>select * from it; ID ---------- 1 2 100 16:56:25 SYS@pdb1>select current_scn from v$database; CURRENT_SCN ----------- 5120605 3 、删除 scott.it 表，并闪回 pdb1 到 scn 5120605 16:57:02 SYS@pdb1>drop table scott.it purge; Table dropped. 16:57:20 SYS@pdb1>shutdown immediate; Pluggable Database closed. 16:57:31 SYS@pdb1>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB1 MOUNTED 16:57:33 SYS@pdb1>conn / as sysdba Connected. 16:57:41 SYS@orcl>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 16:57:44 SYS@orcl>flashback pluggable database pdb1 to scn 5120605; Flashback complete. 16:58:07 SYS@orcl>alter pluggable database pdb1 open resetlogs; Pluggable database altered. 16:58:30 SYS@orcl>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 16:58:57 SYS@orcl>conn scott/oracle@pdb1 Connected. 16:59:15 SCOTT@pdb1>select count(*) from it; COUNT(*) ---------- 3 16:59:25 SCOTT@pdb1>select * from it; ID ---------- 1 2 100 二、采用 share undo 模式 1 、将 local undo 修改成 share undo 模式 查看 local undo 是否开启，默认是开启的； SYS@orcl>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'; ------------------------------------------------------------ ----- LOCAL_UNDO_ENABLED TRUE 以 upgrade 模式重启数据库； 20:33:32 SYS@orcl>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 20:33:38 SYS@orcl>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 20:34:20 SYS@orcl>startup upgrade; ORACLE instance started. Total System Global Area 2466250400 bytes Fixed Size 9137824 bytes Variable Size 587202560 bytes Database Buffers 1862270976 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. 关闭 local undo ； 20:34:50 SYS@orcl>alter database local undo off; Database altered. 20:35:01 SYS@orcl>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 20:36:01 SYS@orcl>startup; ORACLE instance started. Total System Global Area 2466250400 bytes Fixed Size 9137824 bytes Variable Size 587202560 bytes Database Buffers 1862270976 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. 20:36:41 SYS@orcl>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 20:36:47 SYS@orcl>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME LOCAL_UNDO_ENABLED ----------------------------------------------------------------- PROPERTY_VALUE FALSE 20:36:58 SYS@orcl>show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ----------------- temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 20:37:08 SYS@orcl>conn sys/oracle@pdb1 as sysdba Connected. 20:37:46 SYS@pdb1>select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP01 DATA 20:37:55 SYS@pdb1>drop tablespace undotbs1 including contents and datafiles; Tablespace dropped. 20:38:17 SYS@pdb1>select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX TEMP01 DATA 备注： share undo 转化为 local undo 模式，打开 pdb01 时会自动重建，无须人工干预，如果需要在 pdb01 中重建 pdb 则需要 undo 管理方式使用 manual 方式或者在 pdb01 中新建 undo 然后再替换的方式。 录入数据，记录 scn 20:47:53 SCOTT@pdb1>insert into it values(200); 1 row created. 20:47:59 SCOTT@pdb1>commit; Commit complete. 20:48:01 SCOTT@pdb1>select * from it; ID ---------- 1 2 200 100 20:48:08 SCOTT@pdb1>conn sys/oracle@pdb1 as sysdba Connected. 20:48:29 SYS@pdb1>select current_scn from v$database; CURRENT_SCN ----------- 5181471 20:48:37 SYS@pdb1>drop table scott.it; Table dropped. 3 、闪回数据库。 RMAN> flashback pluggable database pdb1 to scn 5181471 auxiliary destination '/home/oracle/aux'; [oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 14 21:24:31 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SYS@orcl>alter pluggable database pdb1 open resetlogs; Pluggable database altered. SYS@orcl>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SYS@orcl>conn scott/oracle@pdb1 Connected. SCOTT@pdb1>select * from it; ID ---------- 1 2 200 100 总结：数据库闪回功能基于已有的备份数据以及归档日志将 pdb 库恢复到某个节点， 12c 上使用 local undo 和 share undo 方式使用命令是不一样的，数据库闪回操作意味着数据的丢失，所以操作时需要注意，是否可以只读的方式来看看闪回数据是否正常呢？
Yicheng16 22.08.14
-- The End --
