목차

Oracle Parameter File (init.ora, spfile.ora)

Initial Parameter File

서버 파라미터 파일 텍스트 파라미터 파일
file binary file text file
수동편집 불가 가능
명명 SPFILE PFILE
기본이름 spfile<SID>.ora init<SID>.ora
  1. SPFILE을 생성하여 초기화 파라미터를 동적으로 유지관리할 것을 권장한다.
  2. 현재의 파라미터파일의 형식을 확인하는 방법이다. (값이 존재한다면 spfile로 시작된 DATABASE 이다)
    SQL> show parameter spfile

PFILE (Parameter FILE)

  1. 일반적으로 $ORACLE_HOME/dbs/init<SID>.ora 로 저장된다.
  2. 인스턴스 기동중 ALTER SYSTEM 이나 ALTER SESSION으로 parameter를 변경할 수 있으나, 인스턴스를 재기동될 때는 pfile에 저장된 값으로 다시 적용된다.
  3. 완전히 파라메터를 변경하고자 할 경우 pfile을 수정해야 한다.

SPFILE (Server Parameter FILE)

  1. 최초 적용시에는 pfile을 읽어 spfile을 만든다.
    CREATE SPFILE='/home/oracle/product/920/dbs/racdb_spfile.ora' FROM PFILE='/home/oracle/product/920/dbs/initTEST.ora';
  2. 이후, 파라메터 변경시에는 ALTER SYSTEM 명령어를 이용하여 변경한다.
  3. RAC 구성시 공유디스크에 spfile을 설정해 두면 한번의 파라메터 변경으로 모든 인스턴스가 동일한 파라메터로 적용된다.
  4. 필요에 따라 pfile이나 spfile 어떤 방식으로도 적용이 가능하다.
  5. alter system을 통해 database가 운영 중에 parameter를 수정할 수 있게 된다.
    즉 parameter 수정때마다 database를 restart 시켜야 하는 필요을 줄여주고, dynamic한 server tuning에 중요한 역할을 할 수 있다.

초기 파라미터 확인 및 수정

  1. 현재 초기 파라미터 확인방법 (보기)
    1. init<SID>.ora 파일을 만들어서 확인한다.
    2. SQL> show parameter; 또는 SQL> select * from v$spparmeter; 명령으로 확인한다.
  2. 초기 파라미터 수정방법
    1. SQL> alter system set <paramter_keys> = true scope=<scope_value>
      1. scope_value는 memory(메모리에만 적용), spfile(SPFILE에만 적용), both(메모리, SPFILE 모두 적용) 
        SQL> alter system set log_checkpoints_to_alert = true scope = both
    2. processes 경우에는 spfile에만 적용이 가능하다. 
      SQL> alter system set processes = 512 scope = spfile

활용예제

spfileSID.ora 가 손상되었을 경우(vi로 수정)

  1. spfile이 크지 않은 관계로 vi등으로 읽으면 마치 text 화일처럼 parameter가 보이는데 이 화일은 실제로는 binary file로 직접 변경하면 안된다.
  2. 특히 이 화일의 header에는 checksum과 기본적인 meta-data 정보등을 포함하여
    manual하게 변경하게 되면 이 checksum의 값이 맞지 않게 되면서,
    이 후 startup시 이 화일을 읽지 않고 initSID.ora file을 읽게 된다.
  3. 오라클DB 운영하면서 spfile에 계속 parameter가 변경된 경우라면 이러한 문제로 tuning등을 통해 변경된 parameter value를 모두 잃게 될 수 있는 것이다.
  4. 주기적으로 parameter value를 백업해 두어야 한다.

현재 DB가 initSID.ora 또는 spfileSID.ora 로 열렸는지 확인하는 방법

  1. 오라클DB가 open 시에 참조한 initial parameter file이 무엇인지 SQL문 조회로 확인할 수 있다. 
    SQL> SHOW parameter spfile
     NAME     TYPE        VALUE 
     spfile   string      ?/dbs/spfile@.ora
    1. ?는 $ORACLE_HOME 을 나타내며, @는 SID를 나타낸다.

initSID.ora <-> spfileSID.ora 변경하는 방법

  1. sysdba 권한이 없으면 권한 부족 오류가 발생한다.
  2. spfile을 사용하다가 그 내용을 pfile(initSID.ora)에 backup 차원에서 반영
  3. spfile대신 initSID.ora를 사용하고자 하는 경우
  4. initSID.ora를 참조하여 spfileSID.ora를 생성 (여기서 SID는 ADMINSCHOOL 이다) 
    SQL> CONNECT / AS sysdba 
