关于flashback

作用

举例

注意事项

flashback query

query 查询这张表曾经的某个时刻

select * from XX as of timestamp sysdate-5/1440;

select * from names as of timestamp to_timestamp('2015-03-30 21:00:00','yyyy-mm-dd hh24:mi:ss');

select * from XX as of SCN 3803277;

使用闪回查询 DML? "UPDATE employees

?? SET salary = (SELECT salary

?? ? ? ? ? ? ? ? ? FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2005-05-04 11:00:00','yyyy-mm-dd hh24:mi:ss')

? ? ? ? ? ? ? ? ? WHERE employee_id = 200)

flashback table

DML 让表回到曾经的某个时刻

1.允许行移动

alter table names enable row movement;

2. 闪回表

flashback table names to timestamp to_timestamp('2015-03-31 11:03:33','yyyy-mm-dd hh24:mi:ss')

如果有外键约束，需要同时闪回两张表

flashback table names,grade to timestamp to_timestamp('2015-03-31 11:03:33','yyyy-mm-dd hh24:mi:ss')

flashback table names,grade to scn 6666666 enable triggers;

可能由于其他原因失败：

1.在删除和闪回之间重用了键值，会报主键约束

2.没有足够的undo信息，报08180

3.其他用户锁定了闪回作用的行，报00054

4.闪回不能跨越DDL，报01466

flashback version query

query 查询一行过去的所有变化版本

查询过去某行变更的所有版本（flashback? version query）

SELECT name,sex,versions_xid,versions_startscn,versions_endscn,versions_starttime+0,versions_endtime+0,versions_operation FROM names versions between scn minvalue and maxvalue where name like 'mdspdb%';

SELECT name,versions_operation FROM names versions between timestamp(systimestamp-2.5/24) and systimestamp where name like 'mdspdb%';

不能用于外部表，临时表或v$视图，因为这些对象不产生undo

flashback transaction query

检索一个事务的所有撤销数据，不考虑它影响多少对象。

前期准备：

1.权限

grant select any transaction to dropper;

2.开启补充日志（重启）

alter database ADD supplemental log data;

3.闪回版本查询

SELECT name,versionalter database add supplemental log data;s_startscn,versions_operation FROM dropper.names versions between scn minvalue and maxvalue;

4.根据所需要的版本查询undo_sql

SELECT *from flashback_transaction_query where logon_user='DROPPER'? where xid=hextoraw('04001F00A6100000');

5 执行闪回事务

6.关闭补充日志（重启）

alter database drop supplemental log data;

flashback transaction（backout）

DML

exec dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('04001F00A6100000'));

flashback data archive

1.创建用于闪回归档的表空间

create tablespace fda datafile 'fda1.dbf' size 10m;

2.创建闪回归档fla1保留7天

create flashback archive fla1 tablespace fda retention 7 year;

3.创建受保护的用户?

create user fbdauser identified by fbdauser;

grant flashback archive administer to fbdauser;

grant flashback archive on fla1 to fbdauser;

4.连接到用户

conn fbdauser/fbdauser

5.创建一张表

create table t1 as select * from all_users;

6.将表更改为闪回归档模式

alter table t1 flashback archive fla1;

(alter table fbdauser.t1 no flashback archive;)

7.查询对象

FBDAUSER@VERO> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

-------------------------------------------------------------------------------------------------------------------------------- -------------------

T1 TABLE

T2 TABLE

SYS_FBA_DDL_COLMAP_79019 TABLE

SYS_FBA_HIST_79019 TABLE PARTITION

SYS_FBA_HIST_79019 TABLE

SYS_FBA_TCRV_79019 TABLE

SYS_FBA_TCRV_IDX_79019 INDEX

7 rows selected.

FBDAUSER@VERO> select segment_name,segment_type from dba_segments where tablespace_name='FDA';

SEGMENT_NAME ? SEGMENT_TYPE

--------------------------------------------------------------------------------- ------------------

SYS_FBA_DDL_COLMAP_79019 ? TABLE

SYS_FBA_TCRV_79019 ? TABLE

SYS_FBA_HIST_79019 ? TABLE PARTITION

SYS_FBA_TCRV_IDX_79019 ? INDEX

FBDAUSER@VERO> alter table t1 drop column created;

Table altered.

FBDAUSER@VERO> truncate table t1;

Table truncated.

FBDAUSER@VERO> drop table t1;

drop table t1

?? ? ? ? ? *

ERROR at line 1:

ORA-55610: Invalid DDL statement on history-tracked table

SYS@VERO> 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_79019"

SYS@VERO> drop tablespace fda? including contents and datafiles;

drop tablespace fda? including contents and datafiles

*

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_79019"

解除闪回控制

SYS@VERO> alter table fbdauser.t1 no flashback? archive;

Table altered.

SYS@VERO> drop flashback archive fla1;

Flashback archive dropped.

此时可以删除闪回用户。

SYS@VERO> drop user fbdauser cascade;

User dropped.

SYS@VERO> drop tablespace fda? including contents and datafiles;

Tablespace dropped.

flashback database

相当于不完全恢复数据库

recovery database until time

1. 配置flashback database

1）设置为归档模式

2）mount下设置

db_recovery_file_dest,db_recovery_file_dest_size

db_flashback_retention_target=2880 scope=both;（分钟）

3）打开闪回日志记录：alter database flashback on;

4）打开数据库

2.监视闪回数据库

v$flashback_database_log

v$flashback_database_stat

3.使用闪回数据库

1）由于flashback并不是每个时间点都有，所以采用恢复到指定时间点方式更保险，可以通过查询flashback方式查看：

startup mount;

flashback database to timestamp to_timestamp('2015-03-15 20:03:18','yyyy-mm-dd hh24:mi:ss’);

flashback database to scn=2728665;

flashback database to sequence=2131 thread=1;

alter database open read only;

2) 由于期望可以恢复更多的数据，所以开始采用TPISP方式

startup mount;

recover database until time ‘2015-03-15 20:03:18’;

alter database open resetlogs;

当闪回操作完成，打开数据库：

1. read only模式用于验证目标时间是否准确

2. resetlogs操作才允许DML

不能将flashback database用于以下状况：

1.控制文件以及被restored或re-created

2.表空间已被drop

3.数据文件被resize过（必须offline数据文件）

4.使用TO BEFORE RESETLOGS用于闪回到最近一次resetlogs的时间点

flashback retention target不能绝对保证flashback可用，取决定于闪回区大小。