ORACLE FlashBack操作

######状态检查################

 

archive log list

 

 

 

set heading on

set linesize 800pages 0

col value for a150

col name for a30

select name, value

  fromv$parameter

 where name in ('db_name',

               'db_recovery_file_dest',

               'db_recovery_file_dest_size',

               'db_flashback_retention_target')

 

 

 

set line 135

col flashback_on fora8

col force_logging fora16

col db_name for a8

select name, flashback_on,force_logging from V$database;

 

 

######开启FlashBack#############

 

shutdown immediate

 

startup mount

 

alter system setdb_recovery_file_dest_size=50G scope=both

 

alter system setdb_recovery_file_dest='/dbfile/flashbackarea' scope=both

 

alter databaseflashback on

 

alter database open

 

#####关闭FlashBack###############

 

shutdown immediate

 

startup mount

 

alter databaseflashback off

 

#####执行FlashBack###############

 

shutdown immediate

 

startup mount

 

FLASHBACK DATABASE TO SCN 855949753469;（也可以是时间）

 

alter database open

 

##########还可以恢复单表###########################

 

或scn

 

flashback table aaa.aaa to timestampto_timestamp('2017/04/11 14:23:18','yyyy/mm/dd hh24:mi:ss');

 

 

 

 select * from aaa.aaa as of timestampto_timestamp('2017/04/11 14:23:18','yyyy/mm/dd hh24:mi:ss');

 

 

或to time=to_date();

 

####闪回删除表##################

 

Flashback table aaa to before drop;

 

####查询回退信息########################

Select * from v$flashback_database_log;

 

Select to_char(oldest_flashback_time, 'YYYY-MM-DDhh24:mi:ss') from v$flashback_database_log;