Oracle Logminer

오라클 Logminer를 사용하여 리두로그 파일 분석

 

-- ################################
-- # REDO LOG Logmnr
-- ################################
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
 
BEGIN
DBMS_LOGMNR.START_LOGMNR(STARTTIME => TO_DATE('2021/12/23 13:45:00','YYYY/MM/DD HH24:MI:SS'), 
                         ENDTIME   => TO_DATE('2021/12/23 13:50:00','YYYY/MM/DD HH24:MI:SS'),
                         OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
--                                    + DBMS_LOGMNR.PRINT_PRETTY_SQL
--                                    + DBMS_LOGMNR.NO_SQL_DELIMITER
--                                    + DBMS_LOGMNR.COMMITTED_DATA_ONLY
);
END;
/
 
SELECT *
FROM V$LOGMNR_CONTENTS A
WHERE 1=1
AND OPERATION IN ('DELETE','INSERT','UPDATE')
AND SEG_NAME = 'T1'
;
 
BEGIN
 DBMS_LOGMNR.END_LOGMNR();
END;
/
 
 
-- ################################
-- # ARCHIVE LOG Logmnr
-- ################################
BEGIN
    DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/arch/TESTDB_1_707251_810588878.arc', OPTIONS => DBMS_LOGMNR.NEW);
    DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/arch/TESTDB_1_707252_810588878.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
    DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/arch/TESTDB_1_707253_810588878.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
    DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/arch/TESTDB_1_707254_810588878.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
    DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/arch/TESTDB_1_707255_810588878.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
END;
/
 
--EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/arch/TESTDB_1_707246_810588878.arc', OPTIONS => DBMS_LOGMNR.NEW);
--EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/arch/TESTDB_1_707247_810588878.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
--EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/arch/TESTDB_1_707248_810588878.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
 
BEGIN
    DBMS_LOGMNR.START_LOGMNR (OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
END;
/
 
SELECT A.*,
    DBMS_LOGMNR.MINE_VALUE(REDO_VALUE,'DBIB0942.T1.OWNER') NEW_VALUE,
    DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE,'DBIB0942.T1.OWNER') OLD_VALUE
FROM V$LOGMNR_CONTENTS A
WHERE 1=1
AND OPERATION IN ('DELETE','INSERT','UPDATE')
AND SEG_NAME = 'T1'
;
 
BEGIN
 DBMS_LOGMNR.END_LOGMNR();
END;
/