[20141208]使用logminer看遠端歸檔檔案.txt
[20141208]使用logminer看遠端歸檔檔案.txt
--如果要在本機看其他主機的歸檔或者日誌檔案,由於dbid不一樣,是無法檢視的.必須要包括字典資訊.
--如果需要檢視別的資料庫的歸檔檔案，必須要有別的資料字典檔案。正常需要使用utl_file_dir，而經常這個引數沒有設定。
--在使用如下命令建立資料字典。
exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle11g/testd/dict_test.ora');
--如果沒有建立引數utl_file_dir：
SYS@test> show parameter utl_file_dir
NAME TYPE VALUE
-------------- -------- --------------------
utl_file_dir string
SYS@test> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle11g/testd/dict_test.ora');
BEGIN dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle11g/testd/dict_test.ora'); END;
*
ERROR at line 1:
ORA-01292: no log file has been specified for the current LogMiner session
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1
--實際上可以使用EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);前提是建立附加日誌。
--就可以把字典放在日誌檔案中。把歸檔以及日誌在拷貝過了就可以了。自己做一個測試：
1.建立測試環境:
SYS@test> alter system switch logfile ;
System altered.
SYS@test> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
SYS@test> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
BEGIN DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); END;
*
ERROR at line 1:
ORA-01354: Supplemental log data must be added to run this command
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3669
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3755
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1
--要求開啟附件日誌。
SYS@test> alter database add supplemental log data;
Database altered.
SYS@test> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
SYS@test> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.
SYS@test> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
$ ls -l 2014_12_08/
total 18704
-rw-r----- 1 oracle oinstall 9434112 2014-12-08 12:00:16 o1_mf_1_14_b8b8lj2y_.arc
-rw-r----- 1 oracle oinstall 12288 2014-12-08 12:00:55 o1_mf_1_15_b8b8mq3g_.arc
-rw-r----- 1 oracle oinstall 9432064 2014-12-08 12:00:58 o1_mf_1_16_b8b8mt39_.arc
--可以發現發生了兩次arhcive log 切換。我估計執行前先切換1次，執行完成後再切換1次。這樣資料字典在o1_mf_1_16_b8b8mt39_.arc
--中。
2.建立測試資料：
create table t1 ( id number,name varchar2(20));
insert into t1 values (1,'aaaa');
insert into t1 values (2,'bbbb');
insert into t1 values (3,'cccc');
commit ;
update t1 set name='BBBB' where id=2;
commit ;
delete t1 where id=3;
commit ;
alter system switch logfile ;
3.拷貝archive log檔案：
$ scp o1_mf_1_1[67]_*.arc oracle11g@192.168.100.40:/home/oracle11g/testd/
oracle11g@192.168.100.40's password:
o1_mf_1_16_b8b8mt39_.arc 100% 9211KB 9.0MB/s 00:00
o1_mf_1_17_b8b9565v_.arc 100% 977KB 977.0KB/s 00:00
4.使用logminer檢視：
--我以前都是在toad裡面使用logminer，在sqlplus測試看看。
exec SYS.DBMS_LOGMNR.ADD_LOGFILE( '/home/oracle11g/testd/o1_mf_1_16_b8b8mt39_.arc', sys.dbms_logmnr.New);
exec SYS.DBMS_LOGMNR.ADD_LOGFILE( '/home/oracle11g/testd/o1_mf_1_17_b8b9565v_.arc', sys.dbms_logmnr.AddFile);
exec SYS.DBMS_LOGMNR.START_LOGMNR ( Options => sys.dbms_logmnr.DICT_FROM_REDO_LOGS );
--很慢不知道為什麼？
SCOTT@test> SELECT scn, operation, row_id, sql_redo FROM V$LOGMNR_CONTENTS WHERE seg_owner = 'SCOTT' AND table_name = 'T1' ORDER BY 1;
SCN OPERATION ROW_ID SQL_REDO
---------- -------------------------------- ------------------ ------------------------------------------------------------
933853 DDL AAAAAAAAAAAAAAAAAB create table t1 ( id number,name varchar2(20));
--僅僅看到建表語句。
exec dbms_logmnr.end_logmnr;
5.改用如下方式：
exec SYS.DBMS_LOGMNR.ADD_LOGFILE( '/home/oracle11g/testd/o1_mf_1_16_b8b8mt39_.arc', sys.dbms_logmnr.New);
exec SYS.DBMS_LOGMNR.ADD_LOGFILE( '/home/oracle11g/testd/o1_mf_1_17_b8b9565v_.arc', sys.dbms_logmnr.AddFile);
exec SYS.DBMS_LOGMNR.START_LOGMNR ( Options => sys.dbms_logmnr.DICT_FROM_REDO_LOGS+sys.dbms_logmnr.COMMITTED_DATA_ONLY+sys.dbms_logmnr.DDL_DICT_TRACKING );
-- 我最後的測試exec SYS.DBMS_LOGMNR.START_LOGMNR ( Options => sys.dbms_logmnr.DICT_FROM_REDO_LOGS+sys.dbms_logmnr.DDL_DICT_TRACKING );
-- 應該可以看到執行的dml語句
-- 補充資料：(來源包本身)
-- Constants for start_logmnr options flag
NO_DICT_RESET_ONSELECT CONSTANT BINARY_INTEGER := 1;
COMMITTED_DATA_ONLY CONSTANT BINARY_INTEGER := 2;
SKIP_CORRUPTION CONSTANT BINARY_INTEGER := 4;
DDL_DICT_TRACKING CONSTANT BINARY_INTEGER := 8;
DICT_FROM_ONLINE_CATALOG CONSTANT BINARY_INTEGER := 16;
DICT_FROM_REDO_LOGS CONSTANT BINARY_INTEGER := 32;
NO_SQL_DELIMITER CONSTANT BINARY_INTEGER := 64;
PRINT_PRETTY_SQL CONSTANT BINARY_INTEGER := 512;
CONTINUOUS_MINE CONSTANT BINARY_INTEGER := 1024;
NO_ROWID_IN_STMT CONSTANT BINARY_INTEGER := 2048;
STRING_LITERALS_IN_STMT CONSTANT BINARY_INTEGER := 4096;
--
SCOTT@test> SCOTT@test> SELECT scn, operation, row_id, sql_redo FROM V$LOGMNR_CONTENTS WHERE seg_owner = 'SCOTT' AND table_name = 'T1' ORDER BY 1;
SCN OPERATION ROW_ID SQL_REDO
------- ----------- ------------------ ---------------------------------------------------------------------------------------------
933853 DDL AAAAAAAAAAAAAAAAAB create table t1 ( id number,name varchar2(20));
933862 INSERT AAANDuAAEAAAAGeAAA insert into "SCOTT"."T1"("ID","NAME") values ('1','aaaa');
933977 INSERT AAANDuAAEAAAAGeAAB insert into "SCOTT"."T1"("ID","NAME") values ('2','bbbb');
933979 INSERT AAANDuAAEAAAAGeAAC insert into "SCOTT"."T1"("ID","NAME") values ('3','cccc');
933988 UPDATE AAANDuAAEAAAAGeAAB update "SCOTT"."T1" set "NAME" = 'BBBB' where "NAME" = 'bbbb' and ROWID = 'AAANDuAAEAAAAGeAAB';
933994 DELETE AAANDuAAEAAAAGeAAC delete from "SCOTT"."T1" where "ID" = '3' and "NAME" = 'cccc' and ROWID = 'AAANDuAAEAAAAGeAAC';
6 rows selected.
exec dbms_logmnr.end_logmnr;
6.補充測試：
--前面的測試先建立資料字典在redo檔案中，後面建立的表自然不在資料字典中，僅僅獲得ddl語句，無法獲取dml語句，必須加入引數sys.dbms_logmnr.DDL_DICT_TRACKING。
--現在在最後建立資料字典在redo檔案中，重複測試：
SYS@test> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.
-- scp o1_mf_1_19_b8b9qhjd_.arc oracle11g@192.168.100.40:/home/oracle11g/testd/
exec SYS.DBMS_LOGMNR.ADD_LOGFILE( '/home/oracle11g/testd/o1_mf_1_17_b8b9565v_.arc', sys.dbms_logmnr.New);
exec SYS.DBMS_LOGMNR.ADD_LOGFILE( '/home/oracle11g/testd/o1_mf_1_19_b8b9qhjd_.arc', sys.dbms_logmnr.AddFile);
exec SYS.DBMS_LOGMNR.START_LOGMNR ( Options => sys.dbms_logmnr.DICT_FROM_REDO_LOGS );
--這步特別慢，不知道問題在哪裡。
SCOTT@test> SELECT scn, operation, row_id, sql_redo FROM V$LOGMNR_CONTENTS WHERE seg_owner = 'SCOTT' AND table_name = 'T1' ORDER BY 1;
SCN OPERATION ROW_ID SQL_REDO
------- ---------- ------------------ -----------------------------------------------------------------------------------------------
933853 DDL AAAAAAAAAAAAAAAAAB create table t1 ( id number,name varchar2(20));
933862 INSERT AAANDuAAEAAAAGeAAA insert into "SCOTT"."T1"("ID","NAME") values ('1','aaaa');
933977 INSERT AAANDuAAEAAAAGeAAB insert into "SCOTT"."T1"("ID","NAME") values ('2','bbbb');
933979 INSERT AAANDuAAEAAAAGeAAC insert into "SCOTT"."T1"("ID","NAME") values ('3','cccc');
933988 UPDATE AAANDuAAEAAAAGeAAB update "SCOTT"."T1" set "NAME" = 'BBBB' where "NAME" = 'bbbb' and ROWID = 'AAANDuAAEAAAAGeAAB';
933994 DELETE AAANDuAAEAAAAGeAAC delete from "SCOTT"."T1" where "ID" = '3' and "NAME" = 'cccc' and ROWID = 'AAANDuAAEAAAAGeAAC';
6 rows selected.
exec dbms_logmnr.end_logmnr;
--總結:
1.執行EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
可以把資料字典放在redo檔案中。前提是要求開啟附件日誌。
SYS@test> alter database add supplemental log data;
2.必要時加入 sys.dbms_logmnr.COMMITTED_DATA_ONLY+sys.dbms_logmnr.DDL_DICT_TRACKING引數。
3.本地 使用DICT_FROM_ONLINE_CATALOG引數，不需要建立資料字典。
4.不知道為什麼這麼慢，差不多11X秒才完成exec SYS.DBMS_LOGMNR.START_LOGMNR ( Options => sys.dbms_logmnr.DICT_FROM_REDO_LOGS );
5.做了10046跟蹤：發現
SQL ID: a5w8kbk6udsn5 Plan Hash: 0
INSERT INTO SYS.LOGMNRLT_110_COL$ ( COL#, SEGCOL#, NAME, TYPE#, LENGTH,
PRECISION#, SCALE, NULL$, INTCOL#, PROPERTY, CHARSETID, CHARSETFORM, SPARE1,
SPARE2, OBJ#, LOGMNR_UID )
VALUES
(:01, :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10, :11, :12, :13, :14, :15,
:16 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 56243 52.63 54.80 660 804 62305 56243
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 56244 52.63 54.81 660 804 62305 56243
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=2 pr=5 pw=0 time=1650 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 660 0.11 0.32
latch: shared pool 1 0.00 0.00
********************************************************************************
SQL ID: fa4kq3unwkka6 Plan Hash: 0
SQL ID: 2jv9waqgh5asr Plan Hash: 0
INSERT INTO SYS.LOGMNRLT_110_OBJ$ ( OBJV#, OWNER#, NAME, NAMESPACE, SUBNAME,
TYPE#, OID$, REMOTEOWNER, LINKNAME, FLAGS, SPARE3, STIME, OBJ#, LOGMNR_UID )
VALUES
(:01, :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10, :11, :12, :13, :14 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 52018 40.15 43.35 710 931 58400 52018
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 52019 40.15 43.35 710 931 58400 52018
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=2 pr=5 pw=0 time=26723 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 710 0.30 0.90
log file switch completion 1 0.08 0.08
latch: shared pool 1 0.00 0.00
--主要慢在這裡。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1360796/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20141210]使用logminer看遠端歸檔檔案補充
- 使用logminer分析歸檔日誌案例
- DM7配置遠端歸檔
- 遠端檔案包含shell
- 沒有字典檔案下，logminer使用。
- logminer異機挖掘歸檔日誌
- 使用BBED跳過歸檔檔案
- 如何使用scp進行遠端複製檔案？
- mac使用系列之scp下載遠端檔案Mac
- 在WINDOWS下使用xcopy遠端複製檔案Windows
- 11.使用make更新歸檔檔案
- 使用RMAN安全刪除歸檔檔案
- RAC歸檔配置方案：使用NFS共享儲存儲存歸檔檔案NFS
- 遠端桌面不能貼上檔案
- NSXMLDocument讀取遠端XML檔案XML
- 使用SSHFS檔案系統遠端掛載目錄
- 使用paramiko遠端執行命令、下發檔案
- Linux使用expect實現遠端拷貝檔案Linux
- PHP下載遠端檔案及獲取檔案內容PHP
- 如何輕鬆歸檔檔案？2種方法輕鬆建立歸檔檔案！
- 怎麼遠端傳輸大檔案？
- 遠端登入和複製檔案
- 資料庫遠端檔案匯入資料庫
- 控制檔案/歸檔日誌
- [20171208]強制刪除歸檔日誌檔案.txt
- PCL——txt檔案轉到PCD檔案
- Linux SSh scp使用【遠端檔案/目錄的傳輸】Linux
- 智慧小程式檔案館——遠端除錯除錯
- rsync udr——遠端大檔案傳輸加速
- C# 判斷遠端檔案是否存在C#
- Windows 遠端桌面檔案傳輸的方法Windows
- 用vim安全編輯遠端檔案(轉)
- powershell 關閉遠端開啟的檔案
- php如何上傳txt檔案，並且讀取txt檔案PHP
- 配置歸檔位置和檔案格式
- 備份歸檔日誌檔案
- CAB歸檔檔案提取工具cabextract
- 日誌檔案和歸檔管理