내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
에러 : 잘못 추가한 Datafile 삭제(alter tablespace drop datafile)
기존 datafile 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SYS@orcl> select * from dba_data_files;
FILE_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS 5242880 640 AVAILABLE 4 YES 3.4360E+10 4194302 160 5177344 632 ONLINE
/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 SYSAUX 293601280 35840 AVAILABLE 3 YES 3.4360E+10 4194302 1280 293535744 35832 ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf
2 UNDOTBS 73400320 8960 AVAILABLE 2 YES 3.4360E+10 4194302 64073334784 8952 ONLINE
/oracle/app/oracle/oradata/orcl/system01.dbf
1 SYSTEM 482344960 58880 AVAILABLE 1 YES 3.4360E+10 4194302 1280 482279424 58872 SYSTEM
/oracle/app/oracle/oradata/orcl/perfstat01.bdf
5 PERFSTAT 524288000 64000 AVAILABLE 5 NO 0 0 0 524222464 63992 ONLINE
/oracle/app/oracle/oradata/orcl/mints01.dbf
6 mints 524288000 64000 AVAILABLE 6 NO 0 0 0 524222464 63992 ONLINE
6 rows selected.
|
새로운 datafile 추가(mints02.dbf)
|
1
2
3
|
SYS@orcl> alter tablespace mints add datafile '/oracle/app/oracle/oradata/orcl/mints02.dbf' size 5m;
Tablespace altered.
|
해결 방법 : 보통은 바로 offline drop 명령어를 이용해 datafile에 data가 쌓이지 않게 한다.
보통은 바로 offline, offline drop 명령어를 이용해 datafile에 data가 쌓이지 않게 한다.
아카이브 모드 인경우 offline 사용
|
1
2
3
|
SYS@orcl> alter database datafile '/oracle/app/oracle/oradata/orcl/mints02.dbf' offline;
Database altered.
|
아카이브 모드가 아닌경우 offline drop사용
|
1
2
3
|
SYS@orcl> alter database datafile '/oracle/app/oracle/oradata/orcl/mints02.dbf' offline drop;
Database altered.
|
datafile을 확인한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SYS@orcl> select * from dba_data_files;
FILE_NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS 5242880 640 AVAILABLE 4 YES 3.4360E+10 4194302 160 5177344 632 ONLINE
/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 SYSAUX 293601280 35840 AVAILABLE 3 YES 3.4360E+10 4194302 1280 293535744 35832 ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf
2 UNDOTBS 73400320 8960 AVAILABLE 2 YES 3.4360E+10 4194302 64073334784 8952 ONLINE
/oracle/app/oracle/oradata/orcl/system01.dbf
1 SYSTEM 482344960 58880 AVAILABLE 1 YES 3.4360E+10 4194302 1280 482279424 58872 SYSTEM
/oracle/app/oracle/oradata/orcl/perfstat01.bdf
5 PERFSTAT 524288000 64000 AVAILABLE 5 NO 0 0 0 524222464 63992 ONLINE
/oracle/app/oracle/oradata/orcl/mints01.dbf
6 mints 524288000 64000 AVAILABLE 6 NO 0 0 0 524222464 63992 ONLINE
/oracle/app/oracle/oradata/orcl/mints02.dbf
7 mints (null) (null) AVAILABLE 7 (nu (null) (null) (null) (null) (null) RECOVER
7 rows selected.
|
offline drop 명령어 사용시 ONLINE_STATUS가 RECOVER상태로 변하고 data가 쌓이지 않는다.
현재 상태에서 datafile을 제거하기 위해서는
alter tablespace mints drop datafile '~~'; 명령어를 사용한다.
이 때 조건으로는
1. 데이터베이스가 OPEN상태어야합니다.
2. 데이터 파일이 비어 있지 않으면 삭제할 수 없습니다.
비어 있지 않고 스키마 객체를 삭제하여 비워 둘 수없는 데이터 파일을 제거해야하는 경우
데이터 파일이 포함 된 TABLESPACE를 삭제해야합니다.
3. TABLESPACE의 첫 번째 또는 유일한 데이터 파일은 삭제할 수 없습니다.
즉 DROP DATAFILE 명령어는 BIG TABLESPACE에서는 사용할 수 없습니다.
4. 읽기 전용 TABLESPACE에서는 데이터 파일을 삭제할 수 없습니다.
5. SYSTEM TABLESPACE의 데이터 파일을 삭제할 수 없습니다 .
6. 로컬 관리 TABLESPACE의 데이터 파일이 오프라인 상태인 경우 삭제할 수 없습니다.
위와 같은 조건이 있기 때문에 datafile을 먼저 online 시켜준다.
|
1
2
3
4
5
6
|
SYS@orcl> alter database datafile '/oracle/app/oracle/oradata/orcl/mints02.dbf' online;
alter database datafile '/oracle/app/oracle/oradata/orcl/mints02.dbf' online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery if it was restored from backup, or END BACKUP if it was not
ORA-01110: data file 7: '/oracle/app/oracle/oradata/orcl/mints02.dbf'
|
이때 에러가 발생하는데 recover 명령어를 이용해서 datafile을 사용가능한 상태로 만들어준다.
|
1
2
3
|
SYS@orcl> recover datafile '/oracle/app/oracle/oradata/orcl/mints02.dbf';
Media recovery complete.
|
offline 시점의 아카이브로그 파일이 존재해야 복구가 가능함
그 후 datafile을 다시 online 시킨다.
|
1
2
3
|
SYS@orcl> alter database datafile '/oracle/app/oracle/oradata/orcl/mints02.dbf' online;
Database altered.
|
datafile을 확인한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SYS@orcl> select * from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS 5242880 640 AVAILABLE 4 YES 3.4360E+10 4194302 160 5177344 632 ONLINE
/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 SYSAUX 293601280 35840 AVAILABLE 3 YES 3.4360E+10 4194302 1280 293535744 35832 ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf
2 UNDOTBS 73400320 8960 AVAILABLE 2 YES 3.4360E+10 4194302 64073334784 8952 ONLINE
/oracle/app/oracle/oradata/orcl/system01.dbf
1 SYSTEM 482344960 58880 AVAILABLE 1 YES 3.4360E+10 4194302 1280 482279424 58872 SYSTEM
/oracle/app/oracle/oradata/orcl/perfstat01.bdf
5 PERFSTAT 524288000 64000 AVAILABLE 5 NO 0 0 0 524222464 63992 ONLINE
/oracle/app/oracle/oradata/orcl/mints01.dbf
6 mints 524288000 64000 AVAILABLE 6 NO 0 0 0 524222464 63992 ONLINE
/oracle/app/oracle/oradata/orcl/mints02.dbf
7 mints 5242880 640 AVAILABLE 7 NO 0 0 0 5177344 632 ONLINE
ONLINE 상태가 된것을 확인한다.
7 rows selected.
|
drop datafile 명령어로 datafile을 지워준다.
|
1
2
3
|
SYS@orcl> alter tablespace mints drop datafile '/oracle/app/oracle/oradata/orcl/mints02.dbf';
Tablespace altered.
|
datafile을 확인한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SYS@orcl> select * from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS 5242880 640 AVAILABLE 4 YES 3.4360E+10 4194302 160 5177344 632 ONLINE
/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 SYSAUX 293601280 35840 AVAILABLE 3 YES 3.4360E+10 4194302 1280 293535744 35832 ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf
2 UNDOTBS 73400320 8960 AVAILABLE 2 YES 3.4360E+10 4194302 64073334784 8952 ONLINE
/oracle/app/oracle/oradata/orcl/system01.dbf
1 SYSTEM 482344960 58880 AVAILABLE 1 YES 3.4360E+10 4194302 1280 482279424 58872 SYSTEM
/oracle/app/oracle/oradata/orcl/perfstat01.bdf
5 PERFSTAT 524288000 64000 AVAILABLE 5 NO 0 0 0 524222464 63992 ONLINE
/oracle/app/oracle/oradata/orcl/mints01.dbf
6 mints 524288000 64000 AVAILABLE 6 NO 0 0 0 524222464 63992 ONLINE
6 rows selected.
|
정상적으로 제거 된것을 확인할 수 있다.
+
아카이브 모드가 아닌 상태에서 log switch 가 일어났다면
위 recover 명령어가 적용되지 않기 때문엔 위 방법을 사용할 수 없다.
+ 12c 이상 버전의 경우 onlie move datafile 명령으로 데이터파일을 옮길 수 있음
오라클 19c 온라인 데이터파일 move 테스트 ( https://positivemh.tistory.com/856 )
오라클 19c 온라인 데이터파일 move 테스트
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c 온라인 데이터파일 MOVE 테스트(online datafile move) 운영 환경에서 datafile이 저장된 디렉토리의 용량이 모자라거나 asm 디스
positivemh.tistory.com
참조 : https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles006.htm#ADMIN11435
https://docs.oracle.com/html/E25494_01/dfiles004.htm