Oracle通过闪回查询(flashback)恢复数据-技术学堂

在以下情况下经常会用到Oracle的闪回查询功能，以撤消对数据库的错误操作：

接下来给两个提醒：

闪回需要在mount状态下进行，可以指定Timestamp/SCN/Sequence进行闪回。

SYS@ORCL>startup mount;

flashback database to timestamp to_timestamp('2016-08-08 15:00:00','yyyy-mm-dd hh24:mi:ss');

# 如果数据恢复的不够理想，可以关闭数据库继续进行恢复

# alter database open read only;

使用时间戮：

1	flashback table table_name to timestamp to_timestamp('2016-08-11 18:00:00','yyyy-mm-dd hh24:mi:ss');

create table flashback_1 as

select * from table_name as of timestamp to_timestamp('2016-08-11 18:00:00','yyyy-mm-dd hh24:mi:ss');

select src from flashback_1;

使用版本：

1	select count(*) from lm_mtrp_syn_channelinfo as of scn 708000000;

获取丢失的行：

SELECT * FROM table_name

AS OF TIMESTAMP TO_TIMESTAMP('2016-08-08 09:30:00', 'YYYY-MM-DD HH:MI:SS')

WHERE field_name = 'field_value';

还原丢失的行：

INSERT INTO table_name (

SELECT * FROM table_name

AS OF TIMESTAMP TO_TIMESTAMP('2016-08-08 09:00:00', 'YYYY-MM-DD HH:MI:SS')

WHERE field_name = 'field_value'

);

CREATE VIEW hour_ago AS

SELECT * FROM table_name AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);

INSERT INTO table_name

(SELECT * FROM table_name AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE))

MINUS

SELECT * FROM table_name);

数据恢复后重新开启数据库：

1	alter database open resetlogs;

Oracle通过闪回查询(flashback)恢复数据