로그마이너로 나온 결과 확인명령어

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : 로그마이너로 나온 결과 확인명령어

조회(한번에 여러개 파일을 확인하면 속도가 느림)

SQL>
set line 200
col timestamp for a20
col seg_owner for a10
col username for a10
col sql_redo for a120
col sql_undo for a120
select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS'), seg_owner, username, sql_redo, sql_undo
from v$logmnr_contents
where sql_redo like '%update%'
and seg_owner='SCOTT';
 
TO_CHAR(TIMESTAMP,' SEG_OWNER  USERNAME   SQL_REDO                                                                                                SQL_UNDO 
------------------- ---------- ---------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7839' and ROWID = 'AAADtqAAGAAAJkzAAA'; update "SCOTT"."EMP2" set "EMPNO" = '7839' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAA';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7698' and ROWID = 'AAADtqAAGAAAJkzAAB'; update "SCOTT"."EMP2" set "EMPNO" = '7698' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAB';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7782' and ROWID = 'AAADtqAAGAAAJkzAAC'; update "SCOTT"."EMP2" set "EMPNO" = '7782' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAC';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7566' and ROWID = 'AAADtqAAGAAAJkzAAD'; update "SCOTT"."EMP2" set "EMPNO" = '7566' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAD';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7788' and ROWID = 'AAADtqAAGAAAJkzAAE'; update "SCOTT"."EMP2" set "EMPNO" = '7788' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAE';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7902' and ROWID = 'AAADtqAAGAAAJkzAAF'; update "SCOTT"."EMP2" set "EMPNO" = '7902' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAF';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7369' and ROWID = 'AAADtqAAGAAAJkzAAG'; update "SCOTT"."EMP2" set "EMPNO" = '7369' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAG';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7499' and ROWID = 'AAADtqAAGAAAJkzAAH'; update "SCOTT"."EMP2" set "EMPNO" = '7499' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAH';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7521' and ROWID = 'AAADtqAAGAAAJkzAAI'; update "SCOTT"."EMP2" set "EMPNO" = '7521' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAI';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7654' and ROWID = 'AAADtqAAGAAAJkzAAJ'; update "SCOTT"."EMP2" set "EMPNO" = '7654' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAJ';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7844' and ROWID = 'AAADtqAAGAAAJkzAAK'; update "SCOTT"."EMP2" set "EMPNO" = '7844' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAK';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7876' and ROWID = 'AAADtqAAGAAAJkzAAL'; update "SCOTT"."EMP2" set "EMPNO" = '7876' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAL';
2019-01-15:14:27:27 SCOTT        UNKNOWN      update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7934' and ROWID = 'AAADtqAAGAAAJkzAAN'; update "SCOTT"."EMP2" set "EMPNO" = '7934' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAN';

실수로 update 한 구문(SQL_REDO)과 되돌릴수 있는 구문(SQL_UNDO)이 나옴

바로 안나온다면 분석할 로그파일을 바꿔가며 확인

참조 :