使用Oracle logminer 找回delete的數據

2、logminer的用法

在用logminer獲取數據字典文件時，需要設置UTL_FILE_DIR參數，然而設置這個參數必須要重新啟動數據庫，生產庫一般不能隨便重啟，從9i以上的版本可以直接使用在線數據文件字典，選項為dbms_logmnr.dict_from_online_catalog 。

exec dbms_logmnr.add_logfile();

exec dbms_logmnr.start_logmnr();

execute dbms_logmnr.end_logmnr;

客戶反饋晚上12點左右刪除了數據，通過flashback已經無法閃回，也沒有導出備份，rman全備倒是有，但是沒有臨時環境,且操作過程較長，此時需要logminer出馬找回被delete的數據。

相關視圖

------------

v$logmnr_contents

SQL> desc v$logmnr_contents

Name Null? Type

----------------------------------------- -------- ----------------------------

SCN NUMBER

START_SCN NUMBER

COMMIT_SCN NUMBER

TIMESTAMP DATE

START_TIMESTAMP DATE

COMMIT_TIMESTAMP DATE

XIDUSN NUMBER

XIDSLT NUMBER

XIDSQN NUMBER

XID RAW(8)

PXIDUSN NUMBER

PXIDSLT NUMBER

PXIDSQN NUMBER

PXID RAW(8)

TX_NAME VARCHAR2(256)

OPERATION VARCHAR2(32)

OPERATION_CODE NUMBER

ROLLBACK NUMBER

SEG_OWNER VARCHAR2(32)

SEG_NAME VARCHAR2(256)

TABLE_NAME VARCHAR2(32)

SEG_TYPE NUMBER

SEG_TYPE_NAME VARCHAR2(32)

TABLE_SPACE VARCHAR2(32)

ROW_ID VARCHAR2(18)

USERNAME VARCHAR2(30)

OS_USERNAME VARCHAR2(4000)

MACHINE_NAME VARCHAR2(4000)

AUDIT_SESSIONID NUMBER

SESSION# NUMBER

SERIAL# NUMBER

SESSION_INFO VARCHAR2(4000)

THREAD# NUMBER

SEQUENCE# NUMBER

RBASQN NUMBER

RBABLK NUMBER

RBABYTE NUMBER

UBAFIL NUMBER

UBABLK NUMBER

UBAREC NUMBER

UBASQN NUMBER

ABS_FILE# NUMBER

REL_FILE# NUMBER

DATA_BLK# NUMBER

DATA_OBJ# NUMBER

DATA_OBJV# NUMBER

DATA_OBJD# NUMBER

SQL_REDO VARCHAR2(4000)

SQL_UNDO VARCHAR2(4000)

RS_ID VARCHAR2(32)

SSN NUMBER

CSF NUMBER

INFO VARCHAR2(32)

STATUS NUMBER

REDO_VALUE NUMBER

UNDO_VALUE NUMBER

SAFE_RESUME_SCN NUMBER

CSCN NUMBER

OBJECT_ID RAW(16)

EDITION_NAME VARCHAR2(30)

CLIENT_ID VARCHAR2(64)

----------------

確定刪除操作的時段，23:30~03:00之間做的delete，還好晚上沒有什麼業務，隻產生了一個歸檔日誌

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/backup/archivelog/1_251_946834694.dbf', 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

解析這裏可以根據時間段或者scn範圍解析，scn和時間轉換可以用

select timestamp_to_scn(to_date('2017-08-12 00:00:01','YYYY-MM-DD HH24:MI:SS')) from dual;

select timestamp_to_scn(to_date('2017-08-12 00:10:01','YYYY-MM-DD HH24:MI:SS')) from dual;

我沒有使用時間段，把整個log進行解析，然後通過sql_redo對應的delete語句查找sql_undo的語句

確認所需的sql，客戶刪除語句類似DELETE from T_WXSCKD ……這樣的操作

select operation,sql_redo,sql_undo from v$logmnr_contents where OPERATION='DELETE' and SQL_REDO like 'delete from "ERP_CC"."T_WXSCKD"%';

輸出結果清楚的看到redo和undo的sql信息，現在要做的是把對應的undo中insert語句導出來，生成sql腳本。

可以加參數美化輸出結果，但是格式化後的sql不適合作為腳本執行：DBMS_LOGMNR.PRINT_PRETTY_SQL

exec dbms_logmnr.start_logmnr(Options =>dbms_logmnr.dict_from_online_catalog + DBMS_LOGMNR.PRINT_PRETTY_SQL);

注意中文的輸出，需要先設置NLS_LANG環境變量

export NLS_LANG="AMERICAN_AMERICA.zhs16gbk"

進行sqlplus格式化，這樣輸出的sql腳本中每個insert都是一行。

SQLPLUS>

set line 1000 --設置行的長度

set pagesize 0 --輸出不換頁

set feedback off --默認的當一條sql發出的時候，oracle會給一個反饋，比如說創建表的時候，如果成功命令行會返回類似：Table created的反饋,off後不顯示反饋

set heading off --不顯示表頭信息

set trimspool on --如果trimspool設置為on，將移除spool文件中的尾部空

set trims on --去掉空字符

set echo off;　　　　 --顯示start啟動的腳本中的每個sql命令，缺省為on

set termout off --不在屏幕上顯示結果

spool 12345.sql --記錄數據到db1.txt

select count(1) from v$logmnr_contents where OPERATION='DELETE' and SQL_REDO like 'delete from "ERP_CC"."T_WXSCKD"%'; --導出數據語句

spool off --收集完畢

創建臨時表,

create table ERP_CC.T_WXSCKD_tmp as select * from ERP_CC.T_WXSCKD where 1=0;

SQL>@12345.sql

commit;

客戶根據條件找回所需的數據。