关于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,sex,versions_xid,versions_startscn,versions_endscn,versions_starttime+0,versions_endtime+0,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,sex,versions_xid,versionalter database add supplemental log data;s_startscn,versions_endscn,versions_starttime+0,versions_endtime+0,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 transactionbackout

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)设置为归档模式

2mount下设置

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.控制文件以及被restoredre-created

2.表空间已被drop

3.数据文件被resize过(必须offline数据文件)

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


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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值