오라클 datafile offline 후 online 테스트2

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c datafile offline 후 online 테스트2

테이블 스페이스를 생성하여 데이터를 넣고 checkpoint and log switch 후 

datafile을 offline 한 뒤 시간 간격을 두고 online 하는 테스트

테스트 내용 요약

0. 데이터 생성 후 checkpoint and log switch

1. datafile offline 직후 online 시도

2. datafile offline 후 여러번 log switch 후(약 1시간 뒤) online 시도

3. datafile offline 후 여러번 log switch 후(약 1시간 뒤) 아카이브 로그 삭제(이동) 후 online 시도

아카이브 로그모드 확인

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

아카이브 모드 설정이 되어있지 않다면 설정

오라클 아카이브 모드 설정 및 경로 설정 https://positivemh.tistory.com/147

테스트 전 체크포인트, 로그스위치(*테스트DB에만 사용할것*) [이 작업을 하지 않아도 테스트 가능함]

발생하는 아카이브 로그 사이즈가 제일 작은값(리두로그에 데이터 없음)이 될때까지 로그 스위치 실행

SQL> alter system checkpoint;
 
System altered.
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
$ cd /app/oracle/arch/
$ ls -al
total 484968
drwxr-xr-x   2 oracle dba      4096 Dec 23 13:13 .
drwxr-xr-x. 10 oracle dba      4096 Dec 12 00:30 ..
-rw-r-----   1 oracle dba 466583040 Dec 23 10:16 1_302_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 10:16 1_303_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 10:16 1_304_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 10:16 1_305_1048278181.arc
-rw-r-----   1 oracle dba  29986304 Dec 23 13:12 1_306_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:13 1_307_1048278181.arc
-rw-r-----   1 oracle dba      1536 Dec 23 13:13 1_308_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:13 1_309_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:13 1_310_1048278181.arc

발생한 아카이브 로그 삭제(*테스트DB에만 사용할것*) [이 작업을 하지 않아도 테스트 가능함]

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

모두 삭제됨

테스트용 테이블 스페이스 생성 및 확인

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

 

테스트용 테이블 생성 및 확인

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

현재 시간 확인

SQL> alter session set nls_date_format = 'YYYY/MM/DD hh24:mi:ss';
 
Session altered.
 
SQL> select sysdate from dual;
 
SYSDATE
-------------------
2020/12/23 13:15:38

각각의 테이블에 테스트용 데이터 삽입

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;
/

테이블 용량 확인

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 갯수 확인

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

체크포인트 후 로그 스위치 3번 실행(본인 redo log 갯수 만큼 실행)

SQL> alter system checkpoint;
 
System altered.
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.

아카이브 로그 확인

$ ls -al
total 364940
drwxr-xr-x   2 oracle dba        90 Dec 23 13:18 .
drwxr-xr-x. 10 oracle dba      4096 Dec 12 00:30 ..
-rw-r-----   1 oracle dba 373683200 Dec 23 13:18 1_311_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:18 1_312_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:18 1_313_1048278181.arc

테스트 데이터 파일 offline 후 확인

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 시도

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 시도

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.

데이터 파일 확인

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 됨

복구한 테이블 데이터와 그렇지 않은 테이블 데이터 확인

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시간 동안 중간중간 여러번 checkpoint, log switch 실행(각각 10분에 1번(checkpoint), 3번(log switch)씩 실행함)

SQL> alter system checkpoint;
 
System altered.
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
.
.

현재 시간 확인

SQL> alter session set nls_date_format = 'YYYY/MM/DD hh24:mi:ss';
 
Session altered.
 
SQL> select sysdate from dual;
 
SYSDATE
-------------------
2020/12/23 14:19:20

약 1시간이 지남

TEST_TS2 datafile online 시도

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 시도

SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts2.dbf';
ORA-00279: change 12363665 generated at 12/23/2020 13:18:51 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_314_1048278181.arc
ORA-00280: change 12363665 for thread 1 is in sequence #314
 
 
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 시 어떤 아카이브까지 적용시킬 것인지 물어봄 이때 엔터 입력

데이터 파일 확인

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 됨

복구한 테이블 데이터와 그렇지 않은 테이블 데이터 확인

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시간 동안 중간중간 여러번 checkpoint, log switch 실행(각각 10분에 1번(checkpoint), 3번(log switch)씩 실행함)

(2번과 동일한 시간대)

SQL> alter system checkpoint;
 
System altered.
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
.
.

현재 시간 확인

SQL> alter session set nls_date_format = 'YYYY/MM/DD hh24:mi:ss';
 
Session altered.
 
SQL> select sysdate from dual;
 
SYSDATE
-------------------
2020/12/23 14:19:20

약 1시간이 지남

현재 아카이브로그 확인

$ ls -al
total 369516
drwxr-xr-x   2 oracle dba      4096 Dec 23 14:19 .
drwxr-xr-x. 10 oracle dba      4096 Dec 12 00:30 ..
-rw-r-----   1 oracle dba 373683200 Dec 23 13:18 1_311_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:18 1_312_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:18 1_313_1048278181.arc
-rw-r-----   1 oracle dba    103424 Dec 23 13:25 1_314_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:25 1_315_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:25 1_316_1048278181.arc
-rw-r-----   1 oracle dba    205824 Dec 23 13:35 1_317_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:35 1_318_1048278181.arc
-rw-r-----   1 oracle dba      2048 Dec 23 13:35 1_319_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:35 1_320_1048278181.arc
-rw-r-----   1 oracle dba    115712 Dec 23 13:46 1_321_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:46 1_322_1048278181.arc
-rw-r-----   1 oracle dba      2048 Dec 23 13:46 1_323_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 13:46 1_324_1048278181.arc
-rw-r-----   1 oracle dba   3964416 Dec 23 14:08 1_325_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 14:08 1_326_1048278181.arc
-rw-r-----   1 oracle dba      2048 Dec 23 14:08 1_327_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 14:08 1_328_1048278181.arc
-rw-r-----   1 oracle dba    222720 Dec 23 14:19 1_329_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 14:19 1_330_1048278181.arc
-rw-r-----   1 oracle dba      1536 Dec 23 14:19 1_331_1048278181.arc
-rw-r-----   1 oracle dba      1024 Dec 23 14:19 1_332_1048278181.arc

