OS환경 : Oracle Linux6.8(64bit)
DB 환경 : Oracle Database 11.2.0.4
쿼리 : 로그마이너로 나온 결과 확인명령어
조회(한번에 여러개 파일을 확인하면 속도가 느림)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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)이 나옴
바로 안나온다면 분석할 로그파일을 바꿔가며 확인
참조 :
'ORACLE > Sql' 카테고리의 다른 글
| user 세션에 대한 사용한 세션 메모리양 확인 (0) | 2017.05.31 |
|---|---|
| 오라클 캐쉬 히트율 확인 (0) | 2017.05.31 |
| 테이블 스페이스 용량확인 (0) | 2017.05.31 |
| 로그 스위치 및 체크포인트 발생 (0) | 2017.05.31 |
| Current 상태의 Redo Group 조회 (0) | 2017.05.31 |