OCA/OCP Oracle 数据库12c考试指南读书笔记：第26章:Flashback

本文主要是介绍OCA/OCP Oracle 数据库12c考试指南读书笔记：第26章:Flashback，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

flashback，以下简称闪回，解决的是用户错误，如误删表，输入错数据等。可以闪回整库，某表或事务。

闪回技术简介

闪回数据库

效果和不完整恢复类似，在闪回点之后的事务全部丢失，数据库以resetlogs模式打开。

闪回查询，事务和表

此3技术均使用undo segment。闪回技术产生于9i。
闪回查询可以查询某一时间点的数据库。这样通过比较就可以知道发生了什么修改。
闪回事务将修复的过程自动化，提交的事务不能回退，当使用闪回查询知道产生修改的事务后，可以通过构建另一事务来抵消之前事务产生的更改。和闪回数据库不同，闪回事务不会丢失数据。
闪回表可以将某一时间点之后对表的改变全部撤销。闪回表会保证参照一致性和约束，因此有时需要将有关联的多表一起闪回。

闪回Drop

相当于undrop，只能闪回drop，不能闪回truncate。

闪回Data Archive （FBDA）

目前描述的闪回技术均有时间限制，闪回数据库受限于闪回log；闪回查询受限于undo retention。闪回drop受限于表空间大小。
和闪回查询和闪回drop不同，闪回Data Archive默认并未启用。为表启用闪回Data Archive后，会建立另一张表存行的所有版本，并可以存任意长时间。闪回Data Archive由后台进程FBDA负责。
FBDA可以设有效期，超期的数据将自动删除。

何时使用闪回

闪回数据库的使用非常慎重，只有当你考虑不完全恢复时才考虑使用，因为恢复速度会快些。例如不小心删除了整个schema。
闪回drop可以恢复表，和闪回数据库不同，闪回drop无需配置。
更细粒度的闪回可考虑闪回表和闪回事务。和闪回drop一样，这些闪回无需配置，但可能需要调整undo segment。
闪回Data Archive用于长期存储，如法律法规要求。
如果需要恢复truncate，只能使用闪回数据库。
另外，只有闪回数据库可以同时保证事务一致性和业务一致性，其它闪回只保证事务一致性。
如果发生介质错误，这时只有利用备份和恢复，闪回并无帮助。

使用闪回查询数据

需要借助undo来重构数据。
需要指定时间，然后映射为SCN。

基本闪回查询

SQL> create table t1 as select * from regions;Table created.SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';Session altered.SQL> select sysdate from dual;SYSDATE
-----------------
17-12-19 08:42:59SQL> select * from t1;REGION_ID REGION_NAME
---------- -------------------------1 Europe2 Americas3 Asia4 Middle East and AfricaSQL> delete from t1 where region_name like 'A%';2 rows deleted.SQL> commit;Commit complete.SQL> select * from t1;REGION_ID REGION_NAME
---------- -------------------------1 Europe4 Middle East and Africa-- 注意，此为minus操作，实际得出的是被删除的数据
SQL> select * from t1 as of timestamp to_timestamp('17-12-19 08:42:59', 'dd-mm-yy hh24:mi:ss') minus select * from t1;REGION_ID REGION_NAME
---------- -------------------------2 Americas3 Asia

如果要执行一系列查询，可以将会话回退到之前的某一时间点。
DBMS_FLASHBACK的介绍见这里。enable_at_time和disable过程分别为启用和关闭闪回模式。

-- 普通用户需要赋权后才能执行以下过程，例如
-- grant execute on DBMS_FLASHBACK to hr;
SQL> execute dbms_flashback.enable_at_time(to_timestamp('17-12-19 08:42:59', 'dd-mm-yy hh24:mi:ss'));PL/SQL procedure successfully completed.SQL> select * from t1;REGION_ID REGION_NAME
---------- -------------------------1 Europe2 Americas3 Asia4 Middle East and AfricaSQL> delete from t1;
delete from t1*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback modeSQL> execute dbms_flashback.disable;PL/SQL procedure successfully completed.SQL> select * from t1;REGION_ID REGION_NAME
---------- -------------------------1 Europe4 Middle East and Africa

注意, sys用户不支持flashback。执行以上过程用户必须具有flashback包的执行权限。

flashback往回查询的时间取决于undo，其实也仅取决于undo，并不需要启用flashback database：

SQL> select flashback_on from v$database;FLASHBACK_ON
------------------
NO

如果希望真的恢复表，可用以下命令，也就是下一节介绍的内容：

SQL> alter table t1 enable row movement;Table altered.SQL> flashback table t1 to timestamp to_timestamp('06-01-23 10:33:55', 'dd-mm-yy hh24:mi:ss');Flashback complete.SQL> select * from t1;REGION_ID REGION_NAME
---------- -------------------------1 Europe2 Americas3 Asia4 Middle East and Africa

闪回表

准确的说，并不是Query，而是使用新的事务抵消之前的事务。
示例：

SQL> insert into regions values(5, 'Africa');
1 row created.SQL> insert into countries values('EE', 'Egypt', 5);
1 row created.SQL> commit;
Commit complete.SQL> select sysdate from dual;
SYSDATE
-----------------
17-12-19 09:12:25SQL> delete from countries where region_id = 5;
1 row deleted.SQL> delete from regions where region_id = 5;
1 row deleted.SQL> commit;
Commit complete.

闪回表，需要启用row movement：

SQL> flashback table regions to timestamp to_timestamp('17-12-19 09:12:25', 'dd-mm-yy hh24:mi:ss');
flashback table regions to timestamp to_timestamp('17-12-19 09:12:25', 'dd-mm-yy hh24:mi:ss')*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabledSQL> alter table regions enable row movement;
Table altered.SQL> alter table countries enable row movement;
alter table countries enable row movement
*
ERROR at line 1:
ORA-14066: illegal option for a non-partitioned index-organized table

由于有参照一致性，因此需要一起恢复：

