oracledml误操作找回数据

最新推荐文章于 2023-10-15 22:01:29 发布

沈亿

最新推荐文章于 2023-10-15 22:01:29 发布

阅读量210

点赞数 1

分类专栏： oracle错误操作的恢复应用文章标签： dml误操作恢复

本文链接：https://blog.csdn.net/u010851843/article/details/84760409

版权

oracle错误操作的恢复应用专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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

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表空间记录

沈亿

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracledml误操作找回数据

DML误操作恢复过程 1在没有提交之前，自己session可以撤销,使用rollback进行回滚 2.提交之后，不能rollback。但是dml事务的提交历史数据在undo中有反操作记录的。默认保存的时间undo_retention=15分钟 SQL&gt; show parameter undo_retention NAME ...
复制链接

扫一扫

专栏目录