Oracle Logminer 日誌挖掘

生產環境中考慮到數據庫的性能問題，很少會打開數據的審計功能，應用層也不會記錄SQL的執行信息；但是生產上經常會遇到某張表的某幾條被修改掉，但是應用又查不到是哪個接口修改的記錄，這時候Logminer 就派上用場了。

Logminer 8i之後的一款免費日誌分析工具：通過分析在線日誌文件或者歸檔日誌文件，返回數據庫DDL/DML操作語句、執行時間、用戶等等可以追查的信息，快速定位問題。

使用Logminer 工具，數據庫需要開啟強製日誌和歸檔模式

數據庫配置

# 開啟強製日誌模式

# 查看當前數據庫日誌模式
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;

# 如果返回結果為NO，則
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE FORCE LOGGING;

# 確認是否已經修改，輸出為YES
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;

# 切換係統日誌
SQL> ALTER SYSTEM SWITCH LOGFILE;

# 數據庫處於歸檔模式
SQL> archive log list
Database log mode          Archive Mode
Automatic archival         Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     107
Next log sequence to archive   109
Current log sequence           109
# 默認歸檔日誌路徑
SQL> show parameter db_recover

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest            string  /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size       big integer 4977M
# 若數據庫處於非歸檔模式，則需要
# 幹淨的關閉掉數據庫
SQL> shutdown immediate
# 打開至mount狀態
SQL> startup mount
# 設置為歸檔模式
SQL> alter database archivelog;
# 開啟數據庫
SQL> alter database open;

Logminer 配置

# 首先安裝 logminer 使用到的包，將創建用於分析的過程和視圖
SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql
SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql

# 參數配置，用於創建字典文件
SQL> show parameter utl

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                 string  /u01/app/oracle/logminer
# 如果VALUE 為 NULL，那麼需要在線修改並重啟實例生效
SQL> alter system set utl_file_dir='/u01/app/oracle/logminer' scope=spfile;

#  創建字典文件
SQL> CREATE DIRECTORY utlfile AS '/u01/app/oracle/logminer';

SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/u01/app/oracle/logminer');

SQL> !ls  /u01/app/oracle/logminer
dictionary.ora

對測試數據表進行操作

#  創建臨時表
SQL>  create table u_logminer.dba_objects as select * from all_objects;
# 更新操作
SQL> select status,owner,object_name from U_LOGMINER.dba_objects where owner = 'U_LOGMINER';

STATUS  OWNER                  OBJECT_NAME
------- ------------------------------ --------------------
INVALID U_LOGMINER             ALL_OBJECTS
INVALID U_LOGMINER             ALL_OBJECTS
INVALID U_LOGMINER             ALL_OBJECTS
INVALID U_LOGMINER             ALL_OBJECTS
 # update 更新
SQL> update U_LOGMINER.dba_objects set STATUS = 'VALID' where owner = 'U_LOGMINER';

4 rows updated.

SQL> commit;

Commit complete.

SQL> select status,owner,object_name from U_LOGMINER.dba_objects where owner = 'U_LOGMINER';

STATUS  OWNER                  OBJECT_NAME
------- ------------------------------ --------------------
VALID   U_LOGMINER             ALL_OBJECTS
VALID   U_LOGMINER             ALL_OBJECTS
VALID   U_LOGMINER             ALL_OBJECTS
VALID   U_LOGMINER             ALL_OBJECTS
# insert 插入
SQL> insert into U_LOGMINER.dba_objects select * from dba_objects where owner = 'U_LOGMINER';

4 rows created.

SQL> commit;

Commit complete.

SQL> select status,owner,object_name from U_LOGMINER.dba_objects where owner = 'U_LOGMINER';

STATUS  OWNER                  OBJECT_NAME
------- ------------------------------ --------------------
VALID   U_LOGMINER             ALL_OBJECTS
VALID   U_LOGMINER             ALL_OBJECTS
VALID   U_LOGMINER             ALL_OBJECTS
VALID   U_LOGMINER             ALL_OBJECTS
VALID   U_LOGMINER             ALL_OBJECTS
VALID   U_LOGMINER             ALL_OBJECTS
VALID   U_LOGMINER             ALL_OBJECTS
VALID   U_LOGMINER             ALL_OBJECTS

8 rows selected.
# delete 刪除
SQL> delete from U_LOGMINER.dba_objects where owner = 'U_LOGMINER';

8 rows deleted.

SQL> commit;

Commit complete.

使用Logminer 分析歸檔日誌

# 添加日誌文件進行分析，第一個文件 dbms_logmnr.NEW，後麵的文件dbms_logmnr.ADDFILE
SQL> BEGIN
  2  dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_08_19/o1_mf_1_106_dshlmn6r_.arc',options=>dbms_logmnr.NEW);
  3  dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_08_19/o1_mf_1_107_dshlmoo2_.arc',options=>dbms_logmnr.ADDFILE);
  4  dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_08_19/o1_mf_1_108_dshm5n1x_.arc',options=>dbms_logmnr.ADDFILE);
  5  end;
  6  /

PL/SQL procedure successfully completed.
# 開始分析，當前是完整分析日誌文件的內容，可以操作時間、SCN等過濾需要分析的內容
SQL> EXECUTE dbms_logmnr.start_logmnr(dictfilename =>'/u01/app/oracle/logminer/dictionary.ora');
# 或者
SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName =>'/u01/app/oracle/logminer/dictionary.ora', StartTime =>to_date('2017-8-19 00:00:00','YYYY-MM-DD HH24:MI:SS'),EndTime =>to_date('2017-8-19 17:00:00','YYYY-MM-DD HH24:MI:SS '));
# 或者
SQL> 
SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName =>'/u01/app/oracle/logminer/dictionary.ora', StartScn>=20, EndScn<= 50);

