[Oracle] oracle datafile offline 후 online 방법

이번 포스트에서는 datafile을 offline후 online하는 방법에 대해서 알아보겠습니다.

 

만약 datafile을 offline을 하였을 경우 해당 datafile에 데이터가 있고,

그 테이블을 select count(*)을 한다면 아래와 같이 애러가 뜰것입니다.

 

ORA-00376: 현재 파일 23를 읽을 수 없습니다
ORA-01110: 23 데이터 파일: 'C:\APP\ORADATA\ORCL\.DBF'

 

일단 no archive log mode에서는 datafile을 offline할 수 없습니다. 만약 offline을 하게 될 경우에 아래와 같은 애러 메시지가 나올 것입니다.

ORA-01145: 매체 복구가 사용으로 설정되지 않으면 즉시 오프라인은 허용되지 않습니다.

 

따라서 아래 포스트에서 데이터베이스를 archive log mode로 변경하신 후에 작업을 진행하여야 합니다.

2024.01.15 - [DB (데이터베이스)/Oracle (오라클)] - [Oracle] oracle database archive log mode 변경 방법

[Oracle] oracle database archive log mode 변경 방법

 

이제 본격적으로 datafile의 offline -> online방법에 대해서 알아보겠습니다.

 

1. 테스트 환경 구축

1-1. 테이블 스페이스 생성

CREATE TABLESPACE TEST DATAFILE 'C:\APP\ORADATA\ORCL\TEST_01.DBF' SIZE 10M autoextend OFF;

ALTER TABLESPACE TEST ADD DATAFILE 'C:\APP\ORADATA\ORCL\TEST_02.DBF' SIZE 10M autoextend OFF;
ALTER TABLESPACE TEST ADD DATAFILE 'C:\APP\ORADATA\ORCL\TEST_03.DBF' SIZE 10M autoextend OFF;

 

이렇게 위와 같이 'TEST'라는 테이블스페이스를 생성하고 10M 크기의 datafile을 3개 생성합니다.

 

SELECT file_name, tablespace_name,  online_status
FROM dba_data_files
WHERE tablespace_name = 'TEST';

 

생성이 완료된 datafile은 dba_data_files를 통해 조회가 가능합니다.

 

 

1-2. 테이블 생성

CREATE TABLE A (name varchar(100), value varchar(100)) TABLESPACE TEST;

 

위와 같이 A라는 테이블을 생성하는데 이 테이블의 테이블스페이스를 TEST로 지정하여 생성합니다.

 

SELECT owner, table_name, tablespace_name, status, logging
FROM dba_tables
WHERE tablespace_name = 'TEST';

 

dba_table를 통해 tablespace가 TEST인 테이블을 조회할 수 있습니다.

 

 

 

1-3. test데이터 입력

BEGIN
	
	FOR i IN 1..1000
	LOOP
		INSERT INTO A VALUES('name' || i, 'value' || i);
								
		COMMIT;
	END LOOP;
END;

위와 같이 pl/sql을 작성하여 1000개의 데이터를 입력했습니다.

 

SELECT 
	a.file_name, 
	a.tablespace_name, 
	a.bytes/1024/1024 AS total_mb,
	b.bytes/1024/1024 AS free_mb,
	online_status
FROM DBA_DATA_FILES a, dba_free_space b
WHERE a.tablespace_name = b.TABLESPACE_NAME
AND a.file_id = b.file_id
AND a.tablespace_name = 'TEST'
ORDER BY 1;

위 쿼리를 통해 어떤 데이터파일에 데이터가 쌓이는지 확인 할 수 있습니다.

이렇게 TEST_01 datafile에 데이터가 쌓이는걸 확인 할 수 있습니다.

 

 

1-4. datafile offline [데이터가 있는 경우 / 데이터가 없는 경우]

현재 TEST_01에는 데이터가 들어가있지만 
TEST_02, TEST_03 에는 데이터가 들어가있지 않습니다.

 

1-4-1. datafile에 데이터가 없는경우

우선 데이터가 없는 TEST_02 datafile을 offline 해보겠습니다.

ALTER DATABASE DATAFILE 'C:\APP\ORADATA\ORCL\TEST_02.DBF' OFFLINE;

 

datafile TEST_02.dbf가 recover상태로 변경되었습니다.

SELECT count(*)
FROM a;

위와 같이 datafile을 offline했음에도 count(*) 조회를 하여 정상적으로 조회가 가능합니다.

 

 

1-4-2. datafile에 데이터가 있는경우

데이터가 있는 TEST_01 datafile을 offline 합니다.

 

ALTER DATABASE DATAFILE 'C:\APP\ORADATA\ORCL\TEST_01.DBF' OFFLINE;

 

 

TEST_01 datafile이 recover상태가 되었습니다. 이후 

SELECT count(*)
FROM a;

 

을 진행한다면

위와 같이

ORA-01110 :  39 데이터 파일: 'C:\APP\ORADATA\ORCL\TEST_01.DBF'

애러를 확인 할 수 있습니다.

 

1-5. datafile online [archive log파일이 있을경우 / archive log파일이 없을경우]

이제 offline 된 datafile을 online상태로 변경할 것입니다. 

우선 TEST_01 datafile이 offline상태가 되었다고 가정을 합니다.

 

 

1-5-1. archive log파일이 있을경우

RECOVER DATAFILE 'C:\APP\ORADATA\ORCL\TEST_01.DBF';

위 명령어를 통해 recover가 가능합니다. 이 때 datafile의 online_status값을 조회한다면

 

아래와 같이 offline상태로 나오는 것을 확인 할 수 있습니다.

ALTER DATABASE DATAFILE 'C:\APP\ORADATA\ORCL\TEST_01.DBF' ONLINE;

 

ALTER DATABASE 문으로 TEST_01 datafile을 online상태로 변경할 수 있습니다.

 

 

 

1-5-2. archive log파일이 없을 경우

위와 같이 archive log파일이 쌓이는데 이 archive log파일이 백업정책에 의해 사라졌을 수도 있습니다. 그러면 이 archive log파일들을 잠시 다른 곳으로 옮긴 후 recover를 진행을 하겠습니다. [.arc 파일이 없어진 환경을 만들기위해]

 

RECOVER DATAFILE 'C:\APP\ORADATA\ORCL\TEST_01.DBF';

 

ORA-00279: 변환 7594316가 (01/15/2024 14:51:05에서 생성된) 스레드 1에
필요합니다
ORA-00289: 제안 : C:\APP\ARCHIVE_FILE\56_1_1145438326.ARC
ORA-00280: 변환 7594316(스레드 1를 위한)가 시퀀스번호 56에 있습니다

위와 같은 oracle 애러 메시지를 확인 할 수 있습니다.

 

이 경우 ORA-00289 애러 메시지에서 확인할 수 있듯이

archive log파일인 '56_1_1145438326.ARC' 파일을 찾아 archive dest에 넣어주면 됩니다.

디렉토리에 필요한 .arc파일을 넣어주고 다시 recover를 진행한다면 정상적인 online상태로 만들 수 있습니다.

 

RECOVER DATAFILE 'C:\APP\ORADATA\ORCL\TEST_01.DBF';

ALTER DATABASE DATAFILE 'C:\APP\ORADATA\ORCL\TEST_01.DBF' ONLINE;