init<SID>.ora와 pfile,spfile의 차이..

init_<SID>.ora가 pfile입니다.
pfile과 spfile에 대해서 간단히 설명하면 다음과 같습니다.

PFILE (Parameter FILE):

 

 - 8i 이전까지 text file형태로 database initialization parameters를 저장된다.
 - 일반적으로 $ORACLE_HOME/dbs/init_.ora 로 저장된다.
 - 인스턴스가 기동될때 이 pfile을 읽어서 데이터베이스에 적용된다.
 - 인스턴스 기동중 ALTER SYSTEM 이나 ALTER SESSION으로 parameter를 변경할 수
   있으나, 인스턴스를 재기동될 때는 pfile에 저장된 값으로 다시 적용된다.
   따라서, 완전히 파라메터를 변경하고자 할 경우 pfile을 수정해야 한다.

SPFILE (Server Parameter FILE):

 

 - 9i부터 도입된 방식으로 binary file 형식으로 database initialization
   parameters를 데이터베이스에서 관리하는 방식이다.
 - 인스턴스가 기동될 때 pfile이 아닌 spfile을 참고하여 데이터베이스에
   적용된다.
 - 최초 적용시에는 아래와 같이 pfile을 읽어 spfile을 만든다.

          CREATE SPFILE='/u01/oracle/product/920/dbs/racdb_spfile.ora'
                 FROM PFILE='/u01/oracle/product/920/dbs/init_TEST.ora';

 - 이후, 파라메터 변경시에는 ALTER SYSTEM 명령어를 이용하여 변경한다.
 - spfile의 장점은, RAC 구성시 공유 디스크에 spfile을 설정해 두면 한번의
   파라메터 변경으로 모든 인스턴스가 동일한 파라메터로 적용된다. 

  필요에 따라, pfile이나 spfile  어떤 방식으로도 적용이 가능합니다.

 

SPFILE 사용법

[개념]

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

[설명]

이 문서에서는 이러한 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 로만 지정 가능한 것이다.
   이러한 sttic parameter를 scope를 지정 안해서 memory로 하거나 both로
   하게 되면 4번에서 설명한 ORA-2095 오류가 발생하게 된다.

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

      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$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';

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

     

? ORACLE 9I 인스턴스 구동을 위한 SPFILE 및 INIT.ORA 파라미터 파일

Oracle 9i 이전 버젼에서는 오라클 인스턴스는 텍스트 파일인 init<SID>.ora 파일
을 이용하여 구동되었으며, 이 파일은 기본적으로 $ORACLE_HOME/dbs 디렉토리 아래
위치하였다.

Oracle 9i에서는 SPFILE이라는 것이 새로 추가 되었는데, DB 서버 내에 저장되는
바이너리 파일이다. 인스턴스에 적용하고자 하는 파라미터값의 변동 사항이
인스턴스를 내리거나 구동시킬때마다 계속해서 적용될 수 있게 할 수 있다.

Oracle 9i에서는 인스턴스에 적용시킬 파라미터를 spfile 또는 init.ora 파일에
저장시킬 수 있다.

Oracle 9i 인스턴스 구동시 참조하는 파일
-  기본 spfile --> spfile<SID>.ora
-  기본 init.ora 파일 --> init<SID>.ora
-  init.ora 파일을 인스턴스 구동시 별도로 지정
-  spfile 을 인스턴스 구동시 별도 지정

위 초기화 파일들의 기본 위치는 다음과 같다.
- UNIX --> $ORACLE_HOME/dbs
- Windows NT / 2000 --> %ORACLE_HOME%\database

다음 예제는 Oracle 9i를 구동시키는 여러 방법을 보여주고 있다.

1. 초기화 파일 지정을 하지 않을 경우

   sqlplus /nolog
   SQL> connect / as sysdba
   SQL> startup

  오라클에서는 먼저 spfile<SID>.ora 파일이 존재하는지 찾아보고없을 경우에는 spfile.ora 파라미터 파일을 찾는다.
두개 파일이 모두 존재하지 않을 경우, init<SID>.ora 파라미터파일을 찾는다. 
만약 init<SID>.ora 파일도 존재하지 않는다면오라클에서는 다음과 같은 에러가 발생한다.

   SQL> startup
   ORA-01078: failure in processing system parameters
   LRM-00109: could not open parameter file
   'D:\ORA901\DATABASE\INITORA901.ORA'

2. init.ora 파일의 지정
   인스턴스 구동시 init.ora 파일을 명시적으로 지정하는 방법은 이전 버젼과 동일하다.

   SQL> startup pfile=d:\ora901\database\initORA901.ora
   ORACLE instance started.

   Total System Global Area  118255568 bytes
   Fixed Size                   282576 bytes
   Variable Size              83886080 bytes
   Database Buffers           33554432 bytes
   Redo Buffers                 532480 bytes
   Database mounted.
   Database opened.

   유의해야 할 점은 spfile 은 init.ora 파일과 같이 인스턴스 구동시
   동적으로 지정할 수 없다는 것이다. 만약 spfile을 명시적으로 지정
   할 경우 다음과 같은 에러가 발생한다.

   SQL> startup spfile=d:\ora901\database\spfileORA901.ora
         SP2-0714: invalid combination of STARTUP options

  데이터베이스를 init.ora 파일을 이용하여 구동하였을 경우 spfile 파라미터 값은 
