如何使用log miner分析oracle日志
當(dāng)我們不小心誤操作致使數(shù)據(jù)庫(kù)數(shù)據(jù)丟失、改變時(shí), 需要對(duì)數(shù)據(jù)庫(kù)對(duì)象做基于時(shí)間點(diǎn)的恢復(fù),找到我們需要的數(shù)據(jù),這個(gè)時(shí)間點(diǎn)不能認(rèn)為精確確定,我們可以通過(guò)對(duì)oracle日志進(jìn)行分析,而獲得無(wú)操作的精確時(shí)間點(diǎn)。
oracle db提供了一個(gè)分析日志包logmnr
logminer 工具的使用
-------對(duì)redo log 進(jìn)行挖掘,找出在某個(gè)時(shí)間點(diǎn)所作的DDL 或DML 操作(包括:時(shí)間點(diǎn)、datablock scn 、sql語(yǔ)句)
實(shí)驗(yàn)測(cè)試
SQL> select name from v$archived_log; NAME -------------------------------------------------- /oracle/arch1/1_2_883536782.dbf SQL> SQL> SQL> delete from scott.t1; 576 rows deleted. SQL> alter system archive log current; System altered. SQL> create table scott.t6 as select * from scott.emp; Table created. SQL> alter system archive log current; System altered. SQL> select name from v$archived_log; NAME -------------------------------------------------- /oracle/arch1/1_2_883536782.dbf /oracle/arch1/1_3_883536782.dbf /oracle/arch1/1_4_883536782.dbf
--啟動(dòng)log miner添加要分析的日志
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_2_883536782.dbf',options=>dbms_logmnr.new); PL/SQL procedure successfully completed.
--添加需要分析的日志
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_3_883536782.dbf',options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed. SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oracle/arch1/1_4_883536782.dbf',options=>dbms_logmnr.addfile) PL/SQL procedure successfully completed.
--執(zhí)行l(wèi)og miner
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); PL/SQL procedure successfully completed.
--查詢(xún)分析結(jié)果
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='T1'; USERNAME SCN TIMESTAMP ------------------------------ ---------- ------------------- SQL_REDO -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- delete from "SCOTT"."T1" where "EMPNO" = '7782' and "ENAME" = 'CLARK' and "JOB" = 'MANAGER' and "MGR" = '7839' and "HIREDATE" = TO_DATE('1981-06-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '245 0' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAAVbSAAFAAAACXABi'; SYS 1494545 2015-06-28 04:24:44 delete from "SCOTT"."T1" where "EMPNO" = '7839' and "ENAME" = 'KING' and "JOB" = 'PRESIDENT' and "MGR" IS NULL and "HIREDATE" = TO_DATE('1981-11-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '500 0' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAAVbSAAFAAAACXABj'; SYS 1494545 2015-06-28 04:24:44 delete from "SCOTT"."T1" where "EMPNO" = '7844' and "ENAME" = 'TURNER' and "JOB" = 'SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE('1981-09-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '1 。。。。。
--結(jié)束log miner 分析
SQL> execute dbms_logmnr.end_logmnr; PL/SQL procedure successfully completed.
以上就是本文的全部?jī)?nèi)容,希望大家可以喜歡。
相關(guān)文章
oracle—SQL技巧之(一)連續(xù)記錄查詢(xún)sql案例測(cè)試
有這樣一個(gè)需求:需要查詢(xún)出某個(gè)客戶(hù)某一年那些天是有連續(xù)辦理過(guò)業(yè)務(wù),本文給予sql實(shí)現(xiàn)語(yǔ)句并測(cè)試,感興趣的朋友可以了解下2013-01-01Oracle中 關(guān)于數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)的使用
本篇文章,小編將為大家介紹,在Oracle中 關(guān)于數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)的使用,有需要的朋友可以參考一下2013-04-04基于ORA-12170 TNS 連接超時(shí)解決辦法詳解
本篇文章是對(duì)ORA-12170 TNS 連接超時(shí)的解決辦法進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06Oracle創(chuàng)建Database Link的兩種方式詳解
Oracle數(shù)據(jù)庫(kù)如何創(chuàng)建Database Link呢?本文我們主要就介紹一下這部分內(nèi)容,Oracle數(shù)據(jù)庫(kù)創(chuàng)建Database Link有兩種方式,一種是通過(guò)菜單,一種是通過(guò)SQL2016-02-02計(jì)算機(jī)名稱(chēng)修改后Oracle不能正常啟動(dòng)問(wèn)題分析及解決
更改計(jì)算機(jī)名稱(chēng)后,oracle不能正常啟動(dòng)的相信有很多的朋友都有遇到過(guò)這種情況吧,接下來(lái)為大家介紹下詳細(xì)的解決方法感興趣的朋友可以參考下哈2013-04-04在ORACLE中SELECT TOP N的實(shí)現(xiàn)方法
這篇文章主要介紹了在ORACLE中SELECT TOP N的實(shí)現(xiàn)方法,非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友參考下2017-01-01