1.设置相关参数
<pre name="code" class="sql">SQL> alter system set db_recovery_file_dest_size=5g;
SQL> alter system set db_recovery_file_dest='/u01/flash_recovery_area';
2.先开启归档再开启闪回
SQL> shutdown immediate
SQL> startup mount
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
3.保留时间参数,默认是24小时,可修改
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> alter system set db_flashback_retention_target=2880;
4.打开数据库,查看可闪回的最早时间,并可进行监控
SQL> alter database open;
SQL> SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'YYYY-MM-DD:HH24:MI:SS') OLDEST FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST
-------------------- -------------------
3104049 2015-01-27:10:40:19
SQL> select name,space_limit,space_used,space_reclaimable from v$recovery_file_dest;
5.可进行闪回
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3124979
SQL> shutdown abort
SQL> startup mount;
SQL> flashback database to scn 3104049;
SQL> alter database open read only; #只读打开,可用于将库回到原来状态
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3104049
SQL> shutdown abort
SQL> startup mount;
SQL> recover database; #恢复数据
SQL> alter database open;<span style="white-space:pre"> </span>#打开后回到原来状态<pre name="code" class="sql">SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3124979
说明:
1)设置闪回区时,要先设置大小,再设置路径,否则报错
SQL> alter system set db_recovery_file_dest='/u01/flash_recovery_area';
alter system set db_recovery_file_dest='/u01/flash_recovery_area'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE
2)闪回时可以指定scn、timestamp或restore point(要手动创建)闪回到相应的时间点
3)闪回后,如果还要将库回到原来状态,要以read only打开;如果使用resetlogs打开闪回后的库,就不能回到原来状态
4)闪回可用于恢复误删数据、过去某个时间的数据等,闪回到某个时间点read only打开后导出数据,再回到原来状态导入数据
5)使用还原点或时间点还原的语句
flashback database to restore point my_restore_point;
flashback database to timestamp sysdate-15/1440;
6)创建还原点并查看
create restore point my_restore_point1;
select name, scn, time from v$restore_point;