데이터와 사람들 DBA 교육 2일차 정리(2장 데이터파일과 테이블스페이스, 3장 데이터 저장 구조, 9장 오라클 넷 서비스(Oracle Net Service))
2장 데이터파일과 테이블스페이스
데이터 파일 테이블스페이스로 관리된다.
테이블스페이스
1. 용량
2. 파일관리
3. 테이블스페이스 -> 세그먼트 -> 익스텐트(익스텐트 안에서 블록으로 나뉨) 단위로 관리
4. 익스텐트 안에 블록이 전부 찰때마다 새로운 익스텐트를 할당 ( 물리적으로 연속되진 않지만 논리적으로 연속되게 함 )
5. 테이블스페이스는 하나이상의 데이터파일로 구성
데이터파일 최대 사이즈 30GB
사이즈를 늘리는건 되는데 줄이는건 안됨
데이터파일 꽉찼는데 DBA가 조치안하면 테이블스페이스 풀(오버플로우) 오류남
Tablespace Size 증설
Alter Tablespace Add DataFile 명령
Alter Database Datafile Reisze 명령
Data File 별 Autoextend On 또는 Off 명령으로 Size 자동 증가여부 결정
일일점검 DBA 필수업무
AUTOEXTEND ON -> 알아서 데이터 파일 용량이 늘어남 ( 관리할거면 OFF 해서 직접 지정하는걸로,, )
테이블 스페이스를 날린다고 데이터 파일이 날라가진 않음.
DROP TABLESPACE TS_TEST INCLUDING CONTENTS AND DATAFILES 명령으로 한꺼번에 지울 수 있음.( 비추천 )
오브젝트 중에 저장공간을 갖고있는게 세그먼트임. ( 테이블, 인덱스, 파티션 등 ) dba_segments 로 조회가능
*V$로 시작하는건 끝에 S가 안붙고 DBA로 시작하는건 뒤에 S가 붙음
데이터 파일 관리 DBA_DATA_FILES, V$DATAFIL, DBA_TEMP_FILES, V$CONTROLEFILE, V$LOGFILE
OS에서 보이면 물리적(파일 등) 안보이면 논리적(테이블스페이스)
테이블 스페이스 종류 5가지 - 데이터 버퍼 캐시로 올라가있음 템프빼고
SYSTEM - DB 스키마 구조, DB Dictionary 저장
SYSAUX - 튜닝을 위한 정보 저장 - AWR, ASH 등
UNDO - 데이터 변경되기 전 정보를 저장, Rollback 시 활용
TEMPORARY - SQL 수행 중 Sort, Hash Join 중에 사용하는 공간으로 PGA 용량이 넘칠 때 활용( 소트할때 오버플로우 나면 여기 공간 사용 함. 대신 엄청 느림 그래서 웬만하면 메모리 공간에서 하려고 함 그건 나중에 배울예정 )
사용자 정의 테이블스페이스(USERS라고 만들어져있음) - 사용자가 만든 스키마 구조가 정장, 업무를 위해 사용자가 만든 모든 테이블 및 인덱스 등 저장
소트를 발생시키는 것 => 조까유
Create Database 시 생성
데이터 딕셔너리 저장되며, "dictionary" view를 통해 확인 가능
주요 저장 내용
데이터베이스의 논리적, 물리적 구조 객체의 정의와 공간 사용 정보
제약조건 정보
사용자 정보
Role 및 Privilege 정보 => role이란 테이블 CURD 권한을 묶어서 유저한테 어떤 롤을 줄지 정하는 것(그룹지정이라고 생각)
감사 및 보안등의 정보
SYS => DB오너
SYSTEM => DBA롤 부여받아서 SYS가 오너인 오브젝트를 관리
보안감사때문에 U_DBA 같은 DBA ROLE을 부여한 계정을 또 만든다고 함. SYSTEM을 써도 다를건 없지만..
System Tablespace 안에 저장돼있는 것
Dictionary View
System Tablespace에 저장된 데이터 딕셔너리의 내용
Base Table이 존재하며, sys가 owner
일반 User를 위하여 View로 제공
데이터파일의 종류인 System Tablespace에 저장되기에, 인스턴스가 Open 상태일 때 조회 가능
CDB_xxx : CDB 포함 모든 PDB Dictionary 정보 조회
DBA_xxx : DBA Role이 있어야 조회가능, 모든 Dictionary 정보 조회 ALL_xxx : 본인이 Access 가능한 Dictionary 정보 조회
USER_xxx : 본인이 Owner인 Dictionary 정보 조회 ※ 예) DBA_TABLES, ALL_TABLES, USER_TABLES,
DBA_OBJECTS, ALL_OBJECTS, USER_OBJECTS
Dynamic Performance View
Instance 기동 이후 모든 Instance 통계정보 조회
Nomout 단계부터 Open 단계까지 다양하게 조회 가능
예) Nomount 단계 : v$instance 조회 가능
Mount 단계 : V$database, v$datafile, v$controlfile 등의 뷰 조회가능 Open 단계 : 모든 뷰 조회 가능
인스턴스 재 기동 시 Clear
주로 성능과 관련된 정보 저장
예) v$statistics, v$session, v$session_wait, v$system_event 등
10g 버전부터 존재하는 ASH, AWR, ADDR View는 수집된 정보를 sysaux 테이블스페이스에 저장
System Tablespace안에 저장되는 것
성능 튜닝 정보 저장
AWR (Auto Workload, Repository) - AWR 리포트 볼때 씀
ADDM (AUTOMATIC DATABASE DIAGNOSTIC MONITOR) - 잘안씀
ASH(Active Session History) - 스냅샷 떠서 DB가 어떤지 볼 수 있음
Temporary Tablespace안에 저장되는 것( 배치 프로세스 처리할 때 많이 씀 )
SQL 수행 중 Sort 또는 Hash Join 시 PGA의 Sort 영역 및 Hash 영역 사용 paramete의 sort_area_size, hash_area_size조회
PGA의 해당 영역이 Over Flow 발생 시 Temporary Tablespace 활용
사용자별로 Temporary Tablespace 지정 가능 => 보통 1개로 운영하는데, 일부 고객사에서 OLTP랑 배치 분리해달라는 요청이 있다고 함.. 그럴때는 그냥 분리해주면 된다고 함.( 2개로 분리해도 별 차이 없음 )
select username, temporary_tablespace from dba_users;
Undo Tablespace의 구동원리
서버 프로세스에서 Insert / Update / Delete 트랜잭션 발생 시 변경 전 데이터 기록
CDB, PDB 공유
서버 프로세스는 Undo Tablespace에 Undo Segment 생성
각 Session 별로 Transaction 발생 시 해당 Transaction 별로 undo segment 할당
Undo Segment 부족 시 하나의 세그먼트에 하나 이상의 트랜잭션이 사용 가능
Undo Tablespace는 인스턴스당 하나 이상이 존재할 수 있으나, 사용되는 것은 하나 만 사용
과거 8버전까지는 Undo 세그먼트를 수동 관리하였으나, 9i 부터 자동 관리 권장
AUM (Automatic Undo Management) => 8버전에 쓰던거라 몰라도 됨 (90년대래..)
파라미터 설정 undo_management = auto (기본 값)
Undo Tablespace의 활용
Transaction Rollback
Instance Recovery 시점의 Rollback
Read Consistency (읽기 일관성)
Undo 관련 Parameter
UNDO_TABLESPACE = undotbs1
Undo Tablespace 지정
최초 Undo Tablespace는 Database 생성 시점에 생성
UNDO_MANAGEMENT = AUTO => AUTO로 하고 바꿀일 없음
Undo Segment 관리를 오라클 DBMS가 자동으로 관리
기본 값이 Auto
UNDO_RETENTION = 3600 (초)
트랜잭션 종료 (commit) 후 Undo 세그먼트의 데이터를 다른 서버 프로세스가 재사용하지 않는 시간
동 파라미터가 길면 Undo Tablespace 용량 증가 필요
너무 짧으면, select 문에서 참조하지 못해 발생하는 Snapshot Too Old 에러 자주 발생
UNDO_RETENTION_GUARANTEE = 3600 (초)
UNDO_RETENTION 파라미터는 Undo Segment 용량이 부족 시 설정된 값을 무시하고 재 사용
동 파라미터 설정 시 Undo_Retention 파라미터는 무시
UNDO_RETENTION_GUARANTEE 에 설정된 시간만큼 무조건 재사용 되지 않음
기본값은 Undo_Retention 파라미터 활용 => 거의 기본값 쓰고 이거는 사용잘 안함.
UNDO TABLESPACE는 한 개 말고는 잘안씀. 새로운거 만들어도 기존꺼만 계속 씀.
문장수준의 읽기 일관성( Consistency READ ) => SELECT
UPDATE / SET 절에서 READ는 => CURRENT MODE
UPDATE 계좌
SET 잔액 = 잔액 + 1000 -> CURRENT MODE
WHERE 회사 = '조인트리' -> 문장수준의 읽기 일관성
UPDATE SET 절에서 읽을때는 BEFORE 이미지를 읽으면 안된다.
컨트롤, 리드로그파일은 디비 죽이고 업데이트 가능, 유저 테이블스페이스는 오프라인 할 수 있음
dba_data_files - dba_free_spcae => 사용가능한 공간
또는
dba_data_files - dba_segments => 더 정확한 사용가능한 공간 ( 세그먼트 단위라 더 정확함 )
스크립트는 pdf참고
컨트롤 파일에 데이터파일 경로가 있으니, 마운트 단계에서 데이터 파일을 이동할 수 있다.
또는
오프라인 시키고 이동시켜도 된다.
테이블스페이스 용량 증설 3가지
Add Datafile
Resize
AutoExtend on
리드로그 파일이 꽉차면 아카이브 되는데 이때 2가지 모드에 따라서 백업이 되냐 안되냐 경우가 있음
1. 아카이브 모드 => 온라인 백업 가능( 온라인 상태에서 백업 받는 것 => 온라인 백업 OR 핫 백업 ), 리커버리 필요,운영DB 경우
2. 노 아카이브 모드 => 디비 죽이고 파일 복사 해서 오프라인 백업밖에 없음, 개발DB인 경우
테이블 스페이스 이동 시 순서
1. 테이블 스페이스 오프라인
2. OS상에서 경로이동
3. 컨트롤 파일 경로수정 ALTER TABLESPACE OR ALTER DATABASE
4. 테이블 스페이스 온라인
9장 오라클 넷 서비스(Oracle Net Service)
리스너 경로
C:\Users\tjdus\Downloads\WINDOWS.X64_193000_db_home\network\admin
listener.ora / 로컬 리스너 => LISTENER / 아무것도 설정안하면 기본 포트는 1521임. 근데 1521로 하면 보안감사 걸림
1. ip주소
2. tcp포트
다이나믹 리스너 / 스테이틱 리스너 => SID_LIST_LISTENER
1. 데이터베이스 연결에 대한 정보
리스너 cmd확인 방법
1. lsnrctl status [옵션 : 리스너명] => 리스너 상태 확인
2. lsnrctl stop [옵션 : 리스너명] => 디폴트 리스너 중지
3. lsnrctl start [옵션 : 리스너명] => 디폴트 리스너 시작
윈도우에서 죽이는 방법( 윈도우 -> 서비스 -> oracle tns listener 중지 ) -> 현장은 거의 유닉스 환경에서 죽임 서버가 리눅스라서
리스너는 세션 연결시에만 사용되고 접속 이후는 서버와 유저가 다이렉트로 연결됨.
cdb => 리스너.ora만 있어도됨
pdb => 리스너.ora랑 로컬 리스너 둘다 있어야 함
리스너 추가는 Net Configuration Assistant 로 하면 됨.
리스너 추가되면 tnsname.ora에 포트 바꿔주면 새로생긴 리스너로 연결됨.
동적 리스너는 pmon이 등록해줌
정적 리스너 추가는 Net Manager 로 하면 됨.
동적은 pmon이 서버의 tnsnames.ora 파일에 존재하는 SID List를 활용(pmon이 동적으로 SID 리스트를 리스너에 등록 SID_LIST_LISTENER 이게 디폴트)
정적은 Net Manager 이용하여 추가(아래 사진에 추가된 ORCL, ORCLPDB 2개가 정적이고 그 위에 ENVS가 동적)
동적으로 접속이 잘안되면 정적까지 등록해주기, 로컬 리스너까지 해주면 완벽함.
PMON이 리스너 등록 제대로 못해줄 때 => alter system register;
로컬 리스너 CMD 등록 방법
alter system set
local_listener = '(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = 기존리스너에 써있는거 그대로)(PORT = 1522)))' scope=both;
로컬 리스너 설정을 새로 만든 포트(4521)로 바꿔주지 않으면 ORCLPDB접속은 할 수 없음.(PDB는 로컬 리스너+리스너 둘다 변경 해야 접속이 가능하기 때문)
로컬 리스너 변경 후에 리스너 서비스 종료했다가 다시 시작해줘야 함.
그래도 안되면 alter system register; 이거 한 번 더 하기.
3장 데이터 저장 구조
모든 캐싱에서 LRU 알고리즘을 사용함
block 사이즈는 8k가 아닌곳이 없음.
블록은 헤더와 데이터가 있다. 정도만 알면된다고 함.
long low 타입은 사용금지, 블록 2번 읽어야 함 ( 대체 타입 varchar, clob )
Block의 상태
Free Block : 신규 Insert가 가능한 블록
Used Block : 빈 공간을 다 사용하여 더 이상 Insert를 받을 수 없는 블록
PCTFREE = 10 (기본값)
블록의 free space가 10% 이하이면, Used Block으로 변경
Update를 위한 공간
PCTUSED = 40 => 오라클이 관리해서 잘 안건드는 설정값
Used Block의 데이터가 삭제되어 40%의 공간이 확보되면Free Block으로 변경
Tablespace의 옵션이 ASSM (Automatic Segment Space Management) 방식일 경우 Oracle이 자동 관리하며, 설정하지 않음
로우 체이닝 / 로우 마이그레이션 => 블록 2번 읽어서 안좋음, 로우 마이그레이션이 일어나면 테이블 재구성 필요
파티션은 세그먼트가 없음 => pct_free가 없음
extent => Segment에 공간을 테이블스페이스로 부터 할당하는 단위
extent 관리 방법 2가지=> lmt(Locally Managed Tablespace) 씀 dmt(Dictionary Management Tablespace) 안씀
lmt(Locally Managed Tablespace) 관리 방법
1. Extent의 상태 및 Tablespace의 여유공간을 Tablespace 자체에서 관리
2. Tablespace 각각의 데이터 파일 Header에 Extent의 상태를 Bitmap으로 관리
3. Segment에 신규 Extent를 할당 받기 위해 Dictionary를 접근해야 하는 병목구간 해소( 비트맵에 할당가능한지 불가능한지를 저장하고 있어서 병목구간이 없어짐. )
4. Extent 크기는 Oracle DBMS가 자동 관리
dmt 는 8버전까지 쓰던거 9버전부터는 알아서 dbms가 관리 잘해줘서 테이블스페이스 만들때 사이즈, 자동증가, 맥스 정도만 정해주면됨
assm 방식
세그먼트 헤더에 블록에 대한 정보를 bitmap으로 관리 => 세그먼트 헤더에서 사용가능한 공간인지 아닌지를 0과 1로 알려줌.( 1이 사용중이였던 것 같음 )
hwm
대량의 Delete 작업 후 테이블 재 구성
HWM 하위로 이동
Full Table scan의 효율 향상
delete
dcl
Extent는 반환하지 않음
truncate
dml
Extent 반환 및 HWM 위치 변동
4장 메모리 관리 기법
메모리 설정은 파라미터 파일에서 설정한다.
11g부터 SGA_TARGET, PGA_AGGREGATE_TARGET 으로 관리 권장
이거 4개만 많이 씀 sga_target, sga_max_size pga_aggregate_target,pga_aggregate_limit
sga, pga 메모리 공간 설정만 해주면 나머지는 디비가 알아서 함 ( 전체 메모리에서 45~50%정도로 설정 )
메모리는 한 번 설정하고나서 따로 건드리지 않음.
대용량 테이블의 Hash Join 및 Sort가 필요
Oracle DBMS의 자동 설정은 PGA 영역의 Over Flow 발생
Temporary Tablespace 영역활용으로 성능상의 문제 야기
세션 별 Sort 또는 Hash 영역을 일시적으로 크게 설정하여 작업 필요
Hash_Area_Size 또는 Sort_Area_Size 최대 값 2GB 설정 가능
※ 주의점
병렬 작업 시 병렬 서버 프로세스 별로 PGA가 증가시켜 놓은 Size 만큼 생성 작업 후 반드시 Workarea_size_policy 설정을 auto로 수정
pga 메모리 크기 128g 기준 4~5g 정도 주면 될듯, sga는 40g정도