[DB Study Season2] 03.파일

서버를 찾는데 필요한 파라미터 파일 : tnsnames.ora, listener.ora, sqlnet.ora, cman.ora, ldap.ora등이 있다. 뒤에 두개는 처음 보는 파일이다.;;;
인스턴스를 시작하는데 필요한 파라미터 파일 : init<ORACLE_SID>.ora, spfile<ORACLE_SID>.ora가 있다.
인스턴스 파라미터 파일은 둘중하나만 있으면 되며, 기본으로는 spfile을 쓰지만 필자가 관리하는 업체에서는 spfile을 구경 할 수가 없었다.
왜냐하면 init<ORACLE_SID>.ora파일은 텍스트 편집기로 편집이 가능하기 때문이다. rman에서도 파라미터 파일을 포함하여 백업하기도 한다.

●파라미터 파일(PFILE)
key와 value가 하나의 쌍으로 구성된다.
예를 들어본다.
alter system set audit_trail=db_extended scope=spfile;
위에서 key=audit_trail 이며, value=db_extended이다.

모든 파라미터 조회는 show parameter or v$parameter테이블을 조회해도 된다.(일반유저는 조회가 안된다 당연한거지만)
11g에서는 344개의 파라미터 파일이 조회 되었다.
일반 유저는 성능 관련 뷰에 대한 조회 권한이 없다. 하지만 v$parameter의 집합을 볼수 있게끔 해보자.

create or replace
function get_param( p_name in varchar2 )
return varchar2
as
    l_param_type  number;
    l_intval      binary_integer;
    l_strval      varchar2(256);
    invalid_parameter exception;
    pragma exception_init( invalid_parameter, -20000 );
begin
    begin
        l_param_type :=
        dbms_utility.get_parameter_value
        ( parnam => p_name,
            intval => l_intval,
          strval => l_strval );
    exception
        when invalid_parameter
        then
            return '*access denied*';
    end;
    if ( l_param_type = 0 )
    then
        l_strval := to_char(l_intval);
    end if;
    return l_strval;
end get_param;
/

위 내용을 스크립트로 만들어서 scott계정에서 실행하자.
그리고 이 기능을 실행한다.
exec dbms_output.put_line( get_param( 'db_block_size' ) );
실행하면 파라미터 값이 조회된다. 값은 8192이며
exec dbms_output.put_line( get_param( 'pga_aggregate_target' ) );
실행하면 접근 거부된다.
메모리 관련 파라미터는 확인 할 수 없으며 제한이 있는 파라미터에 대해 조회를 해보자.

column name format a36
column val format a40
select name, scott.get_param( name ) val
  from v$parameter
 where scott.get_param( name ) = '*access denied*';

위 문장을 실행하면 24개의 행이 접근불가라고 나온다. 직접 확인해보자.
각 버젼별로 존재하는 파라미터 수가 다르며 버젼이 업됨에 따라 사라진 파라미터도 있다.
숨겨진 파라미터도 있지만 이 히든 파라미터는 oracle support의 직접적인 도움없이는 함부로 손대면 안된다.
부작용이 나타날수 있으며 개발환경에서는
_trace_files_public=true
한개의 파라미터만 설정해서 사용해보자. 이 파라미터는 모든 사용자가 읽을수 있는 트레이스 파일을 생성하게끔 해준다.
하지만 이 파라미터는 보안목적상 true로 변경하면 안된다.

레거시 init.ora 파라미터 파일
init.ora파일은 샘플에 가깝다. 이 파일로는 인스턴스를 시작할수 없을거다.
인스턴스를 초기에 시작할때 8~9개의 파라미터 값만 있으면 기동이 된다. 물론 운영하면서 파라미터 값은 늘어날것이고 그 파라미터 값을
잘 기억해야 한다. 한마디로 백업을 하라는 뜻이다.
OS에 따른 파라미터 파일 명칭이다.
Unix : init$ORACLE_SID.ora
Windows : %ORACLE_SID%.ora
기본값(위치)
Unix : $ORACLE_HOME/dbs
Windows : %ORACLE_HOME%\database
인스턴스를 가동할 때 startup 명령어의 옵션으로 pfile=filename 즉
startup pfile='/home/oracle/initPROD.ora'
위와 같이 옵션 사용이 가능하다.
●서버 파라미터 파일 (SPFILE)
spfile은 데이타베이스 서버에 저장되고 서버안에 존재해야 하며 파라미터 설정과 관련하여 단일 소스를 갖는데 필요하다.
데이타베이스 바깥의 파라미터 파일을 텍스트 편집기를 이용한 수작업 관리가 필요없다.(바이너리 파일이기 때문에 편집하면 깨진다.)
파일에 대한 초기 설정
Unix : $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
Windows : %ORACLE_HOME/database/spfile%ORACLE_SID%.ora
spfile로 변환하기
DB를 수동으로 생성했다면 pfile이 있을 것이고 DBCA를 사용해서 생성했다면 spfile이 있을것이다.
show parameter spfile을 실행했을때
value가 빈칸이면 DB는 pfile로 구동된것이다.
그럼 pfile로 spfile을 생성해보자.
create spfile from pfile;
sysoper계정은 spfile을 생성할수 있는 최소권한을 가진 계정이다.(v$뷰에 접근할수 없다.)
show parameter spfile 이라고 명령어를 입력했을때 값이 나오면 spfile, 안나오면 pfile이다.
* RAC를 사용하는 cluster환경에서는 모든 인스턴스가 하나의 spfile을 공유하므로 시스템에서 허용하는 형식을
사용하는 하나의 pfile로 병합해야 한다.

