다른 명령
오라클 파라미터 파일 spfile VS pfile
파라미터 파일의 개념
- 데이터베이스를 기동하기 위해서는 파라미터 파일을 참조하여 SGA 및 기타 필요한 환경을 구성.
- shutdown(종료상태) ->
- 파라미터 파일 읽기(spfile/pfile) ->
- NOMOUNT 단계별로 기동 시 파라미터 파일을 가장 중요한 역활을 함.
- 만약 파라미터 파일이 없을 경우에는 "could not open parameter file '경로 및 파일이름' " 에러가 발생.
파라미터 파일의 종류
정적 파라미터
- 오라클 모든 버전에서 사용가능(8i ~)
- 텍스트 형식으로 OS Editor를 이용하여 변경 가능
- 관련 뷰 : V$PARAMETER
- 동적 파라미터의 종류는 적음
동적 파라미터
- 오라클 특정 버전부터 사용가능(9i ~)
- 바이너리 형식으로 OS Editor를 이용하여 변경 불가능
- 관련 뷰 : V$SPPARAMETER
- 동적 파라미터의 종류는 많음
pfile과 spfile 차이점
- pfile은 OS에서 vi 편집기를 이용하여 파라미터의 값을 변경 가능.
- NOMOUNT 단계 이상이라면 다시 pfile을 사용하지 않음
- spfile은 OS에서 vi 편집기로 절대 수정을 하면 안됨
- NOMOUNT 단계 이상이라면 spfile을 다시 쓰기 때문에 제거,이동 하면 안됨.
파라미터를 설정 변경
- SPFILE
ALTER SYSTEM SET [파라미터이름=values] [SCOPE=(MEMORY/SPFILE/BOTH)]
- PFILE
ALTER SYSTEM SET [파라미터이름=values] [SCOPE=(MEMORY)]
파라미터 파일의 관리
PFILE 파라미터 경로
- 파라미터 파일의 경로는 기본적으로 $ORALE_HOME/dbs 에 있습니다.
- 아래 순서와 같이 search를 하게 됩니다.
$ cd $ORALE_HOME/dbs $ cd ls -l 1.spfiletestdb.ora 2.spfile.ora 3.inittestdb.ora 4.init.ora
동적 파라미터 사용(SPFILE)
- $ORACLE_HOME/dbs/spfiletestdb.ora
- initSID.ora 파일(pfile)이 있어도 상관 없음. spfileSID.ora 파일이 있다면 pfile(initSID.ora)은 절대 반영 되지 않음.
$ cd $ORALE_HOME/dbs $ ls -al spfile* -rw-r----- 1 oracle dba 2560 1월 26 02:17 spfiletestdb.ora
- 데이터베이스를 오픈하지 않고 NOMOUNT 단계에서 상태 확인.
SYS@testdb> startup nomount
현재 pfile , spfile 확인 하는 방법
- 아래와 같이 VALUE에 값이 있을 경우에는 spfile이고 만약 VALUE에 값이 없다면 pfile
SYS@testdb> show parameter pfile NAME TYPE VALUE --------------------------------------------------------------- spfile string /home/oracle/product/10g/dbs/spfiletestdb.ora
현재 파라미터를 몇개나 사용하고 있는지 확인
select isspecified ,count(value) from v$spparameter group by isspecified; ISSPECIFIED COUNT(VALUE) ------------ ------------ FALSE 0 TRUE 22
파라미터 파일 생성
- 정적 파라미터는 데이터베이스 종료 상태에서도 가능
- 동적 파라미터의 경우는 데이터베이스가 NOMOUNT 단계 이상에서 생성해야 함.
정적 파라미터의 경우 - 방법 1
- show parameter dump 로 덤프 파일 경로 확인
- 기본적으로 덤프파일과 동일한 디렉토리에 pfile 디렉토리 존재.
- init.ora.2322 파일을 기존 pfile($ORACLE_HOME/dbs) 위치로 복사.
- 오라클 공인 문서에서는 $ORACLE_HOME/dbs 디렉토리의 init.ora 파일로 변경 하라고 되어 있음.
$ cp init.ora.010201115288 $ORACLE_HOME/dbs
정적 파라미터의 경우 - 방법 2
- NOMOUNT 단계 이상에서 생성.
create pfile from spfile;
- or
create pfile from spfile='/u01/app/oracle/oradata/dbconfig/ORCL/spfileORCL.ora';
동적 파라미터의 경우
- NOMOUNT 단계 이상에서 생성.
create spfile from pfile;
파라미터 값 변경
ALTER SYSTEM SET 명령
ALTER SYSTEM SET 파라미터 이름=파라미터 값 [COMMENT='text'] [SCOPE=MEMORY/SPFILE/BOTH] [SID='sid'/'*']
COMMENT 옵션
- 파라미터 설정 주석을 추가하는 옵션
- 동적 파라미터 파일의 설정 값을 해당 명령어로 변경시 주석을 추가할 수 있게 하는 옵션 .
- 파라미터 설정 값 변경시 이력 관리 및 특이 사항을 메모할 필요가 있을 경우 사용.
SCOPE 옵션
- 파라미터 설정 변경하는 옵션
- 파라미터의 설정을 변경 하는데 있어서 가장 큰 역활을 하는 옵션
- 동적 파라미터(spfile)와 정적 파라미터(pfile) 중 어떤 방식으로 기동 되었는지에 따라서 차이 발생.
- 기본적인 개념은 정적 파라미터는 MEMORY 옵션만 사용할 수 있고, 정적 파라미터는 옵션 모두 사용 가능.
- MEMORY : 파라미터 설정 값 변경을 메모리에서만 적용함. DB종료 후 재기동 할 경우 MEMORY 옵션으로 변경한 파라미터는 변경한 값으로 바뀌지 않음.
- SPFILE : 파라미터 설정 값을 변경 하되 데이터베이스를 재기동 후 변경 됨.
- 실제적으로 현재 메모리 상에서는 기존의 값을 유지 하게 되고 재기동 후에는 변경 된 값을 정상적으로 사용 할 수 있음.
- BOTH : MEMORY + SPFILE 두가지의 옵션을 모두 사용.
- 정적 파라미터(pfile)에서 해당 옵션은 사용 할 수 없음.동적 파라미터를 위한 옵션이라고 생각 할것.
SID 옵션
- 오라클 RAC(Real Application Cluster)를 사용할 경우 변경된 파라미터 값이 적용될 노드를 선택하는 옵션.
- 기본 값은'*'을 사용할 경우 모든 노드에 변경된 설정 값을 적용.
- 특정 노드에만 적용해야 할 경우 해당 인스턴스 번호 지정.
SID=*
- 파라미터 조회시 *.db_block_size=8192 이와 같은 값이 보이는데, *심볼은 rac에서 all instance를 의미함.)
sga pga size 변경시 에러
alter system set sga_max_size=10g scope=both;
- SGA_MAX_SIZE 변경시 에러 발생
The `ORA-02095: specified initialization parameter cannot be modified`
- SGA_MAX_SIZE는 동적 파라미터이기때문에 시스템에 동적으로 반영할수 없다.=> spfile로 저장한 후 DB를 restart 해야 적용된다.
ORA-02095 error 발생시
- The `ORA-02095: specified initialization parameter cannot be modified`
- 변경하고자 하는 파라미터가 정적(static) 파라미터 이기 때문에 alter system 명령으로 바꿀수 없다.
- 해결 방법은
alter system set 파라미터=값 scope=spfile
로 변경 후 db를 restart 해야 적용됨.
해결 방법
- 예시 `SGA_MAX_SIZE` 변경시 에러 발생
SHUTDOWN IMMEDIATE; ALTER SYSTEM SET SGA_MAX_SIZE = <new_size> SCOPE=SPFILE; -- pfile 사용시는 pfile 수동변경 `SGA_MAX_SIZE` parameter. STARTUP;
파라미터 V$PARAMETER 조회
SELECT * FROM V$PARAMETER;
- IMMEDIATE 값 : 즉시 적용이 가능한 파라미터 MEMORY/SPFILE/BOTH 모두 사용 가능
- DEFERRED 값 : 현재 접속 중인 세션은 적용 하지 않고 다음 세션 부터 적용하는 파라미터. MEMORY/SPFILE/BOTH 모두 사용 가능
- FALSE : 데이터베이스를 재기동해야 적용 가능.오직 SPFILE만 사용 가능(Static Parameter)
시스템에서 즉시/다음세션/재기동 적용 가능한 파라미터 건수 조회
SELECT ISSYS_MODIFIABLE ,COUNT(*) FROM v$parameter GROUP BY ISSYS_MODIFIABLE; ISSYS_MODIFIABLE COUNT(*) ------------------ ---------- DEFERRED 7 FALSE 107 IMMEDIATE 145
동적파라미터(spfile) 쿼리로 적용 가능한 파라미터 건수 조회
SELECT b.ISSYS_MODIFIABLE , COUNT(*) FROM v$spparameter a , v$parameter b WHERE a.name = b.name GROUP BY b.ISSYS_MODIFIABLE; ISSYS_MODIFIABLE COUNT(*) ------------------ ---------- DEFERRED 7 FALSE 108 IMMEDIATE 145
파라미터 장애 복구 시나리오
[상황 1] 데이터베이스 종료 상태에서 DBA(관리자) 실수로 spfile, pfile 모두 삭제 했을 경우 복구 방법
- 백업해놓은 pfile 이 있을 경우
- $ORACLE_BASE/admin/testdb/pfile을 $ORACLE_HOME/dbs로 파일을 복사 한 후 DB를 OPEN 하면 됨.
- 11g 이상에서는 디비기동시 기본이 spfile 이므로 pfile=pfile명 옵션을 줘서 startup 해야함.
장애 발생 확인
$ sqlplus / as sysdba SYS@testdb> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/home/oracle/product/10g/dbs/inittestdb.ora'
백업한 pfile을 복구대상db에 복사
cd ~/admin/testdb/pfile -- 백업해놓은 pfile을 복구대상db에 복사 $ cp init_backup_pfile.ora $ORACLE_HOME/dbs/inittestdb.ora
- 복구대상db $ORACLE_HOME/dbs/ 디렉토리에 복사 된 pfile (inittestdb.ora)확인
$ ls backup hc_testdb.dat initdw.ora inittestdb.ora lkTESTDB orapwtestdb
- 데이터파일을 OPEN 하기 이전에 Controlfile의 경로가 맞는지 받드시 확인.
$ vi inittestdb.ora ########################################### # File Configuration ########################################### control_files=( '/home/oracle/oradata/testdb/control01.ctl', '/home/oracle/oradata/testdb/control03.ctl')
SYSDBA로 접속하여 디비 기동
$ sqlplus / as sysdba SYS@testdb> startup ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1267068 bytes Variable Size 92277380 bytes Database Buffers 188743680 bytes Redo Buffers 2924544 bytes Database mounted. Database opened. SYS@testdb> show parameter pfile; NAME TYPE VALUE ---------------------------------------------------- spfile string
[상황 2] pfile을 spfile로 변경 해야 할때
- pfile을 spfile로 만들어 주고 데이터베이스를 재기동 하면 끝.
SYS@testdb> create spfile from pfile; SYS@testdb> shutdown immediate; SYS@testdb> startup ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1267068 bytes Variable Size 92277380 bytes Database Buffers 188743680 bytes Redo Buffers 2924544 bytes Database mounted. Database opened. SYS@testdb> show parameter pfile; NAME TYPE VALUE ------------------------------------------------ spfile string /home/oracle/product/10g/dbs/spfiletestdb.ora