闪回表
闪回事务处理查询
- 闪回表可以将表恢复到特定的时间点。
- 闪回表是一个原位操作。
- 数据库保持联机。
- 使用闪回表可以将一个或多个表还原到特定时间点,而不需要还原备份。
- 将从还原表空间检索数据来执行闪回表操作。
- 需要具有对特定表的FLASHBACK ANY TABLE或FLASHBACK对象权限。
- 需要对要闪回的表具有SELECT/INSERT/DELETE/ALTER权限。
- 必须对要执行闪回操作的表启用行移动。
闪回表实验
SCOTT@PROD1> update emp set sal=sal+1;
14 rows updated.
SCOTT@PROD1> commit;
Commit complete.
--查询出任意一行timestamp或scm
SCOTT@PROD1> select versions_starttime,versions_startscn from emp versions between scn minvalue and maxvalue where empno=7566;
VERSIONS_STARTTIME VERSIONS_STARTSCN
--------------------------------------------------------------------------- -----------------
26-DEC-16 09.05.13 PM 4299989287
SCOTT@PROD1> alter table emp enable row movement; --启动行移动
Table altered.
SCOTT@PROD1> flashback table emp to scn 4299989286; --闪回表
Flashback complete.
SCOTT@PROD1> select sal from emp where empno=7566; --数据恢复
SAL
----------
2975
SCOTT@PROD1> alter table emp disable row movement; --关闭行移动
Table altered.
--查询表是否打开行移动
SCOTT@PROD1> select table_name,row_movement from user_tables;
TABLE_NAME ROW_MOVE
------------------------------ --------
DEMO DISABLED
DEPT DISABLED
EMP DISABLED
BONUS DISABLED
SALGRADE DISABLED
E1 DISABLED
TEXT DISABLED
SYS_TEMP_FBT DISABLED
ALL_OBJECTS_UNLOAD DISABLED
9 rows selected.
--闪回事务处理查询
--增加数据库附加日志
SYS@PROD1> alter database add supplemental log data;
Database altered.
--增加主键的附加日志
SYS@PROD1> alter database add supplemental log data(primary key) columns;
Database altered.
SYS@PROD1> select SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUP SUP SUPPLEME SUP
--- --- -------- ---
YES NO YES NO
SYS@PROD1> conn scott/tiger
Connected.
SCOTT@PROD1> update emp set sal=sal+1;
14 rows updated.
SCOTT@PROD1> commit;
Commit complete.
SCOTT@PROD1> delete emp;
14 rows deleted.
SCOTT@PROD1> commit;
Commit complete.
SCOTT@PROD1> conn / as sysdba
Connected.
SYS@PROD1> desc flashback_transaction_query
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
SYS@PROD1> select versions_xid,versions_operation,sal from scott.emp versions between scn minvalue and maxvalue where empno=7566;
VERSIONS_XID V SAL
---------------- - ----------
0E00060063040000 D 2976
110017004C040000 U 2976
0F001B001A050000 I 2975
0F001B001A050000 D 2976
13000E0058040000 U 2976
2975
6 rows selected.
SYS@PROD1> spool /tmp/emp.sql rep
SYS@PROD1> select undo_sql from flashback_transaction_query where xid='0E00060063040000';
UNDO_SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7934','MILLER','CLERK','7782',TO_DATE('23-JAN-82', 'DD-MON-RR'),'1301',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7902','FORD','ANALYST','7566',TO_DATE('03-DEC-81', 'DD-MON-RR'),'3001',NULL,'20');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7900','JAMES','CLERK','7698',TO_DATE('03-DEC-81', 'DD-MON-RR'),'951',NULL,'30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7876','ADAMS','CLERK','7788',TO_DATE('23-MAY-87', 'DD-MON-RR'),'1101',NULL,'20');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7844','TURNER','SALESMAN','7698',TO_DATE('08-SEP-81', 'DD-MON-RR'),'1501','0','30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7839','KING','PRESIDENT',NULL,TO_DATE('17-NOV-81', 'DD-MON-RR'),'5001',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7788','SCOTT','ANALYST','7566',TO_DATE('19-APR-87', 'DD-MON-RR'),'3001',NULL,'20');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2451',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7698','BLAKE','MANAGER','7839',TO_DATE('01-MAY-81', 'DD-MON-RR'),'2851',NULL,'30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7654','MARTIN','SALESMAN','7698',TO_DATE('28-SEP-81', 'DD-MON-RR'),'1251','1400','30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7566','JONES','MANAGER','7839',TO_DATE('02-APR-81', 'DD-MON-RR'),'2976',NULL,'20');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7521','WARD','SALESMAN','7698',TO_DATE('22-FEB-81', 'DD-MON-RR'),'1251','500','30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7499','ALLEN','SALESMAN','7698',TO_DATE('20-FEB-81', 'DD-MON-RR'),'1601','300','30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'801',NULL,'20');
15 rows selected.
SYS@PROD1> spool off
SYS@PROD1> ho sed '/^insert into/p' -n /tmp/emp.sql >/tmp/emp1.sql
SYS@PROD1> start /tmp/emp1.sql
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
SYS@PROD1> select count(*) from scott.emp;
COUNT(*)
----------
14
--下面尝试在delete表update操作的恢复
SYS@PROD1> spool /tmp/emp_u.sql rep
SYS@PROD1> select undo_sql from flashback_transaction_query where xid='110017004C040000';
UNDO_SQL
----------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '1300' where ROWID = 'AAASYzAAEAAAACTAAN';
update "SCOTT"."EMP" set "SAL" = '3000' where ROWID = 'AAASYzAAEAAAACTAAM';
update "SCOTT"."EMP" set "SAL" = '950' where ROWID = 'AAASYzAAEAAAACTAAL';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAASYzAAEAAAACTAAK';
update "SCOTT"."EMP" set "SAL" = '1500' where ROWID = 'AAASYzAAEAAAACTAAJ';
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAASYzAAEAAAACTAAI';
update "SCOTT"."EMP" set "SAL" = '3000' where ROWID = 'AAASYzAAEAAAACTAAH';
update "SCOTT"."EMP" set "SAL" = '2450' where ROWID = 'AAASYzAAEAAAACTAAG';
update "SCOTT"."EMP" set "SAL" = '2850' where ROWID = 'AAASYzAAEAAAACTAAF';
update "SCOTT"."EMP" set "SAL" = '1250' where ROWID = 'AAASYzAAEAAAACTAAE';
update "SCOTT"."EMP" set "SAL" = '2975' where ROWID = 'AAASYzAAEAAAACTAAD';
update "SCOTT"."EMP" set "SAL" = '1250' where ROWID = 'AAASYzAAEAAAACTAAC';
update "SCOTT"."EMP" set "SAL" = '1600' where ROWID = 'AAASYzAAEAAAACTAAB';
update "SCOTT"."EMP" set "SAL" = '800' where ROWID = 'AAASYzAAEAAAACTAAA';
15 rows selected.
SYS@PROD1> spool off
SYS@PROD1> ho sed '/^update/p' -n /tmp/emp_u.sql >/tmp/emp_u1.sql
SYS@PROD1> start /tmp/emp_u1.sql
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
0 rows updated.
SYS@PROD1> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1301 10
7902 FORD ANALYST 7566 03-DEC-81 3001 20
7900 JAMES CLERK 7698 03-DEC-81 951 30
7876 ADAMS CLERK 7788 23-MAY-87 1101 20
7844 TURNER SALESMAN 7698 08-SEP-81 1501 0 30
7839 KING PRESIDENT 17-NOV-81 5001 10
7788 SCOTT ANALYST 7566 19-APR-87 3001 20
7782 CLARK MANAGER 7839 09-JUN-81 2451 10
7698 BLAKE MANAGER 7839 01-MAY-81 2851 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1251 1400 30
7566 JONES MANAGER 7839 02-APR-81 2976 20
7521 WARD SALESMAN 7698 22-FEB-81 1251 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1601 300 30
7369 SMITH CLERK 7902 17-DEC-80 801 20
14 rows selected.
无法恢复,因为中间跨越了ddl操作,且rowid发生了变化。
实验最后关闭附加日志,因为会大量写入日志
SYS@PROD1> alter database drop supplemental log data; --不能直接删除最小附加日志
alter database drop supplemental log data
*
ERROR at line 1:
ORA-32589: unable to drop minimal supplemental logging
SYS@PROD1> alter database drop supplemental log data(primary key) columns;
Database altered.
SYS@PROD1> alter database drop supplemental log data;
Database altered.
SYS@PROD1> select SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUP SUP SUPPLEME SUP
--- --- -------- ---
NO NO NO NO