오라클 11g R2 XML데이터 update 로그마이너 복구 테스트

2021. 10. 6. 21:20 내맘대로긍정

프린트 하기

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 11.2.0.4

방법 : 오라클 11g R2 XML데이터 update 로그마이너 복구 테스트

xml 타입이 포함된 테이블 생성 후 데이터 삽입 후 데이터를 update 한 뒤

로그마이너로 복구하는 시나리오 + flashback으로 복구하는 시나리오

아카이브 모드 확인

1
2
3
4
5
6
7

SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /oracle/app/oracle/arch
Oldest online log sequence     134
Next log sequence to archive   136
Current log sequence           136

사전 파라미터 설정

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SQL> select supplemental_log_data_min from v$database;
 
SUPPLEME
--------
NO
 
SQL> alter database add supplemental log data;
 
Database altered.
 
SQL> select supplemental_log_data_min from v$database;
 
SUPPLEME
--------
YES

유저 생성 및 권한 부여

1
2
3

SQL> 
create user imsi identified by imsi account unlock;
grant resource, connect to imsi;

샘플 테이블 생성

sample_tab1 : 일반 컬럼만 있는 샘플테이블

sample_xml_tab1 : xml 타입 컬럼이 있는 샘플테이블1

sample_xml_tab2 : xml 타입 컬럼이 있는 샘플테이블2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SQL> 
conn imsi/imsi
create table sample_tab1
(
samid number,
samDat varchar2(5)
);
 
create table sample_xml_tab1
(
samid number,
samDat xmltype
);
 
create table sample_xml_tab2
(
samid number,
samDat xmltype
);

샘플 데이터 삽입

1
2
3
4
5
6
7
8
9
10
11
12

SQL> 
insert into sample_tab1 values (1, 'test1');
insert into sample_tab1 values (2, 'test2');
commit;
 
insert into sample_xml_tab1 values (1, XMLType.createXML('<?xml version="1.0" encoding="UTF-8"?><Sampledata>data1</Sampledata>'));
insert into sample_xml_tab1 values (2, XMLType.createXML('<?xml version="1.0" encoding="UTF-8"?><Sampledata>data2</Sampledata>'));
commit;
 
insert into sample_xml_tab2 values (100, XMLType.createXML('<?xml version="1.0" encoding="UTF-8"?><Sampledata>data100</Sampledata>'));
insert into sample_xml_tab2 values (200, XMLType.createXML('<?xml version="1.0" encoding="UTF-8"?><Sampledata>data200</Sampledata>'));
commit;

데이터 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SQL> 
select samid, samdat from sample_tab1;
 
     SAMID SAMDA
---------- -----
     1 test1
     2 test2
 
SQL> 
col sample for a30
select samid, s1.samDat.extract('/Sampledata/text()').getStringVal() "SAMPLE" from sample_xml_tab1 s1;
 
     SAMID SAMPLE
---------- ------------------------------
     1 data1
     2 data2
 
SQL> 
select samid, s2.samDat.extract('/Sampledata/text()').getStringVal() "SAMPLE" from sample_xml_tab2 s2;
 
     SAMID SAMPLE
---------- ------------------------------
       100 data100
       200 data200

일반 테이블 정상 업데이트 및 확인(where 기입)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SQL> 
update sample_tab1
set samdat='test3'
where samdat='test1';
 
1 row updated.
 
SQL> commit;
 
SQL> 
select samid, samdat from sample_tab1;
     SAMID SAMDA
---------- -----
     1 test3
     2 test2

xml 테이블 정상 업데이트 및 확인(where 기입)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SQL> 
update sample_xml_tab1 s1
set s1.samDat = XMLType('<?xml version="1.0" encoding="UTF-8"?><Sampledata>data3</Sampledata>')
where s1.samDat.extract('/Sampledata/text()').getStringVal() = 'data1';
 
1 row updated.
 
SQL> commit;
 
SQL> select samid, s1.samDat.extract('/Sampledata/text()').getStringVal() "SAMPLE" from sample_xml_tab1 s1;
 
     SAMID SAMPLE
---------- ------------------------------
     1 data3
     2 data2

xml 테이블 비정상 업데이트(where 미기입)

1
2
3
4
5
6
7

SQL> 
update sample_xml_tab2 s2
set s2.samDat = XMLType('<?xml version="1.0" encoding="UTF-8"?><Sampledata>data300</Sampledata>');
 
2 rows updated.
 
SQL> commit;

장애상황 발생(sample_xml_tab2 테이블 모든 데이터가 data300으로 update됨)

1
2
3
4
5
6

SQL> select samid, s2.samDat.extract('/Sampledata/text()').getStringVal() "SAMPLE" from sample_xml_tab2 s2;
 
     SAMID SAMPLE
---------- ------------------------------
       100 data300
       200 data300

