[오라클]Redo log file, tablespace, datafile

[pfile을 이용한 멀티플렉싱]

- pfile을 통해 control.ctl 파일 경로 수정

 

- Database 재시작시 ctl 경로 변경 조회

- ms-sql에서는 mdf(마스터 데이터베이스 파일) 처음에 파일 생성시 만들어짐. 그 이후로는 ndf로 생성

 

 

---

 

 

[Redo log file]

- 데이터가 변경되는 내용을 모두 기록. 복구시 사용하는 파일.

- 반드시 그룹화 되어 있어야 하며 적어도 2개의 그룹이 있어야 한다.

- 그룹안에는 멤버가 하나 이상 존재해야 한다.

- LGWR는 Redo log를 기록한다. 그룹1의 용량이 꽉 찰 경우, 로그 스위치가 발생하여 다음번 그룹2에 기록. 순환식으로 일어난다. 이때, 체크포인트가 발생. 체크포인트는 각 파일에 시퀀스번호를 부여함. 시퀀스번호에 대한 데이터도 기록하므로 컨트롤파일에 대한 정보까지 기록한다.

 

- 관련 명령어

① ALTER SYSTEM SWITCH LOGFILE;  

▶ 저장되는 로그파일을 변경할 때  사용하는 명령어

 

② ALTER SYSTEM CHECKPOINT;

▶ 로그파일의 용량이 꽉차서 로그스위치가 발생하는 것이 아니므로, LGWR가 로그스위치를 하더라도 체크포인트를 생성하지 않음.

그래서 별도로 명령어로 체크포인트를 발생시킴.

 

- 파라미터 파일 설정

FAST_START_MTTR_TARGET = 600

▶ 600초마다 로그 스위치를 발생

 

- 로그파일 그룹 추가/삭제(그룹3개 생성 멤버2개씩)

추가

ALTER DATABASE ADD LOGFILE GROUP 3

(경로/파일명.rdo,                                       --멤버1

경로/파일명.rdo)                                        --멤버2

SIZE 1M ;

 

삭제

ALTER DATABASE DROP LOGFILE GROUP 3;

 

▶그룹 안 멤버끼리 사이즈는 같아야 하지만 그룹마다의 용량은 다를 수 있음. 관리의 편의성 때문.

 

- 로그파일 추가 예시

 

 

- 로그그룹에 멤버 추가/삭제(이미 만들어진 그룹안에 멤버를 추가)

멤버 추가

ALTER DATABASE ADD LOGFILE MEMBER

'경로/파일명.rdo'    TO GROUP 1,

'경로/파일명.rdo'    TO GROUP 2,

'경로/파일명.rdo'    TO GROUP 3;

 

멤버 삭제

ALTER DATABASE DROP LOGFILE MEMBER '경로/파일명.rdo';

▶동일 그룹의 멤버들은 쌍둥이 개념. 디스크가 망가지더라도 복구될 수 있도록 동일 그룹내 멤버들은 각기 다른 디스크에 저장

 

-로그파일 삭제 예시

-로그 파일을 조회하는 동적 딕셔너리

V$log

V$logfile

 

- V$LOG와 V$LOGFILE을 조인문으로 작성하여 조회

@LOG.SQL로 스크립트 저장함

SELECT A.GROUP#,MEMBER,BYTES,SEQUENCE#,B.STATUS

FROM V$LOGFILE A, V$LOG B

 

WHERE A.GROUP# = B.GROUP#

 

- MEMBER라는 컬럼 조회시 사이즈를 29로 맞추라는 명령어

 

- 로그스위치를 진행한 후, 체크포인트를 지정해줘야만 ACTIVE상태에서 INACTIVE로 바뀜(COMMIT)

 

 

- STATUS 종류

① CURRENT : 현재 사용중인 로그파일

② ACTIVE : 커밋되지 않은 상태에서 로그 스위치가 발생했을 때

③ INACTIVE :  활성화되지 않은 상태(커밋 완료)

 

④ UNUSED : 로그 그룹 새로생성 뒤 상태. CURRENT에서는 로그스위치 발생시 UNUSED 로 넘어감.

 

- [그룹1 - 그룹4 - 그룹2 - 그룹3 ] 순서로 반복 회전

 