SQL> flashback table countries to timestamp to_timestamp('17-12-19 09:12:25', 'dd-mm-yy hh24:mi:ss');
flashback table countries to timestamp to_timestamp('17-12-19 09:12:25', 'dd-mm-yy hh24:mi:ss')
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (HR.COUNTR_REG_FK) violated - parent key not
foundSQL> flashback table countries,regions to timestamp to_timestamp('17-12-19 09:12:25', 'dd-mm-yy hh24:mi:ss');Flashback complete.SQL> select * from regions;REGION_ID REGION_NAME
---------- -------------------------1 Europe2 Americas3 Asia4 Middle East and Africa5 AfricaSQL> select * from countries where region_id = 5;CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
EE Egypt                                             5

闪回表需要启用row movement。如果有参照一致性或约束，需要多个表一起恢复。
默认会禁止触发器，不过也可以激活。
闪回和数据库大小关系不大，主要和数据变化量相关。

抛开以上的过程不说，2023年1月6日，我在19c上用以下过程也恢复成功了：

SQL> flashback table regions to timestamp to_timestamp('06-01-23 10:51:39', 'dd-mm-yy hh24:mi:ss');Flashback complete.SQL> flashback table countries to timestamp to_timestamp('06-01-23 10:51:39', 'dd-mm-yy hh24:mi:ss');Flashback complete.

此时，并没有一起恢复，同时表countries也没有启用row movement。

最后，执行清理：

delete from countries where region_id = 5;
delete from regions where region_id = 5;
commit;

闪回版本查询

SQL> select region_id, region_name, versions_xid, versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_operation from regions versions between scn minvalue and maxvalue;REGION_ID REGION_NAME               VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_STARTTIME       VERSIONS_ENDSCN VERSIONS_ENDTIME         V
---------- ------------------------- ---------------- ----------------- ------------------------ --------------- ------------------------ -5 Africa                    06001B009A030000           4741748 17-DEC-19 09.22.33 AM                                             I1 Europe                    06001B009A030000           4741748 17-DEC-19 09.22.33 AM                                             I2 Americas                  06001B009A030000           4741748 17-DEC-19 09.22.33 AM                                             I3 Asia                      06001B009A030000           4741748 17-DEC-19 09.22.33 AM                                             I4 Middle East and Africa    06001B009A030000           4741748 17-DEC-19 09.22.33 AM                                             I4 Middle East and Africa    06001B009A030000           4741748 17-DEC-19 09.22.33 AM                                             D3 Asia                      06001B009A030000           4741748 17-DEC-19 09.22.33 AM                                             D2 Americas                  06001B009A030000           4741748 17-DEC-19 09.22.33 AM                                             D1 Europe                    06001B009A030000           4741748 17-DEC-19 09.22.33 AM                                             D5 Africa                    08001400D0030000           4741678 17-DEC-19 09.22.09 AM                                             D5 Africa                    0100030048030000           4741669 17-DEC-19 09.21.51 AM            4741678 17-DEC-19 09.22.09 AM    I5 Africa                    03000C00E6030000           4741588 17-DEC-19 09.19.59 AM                                             D1 Europe                                                                                        4741748 17-DEC-19 09.22.33 AM2 Americas                                                                                      4741748 17-DEC-19 09.22.33 AM3 Asia                                                                                          4741748 17-DEC-19 09.22.33 AM4 Middle East and Africa                                                                        4741748 17-DEC-19 09.22.33 AM5 Africa                                                                                        4741588 17-DEC-19 09.19.59 AM17 rows selected.

可查询行的所有修改历史。以上结果按时间降序排列，因此需从底部读起。
以上minvalue和maxvalue实际为scn.minvalue和scn.maxvalue，是常数。也可以查询某事件范围，如between timestamp(systimestamp - 1/24) and timestamp
依赖于伪列(pseudocolumns ); 伪列不是ISO标准。以上VERSIONS_XID，VERSIONS_STARTSCN ，VERSIONS_ENDSCN，VERSIONS_OPERATION都是伪列。最著名的伪列是ROWID。
版本查询不支持外部表和视图，因为它们不产生undo。

闪回事务查询

也是利用undo数据，在视图FLASHBACK_TRANSACTION_QUERY中，需要SELECT ANY TRANSACTION权限查询。
此视图显示提交和正在进行的事务，正在进行的事务COMMIT_SCN 和 COMMIT_TIMESTAMP 为NULL。

SQL> desc FLASHBACK_TRANSACTION_QUERYName                                      Null?    Type----------------------------------------- -------- ----------------------------XID                                                RAW(8)START_SCN                                          NUMBERSTART_TIMESTAMP                                    DATECOMMIT_SCN                                         NUMBERCOMMIT_TIMESTAMP                                   DATELOGON_USER                                         VARCHAR2(128)UNDO_CHANGE#                                       NUMBEROPERATION                                          VARCHAR2(32)TABLE_NAME                                         VARCHAR2(256)TABLE_OWNER                                        VARCHAR2(386)ROW_ID                                             VARCHAR2(19)UNDO_SQL                                           VARCHAR2(4000)

因为SQL是集合操作，可能影响多行，而每一行在此视图中都有一条记录。

SQL> update hr.employees set salary=salary*1.1 where employee_id = 206;1 row updated.SQL> commit;Commit complete.SQL> select employee_id, salary, versions_xid from hr.employees versions between scn minvalue and maxvalue where employee_id=206;EMPLOYEE_ID     SALARY VERSIONS_XID
----------- ---------- ----------------206       9130 0A00090043030000206       8300SQL> select operation, undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('0A00090043030000');
select operation, undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('0A001D0042030000');
select operation, undo_sql from FLASHBACK_TRANSACTION_QUERY where xid='0A001D0042030000';

奇怪，查不到结果。

最后，参照了OLL中的例子。
可以看到undo_sql，但最后back out时，执行时间过长，没有结果：