sys 계정 접속 후 로그마이너 진행

현재 redo 확인

1
2
3
4
5
6
7
8
9
10
11
12
13

SQL> conn / as sysdba
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
---------- ---------- ---------------- ---------- ------- ------- --------------------------------------------------
       127        1 INACTIVE            1 (null)  ONLINE  /oracle/app/oracle/oradata/ORCL11/redo01.log
       128        2 CURRENT            1 (null)  ONLINE  /oracle/app/oracle/oradata/ORCL11/redo02.log
       126        3 INACTIVE            1 (null)  ONLINE  /oracle/app/oracle/oradata/ORCL11/redo03.log

파라미터 설정(12cR2 이하)

1
2
3

SQL> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
 
System altered.

DB 재기동

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 2137886720 bytes
Fixed Size            2254952 bytes
Variable Size          989857688 bytes
Database Buffers     1140850688 bytes
Redo Buffers            4923392 bytes
Database mounted.
Database opened.

확인

1
2
3
4
5

SQL> show parameter utl_file_dir
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                 string     /home/oracle/logmnr

파라미터 설정(디렉토리 생성)(12cR2 이상)
12cR2 이상은 디렉토리 생성 후 재기동 필요없음

1
2
3

SQL> create directory dict as '/home/oracle/logmnr';
 
Directory created.

딕셔너리 파일 생성(12cR2 이하)

1

SQL> exec dbms_logmnr_d.build ('logmnrdict.ora','/home/oracle/logmnr');

딕셔너리 파일 생성(12cR2 이상)

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

SQL> !ls -al /home/oracle/logmnr
total 12840
drwxr-xr-x   2 oracle dba     4096 Oct  7 02:29 .
drwx------. 17 oracle dba     4096 Oct  7 01:47 ..
-rw-r--r--   1 oracle dba 13136147 Oct  7 02:29 logmnrdict.ora

분석 할 로그 파일을 등록(update 실행 시간을 모른다면 모든 redo, 아카이브 확인)

1
2
3
4

SQL> 
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/ORCL11/redo01.log',1);
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/ORCL11/redo02.log',3);
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/ORCL11/redo03.log',3);

숫자 의미 : 1: 신규등록, 2: 파일 삭제, 3: 추가등록

등록한 log 파일을 분석

1

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/logmnrdict.ora');

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

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

SQL> 
set lines 200 pages 1000
col time for a20
col seg_owner for a10
col username for a10
col sql_redo for a200
col sql_undo for a200
select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS') time, seg_owner, username, sql_redo, sql_undo
from v$logmnr_contents
where sql_redo like 'update%'
and seg_owner='IMSI'
and table_name = 'SAMPLE_XML_TAB2';
 
TIME             SEG_OWNER    USERNAME
-------------------- ---------- ----------
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-10-07:03:42:20  IMSI    IMSI
update "IMSI"."SAMPLE_XML_TAB2" a set a."SAMDAT" = XMLType('<?xml version="1.0" encoding="UTF-8"?><Sampledata>data100</Sampledata>') where a."SAMID" = '100' and a.ROWID = 'AAAEaQAAEAAAFZTAAA';
update "IMSI"."SAMPLE_XML_TAB2" a set a."SAMDAT" = NULL where a."SAMID" = '100' and a.ROWID = 'AAAEaQAAEAAAFZTAAA';
 
2021-10-07:03:42:20  IMSI    IMSI
update "IMSI"."SAMPLE_XML_TAB2" a set a."SAMDAT" = XMLType('<?xml version="1.0" encoding="UTF-8"?><Sampledata>data200</Sampledata>') where a."SAMID" = '200' and a.ROWID = 'AAAEaQAAEAAAFZTAAB';
update "IMSI"."SAMPLE_XML_TAB2" a set a."SAMDAT" = NULL where a."SAMID" = '200' and a.ROWID = 'AAAEaQAAEAAAFZTAAB';

sql_redo(실행한 update 구문(21, 25번째 줄))과 sql_undo(복구용 update 구문(22, 26번째 줄))이 나옴
하지만 복구해야할 컬럼 데이터인 set a."SAMDAT"의 값이 NULL로 나옴("set a."SAMDAT" = NULL" 부분)

* update 전 값으로 복구할 수가 없음

일반 테이블의 update 구문의 경우 sql_undo에 아래와 같이 변경 전 값이 나옴

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SQL>
set lines 200 pages 1000
col time for a20
col seg_owner for a10
col username for a10
col sql_redo for a200
col sql_undo for a200
select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS') time, seg_owner, username, sql_redo, sql_undo
from v$logmnr_contents
where sql_redo like 'update%'
and seg_owner='IMSI'
and table_name = 'SAMPLE_TAB1';
 