- 로그 멤버 삭제 : CURRENT는 삭제될 수 없다. CURRENT가 아닌 상태로 만들어줘야 삭제 가능

※ 복구에서 CURRENT상태의 로그가 지워졌을때 복구하는 방법 배움. WHY? 디스크 자체가 망가지는 경우가 발생하기 때문

 

- 로그 그룹 삭제 : 마지막 멤버는 삭제 불가. 마지막 멤버까지 지우려면 그룹 자체를 지워야 한다

 

 

---

 

 

[Redo log file 이동]

TESTDB 디렉토리에  REDO01.log, REDO02.log, REDO03.log 를 하드디스크에 저장하는 개념으로 옮길 때,

(오라클 데이터베이스는 로그그룹 2개 이상, 각각 그룹에는 멤버 1개 이상이 되어야 함. 해당 조건 고려하여 옮기는 것 기억)

 

① 로그그룹파일은 2개 이상이어야 함, 로그파일 2개 추가생성 (그룹 4, 5)

 

②기존 로그그룹1,2,3 삭제

 

③원하는 디스크에 로그그룹 생성

 

④ 로그그룹4,5 삭제

 

⑤ 로그파일 위치 조회

 

 

---

 

 

[Tablespace, Datafile]

데이터베이스 > 테이블스페이스 > 데이터파일

 

- Tablespace 종류

① SYSTEM Tablespace : UNDO테이블스페이스 를 내장하고 있다.자신만 가지고 있음.  

② NON-SYSTEM Tablespace :시스템 테이블스페이스를 제외한 모든 테이블스페이스.

 

- Tablespace 생성 방법

CREATE TABLESPACE USERDATA

DATAFILE ‘/UO1/ORADATA/USERDATA01.DBF’ SIZE 100M

AUTOEXTEND ON NEXT 5M MAXSIZE 200B;’

▶ AUTOEXTEND ON 자동으로 사이즈를 증가시켜라

 

-Tablespace 저장공간

① 8KB가 기본단위. (블록 사이즈와 동일)

블록에는 항상 헤더(Header)가 존재. 앞, 뒤 파일에 대한 정보, 메모리 용량 등.

 

② LOCALLY MANAGED tablespace

블록에 대한 정보를 헤더에다가 저장함. 딕셔너리를 가는 것이 아니라 헤더로 바로가서 남은 메모리공간을 확인함.

직접 블록으로 가서 헤더의 값으로 가서 읽는 것이 속도가 더 빠름.

딕셔너리 테이블로 가는 트랜잭션을 감소시킨다.

CREATE TABLESPACE USERDATA

DATAFILE ‘/U01/ORADATA/USERDATA01.DBF’ SIZE 500M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

extent는 누가 관리하게 할 것인지 명시하는 것. 500메가의 공간이 꽉 찼을 경우, 계속 128kb씩 균일하게 늘리라는 명령어.

 

③ DICTIONARY MANAGED tablespace

사용자가 데이터를 넣을 때 딕셔너리로 가서 몇번 블록에 메모리가 남았는지 기록된 내용을 확인-> 데이터 입력 -> 딕셔너리 수정하는 단계를 거침.

이전에 많이 사용하던 방식(10g부터 바뀜).

CREATE TABLESPACE USERDATA

DATAFILE ‘/U01/ORADATA/USERDATA01.DBF’

SIZE 500M EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE

(INITIAL 1M NEXT 5M PCTINCREASE 10);

500메가가 꽉 찬후 이니셜 1m를 늘려라(첫번째의 경우), 그 이후에 증가할땐 5m씩증가

pctincrease 10 : 데이터가 꽉 찼을 경우 (500m+1m+5m) * 10% = 50.6m 증가

 

[Undo Tablespace] 

-생성 방법이 조금 다름. undo segments를 저장할 때 사용.

-다른 어떤 객체도 저장할 수 없음.

-만드는 것은 여러개 만들 수 있지만 사용하는 건 무조건 하나. (여러개를 만드는 이유는 하드디스크가 망가지는 경우를 대비하기 위함).

-반드시 복구해줘야 하는 테이블 스페이스 중 하나. local로 오라클이 관리(딕셔너리 방식 아님!)

 

