자수성가한 부자

set linesize 200
set pagesize 40
set sqlprompt "_user'@'_connect_identifier _privilege>"

현재 내가 접속하고 있는 세션과 db의 timezone을 확인할 수 있다.


현재 시각을 구하는 함수


현재 세션의 시간 표시를 바꾸는 SQL



extract함수
: 날짜에서 특정 부분을 추출. to_char와는 반환하는 타입이 다름

● Backup 의 분류
  ◎ logical backup
      : export, datapump

  ◎ physical backup
      : os명령, RMAN + OSB(Oracle Secure Backup, 10g R2 new feature)
      - 컨트롤 파일을 읽고 -> 백업을 함
         - > datafile과 백업을 마치고 각 파일의 위치와 정보를 control파일에 기록

아래는 RMAN사용법(신뢰성있는 백업이 가능하게 함)


OSB를 이용하면 오라클 관련 파일뿐 아니라 그 외의 파일들도 백업이 가능하게 됨.

● 용어정리
  ◎ full backup      : 데이터가 들어있는 블럭만 백업    { <-> incremental backup }
  ◎ whole backup : 전체백업                                  { <-> partitial backup }
  ◎ offline backup : 정상 shutdown 후 백업. 모든 동기화 정보가 맞음.


  ◎ online backup : shutdown하지 않고 백업. 유저들이 좋아할만한 백업.





● recovery
   ◎ open이 되려면 최소한 갖춰야 하는 요건
      - 모든 control file이 정상이어야 한다.
      - 모든 online datafile이 정상이어야 한다.
      - 리두 로그 파일은 최소한 멤버 1개는 정상이어야 한다.

● flashback
    - backup & recovery에 비해서 빠르다.
    - 변경된 데이터만 복구
    - 유저의 실수에 대해서만 복구가 가능.
   ◎ 종류
       1. flashback database
           - backward, forward 둘다 가능
           - 유저들이 여기저기에 많은 문제를 발생시켰을 때 사용.
           - 언두가 내려쓰는 특정 시점으로 되돌린다음 세세한 내용은 리두로 되돌림
           - 리두는 작업하나하나가 내려써짐
           - 언두는 특정기간(15분 등) 마다 한번씩 내려써짐
           - 평상시에 플래쉬백 로그가 남도록 설정해야 함.
           - mount상태에서 가능함.

       2. flashback table
           - 특정한 시점으로 테이블을 복구한다.
           - 추가적인 공간이 필요없다.
           - 언두 테이블 스페이스에서 데이터를 가져온다.
           - 시스템 테이블에 대해서는 불가능

       3. flashback versions query
           - row level의 플래쉬백

       4. flashback transaction query
           - row level의 플래쉬백


※ os 명령을 이용한 backup은 신뢰할만한 backup이 아님 -> RMAN을 이용할 것.

rollup 
  : 컬럼 갯수가 n개  =>  결과 집합 갯수가 n+1개
    컬럼의 나열 순서가 중요 (결과에 차이가 있으므로)
    8i부터 사용가능
    아래의 예는 3가지 (deptno, job), (deptno), () 아래의 grouping sets와 같다.





cube
 : 컬럼 갯수가 n개 => 결과 집합 갯수 2^n개
   8i부터 사용가능
   위의 예와 아래의 예는 같은 결과를 나타냄




cf) grouping 함수
     : 0과 1의 값만 반환하는 함수로 rollup과 cube와 같이 사용함.
       특정 컬럼을 이용한 그룹핑 결과인지 판단할 때 쓰인다.
       0일 경우 그 컬럼의 그룹핑 결과, 1일 경우 그룹핑 결과가 아님..
 



grouping sets
 : 지정한 컬럼으로 그룹을 짓는 함수. 원하는 그룹핑 결과만 도출할 수 있다.
   rollup과 cube의 단점을 보완하기 위해서 9i부터 나온 기술
   사용예는 위의 rollup과 cube의 예제를 참고할 것.


기타 참고사항

- rollup과 cube의 단점
   : 너무 많은 결과를 나오게 한다.

- excel과 비교시 dbms의 장점과 단점
   장점 : 여러 사람이 사용할 수 있도록 제공
            복원과 복구가 쉬움.
   단점 : 사용하기 어려움으로 교육이 필요. 
            통합 관리로 중앙 부분에 문제가 생기면 전체적으로 사용이 불가능

