oracledml误操作找回数据

DML误操作恢复过程

     1在没有提交之前，自己session可以撤销,使用rollback进行回滚

     2.提交之后，不能rollback。但是dml事务的提交历史数据在undo中有反操作记录的。默认保存的时间undo_retention=15分钟

     SQL> show parameter undo_retention

 

    NAME                                 TYPE                    VALUE

    -------------------------- ---------------------- ----------------------

    undo_retention                       integer                900

    SQL>

   保留时间之内的历史数据能否被覆盖，由guarantee来确定   通过查看undo表空间确认

    SQL> select tablespace_name,retention from dba_tablespaces;

    TABLESPACE_NAME                                          RETENTION

    ----------------------------------------------------- ---------------------

    SYSTEM                                                         NOT APPLY

    SYSAUX                                                         NOT APPLY

    UNDOTBS1                                                     NOGUARANTEE

    TEMP                                                             NOT APPLY

    USERS                                                           NOT APPLY

    UNDOTBS2                                                     NOGUARANTEE

    GGDATA                                                         NOT APPLY

    TEMP01                                                         NOT APPLY

    R1                                                                 NOT APPLY

    R2                                                                 NOT APPLY

    R3                                                                 NOT APPLY

    R4                                                                 NOT APPLY

 

    12 rows selected.

 

    SQL>

    如果确保指定时间内保存

    SQL> alter tablespace undotbs1 retention guarantee;

 

    Tablespace altered.

 

    SQL>

    

    SQL> select tablespace_name,retention from dba_tablespaces;

    TABLESPACE_NAME                                              RETENTION

    ------------------------------------------------------ ----------------------

    SYSTEM                                                          NOT APPLY

    SYSAUX                                                          NOT APPLY

    UNDOTBS1                                                      GUARANTEE

    TEMP                                                              NOT APPLY

    USERS                                                            NOT APPLY

    UNDOTBS2                                                      NOGUARANTEE

    GGDATA                                                          NOT APPLY

    TEMP01                                                           NOT APPLY

    R1                                                                  NOT APPLY

    R2                                                                  NOT APPLY

    R3                                                                  NOT APPLY

    R4                                                                  NOT APPLY

 

    12 rows selected.

 

    SQL>

    如何才能从undo表空间找出我们需要的DML历史数据呢？  

    使用闪回查询，查询的语句，使用as of scn/timestamp。 查询表在指定scn/timestamp的行记录状态   

    sql> select …… from t as of scn <> where …… order by ……；

    sql> select …… from t as of timestamp <> where …… order by ……；

    sql> select …… from t as of timestamp sysdate-1/1440 where …… order by ……； //查询一分钟前  1440=24*60*60-------一天的分钟数       

    例子：

    SQL> create table t as select empno,ename,job,mgr,sal,deptno from emp;  ---------------新建表emp1

    Table created.

    SQL> select * from t;

 

         EMPNO ENAME                JOB                       MGR        SAL     DEPTNO

    ---------- -------------------- ------------------ ---------- ---------- ----------

          7369 SMITH                CLERK                    7902        800         20

          7499 ALLEN                SALESMAN                 7698       1600         30

          7521 WARD                 SALESMAN                 7698       1250         30

          7566 JONES                MANAGER                  7839       2975         20

          7654 MARTIN               SALESMAN                 7698       1250         30

          7698 BLAKE                MANAGER                  7839       2850         30

          7782 CLARK                MANAGER                  7839       2450         10

          7788 SCOTT                ANALYST                  7566       3000         20

          7839 KING                 PRESIDENT                           5000         10

          7844 TURNER               SALESMAN                 7698       1500         30

          7876 ADAMS                CLERK                    7788       1100         20

          7900 JAMES                CLERK                    7698        950         30

          7902 FORD                 ANALYST                  7566       3000         20

          7934 MILLER               CLERK                    7782       1300         10

 

    14 rows selected.

 

    SQL>

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    闪回版本查询精确的时间，或者scn

    select …… from t versions between scn minvalue and maxvalue where ……；

    versions_startscn    开始scn

    versions_starttime   开始时间

    versions_endscn      结束scn

    versions_xid     事务

    versions_operation    ---执行了什么操作  D delete |I insert |U update 

    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    模拟dml误操作，删除t表中的数据，然后进行提交commit操作

     SQL> delete t;

    14 rows deleted.

    SQL> commit;

    Commit complete.

    SQL>

    SQL> select versions_startscn,versions_starttime,versions_operation from t versions between scn minvalue and maxvalue;

 

    VERSIONS_STARTSCN VERSIONS_STARTTIME                                                          VE

    ----------------- --------------------------------------------------------------------------- --

             11512532 08-JAN-16 05.51.01 PM                                                       D  // 执行了delete操作

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

             11512532 08-JAN-16 05.51.01 PM                                                       D

         

    SQL>

     SQL> select * from t as of scn 11512532;

 

     no rows selected

    

    SQL> select * from t as of scn 11512531;//scn比着查询的scn-1       ------------------------------通过scn，查询出误操作的数据

 

             EMPNO ENAME                JOB                       MGR        SAL     DEPTNO

        ---------- -------------------- ------------------ ---------- ---------- ----------

              7369 SMITH                CLERK                    7902        800         20

              7499 ALLEN                SALESMAN                 7698       1600         30

              7521 WARD                 SALESMAN                 7698       1250         30

              7566 JONES                MANAGER                  7839       2975         20

              7654 MARTIN               SALESMAN                 7698       1250         30

              7698 BLAKE                MANAGER                  7839       2850         30

              7782 CLARK                MANAGER                  7839       2450         10

              7788 SCOTT                ANALYST                  7566       3000         20

              7839 KING                 PRESIDENT                           5000         10

              7844 TURNER               SALESMAN                 7698       1500         30

              7876 ADAMS                CLERK                    7788       1100         20

              7900 JAMES                CLERK                    7698        950         30

              7902 FORD                 ANALYST                  7566       3000         20

              7934 MILLER               CLERK                    7782       1300         10

 

        14 rows selected.

 

       SQL> insert into t select * from t as of scn 11512531;     将查询获得的数据重新插入t表

 

        14 rows created.

 

        SQL> commit;

 

        Commit complete.

 

        SQL> select * from t;

 

             EMPNO ENAME                JOB                       MGR        SAL     DEPTNO

        ---------- -------------------- ------------------ ---------- ---------- ----------

              7369 SMITH                CLERK                    7902        800         20

              7499 ALLEN                SALESMAN                 7698       1600         30

              7521 WARD                 SALESMAN                 7698       1250         30

              7566 JONES                MANAGER                  7839       2975         20

              7654 MARTIN               SALESMAN                 7698       1250         30

              7698 BLAKE                MANAGER                  7839       2850         30

              7782 CLARK                MANAGER                  7839       2450         10

              7788 SCOTT                ANALYST                  7566       3000         20

              7839 KING                 PRESIDENT                           5000         10

              7844 TURNER               SALESMAN                 7698       1500         30

              7876 ADAMS                CLERK                    7788       1100         20

              7900 JAMES                CLERK                    7698        950         30

              7902 FORD                 ANALYST                  7566       3000         20

              7934 MILLER               CLERK                    7782       1300         10

 

        14 rows selected.

 

       SQL> select * from t as of timestamp to_timestamp ('08-JAN-16 17.51.00','DD-Mon-RR HH24:MI:SS.FF');  ----------------通过starttime，查询出误操作的数据   //建议使用scn比较方便

 

             EMPNO ENAME                JOB                       MGR        SAL     DEPTNO

        ---------- -------------------- ------------------ ---------- ---------- ----------

              7369 SMITH                CLERK                    7902        800         20

              7499 ALLEN                SALESMAN                 7698       1600         30

              7521 WARD                 SALESMAN                 7698       1250         30

              7566 JONES                MANAGER                  7839       2975         20

              7654 MARTIN               SALESMAN                 7698       1250         30

              7698 BLAKE                MANAGER                  7839       2850         30

              7782 CLARK                MANAGER                  7839       2450         10

              7788 SCOTT                ANALYST                  7566       3000         20

              7839 KING                 PRESIDENT                           5000         10

              7844 TURNER               SALESMAN                 7698       1500         30

              7876 ADAMS                CLERK                    7788       1100         20

              7900 JAMES                CLERK                    7698        950         30

              7902 FORD                 ANALYST                  7566       3000         20

              7934 MILLER               CLERK                    7782       1300         10

 

        14 rows selected.

 

        SQL> select * from t as of timestamp sysdate-1/1440;   ------------------------如果执行误操作的时间较短，可以简单使用sysdate-n/1440！查询n分钟前的dml误操作

 

             EMPNO ENAME                JOB                       MGR        SAL     DEPTNO

        ---------- -------------------- ------------------ ---------- ---------- ----------

              7369 SMITH                CLERK                    7902        800         20

              7499 ALLEN                SALESMAN                 7698       1600         30

              7521 WARD                 SALESMAN                 7698       1250         30

              7566 JONES                MANAGER                  7839       2975         20

              7654 MARTIN               SALESMAN                 7698       1250         30

              7698 BLAKE                MANAGER                  7839       2850         30

              7782 CLARK                MANAGER                  7839       2450         10

              7788 SCOTT                ANALYST                  7566       3000         20

              7839 KING                 PRESIDENT                           5000         10

              7844 TURNER               SALESMAN                 7698       1500         30

              7876 ADAMS                CLERK                    7788       1100         20

              7900 JAMES                CLERK                    7698        950         30

              7902 FORD                 ANALYST                  7566       3000         20

              7934 MILLER               CLERK                    7782       1300         10

 

        14 rows selected.

 

        SQL>

注意： 正常提交会有。如果ddl/tcl等隐式提交的，这样dml不会在undo表空间记录