| 作用 | 举例 | 注意事项 |
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 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可用,取决定于闪回区大小。 |
关于flashback
最新推荐文章于 2019-04-26 10:38:10 发布