한번에 쿼리로 여러 개의 테이블에 데이터를 입력한다.

실습을 함으로써 이해해 볼 수 있다.

● Backup (복사)
  ◎ logical backup
      : export utility(~9i), data pump(10g~)를 이용 
        널리 사용되지만, 완벽한 backup은 아님
        open상태에서만 가능

   ◎ physical backup
      : OS명령, RMAN(Recovery Manager)
        open, closed


● Restore (복원)
    : 손상된 파일 대신 Backup해둔 파일을 재위치 시키는 것.


● Recovery (복구)
    : Restore + Redo적용
      완전 복구(complete)       : 있는 redo를 모두 적용
      불완전 복구(incomplete) : 특정 시점까지만 redo를 적용


● failure의 유형
    1. statement failure
    2. user process failure -> pmon이 정리, sqlnet.expire_time=n (비정상 종료를 빨리 찾게 하는 기능)
    3. network failure -> 장비 또는 tnsnames.ora, listener.ora의 설정
    4. user의 실수 : 심각한 에러
    5. instance failure : smon이 처리
    6. media failure

2009년 11월 12일 수업내용

● 인스턴스 복구
    - 비정상적으로 인스턴스가 종료되었을 때, 파일의 동기화가 맞지 않을 때 리두로그 그룹을 맞추는 일
    - roll forward(open전) : 리두로그 파일에 있는 것을 하나도 빠지지 않고 메모리에 올림(commit이 된 것과 안된 것 공존)
    - roll back(open후, on demand & smon)) : 데이터 블럭과 undo블럭을 메모리로 올림
       참조 : http://cafe.naver.com/gseducation/371


● 인스턴스 리커버리 튜닝
    - 인스턴스 리커버리를 조정(더 낫게 만드는 것이 아님)
    - EM > Administaration > Advisor Central > MTTR Advisor를 이용 
   -> 원하는 값을 주면 리두로그파일의 크기를 권고함


● 데이터베이스에 오직 commit된 데이터만 들어있는 경우
    - only 정상 shutdown

● 데이터베이스에 commit된 데이터과 commit안된 데이터의 공존
    - 평상시
    - 인스턴스 비정상 종료시


● 데이터베이스 모드 수정
    : Backup & Recovery 실습 전 준비사항

      실습 내용은 첨부파일을 참고할 것.


● Backup & Recovery 유형 / 실습

    ◎ 데이터 파일이 손상되는 상황
         : 모든 데이터 파일은 평등하나, 데이터 파일이 어느 테이블 스페이스에 소속되어 있느냐에 따라 대접이 달라짐

        ○ 완전 복구
            - system tablespace or undo tablespace
               : mount 단계에서 복구 (closed 복구: 유저들 접근 안됨)
            - users tablespace
               : 문제있는 파일을 offline으로 설정 -> open단계에서 복구
            - index tablespace
               : 인덱스만 있는 파일이므로 삭제 후 재생성만으로 해결
            - read only ts
               : copy & paste
            - temp file
               : startup시 자동 생성됨

        ○ 불완전 복구
            - 유저 실수로 인한 복구 -> flashback으로 해결.

    ◎ Redo log file
         : 전제 조건 - 다중화되어 있을 것
         - file 1개 삭제
            : copy & paste
         - group 삭제
            : inactive group ->완전 복구
         - active, current group -> 불완전 복구(동)

    ◎ Control file
         - 1개만 손상되었을 경우
            : copy & paste
         - 전부 손상되었을 경우
            : 완전 복구(create control file ...)

    ◎ Archived log group 삭제
         - datafile에 문제가 없을 경우 : whole Backup
         - datafile에 문제가 있을 경우 : 불완전 복구(은)

          백업에 문제가 있을 경우(금)

          실습 내용은 첨부파일 참조할 것.


기타 참고사항

DBA의 의무
- 많은 종류의 failure로부터 DB보호
- MTBF를 증가시켜라.(failure가 나는 주기를 증가시켜라)
- MTTR를 감소시켜라.(recover하는데 시간을 줄여라.)
- 데이터의 손실을 최소화시켜라.