SPFILE값 설정하기
alter system set parameter=value comment=text deffered scope=spfile,memory,both sid=sid | *

 - parameter=value : sga_target=1g (sga_target값을 1g로 설정한다.)
 - comment=text : 옵션이다. v$parameter의 update_comment에 나타난다.
 - deffered : 현제 세션이 아닌 다음 세션 부터 적용
어떤 파라미터가 deffered옵션을 적용하게 되는지 확인해보자.
select name from v$parameter
where issys_modifiable='DEFFERED';

이 중에서 sort_area_size를 예를 들어보자.
alter system set sort_area_size=65536;
에러가 뜬다.
alter system set sort_area_size=65536 deffered;
변경은 되나 차후에 적용된다.

 - scope는 3가지가 있다.
scope=memory : 인스턴스 내에서 즉시 변경이 가능하고 DB재시가 후에는 이전값으로 돌아간다.
scope=spfile : spfile의 값을 변경한다. DB를 재시작해야만 값이 적용된다.
scope=both : spfile에 기록하고 동시에 적용한다.
 - sid=sid | * : cluster환경에서 사용하며 sid=*이 기본값이다. RAC환경이 아니면 설정할 필요없다.

parameter값이 언제 왜 변경되었는지 기록하는 comment를 사용해보자.
select name, value, update_comment
from v$parameter
where name='pga_aggregate_target';

alter system set pga_aggregate_target=256m
comment='AWR recommendation';

다시 조회해보자.
select name, value, update_comment
from v$parameter
where name='pga_aggregate_target';

spfile에서 설정 값 되돌리기
alter system 명령어에서 reset절을 사용하면 된다.
alter system reset parameter=value scope=both | memory | spfile sid='sid | *';

앞서 변경한 sort_area_size값을 기본값으로 되돌리자.
alter system reset sort_area_size scope=spfile;
삭제되었는지 확인해보자.
SQL>conn / as sysoper
SQL>create pfile='/tmp/pfile.tst' from spfile;

spfile을 이용하여 pfile생성하기
create pfile from spfile;
위 명령어로 간단히 생성할수 있다.
이렇게 생성된 파일은 텍스트 파일이며 수정이 가능하고 DB가 재시작된 이후에 적용된다.
그리고 다음 두 가지 목적으로 사용된다.
 - 일회용 파라미터 파일 생성, 변경된 값을 테스트하기 위해 만든 pfile의 결과값을 편집한다.
명시적으로 pfile=<file_name>옵션을 사용하여 DB를 기동하고 작업이 끝나면 정상적으로 spfile로 DB를 기동한다.
 - 주석이 달린 변경 사항에 대한 이력 관리 하기
SQL>conn / as sysdba
SQL>create pfile='init_14_SEP_2011.ora11g.ora' from spfile;
SQL>alter system set pga_aggregate_target=256m comment='AWR recommendation';

훼손된 spfile복구
spfile은 binary파일이기 때문에 편집할수 없다. 다른 파일과는 달리 잘 훼손될 일이 없다.
복구하는 방법은 2가지다.
- Unix일때
~~~]$ strings spfile$ORACLE_SID.ora
....................
 - Windows 일때
write.exe(wordpad)을 열고, 파일 안에 있는 텍스트를 확인해서 인스턴스를 기동할 때 사용할 수 있는pfile을 생성하기 위하여
허용되는 init<ORACLE_SID>.ora안에 붙여넣기 할 수 있다.
또는 alert.log파일의 파라미터 정보를 사용해서 복구 할 수 있다.