-- 按照时间倒序排列，因此需先从底部看起
SQL> select region_id, region_name, versions_xid from regions versions between scn minvalue and maxvalue;REGION_ID REGION_NAME               VERSIONS_XID
---------- ------------------------- ----------------30 Still called Venus        020017009F03000050 Big star                  0A0009004503000040 Red star                  0A0009004503000010 No star                   0A0009004503000020 Many Moons                06001900BA03000010 Two Poles                 06001900BA03000050 Saturn                    040017004C03000040 Mars                      040017004C03000030 Venus                     040017004C03000020 Moon                      040017004C03000010 Pole                      040017004C0300005 Africa1 Europe2 Americas3 Asia4 Middle East and Africa16 rows selected.SQL> select * from regions;REGION_ID REGION_NAME
---------- -------------------------10 No star20 Many Moons30 Still called Venus40 Red star50 Big star5 Africa1 Europe2 Americas3 Asia4 Middle East and Africa10 rows selected.-- 5个insert
SQL> select operation, undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('040017004C030000');OPERATION
----------
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
INSERT
delete from "HR"."REGIONS" where ROWID = 'AAAR02AAMAAAACDAAE';INSERT
delete from "HR"."REGIONS" where ROWID = 'AAAR02AAMAAAACDAAD';INSERT
delete from "HR"."REGIONS" where ROWID = 'AAAR02AAMAAAACDAAC';INSERT
delete from "HR"."REGIONS" where ROWID = 'AAAR02AAMAAAACDAAB';INSERT
delete from "HR"."REGIONS" where ROWID = 'AAAR02AAMAAAACDAAA';BEGIN6 rows selected.-- 两个update
SQL> select operation, undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('06001900BA030000');OPERATION
----------
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
UPDATE
update "HR"."REGIONS" set "REGION_NAME" = 'Moon' where ROWID = 'AAAR02AAMAAAACDAAB';UPDATE
update "HR"."REGIONS" set "REGION_NAME" = 'Pole' where ROWID = 'AAAR02AAMAAAACDAAA';BEGIN-- 3个update
SQL> select operation, undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('0A00090045030000');OPERATION
----------
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
UPDATE
update "HR"."REGIONS" set "REGION_NAME" = 'Saturn' where ROWID = 'AAAR02AAMAAAACDAAE';UPDATE
update "HR"."REGIONS" set "REGION_NAME" = 'Mars' where ROWID = 'AAAR02AAMAAAACDAAD';UPDATE
update "HR"."REGIONS" set "REGION_NAME" = 'Two Poles' where ROWID = 'AAAR02AAMAAAACDAAA';BEGIN-- 一个update
SQL> select operation, undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('020017009F030000');OPERATION
----------
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
UPDATE
update "HR"."REGIONS" set "REGION_NAME" = 'Venus' where ROWID = 'AAAR02AAMAAAACDAAC';BEGINexec dbms_flashback.transaction_backout(numtxns=>2, xids=>sys.xid_array('06001900BA030000', '0A00090045030000'), options=>dbms_flashback.cascade);
-- 一直等待...

闪回和 Undo 数据

40分钟前的可以查询到，40天之前的不行。

SQL> select count(*) from hr.employees as of timestamp(systimestamp - 40/1440);COUNT(*)
----------107SQL> select count(*) from hr.employees as of timestamp(systimestamp - 40);
select count(*) from hr.employees as of timestamp(systimestamp - 40)*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL> show parameter undoNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> show parameter retentionNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
undo_retention                       integer     900

为保证足够的undo，需要为undo表空间设置RETENTION GUARANTEE，同时设置UNDO_RETENTION参数。

执行 Flashback Table 操作

删除表可能由于连到错误的schema，以下语句可显示当前schema:

set sqlprompt "_user'@'_connect_identifier>"

不能恢复truncate，只恢复drop table，表相关的对象除外键外均恢复，如索引，主键等。

Flashback Drop的实现

9i时，drop表是将其在数据字典中的条目删除，空间标识为可重用。如果要恢复，只能做非完全恢复。
在10g时，drop表实际为重命名表。重命名后，除外键备删外，其它均保留。
由于只是重命名，因此有可能可以恢复。删除的表被放入recycle bin中，每个用户都有自己的recycle bin。可查询USER_RECYCLEBIN，全局视图为DBA_RECYCLEBIN
但恢复未必能成功，因为其空间可能被重用，或者又创建了使用原来名字的表。所以，越早恢复，成功率越大。

使用 Flashback Drop

create table tmp(a int);
insert into tmp values(1);
commit;
drop table tmp;
select * from tmp;
flashback table tmp to before drop;
select * from tmp;

flashback drop可以重新命名对象，但不能改变schema。例如：

flashback table tmp to before drop rename to new_name;

两种情况下不能恢复，drop…purge和drop user … cascade，如下：

HR@orclpdb1>drop table tmp purge;Table dropped.HR@orclpdb1>flashback table tmp to before drop;
flashback table tmp to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

recycle bin中的内容可以通过show recyclebin或dba_recyclebin视图查询：

HR@orclpdb1>show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T1               BIN$mdyY0E+vE/DgUwEAAH/vWw==$0 TABLE        2019-12-17:08:42:04
HR@orclpdb1>select object_name, original_name, type from dba_recyclebin;OBJECT_NAME                              ORIGINAL_NAME        TYPE
---------------------------------------- -------------------- -------------------------
BIN$mdyY0E+vE/DgUwEAAH/vWw==$0           T1                   TABLE

管理 Recycle Bin

recycle bin的管理是自动的，也可以禁用：

HR@orclpdb1>show parameter recyclebinNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

查询Recycle Bin

最常用的是show recyclebin命令。也可以查询XXX_recyclebin视图。

HR@orclpdb1>select owner, original_name, type, droptime, can_undrop, space from dba_recyclebin;OWNER      ORIGINAL_NAME        TYPE                      DROPTIME            CAN      SPACE
---------- -------------------- ------------------------- ------------------- --- ----------
HR         T1                   TABLE                     2019-12-17:08:42:04 YES          8