데이터 변경 직후 내려쓴 아카이브 로그를 다른 곳(/home/oracle)으로 이동(삭제)

$ mv 1_314_1048278181.arc /home/oracle/
$ ls -al /home/oracle/*.arc
-rw-r----- 1 oracle dba 103424 Dec 23 13:25 /home/oracle/1_314_1048278181.arc

TEST_TS3 datafile online 시도

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 시도

SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf';
ORA-00279: change 12363665 generated at 12/23/2020 13:18:51 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_314_1048278181.arc
ORA-00280: change 12363665 for thread 1 is in sequence #314
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
[엔터 입력]
ORA-00308: cannot open archived log '/app/oracle/arch/1_314_1048278181.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

recover 시 필요한 아카이브로그를 찾을수 없음(sequence 314 아카이브로그)

recover datafile auto 시도

SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf';
ORA-00279: change 12363665 generated at 12/23/2020 13:18:51 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_314_1048278181.arc
ORA-00280: change 12363665 for thread 1 is in sequence #314
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto [auto 입력]
ORA-00308: cannot open archived log '/app/oracle/arch/1_314_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_314_1048278181.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

recover 시 필요한 아카이브로그를 찾을수 없음(sequence 314 아카이브로그)

recover datafile cancel 시도

SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf';
ORA-00279: change 12363665 generated at 12/23/2020 13:18:51 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_314_1048278181.arc
ORA-00280: change 12363665 for thread 1 is in sequence #314
 
 
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 입력 시도

SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf';
ORA-00279: change 12363665 generated at 12/23/2020 13:18:51 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_314_1048278181.arc
ORA-00280: change 12363665 for thread 1 is in sequence #314
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/oradata/ORCL19/redo01.log [redo 1번 입력]
ORA-00310: archived log contains sequence 333; sequence 314 required
ORA-00334: archived log: '/app/oracle/oradata/ORCL19/redo01.log'
 
 
SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf';
ORA-00279: change 12363665 generated at 12/23/2020 13:18:51 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_314_1048278181.arc
ORA-00280: change 12363665 for thread 1 is in sequence #314
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/oradata/ORCL19/redo02.log [redo 2번 입력]
ORA-00310: archived log contains sequence 331; sequence 314 required
ORA-00334: archived log: '/app/oracle/oradata/ORCL19/redo02.log'
 
 
SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf';
ORA-00279: change 12363665 generated at 12/23/2020 13:18:51 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_314_1048278181.arc
ORA-00280: change 12363665 for thread 1 is in sequence #314
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/oradata/ORCL19/redo03.log [redo 3번 입력]
ORA-00310: archived log contains sequence 332; sequence 314 required
ORA-00334: archived log: '/app/oracle/oradata/ORCL19/redo03.log

현재 redo log의 sequence 와 recover에 필요한 sequence 번호가 맞지 않아 복구가 안됨

아카이브 로그 복원

$ mv /home/oracle/1_314_1048278181.arc /app/oracle/arch/
$ ls -al /app/oracle/arch/1_314_1048278181.arc 
-rw-r----- 1 oracle dba 103424 Dec 23 13:25 /app/oracle/arch/1_314_1048278181.arc

recover datafile 재시도

SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf';
ORA-00279: change 12363665 generated at 12/23/2020 13:18:51 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_314_1048278181.arc
ORA-00280: change 12363665 for thread 1 is in sequence #314
 
 
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.

정상적으로 복구됨

데이터 파일 확인

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 됨

복구한 테이블 데이터 확인

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됨

SQL> recover datafile '/app/oracle/oradata/ORCL19/test_ts3.dbf';
ORA-00279: change 12363665 generated at 12/23/2020 13:18:51 needed for thread 1
ORA-00289: suggestion : /app/oracle/arch/1_314_1048278181.arc
ORA-00280: change 12363665 for thread 1 is in sequence #314
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/1_314_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 332) 가 아닌 

데이터파일을 offline 한 직후 log switch 후 떨어진아카이브 로그(sequence 314) 가 있어야 복구가 가능함

또한 이 아카이브 로그가 없는 경우 일반적인 방법으로는 복구(recover 및 online) 이 불가능함

(offline 전 후 6개 정도의 아카이브로그를 보관 또는 백업서버에서 가져오면 복원이 가능함)

오라클 datafile offline 후 online 테스트1 https://positivemh.tistory.com/679 와 동일한 결과가 나옴

checkpoint 입력 유무는 offline 된 데이터파일을 online 할 시 영향이 없음

참조 : https://positivemh.tistory.com/679

https://positivemh.tistory.com/242

https://positivemh.tistory.com/147

https://positivemh.tistory.com/136

https://docs.oracle.com/html/E25494_01/dfiles004.htm

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-data-files-and-temp-files.html#GUID-D82767AC-3AAC-40D2-9E29-9D01AFED9D6A

 

참조 :