Oracle误删除数据用logminer恢复

一.安装LogMiner

要安装LogMiner工具，必须首先要运行下面这样两个脚本，

l $ORACLE_HOME/rdbms/admin/dbmslsm.sql

2 $ORACLE_HOME/rdbms/admin/dbmslsmd.sql.

这两个脚本必须均以SYS用户身份运行。其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

二.问题解决

甲方DB反应是表FIL_PROJECT被误删除，删除时间为上午10：03

1.查询该表信息：

SQL> select owner,object_name,object_type from dba_objects where object_name='FIL_PROJECT';

OWNER OBJECT_NAME OBJECT_TYPE
-------------------- ----------------------------------- -------------------
SFLCGOOD FIL_PROJECT TABLE
SFLCGOOD20180124 FIL_PROJECT TABLE
SFLCGOOD20180301 FIL_PROJECT TABLE

SQL> select count(*) from SFLCGOOD.FIL_PROJECT;

COUNT(*)
----------
44933

SQL> select count(*) from SFLCGOOD20180124.FIL_PROJECT;

COUNT(*)
----------
43750

SQL> select count(*) from SFLCGOOD20180301.FIL_PROJECT;

COUNT(*)
----------
44000

与DB沟通误删除的为用户SFLCGOOD下的该表。

2.查询redo日志产生时间如下：

SQL> select group#,thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') from v$log;

GROUP# THREAD# SEQUENCE# TO_CHAR(FIRST_TIME,
---------- --- ------- ---------- -------------------
1 1 8456 2018-03-30 14:45:43
2 1 8452 2018-03-30 11:55:42
3 1 8453 2018-03-30 12:45:31
4 1 8454 2018-03-30 13:25:35
5 1 8455 2018-03-30 14:05:36
6 2 3351 2018-03-30 07:05:21
7 2 3352 2018-03-30 09:15:21
8 2 3353 2018-03-30 10:55:23
9 2 3354 2018-03-30 12:45:20
10 2 3355 2018-03-30 14:45:32

查看最近10点的时间sequence#号为 8452 的时间为11:55:42，那么可以找寻其之前的sequence#号进行分析。

SQL> select thread#,sequence#,name,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') from v$archived_log where sequence# in(8449,8450,3352);

THREAD# SEQUENCE# NAME TO_CHAR(FIRST_TIME,
---------- ---------- --------------------------------------------------------------------------- -------------------
1 8449 +ARCH01/sflcdb/archivelog/2018_03_30/thread_1_seq_8449.447.972125735 2018-03-30 10:15:33
2 3352 +ARCH01/sflcdb/archivelog/2018_03_30/thread_2_seq_3352.441.972125723 2018-03-30 09:15:21
1 8450 +ARCH01/sflcdb/archivelog/2018_03_30/thread_1_seq_8450.431.972126939 2018-03-30 10:55:35

使用logminer工具进行分析：

ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy hh24:mi:ss';

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+ARCH01/sflcdb/archivelog/2018_03_30/thread_1_seq_8449.447.972125735', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+ARCH01/sflcdb/archivelog/2018_03_30/thread_2_seq_3352.441.972125723');

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

SELECT OPERATION,SEG_OWNER,TABLE_NAME,SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE OPERATION='DELETE' and table_name='FIL_PROJECT';
SELECT OPERATION,TIMESTAMP,SEG_OWNER,TABLE_NAME from V$LOGMNR_CONTENTS where OPERATION='DELETE' and table_name='FIL_PROJECT';

SELECT count(*) FROM V$LOGMNR_CONTENTS WHERE OPERATION='DELETE' and table_name='FIL_PROJECT';

#########
SQL> SELECT OPERATION,TIMESTAMP,SEG_OWNER,TABLE_NAME from V$LOGMNR_CONTENTS where OPERATION='DELETE' and table_name='FIL_PROJECT';

分析出的删除时间为10：45，不是误操作时间，继续分析。

EXECUTE DBMS_LOGMNR.END_LOGMNR(); （先结束）

3.查找合适的日志继续分析

sequence#在8449时候找到时间为10：45，那么再提前一个sequence#号，找8448.

SQL> select thread#,sequence#,name,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') from v$archived_log where sequence# in(8448，8449,8450);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+ARCH01/sflcdb/archivelog/2018_03_30/thread_1_seq_8448.442.972123333', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+ARCH01/sflcdb/archivelog/2018_03_30/thread_1_seq_8449.447.972125735');
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+ARCH01/sflcdb/archivelog/2018_03_30/thread_1_seq_8450.431.972126939');

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

SQL> SELECT OPERATION,TIMESTAMP,SEG_OWNER,TABLE_NAME from V$LOGMNR_CONTENTS where OPERATION='DELETE' and table_name='FIL_PROJECT';

OPERATION TIMESTAMP SEG_OWNER TABLE_NAME
-------------------------------- ----------------------------- -------------------------------- --------------------------------
DELETE 30-mar-2018 10:03:35 SFLCGOOD FIL_PROJECT
DELETE 30-mar-2018 10:03:35 SFLCGOOD FIL_PROJECT
DELETE 30-mar-2018 10:03:35 SFLCGOOD FIL_PROJECT
DELETE 30-mar-2018 10:03:35 SFLCGOOD FIL_PROJECT
DELETE 30-mar-2018 10:03:36 SFLCGOOD FIL_PROJECT
DELETE 30-mar-2018 10:03:36 SFLCGOOD FIL_PROJECT
DELETE 30-mar-2018 10:03:36 SFLCGOOD FIL_PROJECT
DELETE 30-mar-2018 10:03:36 SFLCGOOD FIL_PROJECT
DELETE 30-mar-2018 10:03:36 SFLCGOOD FIL_PROJECT
DELETE 30-mar-2018 10:03:36 SFLCGOOD FIL_PROJECT
DELETE 30-mar-2018 10:03:36 SFLCGOOD FIL_PROJECT

这回查询到了10：03的操作。

SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE OPERATION='DELETE' and table_name='FIL_PROJECT' and TIMESTAMP='30-mar-2018 10:03:35';

create table SFLCGOOD.FIL_PROJECT_20180330_1 as select * from V$LOGMNR_CONTENTS;

SELECT count(*) FROM SFLCGOOD.FIL_PROJECT_20180330_1 WHERE OPERATION='DELETE' and table_name='FIL_PROJECT';

语句（1）select sql_redo from SFLCGOOD.FIL_PROJECT_20180330_1 WHERE OPERATION='DELETE' and table_name='FIL_PROJECT';

语句（2）select sql_undo from SFLCGOOD.FIL_PROJECT_20180330_1 WHERE OPERATION='DELETE' and table_name='FIL_PROJECT';

备注：

（1）sql_redo查询的结果是delete的操作语句，sql_undo查询的是insert的操作。与DB确认语句（1）的查询结果，正好是误删除的数据。所以用语句2的sql_undo

查询的结构直接插入就可以了。（可以先插入个新表先试试）

（2）V$LOGMNR_CONTENTS 这个视图为用logminer分析出的结果。

posted @ 2018-04-01 13:55 feifeizouzou 阅读(805) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Oracle误删除数据用logminer恢复

公告