Trace File
Trace File은 DB내에서 발생하는 다양한 정보를 디버깅하기 위한 소스역활을 하는 파일이다.
DBMS_MONITOR.SESSION_TRACE_ENABLE을 실행하면 DB 성능 이벤트 관련 트레이스 파일을 생성한다.
다음을 보면 계측화 되었음을 알 수 있다.
V$ views : V$view는 디버그 정보를 포함하고 있다.(v$waintstat, v$session_event)
AUDIT 명령어 : 사후 분석을 위해 DB에 어떤 이벤트를 기록할지를 지정
Resource_manager(DBMS_RESOURCE_MANAGER) : CPU, I/O등 DB자원을 세밀하게 제어하는 기능이다.
실시간 통계조회를 통해 알 수 있다.
oracle_event : 필요할 때 오라클이 트레이스를 생성하게 하거나 진단정보를 생성할 수 있도록 지원
DBMS_TRACE : PL/SQL엔진에 포함된 이 기능은 저장 프로시저 호출 트리구조, 예외 발생, 오류 발생 빠짐없이 기록한다.
Database event trigger : on servererror와 같은 트리거들은 예외사항이나 비정상적인 부분을 모니터링하고 기록(log)을 남긴다.
SQL_Trace/DBMS_MONITOR : Application실행에 의해 생성된 SQL및 대기 이벤트, 기타 성능 진단 관련
정보를 볼 수 있다. SQL_Trace기능은 10046 오라클 이벤트를 이용해서 확장된 기능을 사용할 수 있다.
Trace파일에는 두 종류가 있다.
 - DBMS_MONITOR.SESSION_TRACE_ENABLE로 생성된 파일은 세션에 대한 진단정보를 담고 있으며, Application의
성능을 향상시키는데 필요한 정보를 담고 있다.
 - 서버가 ORA-00600, ORA-03113, ORA-07445의 에러로 인해 트레이스 파일이 생성될 수 있다.

TRACE File 요청
DBMS_MOMITOR(oracle9iR2:alter system set sql_trace=true)의 트레이스 기능을 활성화하여 트레이스 파일을 생성하거나
10046이벤트를 통해 확장된 트레이스 기능을 사용해보자.
alter session set event '10046 trace name context forever, level 12';
이 트레이스 파일들은 DB진단 및 기능과 관련된 다양한 정보를 담고 있으며 DB Application이 내부에서 어떻게 작동하는지에
대해 추적할 수 있게 해준다.

파일 위치
 - dedicated server : user_dump_dest의 파라미터에 정의된 위치에 생성
 - shared server : background_dump_dest의 파라미터에 정의된 위치에 생성

트레이스 파일이 생성되는 위치를 알아보자.
show parameter dump_dest라고 입력을 하면 위치가 나온다.(설정되었다면...)
background_dump_dest : 서버 프로세스가 사용
core_dump_dest : 프로세스 중단과 같은 심각한 문제가 발생했을 때 사용
user_demp_dest : dedicated server가 트레이스 파일을 생성하기 위해 사용한다.

v$parameter 조회
select name, value from v$parameter where name like '%dump_dest%';

v$parameter를 조회하기 위해서 scott.get_param function의 내장 프로시져 DBMS_UTILITY 패키지를 사용해보자.
set servetoutput on
exec dbms_output.put_line(scott.get_param('user_dump_dest'));

oracle11g에 추가된 새로운 기능 - ADR(자동 진단 저장소 : v$diag_info)
v$diag_info를 조회해보자.

with home
as
(select value home from v$diag_info where name='ADR HOME')
select name, case when value <>home.home
then replace (value,home.home,'$home$')
else value

end value
from v$diag_info,home
/

 - Diag Trace(oracle11g) : background dump와 user dump를 모두 포함한 트레이스 파일 정보
 - Default Trace File : 현제 세션에 대한 트레이스 파일명을 의미

ora11gr2_ora_1990.trc - oracle11gr2
[ORACLE_SID]_ora_PROCESSID.trc

oracle11g이전에서는 4개의 view를 조회해야 한다.
v$parameter : user_dump_dest의 위치를 사용하는 트레이스 파일 확인 및 tracefile_identifier를 확인하는데 사용
v$process : 프로세스 ID 찾는데 사용
v$session : 세션 각각에 대한 정확한 정보를 얻을때 사용
v$instance : ORACLE_SID조회하는데 사용

트레이스 파일을 생성하기 위한 쿼리
column trace new_val T
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' || 

case when e.value is not null then '_' || e.value end trace
from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e
where a.addr=b.paddr
and b.audsid=userenv('sessionid')
and c.name='user_dump_dest'
and e.name='tracefile_identifier';
/
trace file을 생성해보자.
SQL>!ls &T
SQL>exec dbms_monitor.session_trace_enable
SQL>!ls &T
oracle9iR1에서는 다음과 같이 하면된다.
select c.value||'_ora'||s.pid||'.trc'

