ITPub博客

闪回 错误的DML 操作

Oracle 作者:18141908802 时间:2018-07-12 15:43:00 0 删除 编辑

在oracle10g版本之前,如果用户对一个表进行了错误的DML操作并且提交了这些操作,而且之前用户也没备份,要恢复该表到操作之前的状态,就要对这个表进行不完全回复。oracle10g中只要使用一条命令就可以了!oracle是利用还原段中的数据来执行这一恢复的。

提交DML操作之后,该操作所使用的还原段就可以被其他的操作使用了,为了保证在进行闪回操作时这些数据仍然在还原段中,可能要重新设置undoretention的数值,单位为妙,表示事务提交的数据至少在还原段里停留的时间。

实验如下:

SQL> create table test as select * from  emp;--首先创建一个test表用于实验。

表已创建。

SQL> select *  from test;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 17-12月-80            800            
        20                                                                      
                                                                                
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300 
        30                                                                      
                                                                                
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500 
        30                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7566 JONES      MANAGER         7839 02-4月 -81           2975            
        20                                                                      
                                                                                
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400 
        30                                                                      
                                                                                
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850            
        30                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7782 CLARK      MANAGER         7839 09-6月 -81           2450            
        10                                                                      
                                                                                
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000            
        20                                                                      
                                                                                
      7839 KING       PRESIDENT            17-11月-81           5000            
        10                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0 
        30                                                                      
                                                                                
      7876 ADAMS      CLERK           7788 23-5月 -87           1100            
        20                                                                      
                                                                                
      7900 JAMES      CLERK           7698 03-12月-81            950            
        30                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7902 FORD       ANALYST         7566 03-12月-81           3000            
        20                                                                      
                                                                                
      7934 MILLER     CLERK           7782 23-1月 -82           1300            
        10                                                                      
                                                                               

已选择14行。

SQL> conn system/yang --
已连接。

2  更改undo_retention的值。
SQL> show parameter undo_retention

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
undo_retention                       integer     900                            
SQL> alter system set undo_retention =7200 ;

系统已更改。

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
undo_retention                       integer     7200         

3 对test表进行DML操作                  

SQL> update test set sal =9999;

已更新14行。

4。接下来,使用带有version子句的查询语句获取刚刚所做的DML操作的version_ID 和其他相关信息。versions_xid,-- 事务号,minvalue 最小值, maxvalue 最大值

SQL> select versions_xid,empno,ename,sal
  2  from test
  3  versions between scn minvalue and maxvalue
  4  where empno =7900;

VERSIONS_XID          EMPNO ENAME             SAL                               
---------------- ---------- ---------- ----------                               
                       7900 JAMES             950        

由于还没有提交DML操作,所以versionid 为空。进行提交。                      

SQL> commit;

提交完成。

SQL> select versions_xid,empno,ename,sal
  2  from test
  3  versions between scn minvalue and maxvalue
  4  where empno =7900;

VERSIONS_XID          EMPNO ENAME             SAL                               
---------------- ---------- ---------- ----------                               
0800050012030000       7900 JAMES            9999                               
                       7900 JAMES             950         

此时 versionid 已经 有值了,为了使用闪回功能,我们要进入到system用户下。                     

SQL> conn system/yang
已连接。
SQL> col operation for a10
SQL> col undo_sql for a80
SQL> select operation,undo_sql
  2  from flashback_transaction_query
  3  where xid = hextoraw('0800050012030000');

OPERATION                                                                       
----------                                                                      
UNDO_SQL                                                                        
--------------------------------------------------------------------------------
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '1300' where ROWID = 'AAARWCAAEAAAAGMAAN';    
                                                                                
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '3000' where ROWID = 'AAARWCAAEAAAAGMAAM';    
                                                                                
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '950' where ROWID = 'AAARWCAAEAAAAGMAAL';     
                                                                               

OPERATION                                                                       
----------                                                                      
UNDO_SQL                                                                        
--------------------------------------------------------------------------------
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '1100' where ROWID = 'AAARWCAAEAAAAGMAAK';    
                                                                                
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '1500' where ROWID = 'AAARWCAAEAAAAGMAAJ';    
                                                                                
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '5000' where ROWID = 'AAARWCAAEAAAAGMAAI';    
                                                                               

OPERATION                                                                       
----------                                                                      
UNDO_SQL                                                                        
--------------------------------------------------------------------------------
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '3000' where ROWID = 'AAARWCAAEAAAAGMAAH';    
                                                                                
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '1250' where ROWID = 'AAARWCAAEAAAAGMAAC';    
                                                                                
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '2975' where ROWID = 'AAARWCAAEAAAAGMAAD';    
                                                                               