문제제기
- nomount -> mount사이의 순서가
   파라미터 파일 읽기 -> SGA 구성 -> BGP실행 -> 진단파일 염
   but 진단 파일에서 BGP가 실행되는 순서가 기록되기 때문에 진단 파일 열고난 후 BGP실행이 나중이 아닌지?? --> 확인요

- 추천 영어 관련 사이트
    http://www.ebse.co.kr

● Optimizer Statistics
  ◎ 정의
       - 옵티마이져(optimizer)가 실행계획을 세울 때 필요한 통계정보

  ◎ 특징
       - 출처는 일부 DBA_로 시작하는 Static Data Dictionary Views
       - 다음과 같은 정보가 포함됨
            1. 테이블 및 인덱스의 크기
            2. 테이블 : ROW의 갯수, 평균 row의 크기, chain의 갯수
            3. 인덱스 : 높이와 delete된 leaf row의 갯수
       - 관련 패키지 : DBMS_STATS
       - 이 내용을 분석해야 SQL 튜닝이 가능함.
       - 주기적으로 수집을 해줘야 함.(변화가 많은 오브젝트를 기준으로)
       - gather_stats_job : 테이블 선별, 히스토그램 비율과 결정을 알아서 해줌.
                                   매일 밤 실행됨. 정확성에 문제가 있을 수 있음.

  ◎ 분류
       ○ System Stats
           : 환경적 데이터의 상황을 알려줌. 9i R2부터 등장.
           - dbms_stats.GATHER_SYSTEM_STATS 

       ○ Data Stats   
           : 내부의 데이터의 상황을 알려줌
           - dbms_stats.GATHER_DATABASE_STATS
           - dbms_stats.GATHER_DICTIONARY_STATS
           - dbms_stats.GATHER_FIXED_OBJECTS_STATS
           - dbms_stats.GATHER_INDEX_STATS
           - dbms_stats.GATHER_SCHEMA_STATS
           - dbms_stats.GATHER_TABLE_STATS

● Performance Statistics
  ◎ 정의
       - 서버 튜닝을 하기 위한 performance의 통계정보

  ◎ 특징
       - V$로 시작하는 Dynamic Performance Views
       - 이 통계 정보를 분석해야 서버 튜닝이 가능함
       - 단점 : 누적된다. 휘발성이다. -> 문제의 원인을 금방 찾을 수 없다.
       - 버전에 따라 통계를 수집하는 방식이 달라짐
           - 8i이전
               utlbstat.sql(b : begin), utlestate.sql(e : end) -> report.txt
               일정 주기 snapshot으로 결과물을 생성, 파일의 위치는 $ORACLE_HOME/rdbms/admin 이다.
           - 8i부터
               statspack : sp*.sql -> perfstat유저가 생성되고, ...
           - 10g부터
               AWR + MMON + ADDM

  ◎ 분류
       ○ Activity
           - v$statname                                                                            -- 363
           - v$sysstat         : 인스턴스 시작이래로 있었던 모든 Activity의 누적  -- 363
           - v$sesstat         : 현재 연결중인 각 session의 Activity의 누적,        -- 363*세션수
           - v$service_stats : 서비스 이름별 time model Activity의 누적           -- 주로 time model 관련 지표
           - v$mystat          : 내 세션의 Activity의 누적                                  -- 363
           ※ 사라진 세션이 문제가 될수도 있다.  -> trace를 조정(cctv설치)

       ○ Wait
           - v$event_name    : 발생 가능한 wait의 목록                                             
           - v$system_event : 인스턴스 시작이래로 경험한 wait(event)의 누적             -> v$system_wait_class
           - v$session_event : 현재 연결중인 각 세션이 경험한 wait(event)의 누적       -> v$session_wait_class
           - v$service_event  : 서비스 이름별 wait의 누적                                         -> v$service_wait_class
           - v$session_wait (10g ASH{Active Session History}) or v$session: 지금 당장 기다리고 있는 wait(event)

       ○ Others
           - v$sql
           - v$latch
           - v$filestat
           - v$pgastat
              ...


기타참고사항

- 대기 이벤트 = System API Call

