logmnr

3/7/2017来源:SQL技巧人气:2669

重做日志是记录数据库所有变更的日志, 只要数据经历了变更,数据的变更前值 和变更后值均会保存在重做日志里。

当遇到由于闪回技术时间窗过小,数据泵没有合适的泵出文件,介质恢复没有合适的备份文件时,重做日志就成为了恢复的最后手段。重做日志挖掘也经常用来实现精确恢复。

v$logmnr_contents视图

v$logmnr_contents视图是logminer挖掘日志的接口,每当v$logmnr_contents被访问一次,相关的重做日志就会被读取一次。实际上日志挖掘是对v$logmnr_contents视图的查询。但是如果在没有启动日志挖掘会话之前,对v$logmnr_conten的读取会报错。

使用logminer                                                               

logminer的4个元素:源数据库、挖掘数据库、logminer字典、重做日志。

1、源数据:指提供需要被挖掘的重做日志的数据库。

2、挖掘数据库:只启动挖掘会话的数据库。挖掘数据库与源数据库可以不是同一个库,但是其硬件平台和字符集与源库一致,挖掘数据库的版本必须大于或者等于原库版本。(源库版本最低Oracle 8i)。

3、logminer字典:logminer字典是将重做记录中的oracle内部对象翻译为可读信息的转换字典。

4、提供需挖掘的重做日志由源库产生,或者属于同一个化身。

使用logminer的前提是开启补充日志。最起码应打开最小补充日志。

查看最小补充日志是否打开

SQL> select supplemental_log_data_min from v$database; SUPPLEME -------- NO

打开最小补充日志

SQL> alter database add supplemental log data; Database altered. 启动最小补充日子的目的是使用logminer具备识别由update命令导致的行迁移,行移动的能力。不然,所有的非sys用户的dml命令将无法通过logminer挖掘。当然也可以启动其他级别的数据库级补充日志,这样会隐式启动最小补充日志。

确定logminr字典的位置

logminer字典来源有三种:源库日志字典、源库在线日志、文本文件日志

1、日志字典:

日志字典是指调用dbms_logmnr_d.build存储过程将logminer字典提取至源数据库的重做日志里。

SQL> exec dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs); PL/SQL PRocedure successfully completed. 可以查看logminer字典被提取到重做日志的那里了。

SQL> select sequence#,name,dictionary_begin,dictionary_end from v$archived_log where dictionary_begin='YES' or dictionary_end='YES';  SEQUENCE#     NAME                                                                                          DIC DIC ----------  -------------------------------------------------------------------------------- 11     /u01/oracle/product/Flash_recovery_area/ORCL/archivelog/2013_06_03/o1_mf_1_11_8tscfj0o_.arc    YES YES

结果表明,日志字典都包含在11号归档日志里面。

使用日志字典有两个好处:

a、不要求挖掘库与源库为同一数据库

b、ddl命名可以更新该字典。使用dll_dict_tracking常量启动挖掘,能更新日志字典,使挖掘信息得到完全体现。

2、使用源库在线字典

使用源库在线数据库字典,不用调用存储过程,直接在启动start_logmnr启动挖掘会话时,通过options指定continous_mine。使用源库在线日志字典,源库和挖掘库必须是同一个,而且ddl命令无法更新该字典。

3、文本文件字典

文本文件字典,是将字典载入一个文本文件中,需要为utl_file_dir指定一个初始化参数。

SQL> alter system set utl_file_dir='/u01/oracle/product/temp' scope=spfile;

utl_file_dir是静态参数,需要重启实例。然后用dbms_logmnr_d.build存储过程的options指定store_in_flat_file常量。

exec dbms_logmnr_d.build(dictionary_filename=>'logminerorcl.ora',dictionary_location=>'/u01/oracle/product/temp',options=>dbms_logmnr_d.store_in_flat_file);

文本文件/u01/oracle/product/temp/logminerorcl.ora就可以作为logminer字典使用了。

注册重做日志

手动注册

SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/oracle/product/flash_recovery_area/ORCL/archivelog/2013_06_03/o1_mf_1_11_8tscfj0o_.arc',

    options=>dbms_logmnr.addfile)

在调用add_loggile之后,可以利用v$logmnr_logs查看已注册的日志;

SQL> select low_scn,thread_id from v$logmnr_logs; 自动注册

自动注册不用调用dbms_logmnr.add_logfile过程,而是通过dbms_logmnr.start_logmnr启动挖掘会话的时候对options参数传入 continuous_mine常量。但是要求:挖掘库必须与源库是同一个;启动会话时,必须制定时间窗(或者SCN)搜索日志;控制文件内必须具有所需日志的记录。

启动挖掘会话

调用dbms_logmnr.start_logmnr启动挖掘会话时,必须明白两点:1、logminer字典如何提供;2、挖掘日志如何注册。

情况1:源库与挖掘库相同,add_file手动注册、在线字典

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

情况2:源库=挖掘库,在线字典,自动注册日志

SQL> exec dbms_logmnr.start_logmnr(

                       starttime=>to_date('2013-06-05 22:30:00','yyyy-mm-dd hh24:mi:ss'),

                        endtime=>to_date('2013-06-05 23:30:00','yyyy-mm-dd hh24:mi:ss'),

                         options=>dbms_logmnr.dict_from_online_catalog+

                                         dbms_logmnr.continuous_mine);

情况3:日志字典、aff_file手动注册日志

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_redo_logs);

情况4:日志字典、自动注册日志

SQL> exec dbms_logmnr.start_logmnr(

                       starttime=>to_date('2013-06-05 22:30:00','yyyy-mm-dd hh24:mi:ss'),

                        endtime=>to_date('2013-06-05 23:30:00','yyyy-mm-dd hh24:mi:ss'),

                         options=>dbms_logmnr.dict_from_redo_logs+

                                         dbms_logmnr.continuous_mine);

启动dbms_logmnr.start_logmnr时,options赋予的常量:

1、committed_data_only:表示在v$logmnr_contents视图时,只返回已经提交了的变更的sql_redo和sql_undo

2、skip_corruption :能够忽略在查询v$logmnt_contents视图时,遭遇到的日志损坏。因为每对该视图的查询,就会扫描日志。

3、ddl_dict_tarcking:能够是logminer日志字典被ddl命令更新。

4、dict_from_online_catalog:声明logminer字典来源于在线数据字典。此参数与dll_dict_tracking不兼容。

5、dict_from_redo_logs:声明logminer字典来自日志字典。

6、no_sql_delimiter:能够将sql_redo和sql_undo后面的分好‘;’ 去掉。

7、print_pretty_sql:简单格式化sql_redo和sql_undo。

8、continuous_mine:说明挖掘日志支持自动注册,不要求调用add_logfile手动注册日志。

9、no_rowid_in_stmt:能够将sql_redo和sql_undo的where子句中‘rowid=’条件去掉。

10、string_literals_in_stmt:指定sql_redo和sql_undo中的number,日期、时间格式类型按照字面值表示。

挖掘

在dbms_logmnr.start_logmnr成功调用之后,就可以对视图v$logmnr_contents查询了。

一把建议建一张与v$logmnr_contents相同的临时表以存储挖掘中想要的数据,以便避免对该视图的多次查询,毕竟,对该视图的一次查询,就会扫描注册的日志。如果日记较多,是一个比较耗时的过程。

SQL>select rownum sql#,sql_redo,sql_undo from v$logmnr_contents where seg_omner='SCOTT' and seg_name='DEPT';

关闭会话 

exec dbms_logmnr.end_logmnr;