트레이스 파일에 꼬리표 달기
v$process와 v$session에 접근 권한이 없다면 user_dump_dest폴더를 읽을 수 있을때
세션 파라미터인 tracefile_identifier를 이용하면 된다.
alter session set tracefile_identifier='KTO';
!ls /home/oracle~~~/orcl?(확인)
...........
exec dbms_monitor.session_trace_enable
!ls /home/oracle~~~/orcl?(확인)

내부 오류를 담기위해 생성된 트레이스 파일들
ORA-00600 오류로 생성된 트레이스 파일은 분석이 힘들지만 DB를 진단하기 위해 매우 유용한 파일이다.
예제를 통해 에러를 만들어보자.
create table t(x int primary key);
insert into t values (1);
exec dbms_stats.gather_table_stats(user,'T');
select count(x)  over() from t;
이렇게 해서 생성된 파일은 우리가 분석할수도 할필요도 없으므로 서비스 요청하는곳에 전송해주자.
oracle11g에서는 ADRCI라는 tool로 문제를 조사한후 묶어서 oracle support로 전송하면 된다.
~~~]$adrci
show problem
--->show incident(에러가 어떤 영향을 미쳤는지 확인해보자)
--->show tracefile -I incident_ID
--->show incident -mode detail -p 'incident_id=xxxx'
oracle support로 보낼 패키지를 생성
ips create package incident xxxx




Log)

내용	데이터베이스에 대한 모든 이력 기록 - 로그스위치, 오류, 테이블스페이스 생성, 온오프라인 시간 등
위치	$ORACLE_HOME/admin/orcl/bdump/alert_orcl.log (10g) c:/app/PARK/diag/rdbms/orcl11/orcl11/trace/alert_orcl11.log (11g)
형태	텍스트 파일

■ 예제 : Alert File을 테이블로 저장

SQL> create or replace directory data_dir as 'c:/oracle/product/10.2.0/admin/orcl/bdump/'; SQL> CREATE TABLE alert_log ( text_line varchar2(255) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( records delimited by newline fields REJECT ROWS WITH ALL NULL FIELDS ) LOCATION ( 'alert_orcl.log' ) ) REJECT LIMIT unlimited /

■ 예제 : alert log 정보 조회

SQL> select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown, to_char(start_time,'dd-mon-yyyy hh24:mi') startup, round((start_time-last_time)*24*60,2) mins_down, round((last_time-lag(start_time) over (order by r)),2) days_up, case when (lead(r) over (order by r) is null ) then round((sysdate-start_time),2) end days_still_up from ( select r, to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time, to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time from ( select r, text_line, lag(text_line,1) over (order by r) start_time, lag(text_line,2) over (order by r) last_time from ( select rownum r, text_line from alert_log where text_line like '___ ___ __ __:__:__ 20__' or text_line like 'Starting ORACLE instance %' /* 아래와 같은 패턴을 찾음 Thu Sep 15 20:00:31 2011 Starting ORACLE instance (normal) */ ) ) where text_line like 'Starting ORACLE instance %' ) /

■ alert log 참고사항

- alert 로그에 관한 유용한 정보 : http://tinyurl.com/y8wkhjt

- ADRCI 툴 이용 - Oracle 11g command line 도구

- Enterprise Manager 사용하여 alert log 중요정보 검색

Password File

■ Password File

내용	원격에서 SYSDBA로 접근할 수 있는 사용자들에 대한 패스워드 파일
위치	$ORACLE_HOME/database/PWDorcl.ora (10g, 11g)
형태	바이너리 파일
	remote_login_password 환경파일 값 = (NONE, EXCLUSIVE, SHARED) $ORACLE_HOME/dbs/init.ora 에 초기값 설정되어 있음.

■ 사용방법

1) 초기 사용환경설정(exclusive 모드 - 하나의 데이터베이스에서만 사용)

alter system set remote_login_passwordfile=exclusive scope=spfile;

2) orapwd 명령어로 파일생성

$orapwd file=orapw$ORACLE_SID password=bar entries=20

 

3) 원격접속

$ sys/bar@ora11gr2 as sysdba

4) sys 가 원격사용자에게 권한을 부여하면 SYSDBA로 접속가능

grant sysdba to ops@kyte

변경추적 File

■ change-tracking File

내용	incremental backup 이후 수정된 블록을 추적하는 내용 RMAN(Recovery Manager) 툴이 이 파일을 이용하여 백업
위치	(alert 명령으로 위치지정하여 생성) alert database enable block change tracking using file '/tmp/changed_blocks.bct';
형태	이진 파일

■ 설명

- 변경추적을 중지하고 파일을 지우는 명령어

alter database disable block change tracking;