OPERATION                                                                       
----------                                                                      
UNDO_SQL                                                                        
--------------------------------------------------------------------------------
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '1250' where ROWID = 'AAARWCAAEAAAAGMAAE';    
                                                                                
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '2850' where ROWID = 'AAARWCAAEAAAAGMAAF';    
                                                                                
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '2450' where ROWID = 'AAARWCAAEAAAAGMAAG';    
                                                                               

OPERATION                                                                       
----------                                                                      
UNDO_SQL                                                                        
--------------------------------------------------------------------------------
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '1600' where ROWID = 'AAARWCAAEAAAAGMAAB';    
                                                                                
UPDATE                                                                          
update "SCOTT"."TEST" set "SAL" = '800' where ROWID = 'AAARWCAAEAAAAGMAAA';     
                                                                                
BEGIN                                                                           
                                                                                
                                                                               

已选择15行。

查询test表的内容,看见所有的工资都变为了9999

SQL> select * from scott.test ;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 17-12月-80           9999            
        20                                                                      
                                                                                
      7499 ALLEN      SALESMAN        7698 20-2月 -81           9999        300 
        30                                                                      
                                                                                
      7521 WARD       SALESMAN        7698 22-2月 -81           9999        500 
        30                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7566 JONES      MANAGER         7839 02-4月 -81           9999            
        20                                                                      
                                                                                
      7654 MARTIN     SALESMAN        7698 28-9月 -81           9999       1400 
        30                                                                      
                                                                                
      7698 BLAKE      MANAGER         7839 01-5月 -81           9999            
        30                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7782 CLARK      MANAGER         7839 09-6月 -81           9999            
        10                                                                      
                                                                                
      7788 SCOTT      ANALYST         7566 19-4月 -87           9999            
        20                                                                      
                                                                                
      7839 KING       PRESIDENT            17-11月-81           9999            
        10                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7844 TURNER     SALESMAN        7698 08-9月 -81           9999          0 
        30                                                                      
                                                                                
      7876 ADAMS      CLERK           7788 23-5月 -87           9999            
        20                                                                      
                                                                                
      7900 JAMES      CLERK           7698 03-12月-81           9999            
        30                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7902 FORD       ANALYST         7566 03-12月-81           9999            
        20                                                                      
                                                                                
      7934 MILLER     CLERK           7782 23-1月 -82           9999            
        10                                                                      
                                                                               

已选择14行。

6 如果要对刚才的操作进行恢复,则先要使用下面的查询获得DML事务所对应的SCN号

SQL> select operation,START_SCN
  2  from flashback_transaction_query
  3  where xid=hextoraw('0800050012030000');

OPERATION   START_SCN                                                           
---------- ----------                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                          

OPERATION   START_SCN                                                           
---------- ----------                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
UPDATE        1371887                                                           
BEGIN         1371887                                                          

已选择15行。

---------------这个是当前的SCN号-------------

SQL> select current_scn from v$database;

CURRENT_SCN                                                                     
-----------                                                                     
    1372969                                                                    

SQL> flashback table scott.test to SCN 1371887;
flashback table scott.test to SCN 1371887
                      *
第 1 行出现错误: 
ORA-08189: 因为未启用行移动功能, 不能闪回表

出现此错误是因为在闪回一个表之前,要先将该表的行移动功能打开,oracle默认是关闭的。
SQL> alter table scott.test enable row movement;

表已更改。

执行闪回操作。

SQL> flashback table scott.test to SCN 1371887;

闪回完成。

SQL> select * from scott.test;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 17-12月-80            800            
        20                                                                      
                                                                                
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300 
        30                                                                      
                                                                                
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500 
        30                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7566 JONES      MANAGER         7839 02-4月 -81           2975            
        20                                                                      
                                                                                
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400 
        30                                                                      
                                                                                
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850            
        30                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7782 CLARK      MANAGER         7839 09-6月 -81           2450            
        10                                                                      
                                                                                
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000            
        20                                                                      
                                                                                
      7839 KING       PRESIDENT            17-11月-81           5000            
        10                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0 
        30                                                                      
                                                                                
      7876 ADAMS      CLERK           7788 23-5月 -87           1100            
        20                                                                      
                                                                                
      7900 JAMES      CLERK           7698 03-12月-81            950            
        30                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7902 FORD       ANALYST         7566 03-12月-81           3000            
        20                                                                      
                                                                                
      7934 MILLER     CLERK           7782 23-1月 -82           1300            
        10                                                                      
                                                                               

已选择14行。

成功。。。。。。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31429259/viewspace-2157758/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2017-01-16

  • 博文量
    85
  • 访问量
    21657