CREATE UNDO TABLESPACE UNDO1

DATAFILE ‘/U01/ORADATA/UNDO01.DBF’ SIZE 40M;

 

[Temporary Tablespace ]

- 가장 기본적인 역할 : 정렬할 경우 공간을 만들어주는 것

- pga가 정렬을 위해 temp 를 찾아갔을 때, 이 tablespace의 저장공간이 없을 경우 System tablespace로 가서 진행됨 > 오라클의 성능이 떨어지게 됨 (이런 경우는 하드디스크가 망가졌을때..)

- temp tbs는 데이터베이스 shutdown 후 startup할 경우 모두 초기화됨

- local이 관리

CREATE TEMPORARY TABLESPACE TEMP

TEMPFILE  ‘/U01/ORADATA/TEMP01.DBF’ SIZE 500M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

 

[Default Temporary Tablespace]

- temp tbs 여러개 만들 수 있음. 단, default 값을 지정해줘야 함

ALTER DATABASE

DEFAULT TEMPORARY TABLESPACE TEMP;

 

- 데이터베이스를 만들면서 temp를 만드는 방법

DEFAULT TEMPORARY TABLESPACE TEMP

TEMPFILE ‘/$HOME/ORADATA/U03/TEMP01.DBF’ SIZE 4M

 

- 새로운 디폴트 테이블스페이스가 생기기전까지는 이 디폴트를 삭제할 수 없음

※ taken offline : DB의 수정작업. DB는 멀쩡하나 특정 테이블스페이스를 수정해야 할 때,

사용자들이 접근할 수 없도록 tablespace만 offline으로 설정할 수 있음.

여러 tablespace중 특정 것만 접근 불가능 하도록 설정하는 것. 하지만 temp의 경우 24시간 접근해야 하므로 offline 설정이 불가능하다.

temporary는 일반 테이블 스페이스로 바꿀 수 없다

 

 

---

 

 

[Read Only Tablespace ]

- 읽기 전용으로 바꾸기 위해서 ALTER 사용, 체크포인트를 생성함.

ALTER TABLESPACE USERDATA READ ONLY;

 

[Tablespace offline 방법]

- tablespace에 접근이 불가능하도록 처리하는 것

- system tablespace는 offline 시킬 수 없음 : 이게 처리되면 db자체를 정지시킨 개념.

- 활성화된 undo segment도 offline 불가 : 처리 될 경우 취소가 불가능하게 되는 것.

- temporary offline 불가: 24시간 online 이어야 함.

설정 : ALTER TABLESPACE USERDATA OFFLINE;

설정 해제 : ALTER TABLESPACE USERDATA ONLINE;

 

[Tablespace 사이즈 변경 방법 - 3가지]

① AUTOEXTEND ON  : 자동으로 증가해라 설정 가능

CREATE TABLESPACE USER_DATA

DATAFILE

‘/U01/ORADATA/USERDATA01.DBF’ SIZE 200M

AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

② ALTER TABLESPACE : 수동으로 얼만큼씩 늘어나라 지정 가능

ALTER DATABASE

DATAFILE ‘/U03/ORADATA/USERDATA02.DBF’

RESIZE 200;

③ 데이터 파일(DBF)을 추가해서 전체 용량이 늘어나게끔 하는 방법

ALTER TABLESPACE USER_DATA

ADD DATAFILE ‘U01/ORADATA/USERDATA03.DBF’ SIZE 200M;

 

[데이터 파일을 이동하는 방법]

- 일반 tablespace를 옮길 때.

① DATABASE는 OPEN 되있고 tablespace는 offline 진행. (진행하기 위해서는 해당 데이터파일이 반드시 존재해야 함)

② OS상에서 파일의 위치를 옮김

③ DBF 파일연결 (명령어 작성)

ALTER TABLESPACE USERDATA RENAME

DATAFILE ‘원본위치’

TO ‘복사위치’;

④ Tablespace를 online으로 변경

ALTER TABLESPACE 테이블스페이스명 ONLINE;

 

[데이터베이스가 마운트 상태일 때 데이터파일을 옮기는 방법]

- system tablespace를 옮길 때. 오라클의 기억을 바꾸는 것.

