OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 12.2.0.1
방법 : 로그마이너 log miner 사용법 12cR2 이상
사전 준비
로그마이너가 있는지 확인
|
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
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> col member for a50
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
---------- ---------- ---------------- ---------- ------- ------- --------------------------------------------------
276 1 CURRENT 1 (null) ONLINE /oracle/app/oracle/oradata/ORA1/redo01.log
275 2 INACTIVE 1 (null) ONLINE /oracle/app/oracle/oradata/ORA1/redo02.log
274 3 INACTIVE 1 (null) ONLINE /oracle/app/oracle/oradata/ORA1/redo03.log
3 rows selected.
|
디렉토리 생성
|
1
2
3
|
SQL> create directory dict as '/home/oracle/logmnr';
Directory created.
|
딕셔너리 파일을 생성
|
1
2
3
4
5
6
7
8
9
|
SQL>
BEGIN
SYS.DBMS_LOGMNR_D.build (
dictionary_filename => 'logmnrdict.ora',
dictionary_location => 'DICT'); <-디렉토리 이름 대문자로 입력
END;
/
PL/SQL procedure successfully completed.
|
디렉토리 파일 생성 확인
|
1
2
3
4
5
|
$ ls -al /home/oracle/logmnr
total 39256
drwxr-xr-x 2 oracle dba 27 Feb 15 12:59 .
drwxr-xr-x. 13 oracle dba 4096 Feb 15 12:58 ..
-rw-r--r-- 1 oracle dba 40192885 Feb 15 12:55 logmnrdict.ora
|
분석 할 로그 파일을 등록(update 실행 시간을 모른다면 모든 redo, 아카이브 확인)
|
1
2
3
4
|
SQL>
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/ORA1/redo01.log',1);
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/ORA1/redo02.log',3);
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/ORA1/redo03.log',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
28
29
30
31
32
|
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-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7839' and ROWID = 'AAAR9LAAHAAAACjAAA'; update "SCOTT"."EMP2" set "EMPNO" = '7839' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAA';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7698' and ROWID = 'AAAR9LAAHAAAACjAAB'; update "SCOTT"."EMP2" set "EMPNO" = '7698' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAB';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7782' and ROWID = 'AAAR9LAAHAAAACjAAC'; update "SCOTT"."EMP2" set "EMPNO" = '7782' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAC';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7566' and ROWID = 'AAAR9LAAHAAAACjAAD'; update "SCOTT"."EMP2" set "EMPNO" = '7566' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAD';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7788' and ROWID = 'AAAR9LAAHAAAACjAAE'; update "SCOTT"."EMP2" set "EMPNO" = '7788' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAE';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7902' and ROWID = 'AAAR9LAAHAAAACjAAF'; update "SCOTT"."EMP2" set "EMPNO" = '7902' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAF';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7369' and ROWID = 'AAAR9LAAHAAAACjAAG'; update "SCOTT"."EMP2" set "EMPNO" = '7369' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAG';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7499' and ROWID = 'AAAR9LAAHAAAACjAAH'; update "SCOTT"."EMP2" set "EMPNO" = '7499' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAH';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7521' and ROWID = 'AAAR9LAAHAAAACjAAI'; update "SCOTT"."EMP2" set "EMPNO" = '7521' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAI';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7654' and ROWID = 'AAAR9LAAHAAAACjAAJ'; update "SCOTT"."EMP2" set "EMPNO" = '7654' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAJ';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7844' and ROWID = 'AAAR9LAAHAAAACjAAK'; update "SCOTT"."EMP2" set "EMPNO" = '7844' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAK';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7876' and ROWID = 'AAAR9LAAHAAAACjAAL'; update "SCOTT"."EMP2" set "EMPNO" = '7876' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAL';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7900' and ROWID = 'AAAR9LAAHAAAACjAAM'; update "SCOTT"."EMP2" set "EMPNO" = '7900' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAM';
2019-02-15:13:51:49 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7934' and ROWID = 'AAAR9LAAHAAAACjAAN'; update "SCOTT"."EMP2" set "EMPNO" = '7934' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAN';
14 rows selected.
|
실수로 update 한 구문(SQL_REDO)과 되돌릴수 있는 구문(SQL_UNDO)이 나옴
바로 안나온다면 분석할 로그파일을 바꿔가며 확인
해당 SQL_UNDO를 복사 후 붙여넣기
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
update "SCOTT"."EMP2" set "EMPNO" = '7839' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAA';
update "SCOTT"."EMP2" set "EMPNO" = '7698' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAB';
update "SCOTT"."EMP2" set "EMPNO" = '7782' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAC';
update "SCOTT"."EMP2" set "EMPNO" = '7566' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAD';
update "SCOTT"."EMP2" set "EMPNO" = '7788' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAE';
update "SCOTT"."EMP2" set "EMPNO" = '7902' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAF';
update "SCOTT"."EMP2" set "EMPNO" = '7369' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAG';
update "SCOTT"."EMP2" set "EMPNO" = '7499' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAH';
update "SCOTT"."EMP2" set "EMPNO" = '7521' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAI';
update "SCOTT"."EMP2" set "EMPNO" = '7654' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAJ';
update "SCOTT"."EMP2" set "EMPNO" = '7844' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAK';
update "SCOTT"."EMP2" set "EMPNO" = '7876' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAL';
update "SCOTT"."EMP2" set "EMPNO" = '7900' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAM';
update "SCOTT"."EMP2" set "EMPNO" = '7934' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAN';
|
확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
|
기존 상태대로 변경됨
제대로 적용 됬다면 커밋
|
1
|
SQL> commit;
|
로그 마이너 중지
|
1
|
SQL> exec dbms_logmnr.end_logmnr();
|
다른방법(dictionary 참조해서 작업)
리두로그 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> col member for a50
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
---------- ---------- ---------------- ---------- ------- ------- --------------------------------------------------
276 1 CURRENT 1 (null) ONLINE /oracle/app/oracle/oradata/ORA1/redo01.log
275 2 INACTIVE 1 (null) ONLINE /oracle/app/oracle/oradata/ORA1/redo02.log
274 3 INACTIVE 1 (null) ONLINE /oracle/app/oracle/oradata/ORA1/redo03.log
3 rows selected.
|
분석 할 로그 파일을 등록(update 실행 시간을 모른다면 모든 redo, 아카이브 확인)
|
1
2
3
4
|
SQL>
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/ORA1/redo01.log',1);
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/ORA1/redo02.log',3);
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/ORA1/redo03.log',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
28
29
30
31
32
|
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-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7839' and ROWID = 'AAAR9LAAHAAAACjAAA'; update "SCOTT"."EMP2" set "EMPNO" = '7839' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAA';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7698' and ROWID = 'AAAR9LAAHAAAACjAAB'; update "SCOTT"."EMP2" set "EMPNO" = '7698' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAB';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7782' and ROWID = 'AAAR9LAAHAAAACjAAC'; update "SCOTT"."EMP2" set "EMPNO" = '7782' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAC';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7566' and ROWID = 'AAAR9LAAHAAAACjAAD'; update "SCOTT"."EMP2" set "EMPNO" = '7566' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAD';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7788' and ROWID = 'AAAR9LAAHAAAACjAAE'; update "SCOTT"."EMP2" set "EMPNO" = '7788' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAE';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7902' and ROWID = 'AAAR9LAAHAAAACjAAF'; update "SCOTT"."EMP2" set "EMPNO" = '7902' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAF';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7369' and ROWID = 'AAAR9LAAHAAAACjAAG'; update "SCOTT"."EMP2" set "EMPNO" = '7369' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAG';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7499' and ROWID = 'AAAR9LAAHAAAACjAAH'; update "SCOTT"."EMP2" set "EMPNO" = '7499' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAH';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7521' and ROWID = 'AAAR9LAAHAAAACjAAI'; update "SCOTT"."EMP2" set "EMPNO" = '7521' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAI';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7654' and ROWID = 'AAAR9LAAHAAAACjAAJ'; update "SCOTT"."EMP2" set "EMPNO" = '7654' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAJ';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7844' and ROWID = 'AAAR9LAAHAAAACjAAK'; update "SCOTT"."EMP2" set "EMPNO" = '7844' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAK';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7876' and ROWID = 'AAAR9LAAHAAAACjAAL'; update "SCOTT"."EMP2" set "EMPNO" = '7876' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAL';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7900' and ROWID = 'AAAR9LAAHAAAACjAAM'; update "SCOTT"."EMP2" set "EMPNO" = '7900' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAM';
2019-02-15:13:59:13 SCOTT SCOTT update "SCOTT"."EMP2" set "EMPNO" = '1000' where "EMPNO" = '7934' and ROWID = 'AAAR9LAAHAAAACjAAN'; update "SCOTT"."EMP2" set "EMPNO" = '7934' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAN';
14 rows selected.
|
실수로 update 한 구문(SQL_REDO)과 되돌릴수 있는 구문(SQL_UNDO)이 나옴
바로 안나온다면 분석할 로그파일을 바꿔가며 확인
해당 SQL_UNDO를 복사 후 붙여넣기
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
update "SCOTT"."EMP2" set "EMPNO" = '7839' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAA';
update "SCOTT"."EMP2" set "EMPNO" = '7698' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAB';
update "SCOTT"."EMP2" set "EMPNO" = '7782' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAC';
update "SCOTT"."EMP2" set "EMPNO" = '7566' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAD';
update "SCOTT"."EMP2" set "EMPNO" = '7788' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAE';
update "SCOTT"."EMP2" set "EMPNO" = '7902' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAF';
update "SCOTT"."EMP2" set "EMPNO" = '7369' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAG';
update "SCOTT"."EMP2" set "EMPNO" = '7499' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAH';
update "SCOTT"."EMP2" set "EMPNO" = '7521' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAI';
update "SCOTT"."EMP2" set "EMPNO" = '7654' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAJ';
update "SCOTT"."EMP2" set "EMPNO" = '7844' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAK';
update "SCOTT"."EMP2" set "EMPNO" = '7876' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAL';
update "SCOTT"."EMP2" set "EMPNO" = '7900' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAM';
update "SCOTT"."EMP2" set "EMPNO" = '7934' where "EMPNO" = '1000' and ROWID = 'AAAR9LAAHAAAACjAAN';
|
확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
|
기존 상태대로 변경됨
제대로 적용 됬다면 커밋
|
1
|
SQL> commit;
|
로그 마이너 중지
|
1
|
SQL> exec dbms_logmnr.end_logmnr();
|
복구완료
sqlplus 에서 작업해서 조금 복잡한데 오렌지나 유료 프로그램을 사용하면 편리하게 로그마이너를 사용할 수 있음
참조 :
https://positivemh.tistory.com/63
'ORACLE > Admin' 카테고리의 다른 글
| grid 설치 시 사전 점검 스크립트 ./runcluvfy.sh (0) | 2019.02.15 |
|---|---|
| diagnostic 경로 변경 (0) | 2019.02.15 |
| 오라클 drop database 데이터베이스 삭제 (0) | 2019.02.15 |
| oracle 9i client 12c 접속 12c client 9i 접속 시도 (0) | 2019.02.13 |
| sqlplus spool 기능 사용 시 명령어 안나오게 하는방법 (0) | 2019.02.11 |