某日，美创运维中心接到电话：****库误删数据了，怎么办？
’不慌不慌，慢慢说，delete 还是 truncate，drop?’
‘delete，但是近一个礼拜陆陆续续删除的，而且没有任何备份。‘
’哦？远程给我，我上来瞅瞅‘
遇到这种delete删除的情况，相对来说还比较好处理，小编心里还是很稳的。
思路一：利用undo闪回查询
根据undo信息，利用undo数据的前镜像，类似一致性读取方法，可以把表置于一个删除前的时间点(或SCN)，从而将数据找回。
例子如下：
1、查询对应时间点的表
select * from emp as of timestamp to_timestamp (‘2019-11-05 08:00:00′,’YYYY-MM-DD HH:MI:SS’);
2、可以选择直接创建一张新表插入查询出来的数据
create table test_tmp as select * from emp as of timestamp to_timestamp ( ‘2019-11-05 08:00:00′,’YYYY-MM-DD HH:MI:SS’ );
注意，这种方法受到undo_retention参数的限制。
默认情况下，undo_retention的值为900s，即在删除数据后的900s之后，undo里面的数据会过期，但如果业务比较繁忙，undo表空间不足的情况下，即便之前的镜像没有过期，镜像数据还是会被新的数据覆盖。
如果查询表留在undo中的前镜像被覆盖的话，进行闪回查询操作的时候会有如下报错。
ORA-08180: no snapshot found based on specified time
遗憾的是，客户环境的undo_retention是900s，一个礼拜前的镜像早就被覆盖了，所以该思路pass。
思路二：logminer挖掘
oracle所有的dml操作都会记录在redo日志之中，所以只要归档文件在，那么之前delete的记录都会还在。
模拟步骤如下，测试库为linux-11g：
1、删除数据并切换归档
delete from scott.emp;
commit;
alter system switch logfile ;
2、找到相关归档日志
select t.THREAD#, t.SEQUENCE#, t.NAME
from v$archived_log t
where t.FIRST_TIME >=to_date (‘2019-11-05 10:24:30’, ‘yyyy-mm-dd hh24:mi:ss’)
and t.NEXT_TIME <=to_date (‘2019-11-05 14:00:30’, ‘yyyy-mm-dd hh24:mi:ss’);
THREAD# SEQUENCE# NAME
———- ———- —————————-
1 2 /app_target/arch/1_2_1023532682.dbf
1 1 /app_target/arch/1_1_1023532682.dbf
1 3 /app_target/arch/1_3_1023532682.dbf
3、安装logminer安装包
SQL>
@$ORACLE_HOME/rdbms/admin/dbmslm.sql
Package created.
Grant succeeded.
Synonym created.
desc dbms_logmnr确认是否安装完成
4、添加日志
添加第一个日志
execute dbms_logmnr.add_logfile(logfilename=>’/app_target/easdb_dg/arch/1_2_1023532682.dbf‘,options=>dbms_logmnr.new);
继续添加
execute dbms_logmnr.add_logfile(logfilename=>’/app_target/easdb_dg/arch/1_1_1023532682.dbf‘,options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>’/app_target/easdb_dg/arch/1_3_1023532682.dbf‘,options=>dbms_logmnr.addfile);
需要特别注意的是，继续添加OPTIONS选项是ADDFILE
5、开启 Logminer
execute dbms_logmnr.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
6、查询 v$logmnr_contents 视图
注意要加条件，不然会有极其多系统表的记录
查看日志中具体的内容，可以看到，小编执行的具体命令是 delete from scott.emp;
在logminer视图中，他被转化成具体的一条一条的delete语句。
select sql_redo from v$logmnr_contents where SEG_OWNER=’SCOTT’;
delete from “SCOTT”.”EMP” where “EMPNO” = ‘7369’ and “ENAME” = ‘SMITH’ and “JOB” = ‘CLERK’ and “MGR” = ‘7902’ and “HIREDATE” = TO_DATE(’17-DEC-80′, ‘DD-MON-RR’) and “SAL” = ‘800’ and “COMM” IS NULL and “DEPTNO” = ’20’ and ROWID = ‘AAAVREAAE
AAAACXAAA’;
delete from “SCOTT”.”EMP” where “EMPNO” = ‘7499’ and “ENAME” = ‘ALLEN’ and “JOB” = ‘SALESMAN’ and “MGR” = ‘7698’ and “HIREDATE” = TO_DATE(’20-FEB-81′, ‘DD-MON-RR’) and “SAL” = ‘1600’ and “COMM” = ‘300’ and “DEPTNO” = ’30’ and ROWID = ‘AAAVR
EAAEAAAACXAAB’;
。。。
14 rows selected.
如果我们想直接插入还原的话，不需要将上面的查询的结果转化，贴心的logmnr提供了sql_undo这一列，sql_undo和sql_redo相对应，如下。
select sql_undo from v$logmnr_contents where SEG_OWNER=’SCOTT’;
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’),’800′,NULL,’20’);
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’),’1600′,’300′,’30’);
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’),’1250′,’500′,’30’);
。。。
14 rows selected.
注意，truncate和drop等ddl的语句的sql_undo都是空的，sql_redo也仅仅是操作的那一条语句，比如 drop table test;所以无法通过logmnr来恢复数据
最后，将 sql_undo 的语句提交给客户，至此，大功告成！
美创运维中心数据库服务团队拥有Oracle ACE 1人、OCM 10余人、数十名Oracle OCP、MySQL OCP、红帽RHCA、中间件weblogic、tuxedo认证、达梦工程师 ，著有《Oracle DBA实战攻略》，《Oracle数据库性能优化方法和最佳实践》，《Oracle内核技术揭秘》等多本数据运维优化书籍。目前运维各类数据库合计2000余套，精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一，拥有海量经验和完善的人员培养体系。并同时提供超融合，私有云整体解决方案。
来源：freebuf.com 2020-06-22 11:33:59 by: database
请登录后发表评论
注册