[转]开启闪回以及闪回的四种原理

1、首先确认db_recovery_file_dest和db_recovery_file_dest_size 有值。 sys@TEST0910> show parameter recovery   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size           big integer 4122M db_recovery_file_dest 放闪回日志，默认路径/u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size 存放闪回日志空间的大小，默认4122M   2、开启闪回 sys@TEST0910> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. sys@TEST0910> startup mount ORACLE instance started.   Total System Global Area 2505338880 bytes Fixed Size                  2230952 bytes Variable Size             587203928 bytes Database Buffers         1895825408 bytes Redo Buffers               20078592 bytes Database mounted. sys@TEST0910> alter database flashback on;   Database altered.   sys@TEST0910> alter database open;   Database altered.   3、查询确认是否开启闪回 sys@TEST0910> select name,flashback_on from v$database;   NAME                                               FLASHBACK_ON -------------------------------------------------- ------------------ TEST0910                                           YES   闪回四种不同原理  

• flashback drop：利用recyelebin 回收站原理
• flashback table TEST10 to before drop [rename to test11];可以重命名

 

• flashback query ，依赖undo数据，先查询，在做insert
• insert into test10 select * from test10 as of timestamp to_timestamp('2013-09-23 11:52:06','yyyy-mm-dd hh24:mi:ss');

 

• flashback table 依赖undo块
• flashback table scott.test10 to scn 1952615
• insert into test10 select * from scott.test10 as of scn 1952615;

        

• flashback database DDL语句，依赖闪回区的闪回日志
• flashback database to timestamp to_timestamp('2013-09-23 15:09:52','yyyy-mm-dd hh24:mi:ss');时间减一秒。

 

• 闪回归档--create as

来源：http://blog.csdn.net/rlhua/article/details/11934831

flashback drop实验（sys用户的除外，它没有回收站）

1、查询原来表的数量 sys@TEST0910> select count(*) from scott.test10;     COUNT(*) ----------         14 2、误操作drop sys@TEST0910> drop table scott.test10;   Table dropped. 3、登陆scoot用户，查询回收站 sys@TEST0910> conn scott/tiger Connected.   scott@TEST0910> show recyclebin ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME ---------------- ------------------------------ ------------ ------------------- TEST10           BIN$5w/YlwePKa7gQwEAAH+tZw==$0 TABLE        2013-09-23:11:40:56 4、查询回收站里的内容 scott@TEST0910>  select * from "BIN$5w/YlwePKa7gQwEAAH+tZw==$0";              ------注意：此处是双引号        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7369 SMITH      CLERK           7902 17-DEC-80        800                    20       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30       7566 JONES      MANAGER         7839 02-APR-81       2975                    20       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10       7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20       7839 KING       PRESIDENT            17-NOV-81       5000                    10       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30       7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20       7900 JAMES      CLERK           7698 03-DEC-81        950                    30       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10   14 rows selected. 5、两种闪回方式 使用insert into TEST10 select * from "BIN$5w/YlwePKa7gQwEAAH+tZw==$0";  闪回 使用flashback table闪回 scott@TEST0910> flashback table TEST10 to before drop [rename to test11];可以重命名   Flashback complete.   scott@TEST0910> select count(*) from test10;     COUNT(*) ----------         14

flashback query实验（基于undo数据）

误操作DML语句，刚刚发生，undo没有被覆盖 1、查询原数据 11:52:06 scott@TEST0910> select count(*) from test10;     COUNT(*) ----------         14   11:52:06 这个时间点还有数据，稍后作为参考 2、误操作 11:52:19 scott@TEST0910> delete from test10;   14 rows deleted.   11:52:31 scott@TEST0910> commit;   Commit complete.   11:52:35 scott@TEST0910> select count(*) from test10;     COUNT(*) ----------          0 3、使用闪回查询 11:54:25 scott@TEST0910> select * from test10 as of timestamp to_timestamp('2013-09-23 11:52:06','yyyy-mm-dd hh24:mi:ss');        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7369 SMITH      CLERK           7902 17-DEC-80        800                    20       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30       7566 JONES      MANAGER         7839 02-APR-81       2975                    20       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10       7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20       7839 KING       PRESIDENT            17-NOV-81       5000                    10       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30       7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20       7900 JAMES      CLERK           7698 03-DEC-81        950                    30       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10   14 rows selected.   4、插入闪回查询的结果 11:54:44 scott@TEST0910> insert into test10 select * from test10 as of timestamp to_timestamp('2013-09-23 11:52:06','yyyy-mm-dd hh24:mi:ss');   14 rows created.   11:55:02 scott@TEST0910> commit;   Commit complete.   11:55:30 scott@TEST0910> select count(*) from test10;     COUNT(*) ----------         14    