PL/SQL procedure successfully completed.
# 結果放在視圖中，注意當會話結束，臨時表會被刪除
SQL> SELECT count(*) FROM v$logmnr_contents; 

  COUNT(*)
----------
     42118
# 將臨時表數據備份至物理表中
SQL> create table u_logMIner.logminer_tmp as select * from  v$logmnr_contents; 

Table created.

日誌文件分析完成，接下來就看看對表到底做了什麼操作

# 根據事務開始時間進行排序，查詢數據表的變更記錄
SQL > SELECT START_TIMESTAMP,COMMIT_TIMESTAMP,sql_redo,sql_undo,machine_name,os_username,username,table_name FROM u_logMIner.logminer_tmp WHERE username='U_LOGMINER' AND table_name='DBA_OBJECTS' order by START_TIMESTAMP;

# sql_redo 更改數據的SQL
# sql_undo 回滾數據的SQL
# machine_name,os_username,username 三位一體定位執行更新的機器、數據庫用戶名

# 如下：
SQL> SELECT sql_redo FROM u_logMIner.logminer_tmp WHERE username='U_LOGMINER' AND table_name='DBA_OBJECTS' order by START_TIMESTAMP;

SQL_REDO
--------------------------------------------------------------------------------
update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'INVALID' where "STATUS" = 'VAL
ID' and ROWID = 'AAAD8EAAIAAAAJAAAf';

update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'INVALID' where "STATUS" = 'VAL
ID' and ROWID = 'AAAD8EAAIAAAAKHAAv';
…………

# 如下：
SQL> SELECT sql_undo FROM u_logMIner.logminer_tmp WHERE username='U_LOGMINER' AND table_name='DBA_OBJECTS' order by START_TIMESTAMP;

SQL_UNDO
--------------------------------------------------------------------------------
update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'VALID' where "STATUS" = 'INVAL
ID' and ROWID = 'AAAD8EAAIAAAAJAAAf';

update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'VALID' where "STATUS" = 'INVAL
ID' and ROWID = 'AAAD8EAAIAAAAKHAAv';

update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'VALID' where "STATUS" = 'INVAL
ID' and ROWID = 'AAAD8EAAIAAAAPoAA0';
………………

SQL拿出來了，就可以定位問題，但是能不能立刻回滾到生產環境，還需要和業務部門進行溝通。

建議在創建數據庫時，就打開強製日誌、歸檔模式，配置 utl_file_dir 參數，避免數據庫重啟對線上的影像

當然也可以直接下其他環境下的數據庫做日誌分析工作，有兩個點需要注意：數據字典和日誌文件，其他和在本機處理沒什麼兩樣。

最後更新：2017-08-19 16:02:15

Oracle Logminer 日誌挖掘

生產環境中考慮到數據庫的性能問題，很少會打開數據的審計功能，應用層也不會記錄SQL的執行信息；但是生產上經常會遇到某張表的某幾條被修改掉，但是應用又查不到是哪個接口修改的記錄，這時候Logminer 就派上用場了。

Logminer 8i之後的一款免費日誌分析工具：通過分析在線日誌文件或者歸檔日誌文件，返回數據庫DDL/DML操作語句、執行時間、用戶等等可以追查的信息，快速定位問題。

使用Logminer 工具，數據庫需要開啟強製日誌和歸檔模式

數據庫配置

Logminer 配置

對測試數據表進行操作

使用Logminer 分析歸檔日誌

日誌文件分析完成，接下來就看看對表到底做了什麼操作

SQL拿出來了，就可以定位問題，但是能不能立刻回滾到生產環境，還需要和業務部門進行溝通。

建議在創建數據庫時，就打開強製日誌、歸檔模式，配置 utl_file_dir 參數，避免數據庫重啟對線上的影像

當然也可以直接下其他環境下的數據庫做日誌分析工作，有兩個點需要注意：數據字典和日誌文件，其他和在本機處理沒什麼兩樣。

上一篇：馬雲公益基金會新版官網上線，啟用mayun.xin！

下一篇：西安門戶網站建設方案

相關內容

熱門內容

最新內容

Oracle Logminer 日誌挖掘

生產環境中考慮到數據庫的性能問題，很少會打開數據的審計功能，應用層也不會記錄SQL的執行信息；但是生產上經常會遇到某張表的某幾條被修改掉，但是應用又查不到是哪個接口修改的記錄，這時候Logminer 就派上用場了。

Logminer 8i之後的一款免費日誌分析工具：通過分析在線日誌文件或者歸檔日誌文件，返回數據庫DDL/DML操作語句、執行時間、用戶等等可以追查的信息，快速定位問題。

使用Logminer 工具，數據庫需要開啟強製日誌和歸檔模式

數據庫配置

Logminer 配置

對測試數據表進行操作

使用Logminer 分析歸檔日誌

日誌文件分析完成，接下來就看看對表到底做了什麼操作

SQL拿出來了，就可以定位問題，但是能不能立刻回滾到生產環境，還需要和業務部門進行溝通。

建議在創建數據庫時，就打開強製日誌、歸檔模式，配置 utl_file_dir 參數，避免數據庫重啟對線上的影像

當然也可以直接下其他環境下的數據庫做日誌分析工作，有兩個點需要注意：數據字典和日誌文件，其他和在本機處理沒什麼兩樣。

上一篇： 馬雲公益基金會新版官網上線，啟用mayun.xin！

下一篇： 西安門戶網站建設方案

相關內容

熱門內容

最新內容

上一篇：馬雲公益基金會新版官網上線，啟用mayun.xin！

下一篇：西安門戶網站建設方案