[转]开启闪回以及闪回的四种原理
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(*) ---------- 14flashback 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(*) ---------- 14flashback 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.