TIME             SEG_OWNER    USERNAME
-------------------- ---------- ----------
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-10-07:03:43:02  IMSI    IMSI
update "IMSI"."SAMPLE_TAB1" set "SAMDAT" = 'test3' where "SAMDAT" = 'test1' and ROWID = 'AAAEaMAAEAAAFZrAAA';
update "IMSI"."SAMPLE_TAB1" set "SAMDAT" = 'test1' where "SAMDAT" = 'test3' and ROWID = 'AAAEaMAAEAAAFZrAAA';

22번째 줄 set "SAMDAT" = 'test1' 부분처럼 update전 값이 나와야하는데

xml 테이블 데이터의 경우 해당 값이 NULL로 나와서 정상적인 데이터 복구가 불가능함

결론 : 일반 테이블 데이터의 경우 update문을 복구할수 있는

sql_undo가 정상적으로 나와서 복구(update 이전값으로 되돌리기)가 가능하지만

xml 테이블 데이터의 경우 update문을 복구할수 있는

sql_undo의 set 부분이 NULL 값으로 나와서 복구(update 이전값으로 되돌리기)가 불가능함

이 경우 flashback 명령(시간이 많이 안지난경우)을 이용하거나 핫백업본과 아카이브 로그(시간이 많이 지난경우)가 있다면

clone db를 만들어서 아카이브를 이용해 update 이전까지만 복구하여 해당 테이블을 복구해야함

일반 테이블 update 로그마이너와 xml 테이블 로그마이너 차이 비교

flashback 복구 방법

현재 db 시간 확인

1
2
3
4

SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') time from dual
TIME
--------------------
2021-10-07:06:30:51

update 입력한 시간 확인

위 로그마이너 조회 구문에서 가져옴 2021-10-07:03:42:20

해당 시간대 + 10초로 테이블 조회

1
2
3
4
5
6
7
8
9
10
11
12
13

SQL> select * from sample_xml_tab2 as of timestamp(to_date('2021-10-07:03:42:30','YYYY-MM-DD:HH24:MI:SS'));
 
     SAMID
----------
SAMDAT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       100
<?xml version="1.0" encoding="US-ASCII"?>
<Sampledata>data100</Sampledata>
 
       200
<?xml version="1.0" encoding="US-ASCII"?>
<Sampledata>data200</Sampledata>

변경전 값이 남아있음

해당 내용으로 CTAS 테이블 생성 후 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SQL> 
create table sample_xml_tab2_bak
as select * from sample_xml_tab2 as of timestamp(to_date('2021-10-07:03:42:30','YYYY-MM-DD:HH24:MI:SS'));
 
Table created.
SQL> select * from sample_xml_tab2_bak;
 
     SAMID
----------
SAMDAT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       100
<?xml version="1.0" encoding="US-ASCII"?>
<Sampledata>data100</Sampledata>
 
       200
<?xml version="1.0" encoding="US-ASCII"?>
<Sampledata>data200</Sampledata>

정상적으로 데이터가 복구됨

이제 이 값으로 기존 테이블테이터 매칭시켜 복구를 진행하면됨

timstamp 구문 참조용

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 30분 이전의 해당 테이블의 데이터 조회
SELECT * FROM SAMPLE_TABLE AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '30' MINUTE);
 
-- 3시간 이전의 해당 테이블의 데이터 조회
SELECT * FROM SAMPLE_TABLE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '3' HOUR);
 
-- 1일 이전의 해당 테이블의 데이터 조회
SELECT * FROM SAMPLE_TABLE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
 
-- 2분전의 데이터  복구
FLASHBACK TABLE SAMPLE_TABLE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE);
 
--Row Movement 비활성화 오류시
ALTER TABLE SAMPLE_TABLE ENABLE ROW MOVEMENT;

참조 :

https://positivemh.tistory.com/424

로그마이너 log miner 사용법 12cR2 이상

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..

positivemh.tistory.com

https://positivemh.tistory.com/63

로그마이너 log miner 사용법

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 2..

positivemh.tistory.com

https://fotog.tistory.com/405

https://jp1020.tistory.com/entry/Oracle-FlashBack-%EC%9D%B4%EC%9A%A9%ED%95%9C-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B3%B5%EA%B5%AC

저작자표시

'ORACLE > Backup&Recover' 카테고리의 다른 글

오라클 11g R2 RAC PSU 패치전 백업 가이드 (0)	2021.10.20
오라클 11g R2 GRID OLR 백업 복구 (0)	2021.10.20
오라클 11g R2 에서 오라클 19c Rman 백업셋 이용 업그레이드 방법 (4)	2021.06.10
오라클 11g R2 hot 백업 후 until scn 을 이용한 복구 방법 (0)	2021.04.23
오라클 11g R2 rman 백업 후 until scn 을 이용한 복구 방법 (0)	2021.04.23

내맘대로긍정

OracleDBA@kakao.com

Category

Menu

Comment

Link