flashback database闪回数据库

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;