1、
以用户
sys as DBA
的角色登录到数据库中
2、检查是否启用了Flashback功能:
select flashback_on from v$database; |
如果结果为:Yes,可继续恢复
3、通过如下语句查询可回滚的时间点(在下面用到的时间都是此SQL查询出的时间点):
select sequence#, first_change#, next_change#, to_char(first_time, 'YYYY-MM-DD HH24:MI:SS') FROM v$log_history order by first_time desc |
太久远的时间点,可能已经被冲了,无法在进行恢复。
4、Flashback delete,闪回表的某个时间点:
select * from fact_prod_delivery AS OF TIMESTAMP TO_TIMESTAMP('2011-11-25 02:05:27', 'YYYY-MM-DD HH24:MI:SS') |
5、Flashback drop,闪回数据表被drop的操作:
a)、查找到删除的表:
select original_name,object_name from recyclebin; |
b)、还原删除的表:
flashback table "BIN$vYuv+g9fTi2exYP9X2048Q==$0" to before drop; |
或者:
flashback table a to before drop rename to B; |
上面两种方法都会将表从recyclebin中清除。也可如下操作:
CREATE TABLE fact_prod_delivery_temp AS select * from "BIN$sovAz7py4eLgQ6wTAR/h4g==$0"; |
6、Flash Query,恢复程序包等:
a)、
通过如下
SQL,
查询出程序包对应的
ID
,分别是
PACKAGE
和
PACKAGE BODY
对应的
ID:
select AO.object_id,AO.object_type from all_objects ao where 1=1 and ao.owner='TBDW' and ao.object_name=upper('dw_load_delivery_etl') |
b)、查询某个时间点是的代码:
SELECT source FROM source$ AS OF TIMESTAMP TO_TIMESTAMP('2011-11-24 00:12:58', 'YYYY-MM-DD HH24:MI:SS') where obj# = 4840653; |
可以pl/sql的report query查询,便于代码拷贝。