가령 SQL*Net message to client 대기는 Server Process가 OS에서 Network Send API 요청을 하고 응답이 오기를 기다린다는 것을 의미한다. OS는 Server Process가 요청한 Data를 TCP Send Buffer에 넣는 것으로 일을 마치고 Server Process에게 응답을 보낸다. 즉, SQL*Net message to client 대기는 실제 Network 전송이 끝나기를 기다린다는 의미가 아니라 OS가 Send Buffer에 성공적으로 Data를 등록하기를 기다린다는 것을 의미한다. Network API들은 이런 속성을 지니고 있다.

- 경영 : 불확실성을 최소화하는 과정


참조 : http://ukja.tistory.com/219

Oracle 리두 로그 파일들은 Oracle 데이타베이스의 작업 및 기록에 대한 유용한 정보를 풍부하게 포함하고 있지만, Oracle8i 이전 까지는 이 정보를 활용할 수 있는 정확하고 간편한 툴이 없었습니다. 로그 파일들은 데이타베이스 복구 수행을 위해 필요한 모든 데이타를 포함하면서, 데이타베이스의 데이타와 메타데이타에 만들어진 모든 변경 사항들도 기록하고 있습니다.

LogMiner는 완벽한 관계형 툴로서, 관리자는 이것을 통해 SQL 을 사용하면서 로그 파일들의 읽기, 분석, 그리고 해석 등을 수행할 수 있고, 또한 Oracle8 포워드로부터 온라인 또는 아카이브된 유효한 리두 로그 파일을 볼 수도 있습니다.

LogMiner 를 통한 로그 파일 분석은 다음과 같은 작업을 수행할 수 있습니다..

• 트랜잭션, 사용자, 테이블 그리고 시간 등을 기반으로 하는 변경 사항들의 특정 집합을 추적합니다. 누가 데이타베이스 객체를 수정했고, 데이타 전후에 무엇이 있었는지를 결정하는 것은 어렵지 않지만, 데이타베이스 변경 사항들을 다시 그들의 소스로 추적해서 감사 (audit) 하는 것과 그것들을 실행 취소하는 것은 그리 쉬운 작업이 아닙니다. 이러한 기능들은 보안 및 관리 툴에서 매우 유용하게 사용될 수 있습니다.
• 부정확한 데이타베이스 수정을 정확하게 지적합니다. 이것은 데이타베이스 레벨이 아니라 애플리케이션 레벨에서 논리적 복구의 수행을 위해 사용될 수 있습니다.
• 튜닝 및 용량 계획을 위한 추가 정보를 제공합니다. 또한 관리자는 트렌드 및 데이타 액세스 패턴을 결정하기 위한 다양한 형태의 기록 분석을 수행할 수도 있습니다.
• 복잡한 애플리케이션들의 디버깅을 위해 중요한 정보를 검색합니다..

LogMiner 는 Oracle 서버가 이미 데이타베이스 복구를 위해 리두 로그 파일들을 유지 관리하고 있기 때문에 시스템 상에 데이타 모음(collection) 오버헤드를 강요하지 않고 이 기능들을 제공하고 있습니다. 또한, LogMiner 는 강력한 관리 및 분석 툴 집합을 위한 기반 기술로서도 사용되고 있습니다.

LogMiner 의 사용 방법

LogMiner는 리두 로그 파일 분석을 위해 마운트된 데이타베이스를 필요로 하지는 않지만, 리두 로그 파일들의 내용을 번역하기 위하여 분석되는 데이타베이스 딕셔녀리에 대한 액세스는 필요로 합니다. LogMiner는 내부 객체 식별자 및 데이타 유형들을 의미 있는 외부 객체 이름 및 데이타 포맷으로 번역하기 위해 딕셔너리를 사용하고 있습니다. 하나의 데이타베이스 로그 파일들이 다른 데이타베이스에서 분석될 수 있도록 데이타베이스 딕셔너리 (딕셔너리 파일이라고 불리는)의 추출이 생성될 수가 있고, 딕셔너리 파일의 생성을 위해서는 PL/SQL 패키지가 제공이 되고 있습니다

