231211 Linux_초기 파라미터 파일, spfile, pfile

■ 초기 파라미터 파일(initialization parameter file)
show paramter spfile

SQL> show paramter spfile

1. 서버 파라미터 파일(SPFILE)
- 일반적으로 사용되는 초기 파라미터 파일 유형
- 오라클 서버(내부적)읽고 쓸 수 있는 이진파일(binary file)이 편집기를(vi) 수동으로 편집해서는 안된다.
- 이 파일은 오라클이 실행되는 서버에 있으며 종료 및 시작과 관계없이 계속 유지된다.
- 기본이름은 spfile<SID>.ora (자동으로 생성)

2. 텍스트 초기 파라미터 파일(PFILE)
- 텍스트 편집기(vi)를 사용하여 수동으로 편집한다.
- 오라클 DB를 open 한 후에 수동으로 편집하더라도 변경한 값을 인식할 수 없다. (restart 해야 적용.)
- DB 를 처음 open 시점에 한번만 access 한다.(실시간 X)
- 기본이름은 init<SID>.ora

# 순서는 startup nomount 단계에서 $ORACLE_HOME/dbs 디렉터리에서 spfile<SID>.ora 를 먼저 찾고 없으면 init<SID>.ora 를 찾는다. 둘다 없으면 오류발생한다

SQL> show parameter instance_name

# parameter 값 변경 
1. static parameter (=:고정된 파라미터)
- 초기 파라미터 파일에서만 변경해야 한다.
- instance 를 재시작해야 적용 된다.
- 시스템 레벨 (ALTER SYSTEM SET), DBA 작업
# SQL

SELECT * FROM v$parameter WHERE issys_modifiable = 'FALSE';

2. dynamic parameter (=:운영중인 파라미터)
- database 온라인 상태인 동안에 변경 가능하다.
- 다음 레벨에서 수정가능하다.
- 세션 레벨 (ALTER SESSION SET), 일반 유저 가능
- 시스템 레벨 (ALTER SYSTEM SET), DBA 작업
- session 기간 또는 scope 설정에 따른 기간 등 유효할 수 있도록 설정
# SQL

SELECT * FROM v$parameter WHERE isses_modifiable = 'TRUE' OR issys_modifiable = 'IMMEDIATE';

# session level 수정할 수 있는 파라미터
- 현재 내 session에만 영향을 준다.

SELECT name, value FROM v$parameter WHERE isses_modifiable = 'TRUE';

# 출력 날짜

select sysdate from dual;
ALTER session set nls_date_format = 'YYYY-MM-DD';
select sysdate from dual;

# dynamic parameter  ex) nls_date_forma

SELECT * FROM v$parameter WHERE name = 'nls_date_format';

# SYSTEM LEVLE :오라클에 접속하는 모든 유저에게 영향을 주는 작업(주의요망)

문법 :

ALTER SYSTEM SET 파라미터 = 값 SCOPE(범위) = ;

# issys_modifiable = 'FALSE' 일 경우
1) SCOPE = SPFILE

SELECT * FROM v$parameter WHERE issys_modifiable = 'FALSE'; 확인

SCOPE = SPFILE
- 변경 사항이 서버 파라미터 파일(SPFILE<SID>.ora)에만 적용되면 현재 instance 에는 변경되지 않는다.
- 변경한 값으로 인식되려면 오라클을 재시작해야 한다. (정상적인 종료 후 다시 open)
- dynamic parameter와 static parameter 모두 변경사항을 다음 시작시에 영구적으로 적용한다.
- static parameter는 scope=spfile 만 설정해야 한다.


SQL> show parameter processes

processes integer 150 : 동시접속가능한 users

#SQL

select * from v$parameter where name='processes'; #SCOPE 설정
ALTER SYSTEM SET processes = 200; # 오류발생
ALTER SYSTEM SET processes = 200 scope=spfile; # 바로 적용이 되지 않는다. 
select * from v$parameter where name='processes';