flashback table实验（基于undo数据）

1、查询原数据 sys@TEST0910> select count(*) from test10;     COUNT(*) ----------         14   1 row selected. 2、查询数据库当前scn号，稍后作为恢复参考 sys@TEST0910> select checkpoint_change# from v$database;   CHECKPOINT_CHANGE# ------------------            1952615   1 row selected. 3、误操作 sys@TEST0910> delete from scott.test10;   14 rows deleted.   sys@TEST0910> commit;   Commit complete.   4、根据之前的scn号使用undo数据闪回查询 sys@TEST0910> select * from scott.test10 as of scn 1952615;        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7369 SMITH      CLERK           7902 17-DEC-80        800                    20       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30       7566 JONES      MANAGER         7839 02-APR-81       2975                    20       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10       7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20       7839 KING       PRESIDENT            17-NOV-81       5000                    10       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30       7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20       7900 JAMES      CLERK           7698 03-DEC-81        950                    30       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10   14 rows selected.   5、将数据还原 可以插入回去，如闪回查询那样：insert into test10 select * from scott.test10 as of scn 1952615; 另外一种，可以用闪回表的方式：flashback table scott.test10 to scn 1952615; 注意，闪回表时要开启 row movement，不然要报错。   sys@TEST0910> flashback table scott.test10 to scn 1952615; flashback table scott.test10 to scn 1952615                       * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled     sys@TEST0910> alter table scott.test10 enable row movement;   Table altered.   sys@TEST0910> flashback table scott.test10 to scn 1952615;   Flashback complete.   sys@TEST0910> select count(*) from scott.test10;     COUNT(*) ----------         14   1 row selected.

flashback database实验（使用lgmnr找出还原时间点）

