오라클 19c awr 데이터 export 방법

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c awr 스냅샷 export 방법

오라클 19c에서 awr 스냅샷을 내보내는 방법을 설명함

다른 db로 옮겨서 보거나 sr에 스냅샷을 올릴때 사용할 수 있음

 

 

sqlplus 실행

$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 4 04:13:09 2022
Version 19.11.0.0.0
 
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
 
SQL> 

 

 

@$ORACLE_HOME/rdbms/admin/awrextr.sql 실행 후 dbid 입력

SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql      
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the       ~
~  following information:                       ~
~     (1) database id                           ~
~     (2) snapshot range to extract                   ~
~     (3) name of directory object                   ~
~     (4) name of dump file                       ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
   DB Id     DB Name      Host
------------ ------------ ------------
* 362905073  ORCL19      ORACLE19
 
 
The default database id is the local one: '362905073'.    To use this
database id, press <return> to continue, otherwise enter an alternative.
 
Enter value for dbid: 362905073 <-- dbid 입력

 

 

보고싶은 스냅샷 리스트 날짜 입력

Enter value for dbid: 362905073
 
Using 362905073 for Database ID
 
 
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
 
 
Enter value for num_days: 2  <-- 보고싶은 스냅샷 날짜 일단위로 입력

 

 

스냅샷 번호 begin, end 입력

Enter value for num_days: 2
 
Listing the last 2 days of Completed Snapshots
 
DB Name        Snap Id      Snap Started
------------ --------- ------------------
ORCL19     11412 03 Jul 2022 17:00
         11413 03 Jul 2022 18:00
         11414 03 Jul 2022 19:00
         11415 03 Jul 2022 20:00
         11416 03 Jul 2022 21:00
         11417 03 Jul 2022 22:00
         11418 03 Jul 2022 23:00
         11419 04 Jul 2022 00:00
         11420 04 Jul 2022 01:00
         11421 04 Jul 2022 02:00
         11422 04 Jul 2022 03:00
         11423 04 Jul 2022 04:00
 
 
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11420 <-- begin snap id 입력
Begin Snapshot Id specified: 11420
 
Enter value for end_snap: 11422  <-- end snap id 입력
End   Snapshot Id specified: 11422

 

 

directory 이름 입력(export 할 위치)

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
 
Directory Name               Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR               /app/oracle/admin/ORCL19/dpdump/
DBMS_OPTIM_ADMINDIR           /app/oracle/product/19.0.0/rdbms/admin
DBMS_OPTIM_LOGDIR           /app/oracle/product/19.0.0/cfgtoollogs
IMSI                   /oracle/dump
JAVA$JOX$CUJS$DIRECTORY$       /app/oracle/product/19.0.0/javavm/admin/
OPATCH_INST_DIR            /app/oracle/product/19.0.0/OPatch
OPATCH_LOG_DIR               /app/oracle/product/19.0.0/rdbms/log
OPATCH_SCRIPT_DIR           /app/oracle/product/19.0.0/QOpatch
ORACLE_BASE               /app/oracle
ORACLE_HOME               /app/oracle/product/19.0.0
ORACLE_OCM_CONFIG_DIR           /app/oracle/product/19.0.0/ccr/state
ORACLE_OCM_CONFIG_DIR2           /app/oracle/product/19.0.0/ccr/state
TEST_PUMP               /home/oracle/pump
XMLDIR                   /app/oracle/product/19.0.0/rdbms/xml
XSDDIR                   /app/oracle/product/19.0.0/rdbms/xml/schema
 
Choose a Directory Name from the above list (case-sensitive).
 
Enter value for directory_name: IMSI  <-- directory 이름 입력
 
Using the dump directory: IMSI

 

 

export 될 파일명 설정

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_11420_11422.
To use this name, press <return> to continue, otherwise enter
an alternative.
 
Enter value for file_name:  <-- 원하는 파일명 입력 또는 엔터

 

 

시간이 조금 걸려서 export 완료

Using the dump file prefix: awrdat_11420_11422
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /oracle/dump
|   awrdat_11420_11422.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   /shcsw/dump
|   awrdat_11420_11422.log
|
 
End of AWR Extract

 

 

파일 확인

$ cd /oracle/dump/
$ ls -al
total 14158956
drwxr-xr-x. 2 oracle dba        4096 Jul  4 04:21 .
drwxr-xr-x. 7 oracle dba          72 Jun 23 19:10 ..
-rw-r-----. 1 oracle dba    29327360 Jul  4 04:23 awrdat_11420_11422.dmp
-rw-r--r--. 1 oracle dba      185442 Jul  4 04:23 awrdat_11420_11422.log

정상적으로 awr 스냅샷이 export 됨

 

 

장애 발생 시 스냅샷 데이터를 업로드하는것도 sr 처리에 도움이 됨

이 스냅샷을 다시 가져오려면 $ORACLE_HOME/rdbms/admin/awrload.sql 을 이용하면됨

 

 

리포트 수집 방법 및 AWR 관련 링크

오라클 19c AWR 란 ( https://positivemh.tistory.com/1013 )

오라클 19c AWR 리포트 수집 방법 ( https://positivemh.tistory.com/1012 )
오라클 19c ASH 리포트 수집 방법 ( https://positivemh.tistory.com/1011 )

오라클 19c AWR 비교 리포트 수집 방법 ( https://positivemh.tistory.com/1014 )

오라클 19c AWR 에 저장된 정보 확인(awrinfo.sql) ( https://positivemh.tistory.com/780 )

오라클 19c AWR  데이터 export 방법 ( https://positivemh.tistory.com/836 )

오라클 무료 모니터링 툴 ASH Viewer 설치 가이드 ( https://positivemh.tistory.com/966 )

 

 

참조 : https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/gathering-database-statistics.html#GUID-CC6A87BD-BFEA-4A1B-9FD1-3445DC11BE72