oracle的闪回技术

闪回缓冲区-闪回日志

mount

alter database flashback on;

select dbid,name,flashback_on from v$database;

show parameter db_recovery_file_dest;

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /backup
db_recovery_file_dest_size           big integer 4G
SQL>

查看闪回缓冲区的路径

可以闪回的到1440分钟之前的数据，就是一天。

SQL> show parameter db_flashback_retention;            闪回记忆      

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

alter system set db_flashback_retention_target=2880 scope=both;

SQL> show parameter db_flashback_retention;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     2880

可以查询表空间是否被闪回保护。

select name,flashback_on from v$tablespace;

关闭对某个表的闪回保护

SQL> select name,flashback_on from v$tablespace;

NAME                           FLA
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES
TEMP                           YES
BANK_TBS                       YES

已选择6行。

SQL> alter tablespace bank_tbs flashback off;

表空间已更改。

SQL> select name,flashback_on from v$tablespace;

NAME                           FLA
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES
TEMP                           YES
BANK_TBS                       NO

闪回数据的方法

查看当前时间。

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
05-8月 -16 04.39.13.635557 下午 +08:00

1、rman

RMAN> flashback database to time=to_date('2016-8-5 16:39:00','YYYY-MM-DD HH23:MI:SS');    回到时间点f

alter database open resetlogs;

flashback database to scn XXX;