오라클 AWR의 기본과 export/import

AWR export / import
최근에 AWR을 이용한 DBMS 점검을 수행해야할 일이 있었다.
하지만 며칠을 해당 사이트에 방문하여 작업을 할 수 없는 관계로 AWR을 export하여 import하여 분석하기로 했다.
AWR(Auto Workload Repository) DBMS의 성능 상태 모니터링을 위해서 기본 1시간에 한번씩 7일간 DBMS에 발생한 이벤트 및 통계 정보 등을 DISK로 저장을 한다.
Oracle Dictionary에서 "WRH$"로 시작하는 테이블에 저장되며 "DBA_HIST_" 로 시작하는 View의 조회를 통해서 확인할 수 있다.
그리고 AWR은 dbms_workload_repository 패키지를 이용해서 조작할 수 있다.
아래는 DISK로 저장하는(snapshot) 주기 및 보관 기간을 10분 주기로 10일간 보관하는 예제 이다.
 begin
   dbms_workload_repository.modify_snapshot_settings(retention => 10*24*60, interval => 10);
 end;
 /

또한 create_snapshot 함수를 이용해서 snapshot을 생성할 수도 있다.
 begin
   dbms_workload_repository.create_snapshot([&flush_level=TYPICAL|ALL|BASIC]);
 end;
 /
그리고 아래 예제를 통해서 snapshot을 삭제할 수도 있다.
 begin
   dbms_workload_repository.drop_snapshot_range(&low_snap_id, &high_snap_id, [&dbid])
 end;
 /

이렇게 DISK로 저장된 snapshot을 보고서 형태로 조회하기 위해서는 dbms_workload_repository 패키지를 이용하는 방법과
 - select * from table(dbms_workload_repository.awr_report_html( &db_id, &inst_num, &begin_snap, &end_snap));  <-- html 형태로 보기
 - select * from table(dbms_workload_repository.awr_report_text( &db_id, &inst_num, &begin_snap, &end_snap));  <-- text 형태로 보기
오라클에서 제공하는 awrrpt.sql을 이용하는 방법이 있다.
 - @?/rdbms/admin/awrrpt
때로는 엑셀 등을 이용해서 chart를 그리고 별도의 보고서를 작성하기 위해서 "DBA_HIST_"로 시작하는 View를 직접 조회할 수도 있다.
위의 dbms_workload_repository를 이용하여 AWR 보고서를 조회하기위해서는
아래와 같은 SQL을 이용해서 database id와 instance number 그리고 begin snapshot id, end snapshot id를 확인할 수 있다.
 - select dbid, snap_id, startup_time, begin_interval_time, end_interval_time
   from   dba_hist_snapshot
   where  begin_interval_time >= to_char('2015.07.10 12:00', 'yyyy.mm.dd hh24:mi')
   and    end_interval_time <= to_char('2015.07.10 15:00', 'yyyy.mm.dd hh24:mi')
   order by snap_id;
  
만들어진 보고서는 begin snapshot과 end snapshot 사이의 delta 값으로 만들어지게 된다.
즉, begin_interval_time과 end_interval_time 사이의 delta 값이 되겠다.  
  
DBMS가 정상적인 경우(아무 문제가 없는)와 문제가 발생한 경우 또는 튜닝 전후를 비교하기 위해 Baseline를 생성해둘 수도 있다.
 begin
   dbms_workload_repository.create_baseline(&start_snap_id, &end_snap_id, &baseline_name, &dbid);
 end;
 /

이렇게 생성한 Baseline은 modify_snapshot_settings 함수로 설정한 보관기간이 지나도 삭제되지 않으며,
Baseline을 삭제할 때 Baseline에 해당하는 snapshot을 함께 삭제할 것인지 cascade 옵션으로 결정할 수 있다.
아래는 생성한 Baseline을 삭제한다.
 begin
   dbms_workload_repository.drop_baseline(&baseline_name, [&cascade], [&dbid]);
 end;
 /

Baseline과 특정 snapshot과의 비교를 위해서는 dbms_workload_repository 패키지를 이용하는 방법과
 - select * from table(dbms_workload_repository.awr_diff_report_html( &db_id, &inst_num, &begin_snap, &end_snap));  <-- html 형태로 보기
 - select * from table(dbms_workload_repository.awr_diff_report_text( &db_id, &inst_num, &begin_snap, &end_snap));  <-- text형태로 보기
오라클에서 제공하는 awrddrpt.sql 이용하는 방법이 있다.
 - @?/rdbms/admin/awrddrpt.sql

간단하게 AWR에 대해서 알아보았으며 각 옵션 등 더 상세한 내용은 Oracle 매뉴얼 또는 다른 문서를 참조하고
이제 AWR을 export하고 import하는 방법에 대해서 알아보자.
AWR export/import는 Oracle의 Data Pump를 이용하게 되며, Data Pump에서 사용할 Oracle Directory 객체를 미리 생성해 두어야 한다.
 - create or replace directory <directory_name> as '<os directory path>';
