Oracle LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle 重作日志文件（归档日志文件）中的具体内容，特别是，该工具可以分析出所有对于数据库操作的DML（insert、update、delete等）语句， 另外还可分析得到一些必要的回滚SQL语句。该工具特别适用于调试、审计或者回退某个特定的事务。下面简述如何使用：
（1）安装logmnr包
SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql
（1）安装logmnr包
SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql
Package created.
Grant succeeded.
Synonym created.
SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
Package created.
Synonym created.
SQL> @$ORACLE_HOME/rdbms/admin/dbmslms.sql
Package created.
No errors.
Grant succeeded.
（2）建数据字典
SQL> show parameter utl
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string
SQL> alter system set utl_file_dir='/home/oracle/' scope=spfile;
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string
SQL> alter system set utl_file_dir='/home/oracle/' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 497995776 bytes
Fixed Size 1337464 bytes
Variable Size 360712072 bytes
Database Buffers 130023424 bytes
Redo Buffers 5922816 bytes
Database mounted.
Database opened.
SQL> show parameter utl
Fixed Size 1337464 bytes
Variable Size 360712072 bytes
Database Buffers 130023424 bytes
Redo Buffers 5922816 bytes
Database mounted.
Database opened.
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /home/oracle/
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /home/oracle/
SQL> execute dbms_logmnr_d.build(dictionary_filename => 'logmnr_dict.ora',dictionary_location => '/home/oracle/');
PL/SQL procedure successfully completed.
（3）添加日志文件
SQL> execute DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/oracle11/oradata/ora11/redo01.log', PTIONS => DBMS_LOGMNR.NEW);
SQL> execute DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/oracle11/oradata/ora11/redo01.log', PTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> execute DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/oracle11/oradata/ora11/redo02.log', PTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> execute DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle/oracle11/oradata/ora11/redo03.log', PTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
（4）分析日志
SQL> execute dbms_logmnr.start_logmnr(DictFileName=> '/home/oracle/logmnr_dict.ora');
SQL> execute dbms_logmnr.start_logmnr(DictFileName=> '/home/oracle/logmnr_dict.ora');
PL/SQL procedure successfully completed.
（5）查询结果
SQL> select sql_redo , sql_undo from v$logmnr_contents where table_name='SOURCE$';
SQL_REDO SQL_UNDO
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE"
) values ('57810','1','library ORDIMLIBS as ''/u01
/oracle/oracle11/product/11.2/lib/libordim11.so'';
');
SQL_UNDO
delete from "SYS"."SOURCE$" where "OBJ#" = '57810'
and "LINE" = '1' and "SOURCE" = 'library ORDIMLIB
S as ''/u01/oracle/oracle11/product/11.2/lib/libor
dim11.so'';' and ROWID = 'AAAADfAABAAAUw9AAA';
SQL> select sql_redo , sql_undo from v$logmnr_contents where table_name='SOURCE$';
SQL_REDO SQL_UNDO
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE"
) values ('57810','1','library ORDIMLIBS as ''/u01
/oracle/oracle11/product/11.2/lib/libordim11.so'';
');
SQL_UNDO
delete from "SYS"."SOURCE$" where "OBJ#" = '57810'
and "LINE" = '1' and "SOURCE" = 'library ORDIMLIB
S as ''/u01/oracle/oracle11/product/11.2/lib/libor
dim11.so'';' and ROWID = 'AAAADfAABAAAUw9AAA';
（6）执行恢复
SQL> delete from "SYS"."SOURCE$"
where "OBJ#" = '57810'
and "LINE" = '1'
and "SOURCE" =
'library ORDIMLIBS as ''/u01/oracle/oracle11/product/11.2/lib/libordim11.so'';'
and ROWID = 'AAAADfAABAAAUw9AAA'; 2 3 4 5 6
SQL> delete from "SYS"."SOURCE$"
where "OBJ#" = '57810'
and "LINE" = '1'
and "SOURCE" =
'library ORDIMLIBS as ''/u01/oracle/oracle11/product/11.2/lib/libordim11.so'';'
and ROWID = 'AAAADfAABAAAUw9AAA'; 2 3 4 5 6
1 row deleted.
注意，v$logmnr_contents 不是一个真实的物理表，数据是存放在内存中;字段SQL_REDO和SQL_UNDO互为反向操作;也可以采用 CAST(create table as select)方式将数据保存到物理表中以便分析/处理;
（7）退出logmnr
SQL> execute dbms_logmnr.end_logmnr;
注意，v$logmnr_contents 不是一个真实的物理表，数据是存放在内存中;字段SQL_REDO和SQL_UNDO互为反向操作;也可以采用 CAST(create table as select)方式将数据保存到物理表中以便分析/处理;
（7）退出logmnr
SQL> execute dbms_logmnr.end_logmnr;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28278387/viewspace-746990/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
关于临时表空间常用的几个查询sql
下一篇：
LOGMNR恢复测试-2
请登录后发表评论
登录
全部评论