- 마운트상태 : parameter file을 읽어 ctl의 위치를 읽어온 상태. DB의 open 전.

① DB shutdown

② os상에서 파일 이동

③ mount 상태로 startup

④ 명령어 작성 : dbf 파일연결

ALTER DATABASE RENAME

FILE  '원본위치'

TO '복사위치';

⑤. alter database open

※ 로그파일을 옮길때도 이 명령어를 사용 가능

 

[테이블 스페이스를 지우는 방법]

-시스템 테이블스페이스, active 상태의 테이블스페이스는 지울 수 없음

-일반적으로 두번째꺼 많이씀. 테이블스페이스도 지워지고 데이터파일도 지워짐

- cascade는 다른 테이블스페이스에서 fk로 사용하고 있으면 해당 제약조건도 제거하고 지우는 것

① INCLUDING CONTENTS : 테이블스페이스만 제거 (SEGMENTS)

② INCLUDING CONTENTS AND DATAFILES : 테이블스페이스의 데이터파일도 같이 제거

③ CASCADE CONSTRAINTS : 제약조건까지 제거(대부분 FOREIGN KEY)

 

[딕셔너리]

① TABLESPACES : DBA_TABLESPACES, V$TABLESPACE

② DATAFILE INFORMATION : DBA_DATA_FILES, V$DATAFILE

③ TEMP FILE INFORMATION : DBA_TEMP_FILES, V$TEMPFILE

 

 

---

 

 

[TABLESPACES 정보 조회]

① @DT.SQL 저장

SELECT TABLESPACE_NAME, STATUS, CONTENTS,

EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT

FROM DBA_TABLESPACES;

모두 LOCAL에서 관리. MANUAL : DICTIONARY를 쓴다.

 

② @DDF.SQL 저장

SELECT TABLESPACE_NAME,BYTES,FILE_NAME FROM DBA_DATA_FILES;

 

③ @TEMP.SQL 저장

TEMP는 별도의 공간에 저장되어있음

SELECT TABLESPACE_NAME, BYTES, FILE_NAME FROM DBA_TEMP_FILES;

 

 

---

 

[TABLESPACES 생성] 

SEGMENT SPACE MANAGEMENT AUTO : 자동으로 관리 설정

 

[TABLESPACES 사이즈 변경] 

 

[조회]

 

 [기존 테이블스페이스에 데이터 파일 추가]

 

 

---

 

 

[TABLESPACES 이동]

① INSA 테이블스페이스 OFFLINE 설정 (어느 누구도 ACCESS 불가능 )

OFFLINE 시켜둔 DBF파일만 이동이 가능함.

 

②오라클에서 변경된 위치를 인식할 수 있도록 명령어 입력 필요.

 

③ DBF파일 이동, ALTER 입력 후 해당 TABLESPACE을 접근할 수 있도록 다시 ONLINE 으로 변경

 

 

---

 

 

[offline상태에서 실행 (일반 T/S)]

① OFFLINE

 

② DBF 파일 연결

ALTER TABLESPACE USERS RENAME

DATAFILE 'C:\APP\ITWILL\ORADATA\TESTDB\USERS01.DBF'

TO 'C:\aDisk\disk2\USERS01.DBF';

 

③ 파일위치 조회

④ ONLINE

⑤ SHUTDOWN -> OS상 파일 (경로 이동 전)기존 파일 삭제 -> STARTUP

※ 잘라내기 후 붙여넣기가 안될 경우, 복사 붙여넣기로 진행 후 삭제.

 

---

 

 

[mount상태에서 실행(system파일)-로그파일을 옮기는 것과 동일함! ]

① SHUTDOWN 시킨 후 SYSTEM01.DBF를 원하는 디스크에 이동시킴

DB SHUTDOWN 상태라 옮길 수 있음.

② MOUNT 상태로 START 함

컨트롤파일에 있는 위치정보 읽음. 데이터베이스가 오픈될경우 오류가 나기때문에 MOUNT단계여야 함

③ 오라클의 기억 변경

④ DB오픈 시킴

⑤ 성공적으로 이동된 것을 볼 수 있음

 

---

 

 [테이블스페이스 삭제]