분석되는 로그 파일들은 동적 성능 뷰로 매핑이 되고(V$ 테이블), PL/SQL 는 로그 파일과 V$ 로그 테이블의 결합을 위해 제공이 되고 있는데, 이 테이블의 모든 행들은 데이타베이스에서 수행되는 논리적 작업에 대한 완벽한 정보를 나타내줍니다. 이 논리적 작업들의 각각에 대하여, 변경의 영향을 논리적으로 기술하기 위해 REDO 및 UNDO SQL 문이 제공되고 있습니다. SQL UNDO 문은 원래의 변경 사항을 롤백하기 위해 사용이 될 수 있고, SQL REDO 문은 데이타베이스 상에서 수행되는 원래의 작업을 나타내고 있습니다..

로그 파일의 데이타는 테이블로 매핑되기 때문에, 표준 SQL 를 통해 또는 지원 언어/인터페이스 (PL/SQL, Java Stored Procedures, Oracle Call Interface, Pro*C, 그리고 SQLJ) 를 사용하여 프로그램적으로 간편하게 액세스될 수 있습니다.

예를 들면, Joe Smith가 EMPLOYEE 테이블에 어떤 변경 사항을 만들었는지 파악하기 위해서, 다음 질의가 실행될 수 있습니다..

select sql_redo, sql_undo from v$logmnr_contents where username = 'joe smith' and seg_name = 'EMPLOYEE';

이 질의는 다음과 같이 포맷된 결과를 반환하는데, 이 결과는 Joe가 처음에 종업원 레코드를 삭제한 다음, 보다 많은 급료의 레코드를 재생성하는 것을 보여주고 있습니다.

SQL_REDO	SQL_UNDO
delete * from EMPLOYEE where EMPNO = 12345 and ROWID = 'AAABOOAABAAEPCABA';	insert into EMPLOYEE (NAME, EMPNO, SALARY) values ('joe smith', 12345, 500);
insert into EMPLOYEE (NAME, EMPNO, SALARY) values('joe smith', 12345, 2500);	delete * from EMPLOYEE where EMPNO = 12345 and ROWID = 'AAABOOAABAAEPCABA';

이 간단한 예는 LogMiner의 사용이 얼마나 강력하면서 간편한지를 나타내고 있습니다. LogMiner를 사용하면 훨씬 더 정교한 분석이 수행될 수가 있습니다: 연속된 명령문들과 트랜잭션들을 통해 변경 사항을 추적합니다; 트리거와 애플리케이션 업데이트가 애플리케이션 디버깅을 위해 어떻게 상호 작용하는지를 파악합니다; 또는 용량 계획 및 튜닝 시간에 대해 테이블이 어떠한 비율로 업데이트가 되는지를 파악합니다.

출처 : http://www.oracle.com/technology/global/kr/deploy/availability/htdocs/logminer.html

● Tuning의 종류

● 개요


● SQL Tuning Advisor
    ◎ 정의
        - SQL의 튜닝을 위해 조언을 해주는 프로세스

    ◎ 특징
        - tuning모드의 optimizer가 분석한 결과를 받아서 전달.
       - optimizer가 권고안을 만들고 STA가 전해준다.

● SQL Access Advisor
    ◎ 정의
        - 인덱스와 MVIEW에 생성에 관한 권고를 해주는 프로세스

● Memory Advisor
    ◎ ASMM
        - Automatic Shared Memory Management의 약자.
        - MMAN(Memory Manager)라는 백그라운드 프로세스가 수분에 한번씩 모니터링한 후 기존에 있는
           메모리 어드바이져의 권고에 따라 SGA ( Shared Pool, Buffer Cache, Large Pool, Java Pool, Streams Pool)의
           크기를 늘이고 줄이는 것은 중요한 임무.

        - sga_target 을 on/off할 수 있는 파라미터
          (shared_pool_size, large_pool_size, java_pool_size, db_cache_size, streams_pool_size 등은 자동으로 관리됨)



기타참고사항

- optimizer의 모드
   ① normal mode : 
       최대 목표는 빠른 판단
   ② tuning mode : 
       문장을 시간을 많이 쓰면서 분석 -> STA에게 전달.

ASMM (Automatic Shared Memory Management)

참고 : http://kin.naver.com/knowhow/detail.nhn?d1id=8&dirId=8&docId=232574&qb=U1FMIFR1bmluZyBBZHZpc29y&enc=utf8&section=kin&rank=1&sort=0&spq=0&pid=fx9ojg331xlssbk/uwVssv--158809&sid=Sx9oA5lnH0sAAFiMCro