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

来源：这里教程网时间：2026-03-03 20:28:24 作者：

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

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

这两个脚本必须均以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; /