溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“logminer怎么使用”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“logminer怎么使用”吧！
LogMiner兩種使用類型，一種是使用源數據庫的數據字典分析DML操作，別一種是摘取LogMiner數據字典到字典文件分析DDL操作。
注意事項：1.使用logmnr工具最好配置補充日志，不然最終不到ddl操作
2.對于ddl操作必須配置utl_file_dir參數，這個參數為logmnr字典文件的目錄,而對于查詢dml操作可以不用配置。
LogMiner 工具即可以用來分析在線，也可以用來分析離線日志文件，即可以分析本身自己數據庫的重作日志文件，也可以用來分析其他數據庫的重作日志文件。
總的說來，LogMiner工具的主要用途有：
1、跟蹤數據庫的變化：可以離線的跟蹤數據庫的變化，而不會影響在線系統的性能。
2、回退數據庫的變化：回退特定的變化數據，減少point-in-time recovery的執行。
3、優化和擴容計劃：可通過分析日志文件中的數據以分析數據增長模式
一、確定數據庫的邏輯損壞時間。假定某個用戶執行drop table誤刪除了重要表sales，通過LogMiner可以準確定位該誤操作的執行時間和SCN值，然后通過基于時間恢復或者基于SCN恢復可以完全恢復該表數據。
二、確定事務級要執行的精細邏輯恢復操作。假定某些用戶在某表上執行了一系列DML操作并提交了事務，并且其中某個用戶的DML操作存在錯誤。通過LogMiner可以取得任何用戶的DML操作及相應的UNDO操作，通過執行UNDO操作可以取消用戶的錯誤操作。
三、執行后續審計。通過LogMiner可以跟蹤Oracle數據庫的所有DML、DDL和DCL操作，從而取得執行這些操作的時間順序、執行這些操作的用戶等信息。
1、LogMiner基本對象
源數據庫（source database）：該數據庫是指包含了要分析重做日志和歸檔日志的產品數據庫。
分析數據庫（mining database）：該數據庫是指執行LogMiner操作所要使用的數據庫。
LogMiner字典：LogMiner字典用于將內部對象ID號和數據類型轉換為對象名和外部數據格式。使用LogMiner分析重做日志和歸檔日志時，應該生成LogMiner字典，否則將無法讀懂分析結果。
2、LogMiner配置要求
（1）源數據庫和分析數據庫 （源數據庫和分析數據庫可以是同一個數據庫）
源數據庫和分析數據庫必須運行在相同硬件平臺上；
分析數據庫可以是獨立數據庫或源數據庫；
分析數據庫的版本不能低于源數據庫的版本；
分析數據庫與源數據庫必須具有相同的字符集。
（2）LogMiner字典：LogMiner字典必須在源數據庫中生成。
（3）重做日志文件
當分析多個重做日志和歸檔日志時，它們必須是同一個源數據庫的重做日志和歸檔日志；
當分析多個重做日志和歸檔日志時，它們必須具有相同的resetlogs scn；
當分析的重做日志和歸檔日志必須在Oracle8.0版本以上。
3、補充日志（suppplemental logging）
重做日志用于實現例程恢復和介質恢復，這些操作所需要的數據被自動記錄在重做日志中。但是，重做應用可能還需要記載其他列信息到重做日志中，記錄其他列的日志過程被稱為補充日志。默認情況下，Oracle數據庫沒有提供任何補充日志，從而導致默認情況下LogMiner無法支持以下特征：
索引簇、鏈行和遷移行；
直接路徑插入；
摘取LogMiner字典到重做日志；
跟蹤DDL；
生成鍵列的SQL_REDO和SQL_UNDO信息；
LONG和LOB數據類型。
因此，為了充分利用LogMiner提供的特征，必須激活補充日志。
語法：
理想情況下，LogMiner字典文件將在完成所有數據庫字典更改后創建，并在創建要分析的任何重做日志文件之前創建。 從Oracle9i發行版本1（9.0.1）開始，可以使用LogMiner將LogMiner字典轉儲到重做日志文件或平面文件，執行DDL操作，并將DDL更改動態應用于LogMiner字典。
另外，應該啟用補充日志記錄（至少是最低級別），以確保您可以利用LogMiner提供的所有功能。 有關在LogMiner中使用補充日志記錄的信息，請參見Oracle數據庫實用程序。
實驗一：開歸檔、不開啟補充日志及不增加logminer數據字典，(使用DBMS_LOGMNR_D.BUILD)
--查看歸檔路徑及路徑下的日志：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL>
SQL>
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area/DBDB/newback
db_recovery_file_dest_size big integer 9G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SQL>
SQL> alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';
Session altered.
SQL> select *
from (SELECT NAME,
THREAD#,
SEQUENCE#,
APPLIED,
ARCHIVED,
COMPLETION_TIME
FROM V$ARCHIVED_LOG order by 6 desc) a
where rownum <= 10;
NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_TIME
--------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --- -------------------
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_15_f6m8flqg_.arc 1 15 NO YES 2018-01-25 17:21:56
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_14_f6m5v9ts_.arc 1 14 NO YES 2018-01-25 16:38:03
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_13_f6m56dgw_.arc 1 13 NO YES 2018-01-25 16:26:52
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_12_f6m4nz1x_.arc 1 12 NO YES 2018-01-25 16:17:35
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_11_f6l9phsx_.arc 1 11 NO YES 2018-01-25 08:37:35
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_10_f6l9pgvh_.arc 1 10 NO YES 2018-01-25 08:37:34
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_9_f6l9pb8o_.arc 1 9 NO YES 2018-01-25 08:37:30
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_8_f6l9p9jl_.arc 1 8 NO YES 2018-01-25 08:37:29
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_7_f6l9p6xp_.arc 1 7 NO YES 2018-01-25 08:37:26
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_6_f6l9p5k2_.arc 1 6 NO YES 2018-01-25 08:37:25
10 rows selected.
查詢得，當前將要歸檔的日志為16
--查詢在線日志組
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 NO CURRENT
2 1 14 1 YES INACTIVE
3 1 15 1 YES INACTIVE
--查詢日志組：
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
1 CURRENT /u01/app/oracle/oradata/DBdb/redo01.log ONLINE
查詢得，當前的日志組為group#1，sequence#為16
--模擬操作：
SQL> conn hr/hr;
Connected.
SQL>
SQL> create table log_a (x int);
Table created.
SQL> insert into log_a values(1);
1 row created.
SQL> insert into log_a values(2);
1 row created.
SQL> insert into log_a values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> update log_a set x=4 where x=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from log_a;
X
----------
4
2
3
--查詢正在工作的日志組
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 NO CURRENT
2 1 14 1 YES INACTIVE
3 1 15 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
1 CURRENT /u01/app/oracle/oradata/DBdb/redo01.log ONLINE
--然后去分析在線redo日志
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/DBdb/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> create table l1_Z1 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
--然后去查看分析的在線redo日志，在sql_redo里面可以看到曾經的操作。
SQL> select start_timestamp,sql_redo,sql_undo from l1_Z1 where sql_redo like '%LOG_A%';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
delete from "SYS"."OBJ$" where "OBJ#" = '90306' and "DATAOBJ insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","
#" = '90306' and "OWNER#" = '84' and "NAME" = 'LOG_A' and "N NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS
AMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and ","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2",
"CTIME" = TO_DATE('2018-01-25 16:13:31', 'yyyy-mm-dd hh34:mi "SPARE3","SPARE4","SPARE5","SPARE6") values ('90306','90306'
:ss') and "MTIME" = TO_DATE('2018-01-25 16:13:31', 'yyyy-mm- ,'84','LOG_A','1',NULL,'2',TO_DATE('2018-01-25 16:13:31', 'y
dd hh34:mi:ss') and "STIME" = TO_DATE('2018-01-25 16:13:31', yyy-mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 16:13:31', 'yyyy-
'yyyy-mm-dd hh34:mi:ss') and "STATUS" = '1' and "REMOTEOWNE mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 16:13:31', 'yyyy-mm-d
R" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID d hh34:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL
$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3 ,NULL);
" = '84' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPAR
E6" IS NULL and ROWID = 'AAAAASAABAAAVC0AAV';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME"," delete from "SYS"."OBJ$" where "OBJ#" = '90317' and "DATAOBJ
NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS #" = '90317' and "OWNER#" = '84' and "NAME" = 'LOG_A' and "N
","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2", AMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and
"SPARE3","SPARE4","SPARE5","SPARE6") values ('90317','90317' "CTIME" = TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-dd hh34:mi
,'84','LOG_A','1',NULL,'2',TO_DATE('2018-01-25 17:48:46', 'y :ss') and "MTIME" = TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-
yyy-mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 17:48:46', 'yyyy- dd hh34:mi:ss') and "STIME" = TO_DATE('2018-01-25 17:48:46',
mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-d 'yyyy-mm-dd hh34:mi:ss') and "STATUS" = '1' and "REMOTEOWNE
d hh34:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL R" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID
,NULL); $" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3
" = '84' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPAR
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
E6" IS NULL and ROWID = 'AAAAASAABAAAVC0AAZ';
insert into "HR"."LOG_A"("X") values ('1'); delete from "HR"."LOG_A" where "X" = '1' and ROWID = 'AAAWDN
AAEAAAAzrAAA';
insert into "HR"."LOG_A"("X") values ('2'); delete from "HR"."LOG_A" where "X" = '2' and ROWID = 'AAAWDN
AAEAAAAzrAAB';
insert into "HR"."LOG_A"("X") values ('3'); delete from "HR"."LOG_A" where "X" = '3' and ROWID = 'AAAWDN
AAEAAAAzrAAC';
實驗繼續：
--查詢：
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 NO CURRENT
2 1 14 1 YES INACTIVE
3 1 15 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
1 CURRENT /u01/app/oracle/oradata/DBdb/redo01.log ONLINE
SQL> l
1 select *
2 from (SELECT NAME,
3 THREAD#,
4 SEQUENCE#,
5 APPLIED,
6 ARCHIVED,
7 COMPLETION_TIME
8 FROM V$ARCHIVED_LOG order by 6 desc) a
9* where rownum <= 10
SQL> /
NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_T
--------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --- ------------
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_15_f6m8flqg_.arc 1 15 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_14_f6m5v9ts_.arc 1 14 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_13_f6m56dgw_.arc 1 13 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_12_f6m4nz1x_.arc 1 12 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_11_f6l9phsx_.arc 1 11 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_10_f6l9pgvh_.arc 1 10 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_9_f6l9pb8o_.arc 1 9 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_8_f6l9p9jl_.arc 1 8 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_7_f6l9p6xp_.arc 1 7 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_6_f6l9p5k2_.arc 1 6 NO YES 25-JAN-18
10 rows selected.
--日志組切換：
SQL> alter system switch logfile;
System altered.
--再次查詢
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 YES ACTIVE
2 1 17 1 NO CURRENT
3 1 15 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
2 CURRENT /u01/app/oracle/oradata/DBdb/redo02.log ONLINE
SQL>
SQL> l
1 select *
2 from (SELECT NAME,
3 THREAD#,
4 SEQUENCE#,
5 APPLIED,
6 ARCHIVED,
7 COMPLETION_TIME
8 FROM V$ARCHIVED_LOG order by 6 desc) a
9* where rownum <= 10
SQL> /
NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_T
--------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --- ------------
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_16_f6mbk78z_.arc 1 16 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_15_f6m8flqg_.arc 1 15 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_14_f6m5v9ts_.arc 1 14 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_13_f6m56dgw_.arc 1 13 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_12_f6m4nz1x_.arc 1 12 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_11_f6l9phsx_.arc 1 11 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_10_f6l9pgvh_.arc 1 10 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_9_f6l9pb8o_.arc 1 9 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_8_f6l9p9jl_.arc 1 8 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_7_f6l9p6xp_.arc 1 7 NO YES 25-JAN-18
10 rows selected.
SQL>
SQL> 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
SQL>
--使用logminer,分析歸檔日志：
SQL> exec DBMS_LOGMNR.ADD_LOGFILE ('/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_16_f6mbk78z_.arc',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> create table dt1 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> select start_timestamp,sql_redo,sql_undo from dt1 where sql_redo like '%LOG_A%';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
delete from "SYS"."OBJ$" where "OBJ#" = '90306' and "DATAOBJ insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","
#" = '90306' and "OWNER#" = '84' and "NAME" = 'LOG_A' and "N NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS
AMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and ","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2",
"CTIME" = TO_DATE('2018-01-25 16:13:31', 'yyyy-mm-dd hh34:mi "SPARE3","SPARE4","SPARE5","SPARE6") values ('90306','90306'
:ss') and "MTIME" = TO_DATE('2018-01-25 16:13:31', 'yyyy-mm- ,'84','LOG_A','1',NULL,'2',TO_DATE('2018-01-25 16:13:31', 'y
dd hh34:mi:ss') and "STIME" = TO_DATE('2018-01-25 16:13:31', yyy-mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 16:13:31', 'yyyy-
'yyyy-mm-dd hh34:mi:ss') and "STATUS" = '1' and "REMOTEOWNE mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 16:13:31', 'yyyy-mm-d
R" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID d hh34:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL
$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3 ,NULL);
" = '84' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPAR
E6" IS NULL and ROWID = 'AAAAASAABAAAVC0AAV';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME"," delete from "SYS"."OBJ$" where "OBJ#" = '90317' and "DATAOBJ
NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS #" = '90317' and "OWNER#" = '84' and "NAME" = 'LOG_A' and "N
","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2", AMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and
"SPARE3","SPARE4","SPARE5","SPARE6") values ('90317','90317' "CTIME" = TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-dd hh34:mi
,'84','LOG_A','1',NULL,'2',TO_DATE('2018-01-25 17:48:46', 'y :ss') and "MTIME" = TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-
yyy-mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 17:48:46', 'yyyy- dd hh34:mi:ss') and "STIME" = TO_DATE('2018-01-25 17:48:46',
mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-d 'yyyy-mm-dd hh34:mi:ss') and "STATUS" = '1' and "REMOTEOWNE
d hh34:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL R" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID
,NULL); $" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3
" = '84' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPAR
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
E6" IS NULL and ROWID = 'AAAAASAABAAAVC0AAZ';
insert into "HR"."LOG_A"("X") values ('1'); delete from "HR"."LOG_A" where "X" = '1' and ROWID = 'AAAWDN
AAEAAAAzrAAA';
insert into "HR"."LOG_A"("X") values ('2'); delete from "HR"."LOG_A" where "X" = '2' and ROWID = 'AAAWDN
AAEAAAAzrAAB';
insert into "HR"."LOG_A"("X") values ('3'); delete from "HR"."LOG_A" where "X" = '3' and ROWID = 'AAAWDN
AAEAAAAzrAAC';
綜上實驗，在沒有開啟補充日志的情況下，ddl操作不能被logminer挖掘出來,且dml操作也不能完全被挖掘出來。
實驗二：開區補充日志
語法：alter database add(drop) supplemental log data；
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
Database altered.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
YES
PS：這里如果不打開的話，在分析歸檔日志的時候，就看不到執行操作的machine、os_name、user_name等等，對分析操作排查問題會產生很大困擾。supplemental lsogging（擴充日志）在通常情況下，redo log 只記錄的進行恢復所必需的信息，但是這些信息對于我們使用redo log進行一些其他應用時是不夠的，例如在 redo log中使用rowid唯一標識一行而不是通過Primary key，如果我們在另外的數據庫分析這些日志并想重新執行某些dml時就可能會有問題，因為不同的數據庫其rowid代表的內容是不同的。在這時候就需要一些額外的信息（columns）加入redo log，這就是supplemental logging。
--檢查：
SQL> conn hr/hr;
Connected.
SQL> create table log_b (x int);
Table created.
SQL> insert into log_b values(1);
1 row created.
SQL> insert into log_b values(2);
1 row created.
SQL> insert into log_b values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> update log_b set x=4 where x=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from log_b;
X
----------
4
2
3
SQL>
--檢查日志組：
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
2 CURRENT /u01/app/oracle/oradata/DBdb/redo02.log ONLINE
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 YES INACTIVE
2 1 17 1 NO CURRENT
3 1 15 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
2 CURRENT /u01/app/oracle/oradata/DBdb/redo02.log ONLINE
SQL>
--分析在線日志：
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/DBdb/redo02.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> create table l1_Z2 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> select start_timestamp,sql_redo,sql_undo from l1_Z2 where sql_redo like '%LOG_B%';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME"," delete from "SYS"."OBJ$" where "OBJ#" = '90320' and "DATAOBJ
NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS #" = '90320' and "OWNER#" = '84' and "NAME" = 'LOG_B' and "N
","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2", AMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and
"SPARE3","SPARE4","SPARE5","SPARE6") values ('90320','90320' "CTIME" = TO_DATE('2018-01-25 18:16:06', 'yyyy-mm-dd hh34:mi
,'84','LOG_B','1',NULL,'2',TO_DATE('2018-01-25 18:16:06', 'y :ss') and "MTIME" = TO_DATE('2018-01-25 18:16:06', 'yyyy-mm-
yyy-mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 18:16:06', 'yyyy- dd hh34:mi:ss') and "STIME" = TO_DATE('2018-01-25 18:16:06',
mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 18:16:06', 'yyyy-mm-d 'yyyy-mm-dd hh34:mi:ss') and "STATUS" = '1' and "REMOTEOWNE
d hh34:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL R" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID
,NULL); $" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3
" = '84' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPAR
E6" IS NULL and ROWID = 'AAAAASAABAAAVC0AAb';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
insert into "HR"."LOG_B"("X") values ('1'); delete from "HR"."LOG_B" where "X" = '1' and ROWID = 'AAAWDQ
AAEAAAAzzAAA';
insert into "HR"."LOG_B"("X") values ('2'); delete from "HR"."LOG_B" where "X" = '2' and ROWID = 'AAAWDQ
AAEAAAAzzAAB';
insert into "HR"."LOG_B"("X") values ('3'); delete from "HR"."LOG_B" where "X" = '3' and ROWID = 'AAAWDQ
AAEAAAAzzAAC';
update "HR"."LOG_B" set "X" = '4' where "X" = '1' and ROWID update "HR"."LOG_B" set "X" = '1' where "X" = '4' and ROWID
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
= 'AAAWDQAAEAAAAzzAAA'; = 'AAAWDQAAEAAAAzzAAA';
SQL>
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 YES INACTIVE
2 1 17 1 NO CURRENT
3 1 15 1 YES INACTIVE
SQL>
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
2 CURRENT /u01/app/oracle/oradata/DBdb/redo02.log ONLINE
SQL> alter system switch logfile;
System altered.
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 YES INACTIVE
2 1 17 1 YES ACTIVE
3 1 18 1 NO CURRENT
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
3 CURRENT /u01/app/oracle/oradata/DBdb/redo03.log ONLINE
SQL>
SQL> l
1 select *
2 from (SELECT NAME,
3 THREAD#,
4 SEQUENCE#,
5 APPLIED,
6 ARCHIVED,
7 COMPLETION_TIME
8 FROM V$ARCHIVED_LOG order by 6 desc) a
9* where rownum <= 10
SQL> /
NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_T
--------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --- ------------
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_17_f6mcyztb_.arc 1 17 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_16_f6mbk78z_.arc 1 16 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_15_f6m8flqg_.arc 1 15 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_14_f6m5v9ts_.arc 1 14 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_13_f6m56dgw_.arc 1 13 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_12_f6m4nz1x_.arc 1 12 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_11_f6l9phsx_.arc 1 11 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_10_f6l9pgvh_.arc 1 10 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_9_f6l9pb8o_.arc 1 9 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_8_f6l9p9jl_.arc 1 8 NO YES 25-JAN-18
10 rows selected.
SQL>
--分析歸檔日志：
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_17_f6mcyztb_.arc',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> create table l1_Z2 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
--查分析結果：
select * from dt2 where sql_redo like '%LOG_B%' and table_name='LOG_B';
select table_name,USERNAME,SESSION#,SERIAL# ,start_timestamp,sql_redo,sql_undo from dt2 where table_name='LOG_B';
上述2個sql的START_TIMESTAMP字段、create表記錄顯示不同。。。
SQL> select table_name,USERNAME,SESSION#,SERIAL# ,start_timestamp,sql_redo,sql_undo from dt2 where table_name='LOG_B';
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMESTAMP SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------------- -------------------------------------------------- --------------------------------------------------
LOG_B HR 46 67 create table log_b (x int);
LOG_B HR 46 67 insert into "HR"."LOG_B"("X") values ('1'); delete from "HR"."LOG_B" where "X" = '1' and ROWID
= 'AAAWDQAAEAAAAzzAAA';
LOG_B HR 46 67 insert into "HR"."LOG_B"("X") values ('2'); delete from "HR"."LOG_B" where "X" = '2' and ROWID
= 'AAAWDQAAEAAAAzzAAB';
LOG_B HR 46 67 insert into "HR"."LOG_B"("X") values ('3'); delete from "HR"."LOG_B" where "X" = '3' and ROWID
= 'AAAWDQAAEAAAAzzAAC';
LOG_B HR 46 67 update "HR"."LOG_B" set "X" = '4' where "X" = '1' update "HR"."LOG_B" set "X" = '1' where "X" = '4'
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMESTAMP SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------------- -------------------------------------------------- --------------------------------------------------
and ROWID = 'AAAWDQAAEAAAAzzAAA'; and ROWID = 'AAAWDQAAEAAAAzzAAA';
增加補充日志實驗證明，dml操作完全能夠被記錄挖掘出來。。。。。。。
實驗三：在開啟補充日志的基礎上，增加logminer數據字典，(使用DBMS_LOGMNR_D.BUILD)
--設置參數utl_file_dir，此目錄用戶存儲logminer數據字典
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string
SQL>
SQL> alter system set utl_file_dir='/home/oracle/logminer' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /home/oracle/logminer
SQL>
--創建logmnr數據字典文件
SQL> exec dbms_logmnr_d.build(dictionary_filename =>'dictionary.ora',dictionary_location =>'/home/oracle/logminer');
PL/SQL procedure successfully completed.
SQL>
--查詢：
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 31 1 NO CURRENT
2 1 29 1 YES INACTIVE
3 1 30 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
1 CURRENT /u01/app/oracle/oradata/DBdb/redo01.log ONLINE
SQL>
--實驗開始：
SQL> conn hr/hr;
Connected.
SQL>
SQL> create table log_c (x int);
Table created.
SQL> insert into log_c values (4);
1 row created.
SQL> insert into log_c values (4);
1 row created.
SQL> insert into log_c values (4);
1 row created.
SQL> insert into log_c values (4);
1 row created.
SQL> commit;
Commit complete.
SQL> delete log_c where rownum<2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> update log_c set x=5 where rownum<2;
1 row updated.
SQL> commit;
Commit complete.
SQL> create table log_c_bak as select * from log_c;
Table created.
SQL>
--再次查詢：
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 31 1 NO CURRENT
2 1 29 1 YES INACTIVE
3 1 30 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
1 CURRENT /u01/app/oracle/oradata/DBdb/redo01.log ONLINE
--執行分析在線日志：
SQL> exec DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/u01/app/oracle/oradata/DBdb/redo01.log',Options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');
PL/SQL procedure successfully completed.
SQL> create table l1_Z5 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> select table_name,USERNAME,SESSION#,SERIAL# ,start_timestamp,sql_redo,sql_undo from l1_Z5 where table_name like 'LOG_C%';
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMEST SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------ -------------------------------------------------- --------------------------------------------------
LOG_C HR 38 11 create table log_c (x int);
LOG_C_BAK HR 38 11 create table log_c_bak as select * from log_c;
--查詢歸檔：
SQL> l
1 select *
2 from (SELECT NAME,
3 THREAD#,
4 SEQUENCE#,
5 APPLIED,
6 ARCHIVED,
7 COMPLETION_TIME
8 FROM V$ARCHIVED_LOG order by 6 desc) a
9* where rownum <= 10
SQL> /
NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_TIME
--------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --- -------------------
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_34_f6mjwpxs_.arc 1 34 NO YES 2018-01-25 19:46:32
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_33_f6mjwjfq_.arc 1 33 NO YES 2018-01-25 19:46:26
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_32_f6mjw7w9_.arc 1 32 NO YES 2018-01-25 19:46:18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_31_f6mjw4yn_.arc 1 31 NO YES 2018-01-25 19:46:14
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_30_f6mh7zlf_.arc 1 30 NO YES 2018-01-25 19:18:23
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_29_f6mgv88w_.arc 1 29 NO YES 2018-01-25 19:11:38
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_28_f6mgv49x_.arc 1 28 NO YES 2018-01-25 19:11:34
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_27_f6mgtvdf_.arc 1 27 NO YES 2018-01-25 19:11:26
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_26_f6mgtrfy_.arc 1 26 NO YES 2018-01-25 19:11:22
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_25_f6mghd5p_.arc 1 25 NO YES 2018-01-25 19:05:17
10 rows selected.
--分析歸檔:【使用exec sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora')分析歸檔】
SQL> exec DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_31_f6mjw4yn_.arc',Options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL>
SQL> exec sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');
PL/SQL procedure successfully completed.
SQL> create table l1_Z6 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> select table_name,USERNAME,SESSION#,SERIAL# ,start_timestamp,sql_redo,sql_undo from l1_Z6 where table_name like 'LOG_C%';
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMEST SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------ -------------------------------------------------- --------------------------------------------------
LOG_C HR 38 11 create table log_c (x int);
LOG_C_BAK HR 38 11 create table log_c_bak as select * from log_c;
--使用dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog)選項分析歸檔日志
SQL> exec DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_31_f6mjw4yn_.arc',Options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> create table l1_Z7 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> select table_name,USERNAME,SESSION#,SERIAL# ,start_timestamp,sql_redo,sql_undo from l1_Z7 where table_name like 'LOG_C%';
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMEST SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------ -------------------------------------------------- --------------------------------------------------
LOG_C HR 38 11 create table log_c (x int);
LOG_C HR 38 11 insert into "HR"."LOG_C"("X") values ('4'); delete from "HR"."LOG_C" where "X" = '4' and ROWID
= 'AAAWDfAAEAAAA2EAAA';
LOG_C HR 38 11 insert into "HR"."LOG_C"("X") values ('4'); delete from "HR"."LOG_C" where "X" = '4' and ROWID
= 'AAAWDfAAEAAAA2EAAB';
LOG_C HR 38 11 insert into "HR"."LOG_C"("X") values ('4'); delete from "HR"."LOG_C" where "X" = '4' and ROWID
= 'AAAWDfAAEAAAA2EAAC';
LOG_C HR 38 11 insert into "HR"."LOG_C"("X") values ('4'); delete from "HR"."LOG_C" where "X" = '4' and ROWID
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMEST SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------ -------------------------------------------------- --------------------------------------------------
= 'AAAWDfAAEAAAA2EAAD';
LOG_C HR 38 11 delete from "HR"."LOG_C" where "X" = '4' and ROWID insert into "HR"."LOG_C"("X") values ('4');
= 'AAAWDfAAEAAAA2EAAA';
LOG_C HR 38 11 update "HR"."LOG_C" set "X" = '5' where "X" = '4' update "HR"."LOG_C" set "X" = '4' where "X" = '5'
and ROWID = 'AAAWDfAAEAAAA2EAAB'; and ROWID = 'AAAWDfAAEAAAA2EAAB';
LOG_C_BAK HR 38 11 create table log_c_bak as select * from log_c;
LOG_C_BAK HR 38 11 insert into "HR"."LOG_C_BAK"("X") values ('5'); delete from "HR"."LOG_C_BAK" where "X" = '5' and R
OWID = 'AAAWDgAAEAAAA2LAAA';
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMEST SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------ -------------------------------------------------- --------------------------------------------------
LOG_C_BAK HR 38 11 insert into "HR"."LOG_C_BAK"("X") values ('4'); delete from "HR"."LOG_C_BAK" where "X" = '4' and R
OWID = 'AAAWDgAAEAAAA2LAAB';
LOG_C_BAK HR 38 11 insert into "HR"."LOG_C_BAK"("X") values ('4'); delete from "HR"."LOG_C_BAK" where "X" = '4' and R
OWID = 'AAAWDgAAEAAAA2LAAC';
11 rows selected.
SQL>
實驗證明，在開啟補充日志和增加logminer數據字段文件的基礎下，ddl/dml被完全記錄下來和挖掘出來，注意：使用dbms_logmnr.start_logmnr時，如果指定參數為dbms_logmnr.dict_from_online_catalog記錄ddl/dml操作，二而指定參數為dictfilename，則只記錄ddl操作。
感謝各位的閱讀，以上就是“logminer怎么使用”的內容了，經過本文的學習后，相信大家對logminer怎么使用這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
