logminer操作步驟
前提：
1 數(shù)據(jù)可以歸檔模式也可以非歸檔模式。
2 需要調(diào)整supplemental_log_data_min才能進(jìn)行日志挖掘。
sys@oratest1(test-for-lihb)> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
sys@oratest1(test-for-lihb)> alter database add supplemental log data;
Database altered.
sys@oratest1(test-for-lihb)> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
3 日志挖掘是基于session的，如果session退出，需要重新挖掘。
步驟：
1 查看當(dāng)前在線日志文件使用情況：
sys@oratest1(test-for-lihb)> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
1 1 565 52428800 5122 NO CURRENT 8490175 2017-11-13 10:37:37 2.8147E+14
2 1 563 52428800 5122 NO INACTIVE 8481985 2017-11-13 07:00:208489600 2017-11-13 10:21:05
3 1 564 52428800 5122 NO INACTIVE 8489600 2017-11-13 10:21:058490175 2017-11-13 10:37:37
2 創(chuàng)建路徑
sys@oratest1(test-for-lihb)> CREATE DIRECTORY utlfile AS '/home/oracle/logmnr';
Directory created.
3 修改參數(shù)
sys@oratest1(test-for-lihb)> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
System altered.
4 建/home/oracle/logmnr目錄，重啟數(shù)據(jù)庫(kù)使utl_file_dir參數(shù)生效
[oracle@test-for-lihb ~]$ mkdir -p /home/oracle/logmnr
sys@oratest1(test-for-lihb)> shutdown immediate
sys@oratest1(test-for-lihb)> startup
5 創(chuàng)建數(shù)據(jù)字典文件
sys@oratest1(test-for-lihb)> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/logmnr');
PL/SQL procedure successfully completed.
6 查看日志文件物理位置（也可以查看歸檔文件）
sys@oratest1(test-for-lihb)> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------------------------------ ------------------------------------------------------------ ---
1 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_1_dhl9h7tz_.log NO
1 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_1_dhl9h8bn_.log YES
2 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_2_dhl9h8mc_.log NO
2 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_2_dhl9h91y_.log YES
3 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_3_dhl9h98x_.log NO
3 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_3_dhl9h9y1_.log YES
6 rows selected.
7 模擬scott用戶插入數(shù)據(jù)
scott@oratest1(70)> truncate table T;
Table truncated.
scott@oratest1(70)> insert into t values (10086);
1 row created.
scott@oratest1(70)> insert into t values (10087);
1 row created.
scott@oratest1(70)> insert into t values (10088);
1 row created.
scott@oratest1(70)> insert into t values (10089);
1 row created.
scott@oratest1(70)> commit;
Commit complete.
8 加入日志文件（一個(gè)日志組如果有多個(gè)成員，只需要加入一個(gè)即可，因?yàn)橥蝗罩窘M所有成員內(nèi)容是相同的。）
第一個(gè)日志文件參數(shù)是dbms_logmnr.NEW
BEGIN
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_3_dhl9h98x_.log',options=>dbms_logmnr.NEW);
END;
/
后續(xù)的日志文件參數(shù)是dbms_logmnr.ADDFILE
BEGIN
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_1_dhl9h7tz_.log',options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_2_dhl9h8mc_.log',options=>dbms_logmnr.ADDFILE);
END;
/
9 開(kāi)始日志挖掘
無(wú)限制挖掘：
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora');
特定時(shí)間段挖掘（未測(cè)試）：
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora',StartTime =>to_date('2013-6-8 00:00:00','YYYY-MM-DD HH24:MI:SS')EndTime =>to_date(''2013-6-8 23:59:59','YYYY-MM-DD HH24:MI:SS '));
10 查看挖掘結(jié)果
sys@oratest1(test-for-lihb)> select sql_redo from v$logmnr_contents where username='SCOTT';
。。。
insert into "SCOTT"."T"("ID") values ('10086');
insert into "SCOTT"."T"("ID") values ('10087');
insert into "SCOTT"."T"("ID") values ('10088');
insert into "SCOTT"."T"("ID") values ('10089');
。。。。
11 關(guān)閉日志挖掘。
EXECUTE DBMS_LOGMNR.END_LOGMNR;
1 數(shù)據(jù)可以歸檔模式也可以非歸檔模式。
2 需要調(diào)整supplemental_log_data_min才能進(jìn)行日志挖掘。
sys@oratest1(test-for-lihb)> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
sys@oratest1(test-for-lihb)> alter database add supplemental log data;
Database altered.
sys@oratest1(test-for-lihb)> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
3 日志挖掘是基于session的，如果session退出，需要重新挖掘。
步驟：
1 查看當(dāng)前在線日志文件使用情況：
sys@oratest1(test-for-lihb)> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
1 1 565 52428800 5122 NO CURRENT 8490175 2017-11-13 10:37:37 2.8147E+14
2 1 563 52428800 5122 NO INACTIVE 8481985 2017-11-13 07:00:208489600 2017-11-13 10:21:05
3 1 564 52428800 5122 NO INACTIVE 8489600 2017-11-13 10:21:058490175 2017-11-13 10:37:37
2 創(chuàng)建路徑
sys@oratest1(test-for-lihb)> CREATE DIRECTORY utlfile AS '/home/oracle/logmnr';
Directory created.
3 修改參數(shù)
sys@oratest1(test-for-lihb)> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
System altered.
4 建/home/oracle/logmnr目錄，重啟數(shù)據(jù)庫(kù)使utl_file_dir參數(shù)生效
[oracle@test-for-lihb ~]$ mkdir -p /home/oracle/logmnr
sys@oratest1(test-for-lihb)> shutdown immediate
sys@oratest1(test-for-lihb)> startup
5 創(chuàng)建數(shù)據(jù)字典文件
sys@oratest1(test-for-lihb)> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/logmnr');
PL/SQL procedure successfully completed.
6 查看日志文件物理位置（也可以查看歸檔文件）
sys@oratest1(test-for-lihb)> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------------------------------ ------------------------------------------------------------ ---
1 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_1_dhl9h7tz_.log NO
1 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_1_dhl9h8bn_.log YES
2 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_2_dhl9h8mc_.log NO
2 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_2_dhl9h91y_.log YES
3 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_3_dhl9h98x_.log NO
3 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_3_dhl9h9y1_.log YES
6 rows selected.
7 模擬scott用戶插入數(shù)據(jù)
scott@oratest1(70)> truncate table T;
Table truncated.
scott@oratest1(70)> insert into t values (10086);
1 row created.
scott@oratest1(70)> insert into t values (10087);
1 row created.
scott@oratest1(70)> insert into t values (10088);
1 row created.
scott@oratest1(70)> insert into t values (10089);
1 row created.
scott@oratest1(70)> commit;
Commit complete.
8 加入日志文件（一個(gè)日志組如果有多個(gè)成員，只需要加入一個(gè)即可，因?yàn)橥蝗罩窘M所有成員內(nèi)容是相同的。）
第一個(gè)日志文件參數(shù)是dbms_logmnr.NEW
BEGIN
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_3_dhl9h98x_.log',options=>dbms_logmnr.NEW);
END;
/
后續(xù)的日志文件參數(shù)是dbms_logmnr.ADDFILE
BEGIN
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_1_dhl9h7tz_.log',options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_2_dhl9h8mc_.log',options=>dbms_logmnr.ADDFILE);
END;
/
9 開(kāi)始日志挖掘
無(wú)限制挖掘：
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora');
特定時(shí)間段挖掘（未測(cè)試）：
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora',StartTime =>to_date('2013-6-8 00:00:00','YYYY-MM-DD HH24:MI:SS')EndTime =>to_date(''2013-6-8 23:59:59','YYYY-MM-DD HH24:MI:SS '));
10 查看挖掘結(jié)果
sys@oratest1(test-for-lihb)> select sql_redo from v$logmnr_contents where username='SCOTT';
。。。
insert into "SCOTT"."T"("ID") values ('10086');
insert into "SCOTT"."T"("ID") values ('10087');
insert into "SCOTT"."T"("ID") values ('10088');
insert into "SCOTT"."T"("ID") values ('10089');
。。。。
11 關(guān)閉日志挖掘。
EXECUTE DBMS_LOGMNR.END_LOGMNR;
本文題目：logminer操作步驟
文章轉(zhuǎn)載：http://bm7419.com/article48/jdjohp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務(wù)器托管、、響應(yīng)式網(wǎng)站、企業(yè)建站、建站公司、網(wǎng)站維護(hù)
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 創(chuàng)新互聯(lián)設(shè)計(jì)師談品牌網(wǎng)站設(shè)計(jì)中色彩如何運(yùn)用 2023-02-18
- 品牌網(wǎng)站設(shè)計(jì)如何達(dá)到展示目的 2022-12-14
- 深圳品牌網(wǎng)站設(shè)計(jì)公司 2014-05-13
- 品牌網(wǎng)站設(shè)計(jì)制作的簡(jiǎn)單步驟 2022-12-03
- 品牌網(wǎng)站設(shè)計(jì)定制解決方案 2021-08-16
- 好的品牌網(wǎng)站設(shè)計(jì)兩大要點(diǎn)必知 2022-06-10
- 品牌網(wǎng)站設(shè)計(jì)都需要一個(gè)“新聞工具包” 2023-03-02
- 如何做好品牌網(wǎng)站設(shè)計(jì)，成都網(wǎng)站建設(shè)公司來(lái)幫您。 2022-08-20
- 品牌網(wǎng)站設(shè)計(jì)開(kāi)發(fā)要點(diǎn) 2016-11-09
- 品牌網(wǎng)站設(shè)計(jì)有哪些問(wèn)題要注意？ 2020-12-26
- 品牌網(wǎng)站設(shè)計(jì)制作過(guò)程中要注意哪些問(wèn)題？ 2020-12-03
- 品牌網(wǎng)站設(shè)計(jì)的四大原則 2013-09-05