No. 17720

9I의 SPFILE 사용에 대해서
========================


PURPOSE
-------
 Oracle9i부터는 database의 initial parameter를 지정하는 initSID.ora file외에
server parameter file이라고 불리는 spfileSID.ora가 새로 소개되었다.
이 spfile을 사용하게 되면 alter system을 통해 database가 운영 중에 
parameter를 수정할 수 있게 되면서 parameter 수정때마다 database를 restart
시켜야 하는 필요을 줄여주고, 앞으로 소개될 dynamic한 server tuning에 
중요한 역할을 할 수 있다.

Explanation
-----------

이 문서에서는 이러한 spfile에 대해서 실제 사용하면서 알아두어야 할 다음과
같은 내용에 대해서 정리하였다.


1. spfile을 vi로 직접 변경한 경우의 영향
2. db open시 spfile을 읽었는지 확인하는 방법
3. initSID.ora를 spfileSID.ora로 migration하거나 그 반대의 방법
4. alter system으로 parameter변경시 scope에 대해서
5. v$parameter와 v$spparameter에 대해서 (static parameter 확인)


1. spfileSID.ora 화일을 vi로 직접 변경한 경우의 영향

   spfile이 크지 않은 관계로 vi등으로 읽으면 마치 text 화일처럼 parameter가
   보이는데 이 화일은 실제로는 binary file로 직접 변경하면 안된다.
   특히 이 화일의 header에는 checksum과 기본적인 meta-data 정보등을 
   포함하여 manual하게 변경하게 되면 이 checksum의 값이 맞지 않게 되면서, 
   이후 startup시 이 화일을 읽지 않고 initSID.ora file을 읽게 된다.

   db를 운영하면서 spfile에 계속 parameter가 변경된 경우라면 이러한 문제로
   tuning등을 통해 변경된 parameter value를 모두 잃게될 수 있는것이다.


2. db open 시 spfile을 읽었는지 확인하는 방법

   db가 open 시에 참조한 initial parameter file이 무엇인지를 확인하기 
   위해서는 다음과 같이 조회하면 된다.

      SQL> show parameter pfile

      NAME                            TYPE        VALUE
      ------------------------------ ----------- ---------------------
      spfile                          string      ?/dbs/spfile@.ora  


   여기에서 ?는 ORACLE_HOME을 나타내고 @는 SID를 나타낸다.


3. initSID.ora를 spfileSID.ora로 migration하거나 그 반대의 방법

   spfile을 사용하다가 그 화일의 내용을 initSID.ora에 backup 차원에서
   반영시켜 두거나, 혹은 spfile대신 initSID.ora를 사용하고자 하는 경우,
   또는 반대로 initSID.ora를 참조하여 spfileSID.ora를 생성하고 하는
   경우 다음과 같이 간단히 작업하면 된다.

      SQL>connect / as sysdba
      SQL>create pfile='initORA9i.ora' from spfile='spfileORA9i.ora';
      SQL>create spfile='spfileORA9i.ora' from pfile='initORA9i.ora';

   위 문장에서 화일명 대신 직접 directory까지 절대 path로 지정할 수 있고,
   화일명은 임의로 지정 후 나중에 사용시 initSID.ora나 spfileSID.ora 형태로
   만들어줄 수 있다.
   
   default인 $ORACLE_HOME/dbs directory이고 SID가 붙는 이름 형태이면 간단히, 
   다음과 같이 지정하여도 된다.

      SQL>create pfile from spfile;
      SQL>create spfile from pfile;

   sysdba 권한이 없으면 권한 부족 오류가 발생한다.

4. alter system으로 parameter 변경 시 scope에 대해서

   spfile을 사용하게 되면 앞에서도 언급한 것과 같이 spfile을 직접 변경하는
   대신 alter system command를 통해 initial parameter를 수정할 수 있다.

   단 이때 alter system command 뒤에 scope를 지정할 수 있는 데 scope로 
   지정가능한 값은 memory/spfile/both 세가지가 된다.
   memory가 이중 default여서 scope를 지정하지 않으면 memory가 된다.

   memory: 변경이 현재 상태에만 영향을 미치며 db가 restartup되면,
           변경 이전값으로 돌아간다.
   spfile: 변경 내용을 spfile에만 저장하고 현재 상태에는 영향을 미치지
           않게 한다. 
           static parameter의 경우는 이 scope만이 지정가능하다.
           즉, spfile을 사용하더라도 static parameter에 대해서는 db 
           운영중에 바로 값을 변경하여 restartup없이 반영하는 것은 불가능
           한 것이다.
   both:   변경 내용을 현재상태에도 바로 반영하고, spfile에도 반영시켜,
           이후 rebooting시에도 영향을 미치도록 한다.

   지정하는 방법은 다음과 같다.

   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;

   단, spfile을 참조하지 않고 init.ora를 참조하여 db가 open한 경우 이러한
   alter system 명령을 통해 initial parameter를 변경하려고 하면 
   다음과 같은 오류가 발생한다.

   특히 spfile에 외부의 변경이 가해져 spfile이 참조되지 않은 경우에 주로
   다음 오류를 만날 수 있다.

      ORA-02095: specified initialization parameter cannot be modified 


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

   initial parameter의 종류에는 db가 운영 중에는 바로 그 값을 변경하여
   반영시킬 수 없고, 다음 startup 후에만 변경된 값이 영향을 주게되는
   parameter가 있다. 
   이러한 parameter를 static parameter라고 부른다.
 
   이 static parameter의 경우는 그래서 alter system으로 변경하더라도,
   scope=spfile 로만 지정 가능한 것이다.
   이러한 static parameter를 scope를 지정 안 해서 memory로 하거나 both로
   하게 되면 4번에서 설명한 ORA-2095 오류가 발생하게 된다.

   그럼 static parameter는 어떻게 확인할 수 있는가?

      SQL>col name format a45
      SQL>select name, issys_modifiable from v$parameter;

   여기에서 보면, issys_modifiable의 값으로 다음 세 가지가 나타난다.

   FLASE:     static parameter로 scope=spfile로만 값을 변경 가능하다.
              즉 값을 변경해도 이후 startup 시부터 영향을 미치게 된다.
   IMMEDIATE: 값을 변경하면 현재 session부터 바로 영향을 받게된다.
   DEFERRED:  변경된 값이 이후 접속되는 session부터 영향을 준다.

   
   alter system을 통해 parameter를 변경하는 경우 변경된 값이 반영이 잘
   되었는가를 확인하려면 다음과 같이 show parameter나 v$parameter를
   조회하고, 현재 반영은 안 되었더라도 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';

   즉, scope=spfile로 parameter를 변경한 경우는 v$spparameter에만 변경
   된 값이 나타나고, show parameter나 v$parameter에서는 변경 전 값이 
   나타나게 된다.