第六章： 不完全恢复--FlashBack
6、flashback 的功能：利用flashback log 或 undo data 对database 可以恢复到过去某个点，可以作为不完恢复的补充
7、flashback分类：
1）flashback database
2）flashback table
3)flashback drop
4）flashback query
8、flashback 的应用
1） flashback drop :除了system表空间，可以恢复被drop 的table
06:52:29 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST TABLE
T01 TABLE
T02 TABLE
7 rows selected.
06:52:31 SQL> drop table t01;
Table dropped.
06:52:38 SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T01 BIN$qrJLbL74ZgvgQKjA8Agb/A==$0 TABLE 2011-08-17:06:52:38
06:52:44 SQL>
--------除了system 表空间，其余表空间都有一个类似windows 回收站，在drop table，实际上把table 改名后放入recyclebin。
06:52:44 SQL> flashback table t01 to before drop;
Flashback complete.
06:54:05 SQL> show recycle;
06:54:07 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST TABLE
T01 TABLE
T02 TABLE
7 rows selected.
06:54:11 SQL> drop table t02 purge; //purge 会彻底的删除table
Table dropped.
06:54:40 SQL> show recycle;
-----------清空recyclebin
06:54:43 SQL> drop table t01;
Table dropped.
06:55:49 SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T01 BIN$qrJLbL75ZgvgQKjA8Agb/A==$0 TABLE 2011-08-17:06:55:49
06:55:51 SQL> purge recyclebin;
Recyclebin purged.
06:55:57 SQL> show recycle;
06:55:59 SQL>
--------------如何恢复同一个schema 下同名的table
06:56:32 SQL> drop table test;
Table dropped.
06:56:42 SQL> create table test as select * from emp;
Table created.
06:56:46 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$qrJLbL76ZgvgQKjA8Agb/A==$0 TABLE
TEST TABLE
6 rows selected.
06:56:50 SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$qrJLbL76ZgvgQKjA8Agb/A==$0 TABLE 2011-08-17:06:56:36
06:56:58 SQL> flashback table test to before drop;
flashback table test to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
06:57:09 SQL> flashback table test to before drop rename to test_old;
Flashback complete.
06:57:32 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST_OLD TABLE
TEST TABLE
6 rows selected.
06:57:36 SQL>
------------system 表空间不存在recyclebin ，表直接被删除
06:57:36 SQL> conn /as sysdba
Connected.
06:58:33 SQL>
06:58:33 SQL> create table test as select * from user_tables;
Table created.
06:58:42 SQL> drop table test;
Table dropped.
06:58:46 SQL> show recycle;
06:58:48 SQL>
2）flashback query：（用于DML 误操作）
利用在undo tablespace 里已经被提交的undo block（未被覆盖），可以通过查询的方式将表里面的记录回到过去某个时间点。
-------模拟环境
07:01:37 SQL> conn scott/tiger
Connected.
07:01:41 SQL>
07:01:41 SQL> select * from test;
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.
07:01:45 SQL> delete from test ;
14 rows deleted.
07:01:59 SQL> commit;
Commit complete.
07:02:00 SQL> rollback;
Rollback complete.
07:02:03 SQL> select * from test;
no rows selected
07:02:05 SQL> insert into test select * from emp where rownum <3;
2 rows created.
07:02:35 SQL> select * from test;
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
07:02:37 SQL> commit;
Commit complete.
07:02:38 SQL>
-----------利用logmnr 找到DML操作的时间点
07:03:03 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES UNUSED 0
2 1 1 52428800 1 NO CURRENT 1261015 17-AUG-11
3 1 0 52428800 1 YES UNUSED 0
07:03:20 SQL> col member for a50
07:03:23 SQL> r
1* select group#,member from v$logfile
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/prod/redo03.log
2 /u01/app/oracle/oradata/prod/redo02.log
1 /u01/app/oracle/oradata/prod/redo01.log
07:03:24 SQL>
11:19:31 SQL> conn /as sysdba
Connected.
11:19:35 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 12 52428800 2 YES INACTIVE 823116 29-SEP-11
2 1 14 52428800 2 NO CURRENT 828692 29-SEP-11
3 2 9 52428800 2 YES INACTIVE 824371 29-SEP-11
4 2 11 52428800 2 NO CURRENT 828868 29-SEP-11
5 1 13 52428800 2 YES INACTIVE 828670 29-SEP-11
6 2 10 52428800 2 YES INACTIVE 828817 29-SEP-11
6 rows selected.
11:19:41 SQL> col member for a50
11:19:57 SQL> select group# ,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
2 +DG1/prod/onlinelog/group_2.262.762877491
2 +RECOVERY/prod/onlinelog/group_2.258.762877501
1 +DG1/prod/onlinelog/group_1.261.762877473
1 +RECOVERY/prod/onlinelog/group_1.257.762877479
3 +DG1/prod/onlinelog/group_3.266.762877849
3 +RECOVERY/prod/onlinelog/group_3.259.762877855
4 +DG1/prod/onlinelog/group_4.267.762877859
4 +RECOVERY/prod/onlinelog/group_4.260.762877867
6 +DG1/prod/onlinelog/group_6.272.763037401
6 +RECOVERY/prod/onlinelog/group_6.262.763037407
5 +DG1/prod/onlinelog/group_5.271.763037441
GROUP# MEMBER
---------- --------------------------------------------------
5 +RECOVERY/prod/onlinelog/group_5.261.763037613
12 rows selected.
11:20:07 SQL> execute dbms_logmnr.add_logfile(logfilename=>'+DG1/prod/onlinelog/group_2.262.762877491',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
11:20:57 SQL> alter session set nls_date_format='yyyy-mm-dd';
Session altered.
11:21:32 SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
11:23:11 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='EMP1';
USERNAME SCN TIMESTAMP SQL_REDO
------------------------------ ---------- ---------- --------------------------------------------------
830293 2011-09-29 delete from "SCOTT"."EMP1" where "EMPNO" = '7369'
and "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MGR
" = '7902' and "HIREDATE" = TO_DATE('1980-12-17',
'yyyy-mm-dd') and "SAL" = '800' and "COMM" IS NULL
and "DEPTNO" = '20' and ROWID = 'AAAM01AAEAAAAGEA
AA';
07:03:24 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo02.log',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
07:04:34 SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
07:04:41 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
07:04:56 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TEST';
SCOTT 1263006 2011-08-17 07:01:59 delete from "SCOTT"."TEST" where "EMPNO" = '7369'
and "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MGR
" = '7902' and "HIREDATE" = TO_DATE('1980-12-17 00
:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '800
' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID
= 'AAAM39AACAAAABEAAA';
07:05:21 SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
---利用flashback query 查询
07:08:42 SQL> conn scott/tiger
Connected.
07:08:48 SQL>
07:08:48 SQL> select * from test as of timestamp to_timestamp('2011-08-17 07:01:59','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.
07:08:50 SQL> insert into test (select * from test as of timestamp to_timestamp('2011-08-17 07:01:59','yyyy-mm-dd hh24:mi:ss'));
14 rows created.
07:09:10 SQL> select * from test;
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
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
16 rows selected.
07:09:13 SQL>
----------基于scn
07:09:13 SQL> conn /as sysdba
Connected.
07:10:28 SQL>
07:10:28 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1263945
07:10:39 SQL> conn scott/tiger
Connected.
07:13:44 SQL> delete from test;
16 rows deleted.
07:13:51 SQL> commit;
Commit complete.
07:13:56 SQL> select * from test as of scn 1263945 ;
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
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
16 rows selected.
flashback table :对表进行闪回（类似flashback query）（同样的是对DML误操作）
Flashback?Table也是使用UNDO tablespace的内容来实现对数据的回退。
07:16:18 SQL> select * from test;
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
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
16 rows selected.
07:16:23 SQL> delete from test;
16 rows deleted.
07:16:50 SQL> commit;
Commit complete.
07:16:52 SQL> select * from test;
no rows selected
07:16:57 SQL> insert into test select * from emp where rownum=1;
1 row created.
07:17:17 SQL> commit;
Commit complete.
07:17:19 SQL> select * from test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
07:17:21 SQL> flashback table test to scn 1264179;
flashback table test to scn 1264179
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
07:17:41 SQL> alter table test enable row movement;
Table altered.
07:18:01 SQL> flashback table test to scn 1264179;
Flashback complete.
07:18:05 SQL> select * from test;
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
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
16 rows selected.
07:18:09 SQL>
-------------基于时间点（通过logmnr 找出误操作的时间点）
05:43:31 SQL> delete from scott.emp1;
14 rows deleted.
05:44:25 SQL> flashback table scott.emp1 to timestamp to_timestamp('2011-03-18 04:50:00','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
05:44:32 SQL> select * from scott.emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