过程：激活日志--删数据--查日志--logmnr--关库--不用转储--mount--flashback数据库（时间减一秒）--read only 开库看正确与否--关库--mount--开库resetlogs DDL语句的恢复：类似不完全恢复，可以恢复到某个时间点，依赖flashback log. logmnr依赖的是redo和归档。 flashback database一般用于ddl操作， dml用flashback query或flashback table。因为他们读undo块 ddl语句不生成undo块   闪回数据库有限制：比如解决不了media recovery等（硬盘坏了等）。还是备份最重要。 1、使用logmnr来记录时间点，首先必须设置有一下参数 show parameter utl; logmnr需要的路径   sys@TEST0910> show parameter utl   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ create_stored_outlines               string utl_file_dir                         string      /u01/app/oracle/utl   使用flashback database首先设置两个参数： db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size           big integer 4122M   sys@TEST0910> show parameter recovery   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_                                                  area db_recovery_file_dest_size           big integer 4122M     而且需要alter database flashback on ys@TEST0910> select name,flashback_on from v$database;   NAME                                               FLASHBACK_ON -------------------------------------------------- ------------------ TEST0910                                           YES   1 row selected.   2、查询原数据 sys@TEST0910> select count(*) from scott.test12;     COUNT(*) ----------         14   1 row selected.   3.激活logmnr，注意，必须有此步骤，不然logmnr出不来数据 sys@TEST0910> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;   Database altered.   4、误操作DDL操作 sys@TEST0910> truncate table scott.test12;   Table truncated.   sys@TEST0910> select count(*) from scott.test12;     COUNT(*) ----------          0   1 row selected.   5、查询当前日志组和归档日志 sys@TEST0910> select * from v$log;       GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------          1          1          7   52428800        512          1 YES INACTIVE               1977009 2013-09-23 15:04:56      1981677 2013-09-23 15:05:11          2          1          8   52428800        512          1 YES ACTIVE                 1981677 2013-09-23 15:05:11      1985831 2013-09-23 15:05:41          3          1          9   52428800        512          1 NO  CURRENT                1985831 2013-09-23 15:05:41   2.8147E+14   3 rows selected.   sys@TEST0910> select * from v$logfile;       GROUP# STATUS  TYPE    MEMBER                                             IS_ ---------- ------- ------- -------------------------------------------------- ---          2         ONLINE  /u01/app/oracle/oradata/test0910/redo02.log        NO          1         ONLINE  /u01/app/oracle/oradata/test0910/redo01.log        NO          3         ONLINE  /u01/app/oracle/oradata/test0910/redo03.log        NO   3 rows selected.   sys@TEST0910> select name from v$archived_log;   NAME ---------------------------------------------------------------------------------------------------- /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_6_94146rg7_.arc /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_7_941477xf_.arc /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_8_941485th_.arc   61 rows selected.     6、执行logmnr   EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', -    '/u01/app/oracle/utl', -     DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -    LOGFILENAME => '/u01/app/oracle/oradata/test0910/redo03.log', -    OPTIONS => DBMS_LOGMNR.NEW); EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -    LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_6_94146rg7_.arc', -    OPTIONS => DBMS_LOGMNR.ADDFILE);    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -    LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_7_941477xf_.arc', -    OPTIONS => DBMS_LOGMNR.ADDFILE);    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -    LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_8_941485th_.arc', -    OPTIONS => DBMS_LOGMNR.ADDFILE); EXECUTE DBMS_LOGMNR.START_LOGMNR( -    DICTFILENAME =>'/u01/app/oracle/utl/dictionary.ora',OPTIONS => -    DBMS_LOGMNR.DDL_DICT_TRACKING); EXECUTE DBMS_LOGMNR.END_LOGMNR;   logminer官方参考：http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#i1009063   以下为执行步骤 sys@TEST0910> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', - > '/u01/app/oracle/utl', - > DBMS_LOGMNR_D.STORE_IN_FLAT_FILE); LogMnr Dictionary Procedure started LogMnr Dictionary File Opened Procedure executed successfully - LogMnr Dictionary Created   PL/SQL procedure successfully completed.   sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - > LOGFILENAME => '/u01/app/oracle/oradata/test0910/redo03.log', - > OPTIONS => DBMS_LOGMNR.NEW);   PL/SQL procedure successfully completed.   sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - > LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_8_941485th_.arc', - > OPTIONS => DBMS_LOGMNR.ADDFILE);   PL/SQL procedure successfully completed.   sys@TEST0910>  EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - > LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_7_941477xf_.arc', - > OPTIONS => DBMS_LOGMNR.ADDFILE);   PL/SQL procedure successfully completed.   sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - > LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_6_94146rg7_.arc', - > OPTIONS => DBMS_LOGMNR.ADDFILE);   PL/SQL procedure successfully completed.   sys@TEST0910> EXECUTE DBMS_LOGMNR.START_LOGMNR( - > DICTFILENAME =>'/u01/app/oracle/utl/dictionary.ora',OPTIONS => - > DBMS_LOGMNR.DDL_DICT_TRACKING);   PL/SQL procedure successfully completed.   7、设置session时间格式   sys@TEST0910> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';   Session altered.     8、查询logmnr分析出的数据 sys@TEST0910> select username,scn,timestamp,sql_redo from v$logmnr_contents   2  where seg_name='TEST12';   USERNAME                              SCN TIMESTAMP           SQL_REDO ------------------------------ ---------- ------------------- -------------------------------------------------- SYS                               1989009 2013-09-23 15:09:53 truncate table scott.test12;   sys@TEST0910> EXECUTE DBMS_LOGMNR.END_LOGMNR;   PL/SQL procedure successfully completed.   8、mount状态下闪回 sys@TEST0910> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. sys@TEST0910> startup mount; ORACLE instance started.   Total System Global Area 2505338880 bytes Fixed Size                  2230952 bytes Variable Size             587203928 bytes Database Buffers         1895825408 bytes Redo Buffers               20078592 bytes Database mounted.   sys@TEST0910> flashback database to timestamp to_timestamp('2013-09-23 15:09:52','yyyy-mm-dd hh24:mi:ss');  此处注意，时间在logmnr查询出的基础上减1秒   Flashback complete.   9、让数据库置于read only，检查数据是否正确恢复 sys@TEST0910> alter database open read only;   Database altered.   sys@TEST0910> select count(*) from scott.test12;     COUNT(*) ----------         14   1 row selected.   10、重新启动数据库，使用resetlogs打开数据库 sys@TEST0910> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. sys@TEST0910> startup mount; ORACLE instance started.   Total System Global Area 2505338880 bytes Fixed Size                  2230952 bytes Variable Size             587203928 bytes Database Buffers         1895825408 bytes Redo Buffers               20078592 bytes Database mounted. sys@TEST0910> alter database open   2  ; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open     sys@TEST0910> alter database open resetlogs;   Database altered.