OS환경 : Oracle Linux6.8(64bit)
DB 환경 : Oracle Database 11.2.0.4 ~ 12cR1
방법 : 로그마이너 log miner 사용법
사전 준비
로그마이너가 있는지 확인
|
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
28
29
30
|
SQL> desc dbms_logmnr
PROCEDURE ADD_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOGFILENAME VARCHAR2 IN
OPTIONS BINARY_INTEGER IN DEFAULT
FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO NUMBER IN DEFAULT
COLUMN_NAME VARCHAR2 IN DEFAULT
PROCEDURE END_LOGMNR
FUNCTION MINE_VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO NUMBER IN DEFAULT
COLUMN_NAME VARCHAR2 IN DEFAULT
PROCEDURE REMOVE_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOGFILENAME VARCHAR2 IN
PROCEDURE START_LOGMNR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
STARTSCN NUMBER IN DEFAULT
ENDSCN NUMBER IN DEFAULT
STARTTIME DATE IN DEFAULT
ENDTIME DATE IN DEFAULT
DICTFILENAME VARCHAR2 IN DEFAULT
OPTIONS BINARY_INTEGER IN DEFAULT
|
default로 설치가 되어 있지 않다면 sysdba 유저로 설치
|
1
|
SQL> @?/rdbms/admin/dbmslm.sql
|
supplemental logging 기능 상태 확인
|
1
2
3
4
5
|
SQL> select supplemental_log_data_min from v$database;
SUPPLEMENTAL_LOG
----------------
NO
|
supplemental logging 기능 활성화
|
1
2
3
|
SQL> alter database add supplemental log data;
Database altered.
|
supplemental logging 기능 상태 재확인
|
1
2
3
4
5
|
SQL> select supplemental_log_data_min from v$database;
SUPPLEMENTAL_LOG
----------------
YES
|
장애상황
scott계정에서 emp2 테이블의 모든 empno를 1000으로 변경
update 구문 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL> conn scott/tiger
update emp2 set empno=1000;
commit;
select * from emp2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1000 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
1000 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
1000 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
1000 JONES MANAGER 7839 02-APR-81 2975 (null) 20
1000 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
1000 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
1000 SMITH CLERK 7902 17-DEC-80 800 (null) 20
1000 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
1000 WARD SALESMAN 7698 22-FEB-81 1250 500 30
1000 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
1000 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
1000 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
1000 JAMES CLERK 7698 03-DEC-81 950 (null) 30
1000 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
|
모든 empno가 1000으로 변경됨
해결방법
재기동이 필요한 로그마이너 방법
경로 생성
|
1
|
$ mkdir -p /home/oracle/logmnr
|
파라미터 파일에 utl_file_dir 파라미터 추가
|
1
2
3
4
5
6
7
8
|
$ vi initORCL.ora
.
.
*.open_cursors=300
*.processes=150
*.remote_listener='prsjedb-scan:1521'
*.remote_login_passwordfile='exclusive'
utl_file_dir='/home/oracle/logmnr'
|
제일 아래에 utl_file_dir='/home/oracle/logmnr' 입력
db 재기동
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> shutdown immediate
SQL> startup pfile=$ORACLE_HOME/dbs/initORCL.ora
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 557845624 bytes
Database Buffers 268435456 bytes
Redo Buffers 2392064 bytes
Database mounted.
Database opened.
|
utl_file_dir 파라미터 확인
|
1
2
3
4
5
|
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /home/oracle/logmnr
|
리두로그 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
col member for a30
select a.sequence#, a.group#, a.status, a.members, b.status, b.type, b.member
from v$log a , v$logfile b
where a.group#=b.group#
order by a.group# asc, a.sequence# asc;
SEQUENCE# GROUP# STATUS MEMBERS STATUS TYPE MEMBER
---------- ---------- ---------------- ---------- ------- ------- ------------------------------
289 1 INACTIVE 1 (null) ONLINE /dev/raw/raw12
290 2 INACTIVE 1 (null) ONLINE /dev/raw/raw13
291 3 CURRENT 1 (null) ONLINE /dev/raw/raw14
229 4 INACTIVE 1 (null) ONLINE /dev/raw/raw15
230 5 CURRENT 1 (null) ONLINE /dev/raw/raw16
228 6 INACTIVE 1 (null) ONLINE /dev/raw/raw17
|
딕셔너리 파일을 생성
|
1
|
SQL> exec dbms_logmnr_d.build ('logmnrdict.ora','/home/oracle/logmnr');
|
분석 할 로그 파일을 등록(update 실행 시간을 모른다면 모든 redo, 아카이브 확인)
|
1
2
3
4
5
6
7
|
SQL>
exec dbms_logmnr.add_logfile('/dev/raw/raw12',1);
exec dbms_logmnr.add_logfile('/dev/raw/raw13',3);
exec dbms_logmnr.add_logfile('/dev/raw/raw14',3);
exec dbms_logmnr.add_logfile('/dev/raw/raw15',3);
exec dbms_logmnr.add_logfile('/dev/raw/raw16',3);
exec dbms_logmnr.add_logfile('/dev/raw/raw17',3);
|
숫자 의미 : 1: 신규등록, 2: 파일 삭제, 3: 추가등록
(아카이브 로그파일을 넣어도됨)
등록한 log 파일을 분석
|
1
|
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/logmnrdict.ora');
|
※ 분석 범위 설정 옵션
startscn (기본값 : 0)
endscn (기본값 : 0)
starttime (기본값 : 01-JAN-1988)
endtime (기본값 : 01-JAN-2988)
dictfilename (기본값 : null)
Options (기본값 : 0)
조회(한번에 여러개 파일을 확인하면 속도가 느림)
|
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)이 나옴
바로 안나온다면 분석할 로그파일을 바꿔가며 확인
해당 SQL_UNDO를 복사 후 붙여넣기
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
update "SCOTT"."EMP2" set "EMPNO" = '7839' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAA';
update "SCOTT"."EMP2" set "EMPNO" = '7698' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAB';
update "SCOTT"."EMP2" set "EMPNO" = '7782' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAC';
update "SCOTT"."EMP2" set "EMPNO" = '7566' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAD';
update "SCOTT"."EMP2" set "EMPNO" = '7788' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAE';
update "SCOTT"."EMP2" set "EMPNO" = '7902' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAF';
update "SCOTT"."EMP2" set "EMPNO" = '7369' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAG';
update "SCOTT"."EMP2" set "EMPNO" = '7499' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAH';
update "SCOTT"."EMP2" set "EMPNO" = '7521' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAI';
update "SCOTT"."EMP2" set "EMPNO" = '7654' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAJ';
update "SCOTT"."EMP2" set "EMPNO" = '7844' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAK';
update "SCOTT"."EMP2" set "EMPNO" = '7876' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAL';
update "SCOTT"."EMP2" set "EMPNO" = '7934' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAN';
|
확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL> select * from scott.emp2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
1000 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
|
기존 상태대로 변경됨
제대로 적용 됬다면 커밋
|
1
|
SQL> commit;
|
로그 마이너 중지
|
1
|
SQL> exec dbms_logmnr.end_logmnr();
|
복구완료
sqlplus 에서 작업해서 조금 복잡한데 오렌지나 유료 프로그램을 사용하면 편리하게 로그마이너를 사용할 수 있음
재기동이 필요없는 로그마이너 방법
리두로그 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
col member for a30
select a.sequence#, a.group#, a.status, a.members, b.status, b.type, b.member
from v$log a , v$logfile b
where a.group#=b.group#
order by a.group# asc, a.sequence# asc;
SEQUENCE# GROUP# STATUS MEMBERS STATUS TYPE MEMBER
---------- ---------- ---------------- ---------- ------- ------- ------------------------------
289 1 INACTIVE 1 (null) ONLINE /dev/raw/raw12
290 2 INACTIVE 1 (null) ONLINE /dev/raw/raw13
291 3 CURRENT 1 (null) ONLINE /dev/raw/raw14
229 4 INACTIVE 1 (null) ONLINE /dev/raw/raw15
230 5 CURRENT 1 (null) ONLINE /dev/raw/raw16
228 6 INACTIVE 1 (null) ONLINE /dev/raw/raw17
|
분석 할 로그 파일을 등록(update 실행 시간을 모른다면 모든 redo, 아카이브 확인)
|
1
2
3
4
5
6
7
|
SQL>
exec dbms_logmnr.add_logfile('/dev/raw/raw12',1);
exec dbms_logmnr.add_logfile('/dev/raw/raw13',3);
exec dbms_logmnr.add_logfile('/dev/raw/raw14',3);
exec dbms_logmnr.add_logfile('/dev/raw/raw15',3);
exec dbms_logmnr.add_logfile('/dev/raw/raw16',3);
exec dbms_logmnr.add_logfile('/dev/raw/raw17',3);
|
숫자 의미 : 1: 신규등록, 2: 파일 삭제, 3: 추가등록
(아카이브 로그파일을 넣어도됨)
등록한 log 파일을 분석
|
1
|
SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
|
현재 운영중인 데이터베이스의 dictionary를 참조하게 할 수 있음
하지만 redo log file이 생성될 시점과 현재의 dictionary는 서로 다를 수 있다는 점을 고려해야함
또한 이 모드로는 'DDL tracking'을 사용할 수 없음
이 모드를 사용할 때는 DBMS_LOGMNR_D.BUILD의 과정이 필요없음
조회(한번에 여러개 파일을 확인하면 속도가 느림)
|
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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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-17:13:38:37 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)이 나옴
바로 안나온다면 분석할 로그파일을 바꿔가며 확인
해당 SQL_UNDO를 복사 후 붙여넣기
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
update "SCOTT"."EMP2" set "EMPNO" = '7839' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAA';
update "SCOTT"."EMP2" set "EMPNO" = '7698' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAB';
update "SCOTT"."EMP2" set "EMPNO" = '7782' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAC';
update "SCOTT"."EMP2" set "EMPNO" = '7566' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAD';
update "SCOTT"."EMP2" set "EMPNO" = '7788' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAE';
update "SCOTT"."EMP2" set "EMPNO" = '7902' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAF';
update "SCOTT"."EMP2" set "EMPNO" = '7369' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAG';
update "SCOTT"."EMP2" set "EMPNO" = '7499' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAH';
update "SCOTT"."EMP2" set "EMPNO" = '7521' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAI';
update "SCOTT"."EMP2" set "EMPNO" = '7654' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAJ';
update "SCOTT"."EMP2" set "EMPNO" = '7844' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAK';
update "SCOTT"."EMP2" set "EMPNO" = '7876' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAL';
update "SCOTT"."EMP2" set "EMPNO" = '7934' where "EMPNO" = '1000' and ROWID = 'AAADtqAAGAAAJkzAAN';
|
확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL> select * from scott.emp2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
1000 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
|
기존 상태대로 변경됨
제대로 적용 됬다면 커밋
|
1
|
SQL> commit;
|
로그 마이너 중지
|
1
|
SQL> exec dbms_logmnr.end_logmnr();
|
복구완료
sqlplus 에서 작업해서 조금 복잡한데 오렌지나 유료 프로그램을 사용하면 편리하게 로그마이너를 사용할 수 있음
추가 쿼리
아카이브 log 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
col name for a40
select name,sequence#,to_char(FIRST_TIME,'YYYY/MM/DD HH24:MI:SS') FIRST_TIME, to_char(NEXT_TIME,'YYYY/MM/DD HH24:MI:SS') NEXT_TIME
from v$archived_log
order by 2;
NAME SEQUENCE# FIRST_TIME NEXT_TIME
------------------------------ ---------- ------------------- -------------------
/arch/arc1/1_299_991691191.dbf 299 2019/01/28 00:02:50 2019/01/28 17:36:32
/arch/arc1/1_300_991691191.dbf 300 2019/01/28 17:36:32 2019/01/28 17:36:35
/arch/arc1/1_301_991691191.dbf 301 2019/01/28 17:36:35 2019/01/31 04:07:41
/arch/arc1/1_302_991691191.dbf 302 2019/01/31 04:07:41 2019/01/31 14:24:22
/arch/arc2/1_303_991691191.dbf 303 2019/01/31 14:24:22 2019/01/31 14:37:24
/arch/arc2/1_304_991691191.dbf 304 2019/01/31 14:37:24 2019/01/31 14:37:24
/arch/arc1/1_305_991691191.dbf 305 2019/01/31 14:41:17 2019/02/01 16:19:17
/arch/arc1/1_306_991691191.dbf 306 2019/02/01 16:19:17 2019/02/03 06:00:01
/arch/arc1/1_307_991691191.dbf 307 2019/02/03 06:00:01 2019/02/05 00:19:34
/arch/arc1/1_308_991691191.dbf 308 2019/02/05 00:19:34 2019/02/07 22:00:25
/arch/arc1/1_309_991691191.dbf 309 2019/02/07 22:00:25 2019/02/10 01:00:20
|
+supplemental logging 관련
일반 테이블의 경우 Chained/Migrated Rows 가 있을 경우 log miner 가 불가능함
로그마이너가 안되는 것에 대해서 V$LOGMNR_CONTENTS 뷰의 SQL_UNDO, SQL_REDO 를 확인 시 'Unsupported' 라고 표기됨
Chained/Migrated Rows 에 대해서는 supplemental 걸어야 지원됨
참조 : https://positivemh.tistory.com/424
http://haisins.epac.to/wordpress/?p=2494
https://dinggur.tistory.com/159
https://positivemh.tistory.com/415
https://cafe.naver.com/prodba/47206
https://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL1583
'ORACLE > Admin' 카테고리의 다른 글
| ALTER SYSTEM ARCHIVE LOG CURRENT 관련 (0) | 2017.10.18 |
|---|---|
| OS와 DB간 디스크 관련 팁 (0) | 2017.07.12 |
| oracle glogin.sql 설정 (0) | 2017.01.11 |
| Oracle ASM (0) | 2016.12.29 |
| 12c R2 샤딩 (Sharding) 관련 링크 (0) | 2016.11.25 |