2) SCOPE = MEMORY
- 변경 사항이 메모리에만 적용한다. 
- 현재 instance 가 변경되고 변경사항이 즉시 적용된다.(실시간 적용)
- 현재만 바꾸고 DB 재시작하면 변경값은 기본값으로 설정된다.(재시작하면 rollback 기능과 비슷)
- 서버 파라미터 파일에는 적용되지 않는다.
  - dynamic parameter 경우 사용가능.
- static parameter 는 불가하다.
# SQL

SELECT * FROM v$parameter WHERE isses_modifiable = 'TRUE' OR issys_modifiable = 'IMMEDIATE';

3) SCOPE = BOTH
- 파라미터의 변경값을 현재 메모리에도 적용되고 서버 파라미터 파일(spfile<SID>.ora)에도 적용한다.
- dynamic parameter 경우 사용가능.(보편적으로)
- static parameter 는 불가하다.
#SQL

select * from v$parameter where issys_modifiable = 'IMMEDIATE';

# DEFERRED : 시스템변경이 현재 SESSION 이 아닌 다음 SESSION 부터 적용된다

ALTER SYSTEM SET 파라미터=값 DEFERRED SCOPE=BOTH(기본값);

- 문장튜닝시, sort 작업할 때 사용한다.

# SQL

SELECT * FROM v$parameter WHERE isses_modifiable = 'TRUE' OR issys_modifiable = 'DEFERRED';

# [oracle@oracle trace]$ tail -F alert_ora11g.log
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TU          NING_TASK"
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNI          NG_TASK"
Sun Dec 10 19:35:29 2023
Thread 1 cannot allocate new log, sequence 10
Private strand flush not complete
  Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
Thread 1 advanced to log sequence 10 (LGWR switch)
  Current log# 1 seq# 10 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
Sun Dec 10 20:45:53 2023
ALTER SYSTEM SET processes=200 SCOPE=SPFILE;
#SQL> ALTER SYSTEM SET sort_area_size = 1048576 DEFERRED;
Sun Dec 10 21:41:49 2023
ALTER SYSTEM SET sort_area_size=1048576 DEFERRED SCOPE=BOTH;

# pfile
SQL> create pfile from spfile; => pfile 생성

[oracle@oracle dbs]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat  initora11g.ora(pfile)  orapwora11g                spfileora11g.ora
init.ora       lkORA11G        spfileora11g_20231208.bak
[oracle@oracle dbs]$ cat initora11g.ora

ora11g.__db_cache_size=297795584
ora11g.__java_pool_size=4194304
ora11g.__large_pool_size=8388608
ora11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=285212672
ora11g.__sga_target=427819008
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=104857600
ora11g.__streams_pool_size=4194304 
*.audit_file_dest='/u01/app/oracle/admin/ora11g/adump' : 감사파일
*.audit_trail='db'
*.compatible='11.2.0.4.0' : 오라클버전
*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl','/u01/app/oracle/fast_r_area/ora11g/control02.ctl' :위치
*.db_block_size=8192 :8k
*.db_domain=''
*.db_name='ora11g'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4353687552
*.diagnostic_dest='/u01/app/oracle' : 진단
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)' :네티워크
*.memory_target=710934528 : 총 size
*.open_cursors=300 : 한 세션당 cursor 갯수
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE' : user 비번
*.sort_area_size=1048576
*.undo_tablespace='UNDOTBS1' 

# pfile 로 시작한 DB
SQL> show parameter spfile : spfile인지 pfile 인지 확인하는 방법

# pfile을 이용해서 spfile 생성
- 수동으로 DB 생성할 때
SQL> create spfile from pfile;

- dbca를 이용하지 않고 수동으로 db 생성시에는 pfile을 만들고 db 생성해야 한다.
[oracle@oracle dbs]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat  initora11g.ora  orapwora11g                spfileora11g.bak
init.ora       lkORA11G        spfileora11g_20231208.bak  spfileora11g.ora(spfile)
SQL> show parameter spfile