Oracle DB 기본 관리 - 02. 데이터베이스 Instance 관리
■ 초기화 parameter file
- 파일 위치: D:\app\oracle\product\12.2.0\dbhome_1\database (Windows)
create pfile from from spfile;- spfile{SID}.ora (binary format) → init{SID}.ora (text format) (반대 방향도 가능)
- 2개 파일이 모두 있을 때는 spfile 우선순위가 더 높다. spfile은 binary이므로 변경이 어렵다.
- initprod.ora을 열면 parameter 확인 가능 (*.{parameter name}만 실제 parameter이며 나머지는 ASMM, AMM 기반 자동 설정 값)
- parameter를 변경하기 위해서는
① alter system set 명령어로 변경 (현재 운영 중인 인스턴스에서만 변경)
② parameter file (pfile) 직접 변경 (인스턴스 재기동 시 적용)
③ 변경 범위를 지정 a) spfile, b) memory, c) both (default)
alter system set shared_pool_size=40M scope both;■ 초기화 parameter 유형
- 기본
- 고급: 대부분 성능 튜닝과 관련
■ 초기화 parameter 예제
- dynamic parameter: DB 운영 중 변경 가능
- static parameter: DB 재기동 필요
■ ASMM (Automatic Shared Memory Management)
- 공유 메모리 parameter를 개별적으로 관리는 어려움
- 메모리에서 수행된 workload 기반으로 메모리를 유동적으로 할당 관리
- 기본적으로 5분마다 parameter 변경하나 out of memory 방지를 위해 수시로 변경하기도 함
- 전체 메모리 중 SGA 메모리는 필요한데 PGA 메모리가 여유 있는 경우에는 ASMM이 유능하지 않음
10g 부터 SGA 구성 요소 ( Buffer Pool , Shared Pool , Large Pool , Java Pool) 메모리를 동적으로 조정가능할 수 있게되었으며 이를 ASMM 기능이라 한다.
원래 Buffer Cache는 db_cache_size , Shared Pool 은 shared_pool_size , Large Pool은 large_pool_size 파라미터를 통해 변경되며 이들 파라미터를 변경하면 Oracle Instance는 재 기동이 되어야 함.
ASMM은 재 기동없이 이들 메모리 값을 동적으로 변경가능하게 한다.
이를 위해 SGA_TARGET , SGA_MAX_TARGET 이라는 신 파라미터가 도입됨.
출처: https://barambunda.tistory.com/10
■ AMM (Automatic Memory Management)
기존 SGA메모리 뿐만 아니라 서버 프로세스의 메모리도 동적으로 조정가능할 수 있게 함. 즉 PGA_AGGREGATE_TARGET 까지 합산된 총 메모리 조정.
이를 위해 MEMORY_TARGET 과 MEMORY_MAX_TARGET 파라미터가 도입됨.
출처: https://barambunda.tistory.com/10
spfile 공유 가능 (RAC 환경에서)
*. : 데이터베이스를 사용하는 모든 인스턴스에 대해 동일한 parameter를 적용하겠다
control file 위치가 parameter file에 저장되어 있음
■ 오라클 데이터베이스 Instance 시작
Shutdown ----- (Instance 시작) ----> Nomount → Mount → Open
1) Shutdown --> Nomount : Instance 시작
startup nomount- parameter file을 읽는다
- alert_sid.log에 기록한다
- 데이터베이스와 instance가 연결되지는 않은 상태
ORACLE_BASE = D:\app\{computer name}\virtual
ORACLE_HOME = D:\app\oracle\product\{version}\dbhome_1
- nomount 상태에서 DB 생성 가능, control file 재생성
- 복구 용이성을 위해 control file은 2개 이상 생성 권고
2) Nomount → Mount
alter database mount;- controlfile open 된다. (parameter file에 controlfile 위치가 저장되어 있다)
- 데이터베이스와 인스턴스가 연결된다
- mount 단계에서 가능한 것 : DB 운영 모드 변경(no archive/archive), Recover DB
- recover 시 archive log를 사용하는데 파일 위치를 control file이 알고 있어서 mount 단계에서 가능하다
- 단 운영 모드를 변경하려면 DB 재기동 필요
3) Mount → Open
alter database open;- datafile, redo log file이 open 된다.
※ Server Control 유틸리티 사용
- $srvctl start database -d {database name} -o mount : 전체 인스턴스를 mount 상태까지 재기동
■ 종료 모드
- shutdown 명령어 이후에는 신규 연결 허용하지 않음
1) shutdown normal (거의 안씀)
- 현재 사용자들의 connection은 그 사용자들이 연결 종료할 때까지 기다린 후, 종료
- disconnect => SQL> exit 로 세션 종료
2) shutdown transactional (거의 안씀)
- 현재 진행중인 트랜잭션을 모두 끝마치면, 서버를 종료
※ TX: DML 집합
※ 정상적인 종료는 아래 3가지 명령어를 확인할 수 있다.
- 데이터베이스가 닫혔습니다. (=메모리의 변경 작업을 datafile, redo log file 다 옮겨썼고 해당 파일들을 닫았다)
- 데이터베이스가 마운트 해제되었습니다. (=데이터베이스와 인스턴스 연결이 끊어진다)
- ORACLE 인스턴스가 종료되었습니다. (=메모리 반환하고 백그라운드 프로세스도 종료된다)
3) shutdown immediate (가장 많이 사용)
- 오라클이 직접 남은 작업들을 정리하고 종료
- 현재 처리중인 SQL문이 있다면 다 stop 시키고 commit 되지 않은 transaction이 있다면 모두 rollback 시킨 후, 종료
4) shutdown abort (운영에서 절대 사용하면 안되는)
- 메모리 변경 작업을 디스크에 반영하지 않고 세션들을 강제 종료하여 DB 종료 (인스턴스 깨부수는 작업...)
- 재기동 시 인스턴스 복구한다
※ startup force (교육 환경에서 주로 사용) = shutdown abort + startup
※ Server Control 유틸리티 사용
- $ srvctl stop database –d {database name} –o abort
■ Trace File
- 파일 위치 : D:\app\oracle (ADR BASE 라고 부름)
. diagnostic_dest가 가르키는 위치에 생성
. D:\app\oracle\{database name}\{instance name}
1) alert_{SID}.log
. DB 문제가 발생했을 때 꼭 확인해야 하는 파일 (경고성 로그 파일)
2) 트레이스 파일
. 튜닝할 때 사용하는 파일
. SP가 만드는 파일은 SQL 문장 튜닝하려고 할 때 os에 파일 떨어뜨린다고? - prod_ora_{server process id}.trc
. D:\app\oracle\diag\rdbms\prod\prod\trace
. 각 서버, 백그라운드 프로세스가 만드는 trace
- trace file을 text 파일로 전환
trprof {trace file name}.trc abc.txt sys=no■ DDL 로그 파일
- ENABLE_DDL_LOGGING을 TRUE로 설정하여 특정 DDL 문을 DDL 로그 파일로 캡처할 수 있다.
■ 디버그 로그 파일
- 오라클 데이터베이스 구성 요소의 올바른 작동에 문제가 되지 않는 조건 , 상태 또는 이벤트에 대한 경고가 있다.
- 우리가 직접 사용할 일은 없다.
※ View : 데이터를 저장하고 있는 것이 아니라 select문을 가리키는 object
- 주의할 점 : SQL문과 동일하며 최적화하여 사용해야 한다. 뷰를 테이블이라 생각하고 뷰 작성 시 인덱스를 활용하지 않거나 컬럼을 가공하여 인덱스를 사용할 수 없는 등 실행 계획을 고려하지 않고 원하는 데이터만 출력하는 것을 목적으로 작성하는 경우가 많기 때문에 일반적으로 뷰 생성을 금지하기도 한다.
1) Dictionary View
- 딕셔너리 뷰가 손상되면 table에 접근 불가
2) Dynamic performance View
- 메모리에서 직접 읽거나 디스크에서 읽어서 보여준다.
- 데이터가 동적이므로 읽기 일관성이 보장되지 않는다.
※ SQL문 처리 순서
⑤ SELECT
① FROM
② WHERE
③ GROUP BY
④ HAVING
⑥ ORDER BY;
※ pseudo column
1) rowid : 테이블 각 레코드(행)이 가지고 있는 고유의 주소
- 암호화 되어 있음
- rowid를 우리가 다 외울 수 없기 때문에 index에 저장하여 사용
2) rownum : 데이터 return 순서
- 성능 향상 목적
- 사용 가능한 연산자: <=, <, =1