OS환경 : Oracle Linux 7.6 (64bit), Windows 10 Pro (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c DUL 이용 Truncate 된 데이터 복구(비공식 툴)
오라클 환경에서 truncate 된 테이블의 데이터도 복구할수 있는 DUL이라는 툴이 있다고 해서 테스트해봄
이 툴의 특징은 노아카이브 모드 상태에서 truncate 한 테이블도
데이터파일만 가지고 복구를 할수 있다는점인데, 테스트해본 결과 정말 복구가 됨
참고로 무료 버전의 경우 1만개 row 까지만 복구가 가능함
테스트1. 전체 데이터파일을 이용해 복구 시도
테스트2. 해당 TS 데이터파일 + SYSTEM TS 데이터파일로 복구 시도
테스트3. 해당 TS 데이터파일만으로 복구 시도
테스트
아카이브 모드 확인
|
1
2
3
4
5
6
|
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /ORA19/app/oracle/arch
Oldest online log sequence 47
Current log sequence 49
|
노아카이브 모드 상태임
샘플 테이블 생성 및 데이터 삽입
|
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
|
SQL>
drop table reco_test purge;
create table reco_test
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
);
DECLARE
TYPE tbl_ins IS TABLE OF reco_test%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..100000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO reco_test VALUES w_ins(i);
COMMIT;
END;
/
|
샘플 테이블 건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from reco_test;
COUNT(*)
----------
100000
|
데이터가 10만건 존재함
샘플 테이블 truncate
|
1
2
3
|
SQL> truncate table reco_test;
Table truncated.
|
샘플 테이블 건수 재확인
|
1
2
3
4
5
|
SQL> select count(*) from reco_test;
COUNT(*)
----------
0
|
데이터가 모두 지워지고 0건이 됨
복구 사전 작업
샘플 테이블이 들어있던 테이블스페이스 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, tablespace_name from dba_segments
where segment_name = 'RECO_TEST';
SEGMENT_NAME TABLESPACE_NAME
-------------------- ------------------------------
RECO_TEST USERS
|
USERS 테이블스페이스에 존재함
DB 전체 데이터 파일 목록 확인
|
1
2
3
4
5
6
7
8
|
SQL> select file_name from dba_data_files;
FILE_NAME
-------------------------------------------------
/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
|
linux 환경에 존재하는 데이터파일들을 윈도우 환경으로 복제
DUL 사용전 java 다운로드
Oracle JAVA 및 Open JDK 다운로드 및 설치 가이드 ( https://positivemh.tistory.com/965 )
DUL 다운로드
https://www.askmac.cn/archives/dul-oracle-data-unloader-%EB%8B%A4%EC%9A%B4%EB%A1%9C%EB%93%9C.html
링크 들어가서 http://zcdn.parnassusdata.com/DUL5108.zip 링크 다운로드
(크롬에서 다운로드 시도시 안전하지 않은 다운로드 차단됨이라고 나오는데 무시하고 다운로드함
바이러스 포탈(https://www.virustotal.com/)에서 바이러스 검사결과 특이사항 나오지 않음)
테스트1. 전체 데이터파일을 이용해 복구 시도
다운로드 후 압축 해제 및 prm.bat 파일 실행
좌측 상단 Start - Recovery Wizard 선택
Next 선택
DICTIONARY MODE 선택
기본값으로 선택
Choose Files 선택
복사한 데이터파일들 모두 선택
Load 선택
Loading 프로세스 실행중
DB가 로딩됨, 유저의 테이블들을 확인가능함
복구하려는 테이블 우클릭 후 Unload truncated data 선택
복구중
복구가 완료됨
무료버전이라 10000개 까지만 복구가 됨 788개는 왜 추가로 더 복구되었는지는 모르겠음
유료 스탠다드를 쓰면 10만개, 유료 엔터프라이즈 버전을 쓰면 무제한으로 복구가능한듯함
복구된 파일이 존재하는 경로로 이동
.truncate 파일, .ctl 파일 2개 다시 linux 로 복사
|
1
2
|
imsi.reco_test.dat.truncated
imsi.reco_test.ctl
|
복사 후 오라클 권한 부여
|
1
2
|
# chown oracle:dba imsi.reco_test.ctl
# chown oracle:dba imsi.reco_test.dat.truncated
|
오라클 유저 폴더로 이동 후 확인
|
1
2
3
4
5
6
7
8
9
|
# mv imsi.reco_test.* /home/oracle/reco/
# su - oracle
$ cd reco/
$ ls -al
합계 1348
drwxr-xr-x 2 oracle oinstall 68 3월 27 14:39 .
drwx------. 9 oracle oinstall 4096 3월 27 14:35 ..
-rw-r--r-- 1 oracle dba 302 3월 27 14:39 imsi.reco_test.ctl
-rw-r--r-- 1 oracle dba 1371177 3월 27 14:39 imsi.reco_test.dat.truncated
|
정상적으로 옮겨짐
파일들 확인(.ctl 파일)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
$ cat imsi.reco_test.ctl
LOAD DATA
INFILE 'imsi.reco_test.dat.truncated'
APPEND
INTO TABLE imsi.reco_test
FIELDS TERMINATED BY ' '
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
"ID1" ,
"ID2" ,
"NAME" ,
"DATE1" ,
"DATE2" ,
"DATE3" DATE "DD-MON-YYYY HH24:MI:SS AD" ,
"PHONE" ,
"PRICE" ,
"QTY" ,
"TEST1" ,
"TEST2" ,
"TEST3"
)
|
sql loader 동작 형식으로 ctl 파일이 존재함
파일들 확인(.truncated 파일)
|
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
|
$ cat imsi.reco_test.dat.truncated
"1" "12660449" "XKAW7" "20130127" "20210213" "18-NOV-2018 06:20:13 AD" "010-6647-5512" "5000" "2" "1234" "SQLP" "A460"
"2" "23107547" "JEU9A" "20140907" "20210105" "13-FEB-2019 04:05:36 AD" "010-7855-4569" "3000" "2" "1234" "SQLP" "A429"
"3" "39124869" "9RGGB" "20160806" "20210526" "24-OCT-2017 14:15:55 AD" "010-5216-1585" "3000" "9" "1234" "SQLP" "A585"
"4" "41404745" "1PT3M" "20190722" "20211219" "24-DEC-2011 04:48:56 AD" "010-5800-1616" "3000" "8" "1234" "SQLP" "A928"
"5" "56269229" "OO2JO" "20200421" "20210312" "24-FEB-2017 03:17:50 AD" "010-3832-6568" "9000" "5" "1234" "SQLP" "A922"
"6" "64943882" "XNJS0" "20170902" "20210607" "08-FEB-2019 12:58:53 AD" "010-6212-7502" "3000" "7" "1234" "SQLP" "A786"
"7" "79315376" "3404K" "20140603" "20210324" "14-APR-2013 03:10:25 AD" "010-9235-7923" "10000" "2" "1234" "SQLP" "A556"
"8" "89003988" "LUZUK" "20150317" "20210528" "22-NOV-2017 04:09:29 AD" "010-7063-9500" "10000" "9" "1234" "SQLP" "A515"
"9" "92963167" "6FXWN" "20160919" "20210711" "27-FEB-2018 14:36:47 AD" "010-9824-8522" "2000" "9" "1234" "SQLP" "A811"
"10" "10736036" "CMKUD" "20190713" "20210420" "21-JUL-2014 15:24:18 AD" "010-2404-7534" "10000" "3" "1234" "SQLP" "A164"
"11" "115930432" "IEW4B" "20180919" "20211023" "08-SEP-2020 07:41:13 AD" "010-9786-1324" "5000" "9" "1234" "SQLP" "A879"
"12" "125084721" "PDVRI" "20201205" "20210509" "22-APR-2015 21:34:07 AD" "010-5369-4683" "4000" "4" "1234" "SQLP" "A494"
"13" "136009941" "SFVDC" "20160723" "20210822" "06-MAY-2021 10:05:17 AD" "010-8764-2248" "10000" "5" "1234" "SQLP" "A183"
"14" "146838030" "MUE6M" "20120423" "20210619" "22-JUL-2020 06:17:05 AD" "010-7878-6295" "10000" "5" "1234" "SQLP" "A743"
"15" "156568736" "PU6F9" "20150910" "20211103" "18-MAR-2015 12:36:36 AD" "010-3905-9992" "6000" "7" "1234" "SQLP" "A432"
"16" "167506711" "MGIUV" "20190720" "20211222" "08-OCT-2016 15:39:22 AD" "010-6108-2554" "9000" "9" "1234" "SQLP" "A885"
"17" "175650714" "2DUM2" "20130309" "20210210" "19-MAR-2011 02:13:45 AD" "010-2599-7647" "3000" "5" "1234" "SQLP" "A899"
"18" "187115957" "VAU6R" "20121111" "20210313" "03-JUN-2011 14:06:02 AD" "010-1602-6728" "2000" "3" "1234" "SQLP" "A989"
"19" "192022451" "UHFU3" "20180822" "20210721" "16-NOV-2019 14:48:56 AD" "010-6594-3726" "8000" "4" "1234" "SQLP" "A837"
"20" "202098554" "SR2AE" "20131004" "20210821" "04-APR-2012 22:52:23 AD" "010-9724-5183" "2000" "10" "1234" "SQLP" "A429"
"21" "216893886" "2WJPC" "20191125" "20210522" "22-NOV-2021 19:04:57 AD" "010-8339-3166" "6000" "9" "1234" "SQLP" "A119"
"22" "229634799" "WFZSF" "20180403" "20211125" "14-APR-2011 10:04:17 AD" "010-1923-2682" "9000" "7" "1234" "SQLP" "A711"
"23" "239616105" "36XOM" "20150404" "20211108" "20-FEB-2011 22:26:37 AD" "010-8924-8532" "8000" "5" "1234" "SQLP" "A176"
"24" "244744792" "CU465" "20181006" "20210905" "04-OCT-2012 15:16:39 AD" "010-8600-3134" "5000" "2" "1234" "SQLP" "A981"
"25" "257958825" "9X1Y4" "20200827" "20210202" "11-MAR-2019 03:23:28 AD" "010-7054-9969" "4000" "2" "1234" "SQLP" "A183"
"26" "262452209" "PYMAV" "20131121" "20210410" "22-MAY-2015 06:03:46 AD" "010-7078-9489" "3000" "6" "1234" "SQLP" "A421"
"27" "275045342" "XI3XN" "20110513" "20210419" "07-NOV-2018 21:11:02 AD" "010-6669-8748" "4000" "6" "1234" "SQLP" "A481"
"28" "285853671" "HEV2F" "20200717" "20210801" "25-OCT-2018 07:23:12 AD" "010-7063-2948" "5000" "10" "1234" "SQLP" "A310"
.
.
|
이 파일은 데이터가 들어있는 파일임
.truncated 파일 라인 수 확인
|
1
2
|
$ wc -l imsi.reco_test.dat.truncated
10788 imsi.reco_test.dat.truncated
|
10788 개의 row가 존재함(무료버전이라 이정도밖에 복구하지 못함)
imsi.reco_test.ctl 파일의 테이블 명 변경(덮어쓰기 방지)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
$ vi imsi.reco_test.ctl
기존
LOAD DATA
INFILE 'imsi.reco_test.dat.truncated'
APPEND
INTO TABLE imsi.reco_test
.
.
변경
LOAD DATA
INFILE 'imsi.reco_test.dat.truncated'
APPEND
INTO TABLE imsi.reco_test_bak
.
.
|
reco_test_bak 테이블 생성
|
1
2
3
|
SQL> create table reco_test_bak as select * from reco_test where 1=2;
Table created.
|
SQL Loader 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ sqlldr control=imsi.reco_test.ctl direct=y
Username:/ as sysdba <<< / as sysdba 입력
SQL*Loader: Release 19.0.0.0.0 - Production on Wed Mar 27 14:57:21 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Path used: Direct
Load completed - logical record count 10788.
Table IMSI.RECO_TEST_BAK:
10788 Rows successfully loaded.
Check the log file:
imsi.reco_test.log
for more information about the load.
|
정상적으로 load 됨
데이터 확인
|
1
2
3
4
5
6
|
$ sqlplus imsi/imsi
SQL> select count(*) from reco_test_bak;
COUNT(*)
----------
10788
|
데이터가 10788개 복구됨
테스트2. 해당 TS 데이터파일 + SYSTEM TS 데이터파일로 복구 시도
프로그램 실행 후 복구 진행
복구된 파일이 존재하는 경로로 이동
.truncate 파일, .ctl 파일 2개 다시 linux 로 복사
|
1
2
|
imsi.reco_test.dat.truncated
imsi.reco_test.ctl
|
복사 후 오라클 권한 부여
|
1
2
|
# chown oracle:dba imsi.reco_test.ctl
# chown oracle:dba imsi.reco_test.dat.truncated
|
오라클 유저 폴더로 이동 후 확인
|
1
2
3
4
5
6
7
8
9
|
# mv imsi.reco_test.* /home/oracle/reco2/
# su - oracle
$ cd reco2/
$ ls -al
합계 1348
drwxr-xr-x 2 oracle oinstall 68 3월 28 09:14 .
drwx------. 9 oracle oinstall 4096 3월 28 09:14 ..
-rw-r--r-- 1 oracle dba 302 3월 28 09:15 imsi.reco_test.ctl
-rw-r--r-- 1 oracle dba 1371177 3월 28 09:15 imsi.reco_test.dat.truncated
|
정상적으로 옮겨짐
imsi.reco_test.ctl 파일의 테이블 명 변경(덮어쓰기 방지)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
$ vi imsi.reco_test.ctl
기존
LOAD DATA
INFILE 'imsi.reco_test.dat.truncated'
APPEND
INTO TABLE imsi.reco_test
.
.
변경
LOAD DATA
INFILE 'imsi.reco_test.dat.truncated'
APPEND
INTO TABLE imsi.reco_test_bak2
.
.
|
reco_test_bak2 테이블 생성
|
1
2
3
|
SQL> create table reco_test_bak2 as select * from reco_test where 1=2;
Table created.
|
SQL Loader 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
$ sqlldr control=imsi.reco_test.ctl direct=y
Username:/ as sysdba <<< / as sysdba 입력
SQL*Loader: Release 19.0.0.0.0 - Production on Wed Mar 28 09:16:14 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Path used: Direct
Load completed - logical record count 10788.
Table IMSI.RECO_TEST_BAK2:
10788 Rows successfully loaded.
Check the log file:
imsi.reco_test.log
for more information about the load.
|
정상적으로 load 됨
데이터 확인
|
1
2
3
4
5
6
|
$ sqlplus imsi/imsi
SQL> select count(*) from reco_test_bak2;
COUNT(*)
----------
10788
|
데이터가 10788개 복구됨
테스트3. 해당 TS 데이터파일만으로 복구 시도
프로그램 실행 후 복구 진행
cat not find the system01.dbf! if you.... 메세지가 발생함
결론 :
이 DUL 툴이 정확히 어떤 방식으로 복구를 하는지 모르겠지만
system ts의 데이터파일에 있는 어떤 정보와 테이블이 존재하는 ts의 데이터파일의 정보로 데이터를 복구해 내는듯함
truncate 된 데이터는 기존의 백업 복구 방식으로는 복구할수 없는데 이렇게 복구를 할수있다는게 정말 신기한것 같음
그것도 DB를 기동한 상태가 아니라 datafile만 가지고 복구를 한다는 점은 매우 흥미로움
노아카이브 모드에서 소량의 데이터(1만개 미만)를 실수로 삭제한 경우 DUL을 사용해 복구하는 방법을 사용하면 될듯함
참조 :
https://www.parnassusdata.com/en
https://www.parnassusdata.com/en/node/269?language=zh-hans
https://zcdn.parnassusdata.com/ORACLE%20PRM-DUL%20data%20unloader%20tool%20manual%200.4.pdf
https://www.askmac.cn/archives/dul-oracle-data-unloader-%EB%8B%A4%EC%9A%B4%EB%A1%9C%EB%93%9C.html
http://m.todaysppc.com/renewal/view.php?id=free&no=217341
https://web.archive.org/web/20130815053626/http://www.fors.com/velpuri2/Backup%20and%20Recovery/UsingDUL
https://otsteam.tistory.com/162
https://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ#DUL
https://blog.naver.com/magicdba/100209195179
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 datapump 테스트 (0) | 2024.04.13 |
|---|---|
| 오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 백업복구(cold backup) 테스트 (0) | 2024.04.01 |
| 오라클 19c rman 백업 압축 알고리즘별 압축률 비교 (0) | 2024.01.18 |
| 오라클 19c rman 백업 이후 datafile이 추가된 경우 복구 상황 시나리오 (0) | 2023.12.21 |
| 오라클 19c rman backup set 방식과 image copy 방식 백업 크기 비교 (0) | 2023.12.21 |