闪回表和闪回事务处理查询

最新推荐文章于 2023-10-17 10:37:31 发布

blockheading

最新推荐文章于 2023-10-17 10:37:31 发布

阅读量453

点赞数

分类专栏： Oracle Backup/Recovery 文章标签：数据库 timestamp

本文链接：https://blog.csdn.net/u013169075/article/details/53889631

版权

Oracle Backup/Recovery 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

闪回表

闪回表可以将表恢复到特定的时间点。
闪回表是一个原位操作。
数据库保持联机。
使用闪回表可以将一个或多个表还原到特定时间点，而不需要还原备份。
将从还原表空间检索数据来执行闪回表操作。
需要具有对特定表的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

blockheading

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
闪回表和闪回事务处理查询

闪回表闪回表可以将表恢复到特定的时间点。闪回表是一个原位操作。数据库保持联机。使用闪回表可以将一个或多个表还原到特定时间点，而不需要还原备份。将从还原表空间检索数据来执行闪回表操作。需要具有对特定表的FLASHBACK ANY TABLE或FLASHBACK对象权限。需要对要闪回的表具有SELECT/INSERT/DELETE/ALTER权限。必须对要执行闪回操作的表启用行移动。
复制链接

扫一扫