내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c datafile offline 후 online 테스트
테이블 스페이스를 생성하여 데이터를 넣고 log switch 후 datafile을 offline 한 뒤 시간 간격을 두고 online 하는 테스트
테스트 내용 요약
0. 데이터 생성 후 log switch
1. datafile offline 직후 online 시도
2. datafile offline 후 여러번 log switch 후(약 1시간 뒤) online 시도
3. datafile offline 후 여러번 log switch 후(약 1시간 뒤) 아카이브 로그 삭제(이동) 후 online 시도
아카이브 로그모드 확인
1 2 3 4 5 6 7 | SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /app/oracle/arch Oldest online log sequence 221 Next log sequence to archive 223 Current log sequence 223 |
아카이브 모드 설정이 되어있지 않다면 설정
오라클 아카이브 모드 설정 및 경로 설정 https://positivemh.tistory.com/147
테스트 전 로그스위치(*테스트DB에만 사용할것*) [이 작업을 하지 않아도 테스트 가능함]
발생하는 아카이브 로그 사이즈가 제일 작은값(리두로그에 데이터 없음)이 될때까지 로그 스위치 실행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> alter system switch logfile; / / / / / / $ cd /app/oracle/arch $ ls -al total 222232 drwxr-xr-x 2 oracle dba 4096 Dec 21 19:20 . drwxr-xr-x. 10 oracle dba 4096 Dec 12 00:30 .. -rw-r----- 1 oracle dba 227513344 Dec 21 19:19 1_224_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 19:19 1_225_1048278181.arc -rw-r----- 1 oracle dba 20480 Dec 21 19:19 1_226_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 19:19 1_227_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 19:19 1_228_1048278181.arc -rw-r----- 1 oracle dba 1536 Dec 21 19:20 1_229_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 19:20 1_230_1048278181.arc |
발생한 아카이브 로그 삭제(*테스트DB에만 사용할것*) [이 작업을 하지 않아도 테스트 가능함]
1 2 3 4 5 6 7 8 | $ rman target / RMAN> delete noprompt archivelog all; $ cd /app/oracle/arch $ ls -al total 4 drwxr-xr-x 2 oracle dba 6 Dec 21 19:24 . drwxr-xr-x. 10 oracle dba 4096 Dec 12 00:30 .. |
모두 삭제됨
테스트용 테이블 스페이스 생성 및 확인
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> create tablespace test_ts1 datafile '/app/oracle/oradata/ORCL19/test_ts1.dbf' size 100m; Tablespace created. SQL> create tablespace test_ts2 datafile '/app/oracle/oradata/ORCL19/test_ts2.dbf' size 100m; Tablespace created. SQL> create tablespace test_ts3 datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf' size 100m; Tablespace created. SQL> set lines 200 pages 1000 col file_name for a50 col tablespace_name for a10 select tablespace_name, file_id, file_name, bytes/1024/1024 mb, online_status from dba_data_files where tablespace_name like 'TEST_TS%' order by 2; TABLESPACE FILE_ID FILE_NAME MB ONLINE_ ---------- ---------- -------------------------------------------------- ---------- ------- TEST_TS1 6 /app/oracle/oradata/ORCL19/test_ts1.dbf 100 ONLINE TEST_TS2 7 /app/oracle/oradata/ORCL19/test_ts2.dbf 100 ONLINE TEST_TS3 8 /app/oracle/oradata/ORCL19/test_ts3.dbf 100 ONLINE |
테스트용 테이블 생성 및 확인
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 | SQL> create table ts_table1(cola varchar2(20), colb number, colc number, cold varchar2(30), cole varchar2(30), colf varchar2(30), colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30)) tablespace test_ts1; Table created. SQL> create table ts_table2(cola varchar2(20), colb number, colc number, cold varchar2(30), cole varchar2(30), colf varchar2(30), colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30)) tablespace test_ts2; Table created. SQL> create table ts_table3(cola varchar2(20), colb number, colc number, cold varchar2(30), cole varchar2(30), colf varchar2(30), colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30)) tablespace test_ts3; Table created. SQL> select table_name, tablespace_name from dba_tables where table_name like 'TS_TABLE%' order by 1; TABLE_NAME TABLESPACE_NAME ----------- ---------- TS_TABLE1 TEST_TS1 TS_TABLE2 TEST_TS2 TS_TABLE3 TEST_TS3 |
현재 시간 확인
1 2 3 4 5 6 7 8 9 | SQL> alter session set nls_date_format = 'YYYY/MM/DD hh24:mi:ss'; Session altered. SQL> select sysdate from dual; SYSDATE ------------------- 2020/12/21 20:58:22 |
각각의 테이블에 테스트용 데이터 삽입
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | SQL> declare type tbl_ins is table of ts_table1%rowtype index by binary_integer; w_ins tbl_ins; begin for i in 1..600000 loop w_ins(i).cola :=i; w_ins(i).colb :=300000; w_ins(i).colc :=99; w_ins(i).cold :='abc'||dbms_random.string('x',10); w_ins(i).cole :='eeeeeeeeeeeeeeee'; w_ins(i).colf :='ffffffffffffffff'; w_ins(i).colg :=9999999; w_ins(i).colh :='hhhhhhhhhhhhhhhhhhhhhhhhhh'; w_ins(i).coli :='iiiiiiiiiiiiiiiiiiiiiiiiii'; end loop; forall i in 1..600000 insert into ts_table1 values w_ins(i); commit; end; / SQL> declare type tbl_ins is table of ts_table2%rowtype index by binary_integer; w_ins tbl_ins; begin for i in 1..600000 loop w_ins(i).cola :=i; w_ins(i).colb :=300000; w_ins(i).colc :=99; w_ins(i).cold :='abc'||dbms_random.string('x',10); w_ins(i).cole :='eeeeeeeeeeeeeeee'; w_ins(i).colf :='ffffffffffffffff'; w_ins(i).colg :=9999999; w_ins(i).colh :='hhhhhhhhhhhhhhhhhhhhhhhhhh'; w_ins(i).coli :='iiiiiiiiiiiiiiiiiiiiiiiiii'; end loop; forall i in 1..600000 insert into ts_table2 values w_ins(i); commit; end; / SQL> declare type tbl_ins is table of ts_table3%rowtype index by binary_integer; w_ins tbl_ins; begin for i in 1..600000 loop w_ins(i).cola :=i; w_ins(i).colb :=300000; w_ins(i).colc :=99; w_ins(i).cold :='abc'||dbms_random.string('x',10); w_ins(i).cole :='eeeeeeeeeeeeeeee'; w_ins(i).colf :='ffffffffffffffff'; w_ins(i).colg :=9999999; w_ins(i).colh :='hhhhhhhhhhhhhhhhhhhhhhhhhh'; w_ins(i).coli :='iiiiiiiiiiiiiiiiiiiiiiiiii'; end loop; forall i in 1..600000 insert into ts_table3 values w_ins(i); commit; end; / |
테이블 용량 확인
1 2 3 4 5 6 7 8 9 10 11 | SQL> col segment_name for a10 select segment_name, bytes/1024/1024 mb from dba_segments where segment_name like 'TS_TABLE%' order by 1; SEGMENT_NA MB ---------- ---------- TS_TABLE1 88 TS_TABLE2 88 TS_TABLE3 88 |
각각의 테이블에 88MB의 데이터가 쌓임
redo log 갯수 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> set lines 200 pages 1000 col member for a70 col status for a10 select l.group#, member, archived, l.status, (bytes/1024/1024) MB from v$log l, v$logfile f where f.group# = l.group# order by 1; GROUP# MEMBER ARC STATUS MB ---------- ---------------------------------------------------------------------- --- ---------- ---------- 1 /app/oracle/oradata/ORCL19/redo01.log YES ACTIVE 1024 2 /app/oracle/oradata/ORCL19/redo02.log YES ACTIVE 1024 3 /app/oracle/oradata/ORCL19/redo03.log NO CURRENT 1024 |
로그 스위치 6번 실행(본인 redo log 갯수*2 만큼 실행)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. |
아카이브 로그 확인
1 2 3 4 5 6 7 8 9 10 | $ ls -al total 262576 drwxr-xr-x 2 oracle dba 4096 Dec 21 20:59 . drwxr-xr-x. 10 oracle dba 4096 Dec 12 00:30 .. -rw-r----- 1 oracle dba 268151808 Dec 21 20:59 1_272_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_273_1048278181.arc -rw-r----- 1 oracle dba 697856 Dec 21 20:59 1_274_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_275_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_276_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_277_1048278181.arc |
테스트 데이터 파일 offline 후 확인
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> alter database datafile '/app/oracle/oradata/ORCL19/test_ts1.dbf' offline; Database altered. SQL> alter database datafile '/app/oracle/oradata/ORCL19/test_ts2.dbf' offline; Database altered. SQL> alter database datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf' offline; Database altered. SQL> set lines 200 pages 1000 col file_name for a50 col tablespace_name for a10 select tablespace_name, file_id, file_name, bytes/1024/1024 mb, online_status from dba_data_files where tablespace_name like 'TEST_TS%' order by 2; TABLESPACE FILE_ID FILE_NAME MB ONLINE_ ---------- ---------- -------------------------------------------------- ---------- ------- TEST_TS1 6 /app/oracle/oradata/ORCL19/test_ts1.dbf RECOVER TEST_TS2 7 /app/oracle/oradata/ORCL19/test_ts2.dbf RECOVER TEST_TS3 8 /app/oracle/oradata/ORCL19/test_ts3.dbf RECOVER |
TEST_TS1, 2, 3 데이터파일모두 offline 후 recover 상태가됨
1. TEST_TS1 datafile offline 직후 online 시도
1 2 3 4 5 6 | SQL> alter database datafile '/app/oracle/oradata/ORCL19/test_ts1.dbf' online; alter database datafile '/app/oracle/oradata/ORCL19/test_ts1.dbf' online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '/app/oracle/oradata/ORCL19/test_ts1.dbf' |
recover 작업이 필요함
recover datafile 후 online 시도
1 2 3 4 5 6 | SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts1.dbf'; Media recovery complete. SQL> alter database datafile '/app/oracle/oradata/ORCL19/test_ts1.dbf' online; Database altered. |
데이터 파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a50 col tablespace_name for a10 select tablespace_name, file_id, file_name, bytes/1024/1024 mb, online_status from dba_data_files where tablespace_name like 'TEST_TS%' order by 2; TABLESPACE FILE_ID FILE_NAME MB ONLINE_ ---------- ---------- -------------------------------------------------- ---------- ------- TEST_TS1 6 /app/oracle/oradata/ORCL19/test_ts1.dbf 100 ONLINE TEST_TS2 7 /app/oracle/oradata/ORCL19/test_ts2.dbf RECOVER TEST_TS3 8 /app/oracle/oradata/ORCL19/test_ts3.dbf RECOVER |
TEST_TS1의 데이터파일이 정상적으로 online 됨
복구한 테이블 데이터와 그렇지 않은 테이블 데이터 확인
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> select * from ts_table1 where rownum <= 1; COLA COLB COLC COLD -------------------- ---------- ---------- ------------------------------ COLE COLF COLG ------------------------------ ------------------------------ ---------- COLH COLI ------------------------------ ------------------------------ COLJ ------------------------------ 60 300000 99 abcD92NM9GDRU eeeeeeeeeeeeeeee ffffffffffffffff 9999999 hhhhhhhhhhhhhhhhhhhhhhhhhh iiiiiiiiiiiiiiiiiiiiiiiiii SQL> select * from ts_table2 where rownum <= 1; select * from ts_table2 where rownum <= 1; * ERROR at line 1: ORA-00376: file 7 cannot be read at this time ORA-01110: data file 7: '/app/oracle/oradata/ORCL19/test_ts2.dbf' SQL> select * from ts_table3 where rownum <= 1; select * from ts_table3 where rownum <= 1 * ERROR at line 1: ORA-00376: file 8 cannot be read at this time ORA-01110: data file 8: '/app/oracle/oradata/ORCL19/test_ts3.dbf' |
ts_table1은 정상적으로 조회가 되지만
ts_table2, ts_table3은 조회가 되지않고 ORA-00376, ORA-01110 메세지가 발생함
2. TEST_TS2 datafile offline 후 여러번 log switch 후(약 1시간 뒤) online 시도
1시간 동안 중간중간 여러번 log switch 실행(10분 마다 4번씩 실행함)
1 2 3 4 5 6 7 8 9 10 11 | SQL> alter system switch logfile; / / / / / / / . . |
현재 시간 확인
1 2 3 4 5 6 7 8 9 | SQL> alter session set nls_date_format = 'YYYY/MM/DD hh24:mi:ss'; Session altered. SQL> select sysdate from dual; SYSDATE ------------------- 2020/12/21 22:01:22 |
약 1시간이 지남
TEST_TS2 datafile online 시도
1 2 3 4 5 6 | SQL> alter database datafile '/app/oracle/oradata/ORCL19/test_ts2.dbf' online; alter database datafile '/app/oracle/oradata/ORCL19/test_ts2.dbf' online * ERROR at line 1: ORA-01113: file 7 needs media recovery ORA-01110: data file 7: '/app/oracle/oradata/ORCL19/test_ts2.dbf' |
recover 작업이 필요함
recover datafile 후 online 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts2.dbf'; ORA-00279: change 12216879 generated at 12/21/2020 20:59:43 needed for thread 1 ORA-00289: suggestion : /app/oracle/arch/1_278_1048278181.arc ORA-00280: change 12216879 for thread 1 is in sequence #278 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} [엔터 입력] Log applied. Media recovery complete. SQL> alter database datafile '/app/oracle/oradata/ORCL19/test_ts2.dbf' online; Database altered. |
recover 시 어떤 아카이브까지 적용시킬 것인지 물어봄 이때 엔터 입력
데이터 파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a50 col tablespace_name for a10 select tablespace_name, file_id, file_name, bytes/1024/1024 mb, online_status from dba_data_files where tablespace_name like 'TEST_TS%' order by 2; TABLESPACE FILE_ID FILE_NAME MB ONLINE_ ---------- ---------- -------------------------------------------------- ---------- ------- TEST_TS1 6 /app/oracle/oradata/ORCL19/test_ts1.dbf 100 ONLINE TEST_TS2 7 /app/oracle/oradata/ORCL19/test_ts2.dbf 100 ONLINE TEST_TS3 8 /app/oracle/oradata/ORCL19/test_ts3.dbf RECOVER |
TEST_TS2의 데이터파일이 정상적으로 online 됨
복구한 테이블 데이터와 그렇지 않은 테이블 데이터 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> select * from ts_table2 where rownum <= 1; COLA COLB COLC COLD -------------------- ---------- ---------- ------------------------------ COLE COLF COLG ------------------------------ ------------------------------ ---------- COLH COLI ------------------------------ ------------------------------ COLJ ------------------------------ 60 300000 99 abc2NI33HVRNG eeeeeeeeeeeeeeee ffffffffffffffff 9999999 hhhhhhhhhhhhhhhhhhhhhhhhhh iiiiiiiiiiiiiiiiiiiiiiiiii SQL> select * from ts_table3 where rownum <= 1; select * from ts_table3 where rownum <= 1 * ERROR at line 1: ORA-00376: file 8 cannot be read at this time ORA-01110: data file 8: '/app/oracle/oradata/ORCL19/test_ts3.dbf' |
ts_table2은 정상적으로 조회가 되지만
ts_table3은 조회가 되지않고 ORA-00376, ORA-01110 메세지가 발생함
3. TEST_TS3 datafile offline 후 여러번 log switch 후(약 1시간 뒤) 아카이브 로그 삭제(이동) 후 online 시도
1시간 동안 중간중간 여러번 log switch 실행(2번 테스트와 같은 시간대)(10분 마다 4번씩 실행함)
1 2 3 4 5 6 7 8 9 10 11 | SQL> alter system switch logfile; / / / / / / / . . |
현재 시간 확인
1 2 3 4 5 6 7 8 9 | SQL> alter session set nls_date_format = 'YYYY/MM/DD hh24:mi:ss'; Session altered. SQL> select sysdate from dual; SYSDATE ------------------- 2020/12/21 22:01:22 |
약 1시간이 지남
현재 아카이브로그 확인
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 33 34 | $ ls -al total 310244 drwxr-xr-x 2 oracle dba 4096 Dec 21 22:00 . drwxr-xr-x. 10 oracle dba 4096 Dec 12 00:30 .. -rw-r----- 1 oracle dba 268151808 Dec 21 20:59 1_272_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_273_1048278181.arc -rw-r----- 1 oracle dba 697856 Dec 21 20:59 1_274_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_275_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_276_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_277_1048278181.arc -rw-r----- 1 oracle dba 4139008 Dec 21 21:09 1_278_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:09 1_279_1048278181.arc -rw-r----- 1 oracle dba 3072 Dec 21 21:09 1_280_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:09 1_281_1048278181.arc -rw-r----- 1 oracle dba 100864 Dec 21 21:22 1_282_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:22 1_283_1048278181.arc -rw-r----- 1 oracle dba 2048 Dec 21 21:22 1_284_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:22 1_285_1048278181.arc -rw-r----- 1 oracle dba 74240 Dec 21 21:32 1_286_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:32 1_287_1048278181.arc -rw-r----- 1 oracle dba 1536 Dec 21 21:32 1_288_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:32 1_289_1048278181.arc -rw-r----- 1 oracle dba 97792 Dec 21 21:42 1_290_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:42 1_291_1048278181.arc -rw-r----- 1 oracle dba 1536 Dec 21 21:42 1_292_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:42 1_293_1048278181.arc -rw-r----- 1 oracle dba 195072 Dec 21 21:51 1_294_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:51 1_295_1048278181.arc -rw-r----- 1 oracle dba 7680 Dec 21 21:51 1_296_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:51 1_297_1048278181.arc -rw-r----- 1 oracle dba 37086208 Dec 21 22:00 1_298_1048278181.arc -rw-r----- 1 oracle dba 10752 Dec 21 22:00 1_299_1048278181.arc -rw-r----- 1 oracle dba 3786240 Dec 21 22:00 1_300_1048278181.arc -rw-r----- 1 oracle dba 3238912 Dec 21 22:00 1_301_1048278181.arc |
데이터 변경시점(부터 몇분뒤까지)에 내려쓴 아카이브로그를 다른 곳(/home/oracle)으로 이동(삭제)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | $ cd /app/oracle/arch/ $ ls -al 1_27*.arc 1_28*.arc -rw-r----- 1 oracle dba 268151808 Dec 21 20:59 1_272_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_273_1048278181.arc -rw-r----- 1 oracle dba 697856 Dec 21 20:59 1_274_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_275_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_276_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 20:59 1_277_1048278181.arc -rw-r----- 1 oracle dba 4139008 Dec 21 21:09 1_278_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:09 1_279_1048278181.arc -rw-r----- 1 oracle dba 3072 Dec 21 21:09 1_280_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:09 1_281_1048278181.arc -rw-r----- 1 oracle dba 100864 Dec 21 21:22 1_282_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:22 1_283_1048278181.arc -rw-r----- 1 oracle dba 2048 Dec 21 21:22 1_284_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:22 1_285_1048278181.arc -rw-r----- 1 oracle dba 74240 Dec 21 21:32 1_286_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:32 1_287_1048278181.arc -rw-r----- 1 oracle dba 1536 Dec 21 21:32 1_288_1048278181.arc -rw-r----- 1 oracle dba 1024 Dec 21 21:32 1_289_1048278181.arc $ mv 1_27*.arc 1_28*.arc /home/oracle/ $ ls -al 1_27* 1_28* ls: cannot access 1_27*: No such file or directory ls: cannot access 1_28*: No such file or directory |
TEST_TS3 datafile online 시도
1 2 3 4 5 6 | SQL> alter database datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf' online; alter database datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf' online * ERROR at line 1: ORA-01113: file 8 needs media recovery ORA-01110: data file 8: '/app/oracle/oradata/ORCL19/test_ts3.dbf' |
recover 작업이 필요함
recover datafile 시도
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf'; ORA-00279: change 12216879 generated at 12/21/2020 20:59:43 needed for thread 1 ORA-00289: suggestion : /app/oracle/arch/1_278_1048278181.arc ORA-00280: change 12216879 for thread 1 is in sequence #278 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} [엔터 입력] ORA-00308: cannot open archived log '/app/oracle/arch/1_278_1048278181.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 |
recover 시 필요한 아카이브로그를 찾을수 없음(sequence 278 아카이브로그)
recover datafile auto 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf'; ORA-00279: change 12216879 generated at 12/21/2020 20:59:43 needed for thread 1 ORA-00289: suggestion : /app/oracle/arch/1_278_1048278181.arc ORA-00280: change 12216879 for thread 1 is in sequence #278 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto [auto 입력] ORA-00308: cannot open archived log '/app/oracle/arch/1_278_1048278181.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 ORA-00308: cannot open archived log '/app/oracle/arch/1_278_1048278181.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 |
recover 시 필요한 아카이브로그를 찾을수 없음(sequence 278 아카이브로그)
recover datafile cancel 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf'; ORA-00279: change 12216879 generated at 12/21/2020 20:59:43 needed for thread 1 ORA-00289: suggestion : /app/oracle/arch/1_278_1048278181.arc ORA-00280: change 12216879 for thread 1 is in sequence #278 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel [cancel 입력] Media recovery cancelled. SQL> alter database datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf' online; alter database datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf' online * ERROR at line 1: ORA-01113: file 8 needs media recovery ORA-01110: data file 8: '/app/oracle/oradata/ORCL19/test_ts3.dbf' |
cancel은 말 그대로 복구작업을 취소하는 것이기 때문에 복구가 되지 않음
recover datafile 현재 redo 입력 시도
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 33 | SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf'; ORA-00279: change 12216879 generated at 12/21/2020 20:59:43 needed for thread 1 ORA-00289: suggestion : /app/oracle/arch/1_278_1048278181.arc ORA-00280: change 12216879 for thread 1 is in sequence #278 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /app/oracle/oradata/ORCL19/redo01.log [redo 1번 입력] ORA-00310: archived log contains sequence 301; sequence 278 required ORA-00334: archived log: '/app/oracle/oradata/ORCL19/redo01.log' SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf'; ORA-00279: change 12216879 generated at 12/21/2020 20:59:43 needed for thread 1 ORA-00289: suggestion : /app/oracle/arch/1_278_1048278181.arc ORA-00280: change 12216879 for thread 1 is in sequence #278 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /app/oracle/oradata/ORCL19/redo02.log [redo 2번 입력] ORA-00310: archived log contains sequence 302; sequence 278 required ORA-00334: archived log: '/app/oracle/oradata/ORCL19/redo02.log' SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf'; ORA-00279: change 12216879 generated at 12/21/2020 20:59:43 needed for thread 1 ORA-00289: suggestion : /app/oracle/arch/1_278_1048278181.arc ORA-00280: change 12216879 for thread 1 is in sequence #278 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /app/oracle/oradata/ORCL19/redo03.log [redo 3번 입력] ORA-00310: archived log contains sequence 300; sequence 278 required ORA-00334: archived log: '/app/oracle/oradata/ORCL19/redo03.log' |
현재 redo log의 sequence 와 recover에 필요한 sequence 번호가 맞지 않아 복구가 안됨
아카이브 로그 복원
1 2 3 4 | $ cd /home/oracle/ $ mv 1_278_1048278181.arc /app/oracle/arch/ $ ls -al /app/oracle/arch/1_278* -rw-r----- 1 oracle dba 4139008 Dec 21 21:09 /app/oracle/arch/1_278_1048278181.arc |
recover datafile 재시도
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf'; ORA-00279: change 12216879 generated at 12/21/2020 20:59:43 needed for thread 1 ORA-00289: suggestion : /app/oracle/arch/1_278_1048278181.arc ORA-00280: change 12216879 for thread 1 is in sequence #278 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} [엔터 입력] Log applied. Media recovery complete. SQL> alter database datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf' online; Database altered. |
정상적으로 복구됨
데이터 파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a50 col tablespace_name for a10 select tablespace_name, file_id, file_name, bytes/1024/1024 mb, online_status from dba_data_files where tablespace_name like 'TEST_TS%' order by 2; TABLESPACE FILE_ID FILE_NAME MB ONLINE_ ---------- ---------- -------------------------------------------------- ---------- ------- TEST_TS1 6 /app/oracle/oradata/ORCL19/test_ts1.dbf 100 ONLINE TEST_TS2 7 /app/oracle/oradata/ORCL19/test_ts2.dbf 100 ONLINE TEST_TS3 8 /app/oracle/oradata/ORCL19/test_ts3.dbf 100 ONLINE |
TEST_TS3의 데이터파일이 정상적으로 online 됨
복구한 테이블 데이터 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> select * from ts_table3 where rownum <= 1; COLA COLB COLC COLD -------------------- ---------- ---------- ------------------------------ COLE COLF COLG ------------------------------ ------------------------------ ---------- COLH COLI ------------------------------ ------------------------------ COLJ ------------------------------ 60 300000 99 abcW5GI1YKK4T eeeeeeeeeeeeeeee ffffffffffffffff 9999999 hhhhhhhhhhhhhhhhhhhhhhhhhh iiiiiiiiiiiiiiiiiiiiiiiiii |
데이터가 정상적으로 조회됨
*추가
아래와 같이 아카이브로그가 다른 경로에 있다면
아카이브로그가 있는 경로 전체를 적어주면 똑같이 Log applied됨
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf'; ORA-00279: change 12216879 generated at 12/21/2020 20:59:43 needed for thread 1 ORA-00289: suggestion : /app/oracle/arch/1_278_1048278181.arc ORA-00280: change 12216879 for thread 1 is in sequence #278 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /home/oracle/1_278_1048278181.arc [/home/oracle 경로로 이동한 아카이브파일 입력] Log applied. Media recovery complete. SQL> alter database datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf' online; Database altered. |
결론 : 데이터파일을 offline 한 경우 현재 시점의 아카이브 로그(sequence 300) 가 아닌
데이터파일을 offline 한 직후 log switch 후 떨어진 아카이브 로그(sequence 278) 가 있어야 복구가 가능함
또한 이 아카이브 로그가 없는 경우 일반적인 방법으로는 복구(recover 및 online) 이 불가능함
(offline 전 후 6개 정도의 아카이브로그를 보관 또는 백업서버에서 가져오면 복원이 가능함)
참조 : https://positivemh.tistory.com/242
https://positivemh.tistory.com/147
https://positivemh.tistory.com/136'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 11g R2 begin backup 후 end backup 을 하지 않고 복구(샘플데이터 생성 후 파일 복사) (0) | 2021.01.04 |
|---|---|
| 오라클 datafile offline 후 online 테스트2 (0) | 2020.12.23 |
| 오라클 11g R2 엔진 백업 복구 테스트 (8) | 2020.11.27 |
| 오라클 9i R2 block corruption 복구 테스트 (0) | 2020.08.26 |
| rman 백업 스크립트(Windows Server) (3) | 2020.07.26 |