通过日志挖掘进行数据恢复

第一步:
执行创建挖掘的脚本

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

SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/dbmslmd.sql

@?/rdbms/admin/dbmslmd.sql
Package created.

Synonym created.

SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/dbmslm.sql

@?/rdbms/admin/dbmslm.sql

Package created.

Grant succeeded.

Synonym created.

alter system set utl_file_dir='/home/oracle' scope=spfile;   注意路径不要多空格或者/符号

生成数据字典文件exec dbms_logmnr_d.build( 'dictionary.ora', '/home/oracle');

在使用 LogMiner 工具分析redo log文件之前，可以使用DBMS_LOGMNR_D包将数据字典导出为一个文本文件。该字典文件是可选的，但是如果没有它，LogMiner解释出来的语句中关于数据字典中的部分（如表名、列名等）和数值都将是16进制的形式，我们是无法直接理解的。

第二步：
可选：更改日志记录格式，在日志记录信息不全的时候可以选择更改

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

NO
SQL> alter database add supplemental log data;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

YES
alter database drop supplemental log data;关闭补全日志

第三步：创建要分析的日志列表

exec dbms_logmnr.add_logfile('+DATA/his/archivelog/2024_05_10/thread_3_seq_40792.836.1168560409', dbms_logmnr.new);  
exec dbms_logmnr.add_logfile('+DATA/his/archivelog/2024_05_10/thread_3_seq_40793.4291.1168561041', dbms_logmnr.addfile);  
exec dbms_logmnr.add_logfile('+DATA/his/archivelog/2024_05_10/thread_2_seq_41826.3767.1168561043', dbms_logmnr.addfile);  
exec dbms_logmnr.add_logfile('+DATA/his/archivelog/2024_05_10/thread_3_seq_40794.5438.1168561629', dbms_logmnr.addfile);  
exec dbms_logmnr.add_logfile('+DATA/his/archivelog/2024_05_10/thread_1_seq_43032.5668.1168561629', dbms_logmnr.addfile);  
exec dbms_logmnr.add_logfile('+DATA/his/archivelog/2024_05_10/thread_2_seq_41827.2516.1168566089', dbms_logmnr.addfile);  
exec dbms_logmnr.add_logfile('+DATA/his/archivelog/2024_05_10/thread_2_seq_41828.9804.1168566133', dbms_logmnr.addfile);  
exec dbms_logmnr.add_logfile('+DATA/his/archivelog/2024_05_10/thread_3_seq_40795.3234.1168584127', dbms_logmnr.addfile);  
exec dbms_logmnr.add_logfile('+DATA/his/archivelog/2024_05_10/thread_2_seq_41829.5589.1168584127', dbms_logmnr.addfile);  
exec dbms_logmnr.add_logfile('+DATA/his/archivelog/2024_05_10/thread_1_seq_43033.9955.1168584127', dbms_logmnr.addfile);

第四步：生成数据字典使用数据字典进行查询

exec dbms_logmnr_d.build( 'dictionary.ora', 'E:\arch');
exec dbms_logmnr.start_logmnr( dictfilename=>'/home/oracle/dictionary.ora');指定数据字典
或者使用在线字典进行查询
exec sys.dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

EXEC SYS.DBMS_LOGMNR.START_LOGMNR(options=>dbms_logmnr.dict_from_online_catalog,COMMITTED_DATA_ONLY);

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';
exec sys.dbms_logmnr.start_logmnr(STARTTIME =>'2024-05-10 01:00:00',ENDTIME=>'2024-05-10 02:00:00',options=>dbms_logmnr.dict_from_online_catalog);

若想分组DML语句提交事务，使用如下示例：

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
  OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
             DBMS_LOGMNR.COMMITTED_DATA_ONLY);

若想格式化输出SQL，使用如下示例：

EXECUTE DBMS_LOGMNR.START_LOGMNR(-
  OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
             DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
             DBMS_LOGMNR.PRINT_PRETTY_SQL);

若想按时间范围过滤输出，使用如下示例：

EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME =>'2024-05-10 01:00:00',ENDTIME=>'2024-05-10 02:00:00',OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

exec sys.dbms_logmnr.END_LOGMNR();

select count(*)  from v$logmnr_contents;
第五步:
将相关表进行备份
create table  hisrun.test_logmnr01  as select * from v$logmnr_contents;

第六步： 对挖掘sql进行处理
create table   LOGMNR_NEW as   SELECT / +full(t1)/COMMIT_TIMESTAMP,TIMESTAMP,regexp_substr(replace(sql_redo,'and ROWID','?'),'[^?]+',1,1) sql_redo FROM  test_logmnr t1
where TABLE_NAME ='T2' and OPERATION  in ('INSERT','UPDATE','DELETE','LOB_WRITE');

TMMBW01

第七步： 进行恢复
set serverout on
DECLARE
c_limit   CONSTANT PLS_INTEGER DEFAULT 1000;
v_sql varchar2(32767);
CURSOR c1 IS select / +full(t1)/ rtrim(sql_redo,';') from logmnr_new t order by COMMIT_TIMESTAMP,TIMESTAMP;  --undo desc
TYPE typ1 IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
redo         typ1;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO redo LIMIT c_limit;
EXIT WHEN redo.COUNT = 0;
FOR indx IN 1 .. redo.COUNT
LOOP
begin
v_sql:=redo(indx);
dbms_output.put_line(v_sql);
execute immediate v_sql;
exception
when others then
dbms_output.put_line('ERROR:'||v_sql);
end;
END LOOP;
commit;
END LOOP;
CLOSE c1;
END;
/

-----undo 修复

create table   LOGMNR_NEW2 as   SELECT / +full(t1)/COMMIT_TIMESTAMP,TIMESTAMP,regexp_substr(replace(SQL_UNDO,'and ROWID','?'),'[^?]+',1,1) SQL_UNDO FROM  test_logmnr t1
where TABLE_NAME ='T2' and OPERATION  in ('INSERT','UPDATE','DELETE','LOB_WRITE');

第七步： 进行恢复
set serverout on
DECLARE
c_limit   CONSTANT PLS_INTEGER DEFAULT 1000;
v_sql varchar2(32767);
CURSOR c1 IS select / +full(t1)/ rtrim(SQL_UNDO,';') from LOGMNR_NEW2 t order by COMMIT_TIMESTAMP desc,TIMESTAMP desc;
TYPE typ1 IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
undo         typ1;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO undo LIMIT c_limit;
EXIT WHEN undo.COUNT = 0;
FOR indx IN 1 .. undo.COUNT
LOOP
begin
v_sql:=undo(indx);
dbms_output.put_line(v_sql);
execute immediate v_sql;
exception
when others then
dbms_output.put_line('ERROR:'||v_sql);
end;
END LOOP;
commit;
END LOOP;
CLOSE c1;
END;
/