공백으로 남아 있게 된다.

     NAME                              TYPE        VALUE
spfile                              string

3. 사용하고자 하는 spfile의 지정

   사용하고자 하는 spfile을 지정하고자 할 경우에는 init.ora 파일을 사용하여야 하며, init.ora 파일에는 spfile의 절대 경로만 지정되어 있어야 한다.

   spfile=d:\ora901\database\spfiletest.ora

   SQL> startup pfile=d:\ora901\database\inittest.ora
   ORACLE instance started.

   Total System Global Area  122449892 bytes
   Fixed Size                   282596 bytes
   Variable Size              88080384 bytes
   Database Buffers           33554432 bytes
   Redo Buffers                 532480 bytes
   Database mounted.
   Database opened.

   이 경우에는 SQL*Plus에서 인스턴스 구동 시 사용된 spfile을 
   다음과 같이 확인해 볼 수 있다.

   SQL> show parameter spfile

   NAME                           TYPE        VALUE
spfile                           string       d:\ora901\database\spfiletest.ora

   사용 중인 spfile의 내용은 다음과 같이 지정 가능하다.

   SQL>alter system set processes = 200 scope=spfile;

        System altered.

   SQL> select name, value from v$spparameter
         where name = 'processes';

   NAME            VALUE
   processes         200

 

 

    
  
No. 18660

HOW TO SET EVENTS IN THE SPFILE
===============================

PURPOSE
 
  이 자료는 oracle 9i부터 제공되는 SPFILE을 사용할 때 trace와 debug
  event를 설정하는 방법을 기술하고 있다.

How to Set EVENTS in the SPFILE:
================================
 
  기존에 pfile을 사용할 때에는 pfile을 text editor를 사용하여 직접 편집하여
  event를 설정하였다. 그러나 oracle 9i의 SPFILE은 binary file이므로 이러한
  방법으로 event를 설정할 수 없다.  대신 'ALTER SYSTEM' 명령을 사용하여
  이러한 event를 설정할 수 있다.
 
  

 1) 아래는 PFILE에 설정할 수 있는 event에 대한 예제이다.

      event='10325 trace name context forever, level 10'
      event='10015 trace name context forever, level 1'

      event설정들 사이에 다른 parameter들이 기술되면 가장 마지막 event만이
      적용된다는 사실을 주의하자.

 2)
    a) SPFILE에 event를 설정할 수 있는 유일한 방법은 SQL interface를 이용하는
       것이다.
         
      SQL> ALTER SYSTEM SET
            EVENT='10325 trace name context forever,
                   level 10:10015 trace name context forever, level 1'
            COMMENT='Debug tracing of control and rollback' SCOPE=SPFILE;
 
      System altered.

      위에서 설정된 EVENT가 활성화 되려면 instance를 restartup해야 한다.   

    b) 이와 같이 SQL interface를 통하여 event를 설정하려면 instance는 반드시
       SPFILE과 함께 startup되어 있어야 한다. 만약 그렇지 않다면 다음과 같은
       Error message를 만나게 된다.
   
      SQL> ALTER SYSTEM SET
           EVENT='10325 trace name context forever,
           level 10:10015 trace name context forever, level 1' 
           COMMENT='Debug tracing of control and rollback' SCOPE=SPFILE;
                                                           ^^^^^^^^^^^^^
      ALTER SYSTEM SET
      *
      ERROR at line 1:
      ORA-32001: write to SPFILE requested but no SPFILE specified at startup

  c) Active instance에 SQL interface를 통해여 직접 EVENT를 적용할 수는 없다.
       EVENT는 SQL interface를 통하여 SPFILE에만 적용될 뿐 현재의 active instance에
       는 적용될 수 없다. SPFILE에 적용된 event를 활성화하려면 DB를 반드시
       restartup 해야한다.

      SQL> ALTER SYSTEM SET
           EVENT='10325 trace name context forever,
           level 10:10015 trace name context forever, level 1' 
           COMMENT='Debug tracing of control and rollback' SCOPE=BOTH ;
                                                           ^^^^^^^^^^
      EVENT='10325 trace name context forever,
      *
      ERROR at line 2:
      ORA-02095: specified initialization parameter cannot be modified

   d) 이 명령은 NOMOUNT 상태의 instance와 함께 수행될 수 있다.
   
   e) 여러개의 EVENT가 기술될 때는 한 Line안에 모두 기술하며
      ':' 를 사용하여 구분짓는다.

 3) 모든 event를 제거하기 위해서는 다음과 같은 방법을 사용한다:
     
      SQL> ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ;

      System altered.

    '*' 은 RAC(Real Application Cluster)환경에서의 instance name임

 4) Dumping과 Tracing을 위해 즉시 event를 설정하려면 ALTER SESSION명령을
    사용해야 한다.
 
      SQL> ALTER SESSION SET EVENTS='immediate trace name controlf level 2' ;

      System altered.

[출처] init<SID>.ora와 pfile,spfile의 차이..|작성자 까망

9i 에서 sga_max_size 의 경우 다음과 같다.

SQL> select name, issys_modifiable from v$parameter where name = 'sga_max_size';

NAME                                 ISSYS_MOD
---------------------------------------------------------------- ---------
sga_max_size                             FALSE

1 row selected.

즉, scope=both 나 memory 가 안된다는 사실.