最重要的是can_undrop

system表空间中的表不能undrop。

重用Recycle Bin空间

空间不够时，会先清理Recycle Bin.

从备份中执行表恢复

也成为Table PITR（Point-In-Time Recovery）
和闪回是不同的技术，但效果一样，都是将表恢复到之前状态。并且不受undo的限制。
在orclpdb1中创建表空间和表：

create tablespace testts datafile 'testts' size 10m;
create table system.testtab tablespace testts as select * from all_users;

备份表空间，注意PDB的写法：

$ rman target / catalog rcat_owner/Welcome1@rcat
RMAN> backup tablespace orclpdb1:testts;

记录SCN并删除表：

SQL> select current_scn from v$database;CURRENT_SCN
-----------5930910SQL> drop table system.testtab;Table dropped.

使用RMAN恢复，注意PDB的语法：

RMAN> recover table system.testtab of pluggable database orclpdb1 until scn 5930910 auxiliary destination '/tmp'

这个过程很漫长，输出如下：

Starting recover at 21-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4Creating automatic instance, with SID='kvta'initialization parameters used for automatic instance:
db_name=ORCLCDB
db_unique_name=kvta_pitr_orclpdb1_ORCLCDB
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/opt/oracle
_system_trig_enabled=FALSE
sga_target=1152M
processes=200
db_create_file_dest=/tmp
log_archive_dest_1='location=/tmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file usedstarting up automatic instance ORCLCDBOracle instance startedTotal System Global Area    1207959448 bytesFixed Size                     9133976 bytes
Variable Size                318767104 bytes
Database Buffers             872415232 bytes
Redo Buffers                   7643136 bytes
Automatic instance createdcontents of Memory Script:
{
# set requested point in time
set until  scn 5930910;
# restore the controlfile
restore clone controlfile;# mount the controlfile
sql clone 'alter database mount clone database';# archive current online log
sql 'alter system archive log current';
# resync catalog
resync catalog;
}
executing Memory Scriptexecuting command: SET until clauseStarting restore at 21-DEC-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=180 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=181 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=22 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=182 device type=DISKchannel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u02/fra/ORCLCDB/autobackup/2019_12_21/o1_mf_s_1027639956_gzwgrnmc_.bkp
channel ORA_AUX_DISK_1: piece handle=/u02/fra/ORCLCDB/autobackup/2019_12_21/o1_mf_s_1027639956_gzwgrnmc_.bkp tag=TAG20191221T233236
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/ORCLCDB/controlfile/o1_mf_gzwhwyq2_.ctl
Finished restore at 21-DEC-19sql statement: alter database mount clone databasesql statement: alter system archive log currentstarting full resync of recovery catalog
full resync completecontents of Memory Script:
{
# set requested point in time
set until  scn 5930910;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  9 to new;
set newname for clone datafile  1 to new;
set newname for clone datafile  11 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  10 to new;
set newname for clone tempfile  1 to new;
set newname for clone tempfile  3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  9, 1, 11, 4, 3, 10;switch clone datafile all;
}
executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed tempfile 1 to /tmp/ORCLCDB/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 21-DEC-19
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4channel ORA_AUX_DISK_1: restoring datafile 00009
input datafile copy RECID=9 STAMP=1026594967 file name=/u02/fra/backups/ORCLCDB_20191209_25uj163u
destination for restore of datafile 00009: /tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: restoring datafile 00001
input datafile copy RECID=7 STAMP=1026594939 file name=/u02/fra/backups/ORCLCDB_20191209_21uj161h
destination for restore of datafile 00001: /tmp/ORCLCDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_3: restoring datafile 00011
input datafile copy RECID=12 STAMP=1026594983 file name=/u02/fra/backups/ORCLCDB_20191209_27uj164f
destination for restore of datafile 00011: /tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_4: restoring datafile 00004
input datafile copy RECID=8 STAMP=1026594939 file name=/u02/fra/backups/ORCLCDB_20191209_24uj161h
destination for restore of datafile 00004: /tmp/ORCLCDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_3: copied datafile copy of datafile 00011, elapsed time: 00:00:03
output file name=/tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_undotbs1_gzwhxfq2_.dbf RECID=16 STAMP=1027641135
channel ORA_AUX_DISK_3: restoring datafile 00003
input datafile copy RECID=5 STAMP=1026594939 file name=/u02/fra/backups/ORCLCDB_20191209_22uj161h
destination for restore of datafile 00003: /tmp/ORCLCDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00009, elapsed time: 00:00:39
output file name=/tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_system_gzwhxfp5_.dbf RECID=17 STAMP=1027641163
channel ORA_AUX_DISK_1: restoring datafile 00010
input datafile copy RECID=6 STAMP=1026594939 file name=/u02/fra/backups/ORCLCDB_20191209_23uj161h
destination for restore of datafile 00010: /tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_4: copied datafile copy of datafile 00004, elapsed time: 00:00:46
output file name=/tmp/ORCLCDB/datafile/o1_mf_undotbs1_gzwhxfqh_.dbf RECID=18 STAMP=1027641175
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00010, elapsed time: 00:01:15
output file name=/tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_sysaux_gzwhyn4t_.dbf RECID=19 STAMP=1027641245
channel ORA_AUX_DISK_3: copied datafile copy of datafile 00003, elapsed time: 00:02:01
output file name=/tmp/ORCLCDB/datafile/o1_mf_sysaux_gzwhxjxo_.dbf RECID=20 STAMP=1027641251
channel ORA_AUX_DISK_2: copied datafile copy of datafile 00001, elapsed time: 00:02:14
output file name=/tmp/ORCLCDB/datafile/o1_mf_system_gzwhxfpf_.dbf RECID=21 STAMP=1027641260
Finished restore at 21-DEC-19datafile 9 switched to datafile copy
input datafile copy RECID=22 STAMP=1027641267 file name=/tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_system_gzwhxfp5_.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=23 STAMP=1027641267 file name=/tmp/ORCLCDB/datafile/o1_mf_system_gzwhxfpf_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=24 STAMP=1027641267 file name=/tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_undotbs1_gzwhxfq2_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=25 STAMP=1027641267 file name=/tmp/ORCLCDB/datafile/o1_mf_undotbs1_gzwhxfqh_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=26 STAMP=1027641267 file name=/tmp/ORCLCDB/datafile/o1_mf_sysaux_gzwhxjxo_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=27 STAMP=1027641267 file name=/tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_sysaux_gzwhyn4t_.dbfcontents of Memory Script:
{
# set requested point in time
set until  scn 5930910;
# online the datafiles restored or switched
sql clone 'ORCLPDB1' "alter database datafile9 online";
sql clone "alter database datafile  1 online";
sql clone 'ORCLPDB1' "alter database datafile11 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone 'ORCLPDB1' "alter database datafile10 online";
# recover and open database read only
recover clone database tablespace  "ORCLPDB1":"SYSTEM", "SYSTEM", "ORCLPDB1":"UNDOTBS1", "UNDOTBS1", "SYSAUX", "ORCLPDB1":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile  9 onlinesql statement: alter database datafile  1 onlinesql statement: alter database datafile  11 onlinesql statement: alter database datafile  4 onlinesql statement: alter database datafile  3 onlinesql statement: alter database datafile  10 onlineStarting recover at 21-DEC-19
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4starting media recoveryarchived log for thread 1 with sequence 32 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_10/o1_mf_1_32_gyzdmsnf_.arc
archived log for thread 1 with sequence 33 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_10/o1_mf_1_33_gyzdmsq7_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_11/o1_mf_1_1_gz1vj598_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_12/o1_mf_1_2_gz43g4bn_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_12/o1_mf_1_3_gz44nv7h_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_16/o1_mf_1_4_gzh03pow_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_17/o1_mf_1_5_gzj8gmdf_.arc
archived log for thread 1 with sequence 6 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_17/o1_mf_1_6_gzjn8zgh_.arc
archived log for thread 1 with sequence 7 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_18/o1_mf_1_7_gzmqy0d2_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_19/o1_mf_1_8_gzp0dowp_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_19/o1_mf_1_9_gzp223sd_.arc
archived log for thread 1 with sequence 10 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_20/o1_mf_1_10_gzr2jvqm_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_20/o1_mf_1_11_gzr6lkst_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_20/o1_mf_1_12_gzs195gz_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_20/o1_mf_1_13_gzs4n468_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_21/o1_mf_1_14_gztw78kt_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_21/o1_mf_1_15_gzvcgn0y_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_21/o1_mf_1_16_gzw4k54v_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_21/o1_mf_1_17_gzwh84to_.arc
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_10/o1_mf_1_32_gyzdmsnf_.arc thread=1 sequence=32
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_10/o1_mf_1_33_gyzdmsq7_.arc thread=1 sequence=33
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_11/o1_mf_1_1_gz1vj598_.arc thread=1 sequence=1
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_12/o1_mf_1_2_gz43g4bn_.arc thread=1 sequence=2
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_12/o1_mf_1_3_gz44nv7h_.arc thread=1 sequence=3
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_16/o1_mf_1_4_gzh03pow_.arc thread=1 sequence=4
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_17/o1_mf_1_5_gzj8gmdf_.arc thread=1 sequence=5
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_17/o1_mf_1_6_gzjn8zgh_.arc thread=1 sequence=6
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_18/o1_mf_1_7_gzmqy0d2_.arc thread=1 sequence=7
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_19/o1_mf_1_8_gzp0dowp_.arc thread=1 sequence=8
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_19/o1_mf_1_9_gzp223sd_.arc thread=1 sequence=9
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_20/o1_mf_1_10_gzr2jvqm_.arc thread=1 sequence=10
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_20/o1_mf_1_11_gzr6lkst_.arc thread=1 sequence=11
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_20/o1_mf_1_12_gzs195gz_.arc thread=1 sequence=12
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_20/o1_mf_1_13_gzs4n468_.arc thread=1 sequence=13
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_21/o1_mf_1_14_gztw78kt_.arc thread=1 sequence=14
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_21/o1_mf_1_15_gzvcgn0y_.arc thread=1 sequence=15
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_21/o1_mf_1_16_gzw4k54v_.arc thread=1 sequence=16
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_21/o1_mf_1_17_gzwh84to_.arc thread=1 sequence=17
media recovery complete, elapsed time: 00:01:32
Finished recover at 21-DEC-19sql statement: alter database open read onlycontents of Memory Script:
{
sql clone 'alter pluggable database  ORCLPDB1 open read only';
}
executing Memory Scriptsql statement: alter pluggable database  ORCLPDB1 open read onlycontents of Memory Script:
{sql clone "create spfile from memory";shutdown clone immediate;startup clone nomount;sql clone "alter system set  control_files =''/tmp/ORCLCDB/controlfile/o1_mf_gzwhwyq2_.ctl'' comment=''RMAN set'' scope=spfile";shutdown clone immediate;startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Scriptsql statement: create spfile from memorydatabase closed
database dismounted
Oracle instance shut downconnected to auxiliary database (not started)
Oracle instance startedTotal System Global Area    1207959448 bytesFixed Size                     9133976 bytes
Variable Size                318767104 bytes
Database Buffers             872415232 bytes
Redo Buffers                   7643136 bytessql statement: alter system set  control_files =   ''/tmp/ORCLCDB/controlfile/o1_mf_gzwhwyq2_.ctl'' comment= ''RMAN set'' scope=spfileOracle instance shut downconnected to auxiliary database (not started)
Oracle instance startedTotal System Global Area    1207959448 bytesFixed Size                     9133976 bytes
Variable Size                318767104 bytes
Database Buffers             872415232 bytes
Redo Buffers                   7643136 bytessql statement: alter database mount clone databasecontents of Memory Script:
{
# set requested point in time
set until  scn 5930910;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  16 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  16;switch clone datafile all;
}
executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEStarting restore at 21-DEC-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=182 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=18 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=183 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=22 device type=DISKchannel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00016 to /tmp/KVTA_PITR_ORCLPDB1_ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_testts_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/fra/backups/ORCLCDB_20191221_2muk12kg
channel ORA_AUX_DISK_1: piece handle=/u02/fra/backups/ORCLCDB_20191221_2muk12kg tag=TAG20191221T233231
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 21-DEC-19datafile 16 switched to datafile copy
input datafile copy RECID=29 STAMP=1027641447 file name=/tmp/KVTA_PITR_ORCLPDB1_ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_testts_gzwj7661_.dbfcontents of Memory Script:
{
# set requested point in time
set until  scn 5930910;
# online the datafiles restored or switched
sql clone 'ORCLPDB1' "alter database datafile16 online";
# recover and open resetlogs
recover clone database tablespace  "ORCLPDB1":"TESTTS", "ORCLPDB1":"SYSTEM", "SYSTEM", "ORCLPDB1":"UNDOTBS1", "UNDOTBS1", "SYSAUX", "ORCLPDB1":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile  16 onlineStarting recover at 21-DEC-19
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4starting media recoveryarchived log for thread 1 with sequence 17 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_21/o1_mf_1_17_gzwh84to_.arc
archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_21/o1_mf_1_17_gzwh84to_.arc thread=1 sequence=17
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-DEC-19database openedcontents of Memory Script:
{
sql clone 'alter pluggable database  ORCLPDB1 open';
}
executing Memory Scriptsql statement: alter pluggable database  ORCLPDB1 opencontents of Memory Script:
{
# create directory for datapump import
sql 'ORCLPDB1' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp''";
# create directory for datapump export
sql clone 'ORCLPDB1' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp''";
}
executing Memory Scriptsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''Performing export of tables...EXPDP> Starting "SYS"."TSPITR_EXP_kvta_camu":EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATAEXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSEXPDP> Processing object type TABLE_EXPORT/TABLE/TABLEEXPDP> . . exported "SYSTEM"."TESTTAB"                          11.03 KB      49 rowsEXPDP> Master table "SYS"."TSPITR_EXP_kvta_camu" successfully loaded/unloadedEXPDP> ******************************************************************************EXPDP> Dump file set for SYS.TSPITR_EXP_kvta_camu is:EXPDP>   /tmp/tspitr_kvta_25139.dmpEXPDP> Job "SYS"."TSPITR_EXP_kvta_camu" successfully completed at Sat Dec 21 23:59:01 2019 elapsed 0 00:00:54
Export completedcontents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory ScriptOracle instance shut downPerforming import of tables...IMPDP> Master table "SYS"."TSPITR_IMP_kvta_olFe" successfully loaded/unloadedIMPDP> Starting "SYS"."TSPITR_IMP_kvta_olFe":IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLEIMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATAIMPDP> . . imported "SYSTEM"."TESTTAB"                          11.03 KB      49 rowsIMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSIMPDP> Job "SYS"."TSPITR_IMP_kvta_olFe" successfully completed at Sat Dec 21 23:59:29 2019 elapsed 0 00:00:11
Import completedRemoving automatic instance
Automatic instance removed
auxiliary instance file /tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_temp_gzwj4o77_.tmp deleted
auxiliary instance file /tmp/ORCLCDB/datafile/o1_mf_temp_gzwj4l8d_.tmp deleted
auxiliary instance file /tmp/KVTA_PITR_ORCLPDB1_ORCLCDB/onlinelog/o1_mf_3_gzwj79rh_.log deleted
auxiliary instance file /tmp/KVTA_PITR_ORCLPDB1_ORCLCDB/onlinelog/o1_mf_2_gzwj79og_.log deleted
auxiliary instance file /tmp/KVTA_PITR_ORCLPDB1_ORCLCDB/onlinelog/o1_mf_1_gzwj79m4_.log deleted
auxiliary instance file /tmp/KVTA_PITR_ORCLPDB1_ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_testts_gzwj7661_.dbf deleted
auxiliary instance file /tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_sysaux_gzwhyn4t_.dbf deleted
auxiliary instance file /tmp/ORCLCDB/datafile/o1_mf_sysaux_gzwhxjxo_.dbf deleted
auxiliary instance file /tmp/ORCLCDB/datafile/o1_mf_undotbs1_gzwhxfqh_.dbf deleted
auxiliary instance file /tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_undotbs1_gzwhxfq2_.dbf deleted
auxiliary instance file /tmp/ORCLCDB/datafile/o1_mf_system_gzwhxfpf_.dbf deleted
auxiliary instance file /tmp/ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile/o1_mf_system_gzwhxfp5_.dbf deleted
auxiliary instance file /tmp/ORCLCDB/controlfile/o1_mf_gzwhwyq2_.ctl deleted
auxiliary instance file tspitr_kvta_25139.dmp deleted
Finished recover at 21-DEC-19

验证及清理：

SQL> select count(*) from system.testtab;COUNT(*)
----------49SQL> drop tablespace testts including contents and datafiles;Tablespace dropped.

从以上过程可知，表的时间点恢复分为几个步骤，RMAN将以上过程自动化了。：

1. 创建辅助实例
2. 恢复SYSTEM, SYSAUX, and UNDO到指定时间点
3. 恢复包含此表的表空间的数据文件到指定时间点
4. 使用DataPump从辅助实例导出表
5. 使用DataPump导入表
6. 删除辅助实例

临时目录下和辅助实例相关的文件如下：

# ls -lR KVTA_PITR_ORCLPDB1_ORCLCDB/
KVTA_PITR_ORCLPDB1_ORCLCDB/:
total 0
drwxr-x---. 3 oracle oinstall 22 Dec 21 23:57 974B2FC950D006CEE0530100007FB806
drwxr-x---. 2 oracle oinstall  6 Dec 21 23:59 onlinelogKVTA_PITR_ORCLPDB1_ORCLCDB/974B2FC950D006CEE0530100007FB806:
total 0
drwxr-x---. 2 oracle oinstall 40 Dec 21 23:59 datafileKVTA_PITR_ORCLPDB1_ORCLCDB/974B2FC950D006CEE0530100007FB806/datafile:
total 102408
-rw-r-----. 1 oracle oinstall 104865792 Dec 21 23:57 o1_mf_undo_1_gzwj7v56_.dbfKVTA_PITR_ORCLPDB1_ORCLCDB/onlinelog:
total 0

描述和使用闪回数据归档

闪回数据归档（FBDA，FlashBack Data Archive）可保证闪回到任意时间，并可在数据过期后清理。后台进程名也称为FBDA。
闪回数据归档需要单独的表空间建立归档，并将指定的表加入归档，并指定有效期。
示例：

connect sys@orclpdb1 as sysdba
create tablespace fda datafile 'fda1.dbf' size 10m;
create flashback archive fla1 tablespace fda retention 5 year;
-- flashback archive 可将表添加到归档中，flashback archive administrator权限可创建，删除和更改归档
alter flashback archive fla1 modify tablespace fda quota 1g;
grant dba to fbdauser identified by fbdauser;
grant flashback archive on fla1 to fbdauser;
connect fbdauser/fbdauser@orclpdb1
create table t1 as select * from all_users;
-- 将表加入归档
alter table t1 flashback archive fla1;
alter table hr.employees flashback archive fla1;SQL> select object_name, object_type from user_objects;
OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
T1                   TABLE-- 下面的命令需要几分钟后才会有结果输出，可是我这过了很久也没有结果
SQL> select segment_name, segment_type from dba_segments where tablespace_name='FDA';drop table t1;
drop user fbdauser cascade;
SQL> select count(*) from t1 as of timestamp(sysdate - 5/1440);COUNT(*)
----------50
SQL> select * from t1;no rows selected
-- 问题是是否可以利用FBDA闪回表呢？我失败了：
SQL> alter table t1 enable row movement;Table altered.
SQL> flashback table t1 to timestamp to_timestamp(sysdate - 30/1440);
flashback table t1 to timestamp to_timestamp(sysdate - 30/1440)*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed-- 以下命令均失败
SQL> drop table t1;
drop table t1*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked tableSQL> drop tablespace fda including contents and datafiles;
drop tablespace fda including contents and datafiles
*
ERROR at line 1:
ORA-55641: Cannot drop tablespace used by Flashback Data ArchiveSQL> connect sys/Welcome1@orclpdb1 as sysdba
Connected.
SQL> drop user fbdauser cascade;
drop user fbdauser cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table
"FBDAUSER"."SYS_FBA_TCRV_73929"-- 最终还是恢复成功了，计算好时间就行
SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';Session altered.SQL> select object_name, created, last_ddl_time from dba_objects where owner='FBDAUSER' and object_name='T1';OBJECT_NAME              CREATED           LAST_DDL_TIME
------------------------ ----------------- -----------------
T1                       22-12-19 11:12:29 22-12-19 12:29:04SQL> flashback table t1 to timestamp to_timestamp('22-12-19 11:12:29', 'dd-mm-yy hh24:mi:ss');
flashback table t1 to timestamp to_timestamp('22-12-19 11:12:29', 'dd-mm-yy hh24:mi:ss')*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changedSQL> flashback table t1 to timestamp to_timestamp('22-12-19 11:13:29', 'dd-mm-yy hh24:mi:ss');Flashback complete.SQL> select count(*) from t1;COUNT(*)
----------50
-- 清理：
alter table fbdauser.t1 no flashback archive;
alter table hr.employees no flashback archive;
drop flashback archive fla1;
drop tablespace fda including contents and datafiles;
connect sys/Welcome1@orclpdb1 as sysdba
drop user fbdauser cascade

和FBDA相关的视图有3个：

SQL> select FLASHBACK_ARCHIVE_NAME, RETENTION_IN_DAYS, STATUS from DBA_FLASHBACK_ARCHIVE;FLASHBACK_ARCHIVE_NA RETENTION_IN_DAYS STATUS
-------------------- ----------------- -------
FLA1                              1825SQL> select FLASHBACK_ARCHIVE_NAME, TABLESPACE_NAME, QUOTA_IN_MB from DBA_FLASHBACK_ARCHIVE_TS;FLASHBACK_ARCHIVE_NA TABLESPACE_NAME      QUOTA_IN_MB
-------------------- -------------------- --------------------
FLA1                 FDA                  1024SQL> select * from DBA_FLASHBACK_ARCHIVE_TABLES;TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME       STATUS
---------- ---------- -------------------- ------------------------ -------------
T1         FBDAUSER   FLA1                 SYS_FBA_HIST_73929       ENABLED
EMPLOYEES  HR         FLA1                 SYS_FBA_HIST_73026       ENABLED

闪回数据库

闪回数据库的效果与不完全恢复类似，但实现不同。

闪回数据库架构

一旦开启，后台进程RVWR会将变化的数据块从数据库buffer cache中写到闪回buffer，然后冲刷到闪回日志。和redo log记录变化不同，闪回日志记录的是完整的数据块，另外也没有归档一说。
处于性能考虑，数据块的改变只是记录了子集而非全集，因此恢复时不同的数据块可恢复的时间点可能不一样，因此数据文件是不一致的，还需要借助日志（主要是归档，因此需要开启归档）恢复到指定时间点。
那和不完全恢复相比，闪回数据库的好处是什么？第一是速度，恢复速度和数据库的改变频度相关，而不完全恢复和数据库大小有关。第二是较简单。

配置闪回数据库

1. 确认数据库已开启归档

SQL> select log_mode from v$database;LOG_MODE
------------
ARCHIVELOG

3. 配置FRA的大小和位置

SQL> show parameter db_recovery;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/fra
db_recovery_file_dest_size           big integer 5G

5. 设置闪回保留期（分钟）

SQL> show parameter DB_FLASHBACK_RETENTION_TARGETNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

7. 启用闪回日志
   如果是PDB，需要先开启local undo :

SQL> shutdown immediate;
Pluggable Database closed.
SQL> alter pluggable database orclpdb1 open read write restricted force;Pluggable database altered.SQL> alter pluggable database orclpdb1 enable force logging;Pluggable database altered.SQL> alter pluggable database orclpdb1 open read write force;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------3 ORCLPDB1                       READ WRITE NO
SQL> select pdb_name, force_logging, force_nologging from cdb_pdbs;PDB_NAME         FORCE_LOGGING                           FOR
---------------- --------------------------------------- ---
ORCLPDB1         YES                                     NO

SELECT con_id, tablespace_name  FROM   cdb_tablespaces WHERE  tablespace_name LIKE 'UNDO%' ORDER BY con_id
shutdown immediate;
startup upgrade;
alter database local undo on;
shutdown immediate;
startup;

然后：

-- 在CDB$ROOT中进行。
SQL> alter database flashback on;Database altered.SQL> alter session set container=orclpdb1;Session altered.SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-03001: unimplemented feature

监控闪回数据库

SQL> SELECT flashback_on FROM v$database;FLASHBACK_ON
------------------
YES
$ ps -ef|grep rvwr
oracle   11699     1  0 21:53 ?        00:00:00 ora_rvwr_ORCLCDB
SQL> select retention_target, flashback_size, oldest_flashback_time from v$flashback_database_log;SQL> select retention_target, flashback_size, oldest_flashback_time from v$flashback_database_log;RETENTION_TARGET FLASHBACK_SIZE OLDEST_FLASHBACK_
---------------- -------------- -----------------1440      419430400 22-12-19 21:53:16SQL> select end_time, flashback_data, db_data, redo_data from v$flashback_database_stat;END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA
----------------- -------------- ---------- ----------
22-12-19 22:29:54       28844032   34078720   33195008
SQL> select * from v$sgastat where name = 'flashback generation buff';POOL           NAME                            BYTES     CON_ID
-------------- -------------------------- ---------- ----------
shared pool    flashback generation buff     7656920          1

使用闪回数据库

可以被SQL*Plus和RMAN使用，方法一样。

1. shutdown数据库
2. mount数据库
3. 闪回到时间点，SCN或log sequence number
4. RESETLOGS方式打开数据库

使用SQL*Plus闪回

SQL> create table t1 as select * from user_tables;Table created.SQL> select count(*) from t1;COUNT(*)
----------1551SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';Session altered.SQL> select sysdate from dual;SYSDATE
-----------------
22-12-19 22:55:50
SQL> shutdown abort;
Pluggable Database closed.
SQL> flashback pluggable database orclpdb1 to timestamp to_timestamp('22-12-19 22:55:50','dd-mm-yy hh24:mi:ss');Flashback complete.
-- 在只读状态下确认数据在
SQL> alter database open read only;Database altered.SQL> select count(*) from t1;COUNT(*)
----------1551
SQL> alter session set container=cdb$root;Session altered.
-- 确认后，最后才open resetlogs
SQL> alter pluggable database orclpdb1 open resetlogs;Pluggable database altered.
SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 ORCLPDB1                       READ WRITE NO
SQL> alter session set container=orclpdb1;Session altered.SQL> select count(*) from t1;COUNT(*)
----------1551

使用RMAN闪回

SQL*Plus闪回数据库时，只支持时间戳和SCN，RMAN多支持一个log序列号。

SQL>  select flashback_on from v$database;FLASHBACK_ON
------------------
YESSQL> create table t1 as select * from user_tables;Table created.SQL> select to_char(sysdate, 'yyyy-mm-dd:hh24:mi:ss') as t from dual;T
-------------------
2019-12-22:23:05:35SQL> truncate table t1;Table truncated.-- 以下恢复手段均不可行
SQL> select * from t1 as of timestamp to_timestamp('2019-12-22:23:05:35', 'yyyy-mm-dd:hh24:mi:ss');
select * from t1 as of timestamp to_timestamp('2019-12-22:23:05:35', 'yyyy-mm-dd:hh24:mi:ss')*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changedSQL> flashback table t1 to timestamp to_timestamp('2019-12-22:23:05:35', 'yyyy-mm-dd:hh24:mi:ss');
flashback table t1 to timestamp to_timestamp('2019-12-22:23:05:35', 'yyyy-mm-dd:hh24:mi:ss')*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYSSQL> flashback table t1 to before drop;
flashback table t1 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BINSQL> flashback pluggable database orclpdb1 to timestamp to_timestamp('2019-12-22:23:05:35', 'yyyy-mm-dd:hh24:mi:ss');Flashback complete.
SQL> alter pluggable database open resetlogs;Pluggable database altered.SQL> select count(*) from t1;COUNT(*)
----------1551

总结

A. Flashback Table
B. Flashback Transaction Query
C. Flashback Query
D. Flashback Version Query
以上4中依赖undo.
E. Flashback Drop - 依赖recycle bin
F. Flashback Database - 依赖FRA中的flashback logging
G. Flashback Data Archive - 依赖专门的表空间

normal restore point和guaranteed restore point的区别：https://community.oracle.com/thread/710362?start=0&tstart=0

参考

1. https://oracle-base.com/articles/12c/multitenant-rman-backup-recovery-cdb-and-pdb-12cr1#tablespace-and-datafile-backups
2. https://oracle-base.com/articles/12c/multitenant-flashback-pdb-12cr2
3. https://geodatamaster.com/tag/ora-03001-unimplemented-feature/

这篇关于OCA/OCP Oracle 数据库12c考试指南读书笔记：第26章:Flashback的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---