安裝和使用LogMiner工具
安裝LogMiner工具
以SYSDBA身份執行
[oracle@localhost ~ Thu Apr 28]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 28 15:57:33 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@EXAM> @$ORACLE_HOME/rdbms/admin/dbmslm.sql;
Package created.
Grant succeeded.
sys@EXAM> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql;
Package created.
第一個指令碼用來建立DBMS_LOGMNR包，該包用來分析日誌檔案。
第二個指令碼用來建立DBMS_LOGMNR_D包，該包用來建立資料字典檔案。
預設一般都安裝了的吧 可以分析日誌檔案 異機分析的話指定原資料庫的字典檔案即可
使用LogMiner工具
1、建立資料字典檔案（data-dictionary）
（1）首先在引數檔案中，指定資料字典檔案的位置，也就是新增一個引數UTL_FILE_DIR，
UTL_FILE_DIR = ($ORACLE_BASE/utlfiledir) ,重新啟動資料庫，使新加的引數生效。
[oracle@localhost ~ Thu Apr 28]$ mkdir $ORACLE_BASE/utlfiledir
[oracle@localhost ~ Thu Apr 28]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 28 15:59:47 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@EXAM> alter system set utl_file_dir='/home/ora10g/utlfiledir' scope=spfile;
System altered.
sys@EXAM> startup force;
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218388 bytes
Variable Size 83888300 bytes
Database Buffers 88080384 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
sys@EXAM> show parameter utl_file_dir;
NAME TYPE VALUE
------------------------------------ ----------------------
------------------------------
utl_file_dir string /home/ora10g/utlfiledir
sys@EXAM> exec dbms_logmnr_d.build('logmnr_1.ora','/home/ora10g/utlfiledir');
LogMnr Dictionary Procedure started
LogMnr Dictionary File Opened
Procedure executed successfully - LogMnr Dictionary Created
PL/SQL procedure successfully completed.
注意事項：定義utl_file_dir 引數最好使用完整路徑,使用預定義環境變數會導致無法開啟文
件目錄
2、建立要分析的日誌檔案列表：獲取歸檔或者聯機日誌列表
sys@EXAM> set linesize 220
col size for 999.99 heading "SIZE"
col applied for a5 heading "APP"
col deleted for a3 heading "DEL"
col status for a3 heading "ST"
col fal for a5
col name for a40
col SEQ for 999 heading "SEQ#"
col REG for a4
col archival_thread# for 99 heading "THR#"
sys@EXAM> select sequence# "SEQ",name,first_change# "FIRST",next_change# "NEXT",blocks*block_size/1024/1024 "size",archival_thread#,applied,registrar "REG",deleted,status,fal from v$archived_log;
SEQ# NAME FIRST NEXT SIZE
ARCHIVAL_THREAD# APP REG DEL ST FAL
---- ---------------------------------------- ---------- ---------- -------
---------------- ----- ---- --- --- -----
6 /home/new/1_6_747929818.dbf 610480 619684 12.86
1 NO ARCH NO A NO
6 610480 619684 12.86
1 NO ARCH YES D NO
6 610480 619684 12.86
1 NO ARCH YES D NO
7 /home/new/1_7_747929818.dbf 619684 619700 .00
1 NO ARCH NO A NO
7 619684 619700 .00
1 NO ARCH YES D NO
7 619684 619700 .00
1 NO ARCH YES D NO
8 /home/new/1_8_747929818.dbf 619700 619717 .01
1 NO ARCH NO A NO
8 619700 619717 .01
1 NO ARCH YES D NO
8 619700 619717 .01
1 NO ARCH YES D NO
9 /home/new/1_9_747929818.dbf 619717 647196 10.85
1 NO ARCH NO A NO
9 619717 647196 10.85
1 NO ARCH YES D NO
9 619717 647196 10.85
1 NO ARCH YES D NO
10 /home/new/1_10_747929818.dbf 647196 681609 20.75
1 NO ARCH NO A NO
10 647196 681609 20.75
1 NO ARCH YES D NO
11 /home/new/1_11_747929818.dbf 681609 695099 48.47
1 NO ARCH NO A NO
11 681609 695099 48.47
1 NO ARCH YES D NO
12 /home/new/1_12_747929818.dbf 695099 710083 48.47
1 NO ARCH NO A NO
12 695099 710083 48.47
1 NO ARCH YES D NO
13 /home/new/1_13_747929818.dbf 710083 738902 27.14
1 NO ARCH NO A NO
13 710083 738902 27.14
1 NO ARCH YES D NO
14 /home/new/1_14_747929818.dbf 738902 739218 .06
1 NO ARCH NO A NO
14 738902 739218 .06
1 NO ARCH YES D NO
15 /home/new/1_15_747929818.dbf 739218 739220 .00
1 NO ARCH NO A NO
15 739218 739220 .00
1 NO ARCH YES D NO
16 /home/new/1_16_747929818.dbf 739220 739225 .00
1 NO ARCH NO A NO
16 739220 739225 .00
1 NO ARCH YES D NO
17 /home/new/1_17_747929818.dbf 739225 739275 .00
1 NO ARCH NO A NO
17 739225 739275 .00
1 NO ARCH YES D NO
18 /home/new/1_18_747929818.dbf 739275 762561 48.47
1 NO ARCH NO A NO
18 739275 762561 48.47
1 NO ARCH YES D NO
19 /home/new/1_19_747929818.dbf 762561 777385 48.47
1 NO ARCH NO A NO
19 762561 777385 48.47
1 NO ARCH YES D NO
20 /home/new/1_20_747929818.dbf 777385 801870 18.28
1 NO ARCH NO A NO
20 /home/ora10g/flash_recovery_area/EXAM/ar 777385 801870 18.28
1 NO ARCH NO A NO
chivelog/2011_04_16/o1_mf_1_20_6tktdnlb_
.arc
21 /home/new/1_21_747929818.dbf 801870 814146 48.47
1 NO ARCH NO A NO
21 /home/ora10g/flash_recovery_area/EXAM/ar 801870 814146 48.47
1 NO ARCH NO A NO
chivelog/2011_04_16/o1_mf_1_21_6tktrx92_
.arc
22 /home/new/1_22_747929818.dbf 814146 845578 45.25
1 NO ARCH NO A NO
22 /home/ora10g/flash_recovery_area/EXAM/ar 814146 845578 45.25
1 NO ARCH NO A NO
chivelog/2011_04_16/o1_mf_1_22_6tkzxz30_
.arc
23 /home/new/1_23_747929818.dbf 845578 848294 4.71
1 NO ARCH NO A NO
23 /home/ora10g/flash_recovery_area/EXAM/ar 845578 848294 4.71
1 NO ARCH NO A NO
chivelog/2011_04_16/o1_mf_1_23_6tl1ccp4_
.arc
24 /home/new/1_24_747929818.dbf 848294 848297 .01
1 NO ARCH NO A NO
24 /home/ora10g/flash_recovery_area/EXAM/ar 848294 848297 .01
1 NO ARCH NO A NO
chivelog/2011_04_16/o1_mf_1_24_6tl1ck82_
.arc
25 /home/new/1_25_747929818.dbf 848297 848301 .01
1 NO ARCH NO A NO
25 /home/ora10g/flash_recovery_area/EXAM/ar 848297 848301 .01
1 NO ARCH NO A NO
chivelog/2011_04_16/o1_mf_1_25_6tl1cq1p_
.arc
26 /home/new/1_26_747929818.dbf 848301 848502 1.11
1 NO ARCH NO A NO
26 /home/ora10g/flash_recovery_area/EXAM/ar 848301 848502 1.11
1 NO ARCH NO A NO
chivelog/2011_04_16/o1_mf_1_26_6tl1jrv8_
.arc
27 /home/new/1_27_747929818.dbf 848502 873111 13.95
1 NO ARCH NO A NO
27 /home/ora10g/flash_recovery_area/EXAM/ar 848502 873111 13.95
1 NO ARCH NO A NO
chivelog/2011_04_16/o1_mf_1_27_6tl421hn_
.arc
28 /home/new/1_28_747929818.dbf 873111 890072 48.47
1 NO ARCH NO A NO
28 /home/ora10g/flash_recovery_area/EXAM/ar 873111 890072 48.47
1 NO ARCH NO A NO
chivelog/2011_04_16/o1_mf_1_28_6tlc2k6p_
.arc
29 /home/new/1_29_747929818.dbf 890072 907452 48.47
1 NO ARCH NO A NO
30 /home/new/1_30_747929818.dbf 907452 944572 41.78
1 NO ARCH NO A NO
31 /home/new/1_31_747929818.dbf 944572 951374 48.47
1 NO ARCH NO A NO
32 /home/new/1_32_747929818.dbf 951374 974104 8.99
1 NO ARCH NO A NO
32 /home/ora10g/flash_recovery_area/EXAM/ar 951374 974104 8.99
1 NO ARCH NO A NO
chivelog/2011_04_23/o1_mf_1_32_6v45xlty_
.arc
33 /home/new/1_33_747929818.dbf 974104 985473 48.47
1 NO ARCH NO A NO
34 /home/new/1_34_747929818.dbf 985473 1001378 48.47
1 NO ARCH NO A NO
34 /home/ora10g/flash_recovery_area/EXAM/ar 985473 1001378 48.47
1 NO ARCH NO A NO
chivelog/2011_04_23/o1_mf_1_34_6v4kv76m_
.arc
35 /home/new/1_35_747929818.dbf 1001378 1017712 48.47
1 NO ARCH NO A NO
35 /home/ora10g/flash_recovery_area/EXAM/ar 1001378 1017712 48.47
1 NO ARCH NO A NO
chivelog/2011_04_23/o1_mf_1_35_6v4tfdqh_
.arc
36 /home/new/1_36_747929818.dbf 1017712 1040900 11.08
1 NO ARCH NO A NO
36 /home/ora10g/flash_recovery_area/EXAM/ar 1017712 1040900 11.08
1 NO ARCH NO A NO
chivelog/2011_04_23/o1_mf_1_36_6v4xn4yc_
.arc
37 /home/new/1_37_747929818.dbf 1040900 1041272 .18
1 NO FGRD NO A NO
37 /home/ora10g/flash_recovery_area/EXAM/ar 1040900 1041272 .18
1 NO FGRD NO A NO
chivelog/2011_04_23/o1_mf_1_37_6v4xxdsq_
.arc
38 /home/new/1_38_747929818.dbf 1041272 1048574 25.45
1 NO ARCH NO A NO
38 /home/ora10g/flash_recovery_area/EXAM/ar 1041272 1048574 25.45
1 NO ARCH NO A NO
chivelog/2011_04_23/o1_mf_1_38_6v5194cy_
.arc
39 /home/new/1_39_747929818.dbf 1048574 1076514 22.33
1 NO ARCH NO A NO
39 /home/ora10g/flash_recovery_area/EXAM/ar 1048574 1076514 22.33
1 NO ARCH NO A NO
chivelog/2011_04_28/o1_mf_1_39_6vl2cgjd_
.arc
40 /home/new/1_40_747929818.dbf 1076514 1082494 48.47
1 NO ARCH NO A NO
40 /home/ora10g/flash_recovery_area/EXAM/ar 1076514 1082494 48.47
1 NO ARCH NO A NO
chivelog/2011_04_28/o1_mf_1_40_6vl4kl0p_
.arc
41 /home/new/1_41_747929818.dbf 1082494 1108808 30.27
1 NO ARCH NO A NO
41 /home/ora10g/flash_recovery_area/EXAM/ar 1082494 1108808 30.27
1 NO ARCH NO A NO
chivelog/2011_04_28/o1_mf_1_41_6vl80739_
.arc
72 rows selected.
sys@EXAM> exec dbms_logmnr.add_logfile('/home/new/1_40_747929818.dbf',dbms_logmnr.new);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
再次新增歸檔日誌
sys@EXAM> exec dbms_logmnr.add_logfile('/home/new/1_40_747929818.dbf',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
新增聯機日誌
sys@EXAM> set linesize 200
col GROUP format 99
col sequence# format 999 heading "SEQ"
col blocksize for 99999 heading "SIZE"
col members for 99 heading "MEM"
col archived for a3 heading "ARC"
col status for a10
col TYPE for a10
col MEM for a60 heading "FILE"
sys@EXAM> select f.member "MEM",f.type "TYPE",l.group# "GROUP",sequence#,bytes,members,archived,l.status "STATUS" from v$log l,v$logfile f where l.group#=f.group# order by l.group#;
MEM
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
----------------------------------
TYPE GROUP SEQ BYTES MEM ARC ST
---------- ----- ---- ---------- --- --- ----------
/home/ora10g/oradata/exam/redo01.log
ONLINE 1 41 52428800 2 YES INACTIVE
/home/new/redo0102.log
ONLINE 1 41 52428800 2 YES INACTIVE
/home/ora10g/oradata/exam/redo02.log
ONLINE 2 42 52428800 1 NO CURRENT
/home/ora10g/oradata/exam/redo03.log
ONLINE 3 40 52428800 1 YES INACTIVE
sys@EXAM> exec dbms_logmnr.add_logfile('/home/ora10g/oradata/exam/redo02.log',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
3,檢視加入分析的聯機和歸檔日誌
sys@EXAM> set linesize 220
col log_id for 9999
col filename for a40
col low_time for a20
col high_time for a20
col low_scn for 9999999999
col next_scn for 9999999999
col blocksize for 9999
col filesize/1024 for 99999
sys@EXAM> select log_id,filename,low_time,high_time,low_scn,next_scn,blocksize,filesize/1024 from v$logmnr_logs;
LOG_ID FILENAME LOW_TIME HIGH_TIME
LOW_SCN NEXT_SCN SIZE FILESIZE/1024
------ ---------------------------------------- --------------------
-------------------- ----------- ----------- ----- -------------
40 /home/new/1_40_747929818.dbf 28-APR-11 28-APR-11
1076514 1082494 512 49636.5
41 /home/new/redo0102.log 28-APR-11 28-APR-11
1082494 1108808 512 31000
42 /home/ora10g/oradata/exam/redo02.log 28-APR-11 01-JAN-88
1108808 ########### 512 31000
Elapsed: 00:00:00.00
4,刪除新增的日誌
sys@EXAM> exec dbms_logmnr.add_logfile('/home/new/1_40_747929818.dbf',dbms_logmnr.removefile);
PL/SQL procedure successfully completed.
5,開始日誌分析
（1）無限制條件，即用資料字典檔案對要分析的日誌檔案所有內容做分析：
sys@EXAM> exec dbms_logmnr.start_logmnr;
PL/SQL procedure successfully completed.
（2）帶限制條件：
可以用scn號或時間做限制條件（指定dictfilename資料字典檔案 以免產生的日誌分析使用內
部資料表示法 很難閱讀）
exec dbms_logmnr.start_logmnr(startscn=>xxxxxxxxx,
endscn=>xxxxxxxx,
dictfilename=>'xxxxxxx',
starttime=>'xxxxxxxxx',
endtime=>'xxxxx');
6、分析後釋放記憶體：
sys@EXAM> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
檢視相關檢視
sys@EXAM> col table_name for a30
sys@EXAM> col table_name for a30
sys@EXAM> select * from dict where table_name like '%LOGMNR%';
TABLE_NAME
------------------------------
COMMENTS
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
------------------------------------------------------
DBA_LOGMNR_LOG
DBA_LOGMNR_SESSION
DBA_LOGMNR_PURGED_LOG
V$LOGMNR_CONTENTS
Synonym for V_$LOGMNR_CONTENTS
V$LOGMNR_PARAMETERS
Synonym for V_$LOGMNR_PARAMETERS
V$LOGMNR_DICTIONARY
Synonym for V_$LOGMNR_DICTIONARY
V$LOGMNR_LOGS
Synonym for V_$LOGMNR_LOGS
V$LOGMNR_STATS
Synonym for V_$LOGMNR_STATS
V$LOGMNR_DICTIONARY_LOAD
Synonym for V_$LOGMNR_DICTIONARY_LOAD
GV$LOGMNR_CONTENTS
Synonym for GV_$LOGMNR_CONTENTS
GV$LOGMNR_PARAMETERS
Synonym for GV_$LOGMNR_PARAMETERS
GV$LOGMNR_DICTIONARY
Synonym for GV_$LOGMNR_DICTIONARY
GV$LOGMNR_LOGS
Synonym for GV_$LOGMNR_LOGS
V$LOGMNR_LOGFILE
Synonym for V_$LOGMNR_LOGFILE
V$LOGMNR_PROCESS
Synonym for V_$LOGMNR_PROCESS
V$LOGMNR_LATCH
Synonym for V_$LOGMNR_LATCH
V$LOGMNR_TRANSACTION
Synonym for V_$LOGMNR_TRANSACTION
V$LOGMNR_REGION
Synonym for V_$LOGMNR_REGION
V$LOGMNR_CALLBACK
Synonym for V_$LOGMNR_CALLBACK
V$LOGMNR_SESSION
Synonym for V_$LOGMNR_SESSION
GV$LOGMNR_LOGFILE
Synonym for GV_$LOGMNR_LOGFILE
GV$LOGMNR_PROCESS
Synonym for GV_$LOGMNR_PROCESS
GV$LOGMNR_LATCH
Synonym for GV_$LOGMNR_LATCH
GV$LOGMNR_TRANSACTION
Synonym for GV_$LOGMNR_TRANSACTION
GV$LOGMNR_REGION
Synonym for GV_$LOGMNR_REGION
GV$LOGMNR_CALLBACK
Synonym for GV_$LOGMNR_CALLBACK
GV$LOGMNR_SESSION
Synonym for GV_$LOGMNR_SESSION
GV$LOGMNR_STATS
Synonym for GV_$LOGMNR_STATS
GV$LOGMNR_DICTIONARY_LOAD
Synonym for GV_$LOGMNR_DICTIONARY_LOAD
29 rows selected.
相關包及過程
sys@EXAM> desc dbms_logmnr
PROCEDURE ADD_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOGFILENAME VARCHAR2 IN
OPTIONS BINARY_INTEGER IN DEFAULT
FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO NUMBER IN DEFAULT
COLUMN_NAME VARCHAR2 IN DEFAULT
PROCEDURE END_LOGMNR
FUNCTION MINE_VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO NUMBER IN DEFAULT
COLUMN_NAME VARCHAR2 IN DEFAULT
PROCEDURE REMOVE_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOGFILENAME VARCHAR2 IN
PROCEDURE START_LOGMNR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
STARTSCN NUMBER IN DEFAULT
ENDSCN NUMBER IN DEFAULT
STARTTIME DATE IN DEFAULT
ENDTIME DATE IN DEFAULT
DICTFILENAME VARCHAR2 IN DEFAULT
OPTIONS BINARY_INTEGER IN DEFAULT
sys@EXAM> desc dbms_logmnr_d
PROCEDURE BUILD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME VARCHAR2 IN DEFAULT
DICTIONARY_LOCATION VARCHAR2 IN DEFAULT
OPTIONS NUMBER IN DEFAULT
PROCEDURE SET_TABLESPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NEW_TABLESPACE VARCHAR2 IN
建立資料字典的目的: 讓LogMiner引用涉及到內部資料字典中的部分時為他們實際的名字，而
不是系統內部的16進位制。資料字典檔案是一個文字檔案，使用包 DBMS_LOGMNR_D來建立。如果
我們要分析的資料庫中的表有變化，影響到庫的資料字典也發生變化，這時就需要重新建立該
字典檔案。另外一種情況是在分析另外一個資料庫檔案的重作日誌時，也必須要重新生成一遍
被分析資料庫的資料字典檔案。 在使用LogMiner工具分析redo log檔案之前，可以使用
DBMS_LOGMNR_D 包將資料字典匯出為一個文字檔案。該字典檔案是可選的，但是如果沒有它，
LogMiner解釋出來的語句中關於資料字典中的部分（如表名、列名等）和數值都將是16進位制的
形式，我們是無法直接理解的。
例如，下面的SQL語句：
INSERT INTO dm_dj_swry (rydm, rymc) VALUES (00005, '張三');
LogMiner解釋出來的結果將是下面這個樣子:
insert into Object#308(col#1, col#2) values (hextoraw('c30rte567e436'), hextoraw
('4a6f686e20446f65'));
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14359/viewspace-694033/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 如何使用m工具安裝和管理MongoDBMongoDB
- 圖片標註工具labelme安裝和使用
- percona-toolkit工具包的安裝和使用
- sqlplus和sqlldr工具安裝SQL
- hydra 安裝和使用
- ActiveMq安裝和使用MQ
- docker安裝和使用Docker
- Elasticsearch 安裝和使用Elasticsearch
- StegSolve安裝和使用
- 安裝和使用 Composer
- httprunner 安裝和使用HTTP
- elasticsearch匯入匯出工具elasticdump安裝和使用小記Elasticsearch
- 誰來教我滲透測試——VMware工具安裝和使用
- 使用oracle的logminer同步資料Oracle
- protobuf 編譯工具安裝與使用編譯
- ELK esrally 壓測工具安裝使用
- bundletool工具使用(Android aab包安裝)Android
- 002 安裝Rust和WebAssembly工具鏈和helloworldRustWeb
- SSDB安裝和使用初探
- azkaban的安裝和使用
- 如何安裝和使用DockerDocker
- FTP的安裝和使用FTP
- Metabase 安裝和使用教程
- GitLab 的安裝和使用Gitlab
- yarn的安裝和使用Yarn
- Go的安裝和使用Go
- CMake的安裝和使用
- tcping的安裝和使用TCP
- Flarum 安裝和使用教程
- Meilisearch 安裝和使用教程
- Python的安裝和使用Python
- Linux和Windows下網路測試工具的安裝使用（iperf）LinuxWindows
- Alibaba 資料庫遷移開源工具 Datax 安裝和使用資料庫開源工具
- [ Module ] 環境變數管理工具 Module 安裝和使用變數
- redis資料備份和遷移工具redis-dump安裝和使用初探Redis
- 工具安裝
- 關於soapUi工具的安裝使用教程UI
- win10安裝beego和bee工具Win10Go
- 使用docker安裝mysql和redisDockerMySqlRedis