SQL> CREATE pfile='initADMINSCHOOL.ora' FROM spfile='spfileADMINSCHOOL.ora'; 
SQL> CREATE spfile='spfileADMINSCHOOL.ora' FROM pfile='initADMINSCHOOL.ora';
  5. 위의 예처럼 화일명 대신 직접 directory까지 절대 path로 지정할 수 있고,
    화일명은 임의로 지정 후 나중에 사용시 initSID.ora나 spfileSID.ora 형태로 만들어줄 수 있다.
  6. default인 $ORACLE_HOME/dbs directory이고 SID가 붙는 이름 형태이면 간단히,다음과 같이 지정하여도 된다. 
    SQL> CREATE pfile FROM spfile; 
SQL> CREATE spfile FROM pfile;

parameter 변경시 scope 정하기

  1. spfile을 사용하게 되면 spfile을 직접 변경하는 대신 alter system 명령을 통해 initial parameter를 수정할 수 있다.
  2. 이때 alter system 명령 뒤에 scope를 지정할 수 있는 데 scope 값은 memory/spfile/both 세가지 중 하나를 선택할 수 있다.
    scope를 지정하지 않으면 default 값인 memory가 적용된다.
memory 변경이 현재 상태에만 영향을 미치며 데이타베이스가 재시작되면, 변경 이전 값으로 돌아간다.
spfile 변경 내용을 spfile에만 저장하고 현재 상태에는 영향을 미치지 않는다.
static parameter의 경우는 이 scope만이 지정가능하다.
즉, spfile을 사용하더라도 static parameter에 대해서는
db 운영중에 바로 값을 변경하여 restartup없이 반영하는 것은 불가능하다.
both 변경 내용을 현재상태에도 바로 반영하고 spfile에도 반영되어 이후 데이타베이스 재시작시에도 영향을 미치도록 한다.
  1. 예를 들어보자.
    SQL> ALTER system SET open_cursors=300; 
SQL> ALTER system SET open_cursors=300 scope=spfile; 
SQL> ALTER system SET open_cursors=300 scope=BOTH;
  2. 단, spfile을 참조하지 않고 init.ora를 참조하여 db가 open한 경우
    이러한 alter system 명령을 통해 initial parameter를 변경하려고 하면
    다음과 같은 오류가 발생한다. 특히 spfile에 외부의 변경이 가해져 spfile이 참조되지 않은 경우에 주로 다음 오류를 만날 수 있다. 
    ORA-02095: specified initialization parameter cannot be modified

v$parameter와 v$spparameter에 대해서 (static parameter 확인)

  1. initial parameter의 종류에는 db가 운영중에는 바로 그 값을 변경하여 반영시킬 수 없고,
    다음 startup 후에만 변경된 값이 영향을 주게되는 parameter가 있다.
    이러한 parameter를 static parameter라고 부른다.
  2. static parameter의 경우는 그래서 alter system으로 변경하더라도 scope=spfile 로만 지정 가능한 것이다.
  3. 이러한 sttic parameter를 scope를 지정 안해서 memory로 하거나 both로 하게 되면 위의 ORA-2095 오류가 발생하게 된다.
static parameter 확인방법
  1. 다음 명령을 실행해보자. 
    SQL> SELECT name, issys_modifiable FROM v$parameter;
  2. issys_modifiable의 값으로 다음 세가지가 나타난다.
FLASE static parameter로 scope=spfile로만 값을 변경 가능하다.
즉 값을 변경해도 이후 startup 시부터 영향을 미치게 된다.
IMMEDIATE 값을 변경하면 현재 session부터 바로 영향을 받게된다.
DEFERRED 변경된 값이 이후 접속되는 session부터 영향을 준다.
parameter 반영 확인방법
  1. alter system을 통해 parameter를 변경하는 경우 변경된 값이 반영이 잘 되었는가를 확인하려면
    다음과 같이 show parameter나 v$prameter를 조회하고,
    현재 반영은 안 되었더라도 spfile에 저장은 되었는지를 확인하려면 v$spparameter를 조회하면 된다. 
    SQL> SHOW parameter open_cursors 
SQL> SELECT VALUE FROM v$prameter WHERE name = 'open_cursors'; 
SQL> SELECT VALUE FROM v$spparameter WHERE name = 'open_cursors';
  1. 즉, scope=spfile로 parmaeter를 변경한 경우는
    v$spparameter에만 변경된 값이 나타나고,
    show parameter나 v$parameter에서는 변경전 값이 나타나게 된다.
  1. Hidden Parameter 까지 조회가능하다. 
    SQL> SELECT n.ksppinm name , v.ksppstvl VALUE FROM x$ksppi n , x$ksppsv v
     WHERE n.indx=v.indx
     AND n.ksppinm LIKE '%';