이미 Data Pump를 위한 directory가 있다면 별도로 생성할 필요는 없다.
AWR export는 오라클에서 제공하는 awrextr.sql 을 실행하면 된다.
 - @?/rdbms/admin/awrextr.sql
awrextr.sql을 실행하면 아래와 같은 절차로 진행하게되며, 완료되면 지정한 directory에 export된 파일이 생성된다.
 1. database id를 입력
 2. display될 snapshot id의 기간을 일단위로 입력
    ex) 최근 2일치의 snapshot id를 보고싶다면 2를 입력
 3. export받을 begin snapshot id와 end snapshot id 입력
 4. export받는 데이터 파일이 생성될 oracle directory명 입력
 5. export받을 file명(기본으로 dmp 확장자가 붙으니 file명만 입력)

그 다음 AWR을 import 해보자. AWR import도 오라클에서 제공하는 awrload.sql을 실행하면 된다.
주의사항은 export받은 DBID와 동일한 DBID로 운영중인 DBMS에는 import할 수가 없다.
즉 export 받은 DBMS에는 import할 수가 없다는 것이다. 결국 다른 DBMS가 필요하다.
먼저 export 받은 파일을 Oracle Directory로 지정된 OS 디렉토리에 복사한후 awrload.sql을 실행한다.
 - @?/rdbms/admin/awrload.sql
awrload.sql을 실행면 아래와 같은 절차로 진행하게되며, 완료되면 "DBA_HIST_" View를 통해 조회할 수 있다.
 - export 파일을 복사한 Oracle directory명 입력
 - export file명 입력(입력받은 file명에 기본으로 dmp 확장자가 붙으니 확장자를 제외한 file명만 입력)
 - import할 동안 사용될 임시 user명을 입력(이 user는 import가 완료되면 자동으로 삭제되니 기본값을 사용)
 - 임시 user가 사용할 default tablespace 입력(변경 가능하나 기본값을 사용)
 - 임시 user가 사용할 임시 tablespace 입력(변경 가능하나 기본값을 사용)
여기까지 진행하면 import가 시작되며, import가 모두 진행된 이후에는 import를 수행한 DBMS의 AWR 테이블인 WRH$ 테이블에 import 데이터를 migration 하게된다.
이후 import시 사용한 임시 user는 cascade 옵션으로 자동 삭제된다.
import를 진행한 후에 WRH$ 테이블에 import 데이터를 밀어넣을 때 Oracle의 version이 다르면 문제가 발생하게 된다.
내가 AWR을 export한 Oracle version은 11.2.0.4 인데 import한 Oracle version은 11.2.0.1 이었다.
마이너 버전에서 문제가 발생한 것이다. 확인해 보니 11.2.0.4 버전의 AWR과 11.2.0.1 버전의 AWR에는 다음과 같은 차이가 있었다.

11.2.0.1	컬럼개수	11.2.0.4	컬럼개수	차이개수
WRH$_ACTIVE_SESSION_HISTORY	67	WRH$_ACTIVE_SESSION_HISTORY	70	-3
WRH$_ACTIVE_SESSION_HISTORY_BL	67	WRH$_ACTIVE_SESSION_HISTORY_BL	70	-3
WRH$_BUFFERED_QUEUES	11	WRH$_BUFFERED_QUEUES	23	-12
WRH$_BUFFERED_SUBSCRIBERS	13	WRH$_BUFFERED_SUBSCRIBERS	26	-13
WRH$_PERSISTENT_QUEUES	21	WRH$_PERSISTENT_QUEUES	29	-8
WRH$_PERSISTENT_SUBSCRIBERS	16	WRH$_PERSISTENT_SUBSCRIBERS	22	-6
WRH$_RSRC_CONSUMER_GROUP	35	WRH$_RSRC_CONSUMER_GROUP	41	-6
WRH$_RSRC_PLAN	9	WRH$_RSRC_PLAN	10	-1
WRM$_SNAPSHOT	12	WRM$_SNAPSHOT	13	-1
없음		WRH$_MVPARAMETER	8	-8
없음		WRH$_MVPARAMETER_BL	8	-8
없음		WRH$_PERSISTENT_QMN_CACHE	27	-27
없음		WRH$_TABLESPACE	7	-7
없음		WRM$_SNAPSHOT_DETAILS	6	-6

11.2.0.4 버전에서는 11.2.0.1 버전보다 더 많은 성능 정보를 저장하는 것으로 업그레이드가 되어서 정상적으로 migration을 할 수 없었던 것이다.
결국 11.2.0.4 버전으로 업그레이드를 수행하고서야 정상적으로 데이터를 migration을 할 수 있었다.
12c 버전에서 11g 버전의 AWR 데이터를 정상적으로 migration이 되는지를 확인해 볼 필요가 있겠다.

다음에는 AWR 을 이용해서 Excel 등으로 별도의 보고서를 만드는 방법을 게시하도록 하겠다.