AWR
Oracle DB의 상태의 진단이 필요할 때 많이 사용하는 정보로, EM의 진단팩(Diagnostic Pack)에 대한 라이센스가 필요하다.
- AWR 경로
[ora11g@oraclevm ~]$ cd $ORACLE_HOME
[ora11g@oraclevm db_1]$ cd rdbms
[ora11g@oraclevm rdbms]$ cd admin
[ora11g@oraclevm admin]$ pwd
/u01/app/oracle/11.2.0.3/db_1/rdbms/admin
[ora11g@oraclevm admin]$ ls -al | grep awrrpt
-rw-r--r--. 1 ora11g dba 7704 Jul 25 2011 awrrpti.sql
-rw-r--r--. 1 ora11g dba 1999 Oct 24 2003 awrrpt.sql
- awrrpti.sql RAC에서 다른 노드(DB 서버)도 함께 출력할때 사용하는 레포트
- awrrpt.sql 로컬 DB에서 사용하는 레포트
- AWR 실행방법
[ora11g@oraclevm awr]$ pwd -- 레포트 출력할 디렉토리로 이동
/home/ora11g/awr
[ora11g@oraclevm awr]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 22 10:09:20 2022
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
589883721 DB11G 1 DB11G
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 589883721 1 DB11G DB11G oraclevm
Using 589883721 for database Id
Using 1 for instance number
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: 7
Listing the last 7 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
DB11G DB11G 83 16 Nov 2022 01:56 1
84 16 Nov 2022 13:10 1
85 16 Nov 2022 14:00 1
86 16 Nov 2022 15:00 1
87 16 Nov 2022 16:00 1
88 16 Nov 2022 17:00 1
89 16 Nov 2022 23:53 1
90 17 Nov 2022 04:26 1
91 17 Nov 2022 20:31 1
92 18 Nov 2022 07:50 1
93 18 Nov 2022 09:00 1
94 18 Nov 2022 10:00 1
95 18 Nov 2022 11:00 1
96 18 Nov 2022 12:00 1
97 18 Nov 2022 13:00 1
98 18 Nov 2022 14:00 1
99 18 Nov 2022 15:00 1
100 18 Nov 2022 16:00 1
101 19 Nov 2022 01:24 1
102 19 Nov 2022 16:42 1
103 19 Nov 2022 20:28 1
104 20 Nov 2022 05:46 1
105 20 Nov 2022 07:00 1
106 20 Nov 2022 08:00 1
107 20 Nov 2022 09:00 1
108 20 Nov 2022 10:00 1
109 20 Nov 2022 13:31 1
110 20 Nov 2022 15:16 1
111 20 Nov 2022 16:00 1
112 21 Nov 2022 01:36 1
113 21 Nov 2022 07:03 1
114 21 Nov 2022 08:00 1
115 21 Nov 2022 09:00 1
116 21 Nov 2022 10:00 1
117 21 Nov 2022 11:00 1
118 21 Nov 2022 12:00 1
119 21 Nov 2022 13:00 1
120 21 Nov 2022 14:00 1
121 21 Nov 2022 15:00 1
122 21 Nov 2022 16:00 1
123 21 Nov 2022 17:00 1
124 21 Nov 2022 18:00 1
125 22 Nov 2022 09:11 1
126 22 Nov 2022 10:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 99
Begin Snapshot Id specified: 99
Enter value for end_snap: 126
End Snapshot Id specified: 126
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_99_126.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_99_126.html
[ora11g@oraclevm awr]$ ls -al
total 488
drwxr-xr-x. 2 ora11g dba 34 Nov 22 10:12 .
drwx------. 20 ora11g dba 4096 Nov 22 10:11 ..
-rw-r--r--. 1 ora11g dba 491937 Nov 22 10:10 awrrpt_1_99_126.html
AWR snapshot 일자 중 선택한 Begin_snap ~ end_snap비어있는 스냅파일이 존재하게 되면
아래와 같이 ORA-20200 에러 출력
[ora11g@oraclevm awr]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 22 10:03:54 2022
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
589883721 DB11G 1 DB11G
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 589883721 1 DB11G DB11G oraclevm
Using 589883721 for database Id
Using 1 for instance number
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:7
Listing the last 7 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
DB11G DB11G 83 16 Nov 2022 01:56 1
84 16 Nov 2022 13:10 1
85 16 Nov 2022 14:00 1
86 16 Nov 2022 15:00 1
87 16 Nov 2022 16:00 1
88 16 Nov 2022 17:00 1
89 16 Nov 2022 23:53 1
90 17 Nov 2022 04:26 1
91 17 Nov 2022 20:31 1
92 18 Nov 2022 07:50 1
93 18 Nov 2022 09:00 1
94 18 Nov 2022 10:00 1
95 18 Nov 2022 11:00 1
96 18 Nov 2022 12:00 1
97 18 Nov 2022 13:00 1
98 18 Nov 2022 14:00 1
99 18 Nov 2022 15:00 1
100 18 Nov 2022 16:00 1
101 19 Nov 2022 01:24 1
102 19 Nov 2022 16:42 1
103 19 Nov 2022 20:28 1
104 20 Nov 2022 05:46 1
105 20 Nov 2022 07:00 1
106 20 Nov 2022 08:00 1
107 20 Nov 2022 09:00 1
108 20 Nov 2022 10:00 1
109 20 Nov 2022 13:31 1
110 20 Nov 2022 15:16 1
111 20 Nov 2022 16:00 1
112 21 Nov 2022 01:36 1
113 21 Nov 2022 07:03 1
114 21 Nov 2022 08:00 1
115 21 Nov 2022 09:00 1
116 21 Nov 2022 10:00 1
117 21 Nov 2022 11:00 1
118 21 Nov 2022 12:00 1
119 21 Nov 2022 13:00 1
120 21 Nov 2022 14:00 1
121 21 Nov 2022 15:00 1
122 21 Nov 2022 16:00 1
123 21 Nov 2022 17:00 1
124 21 Nov 2022 18:00 1
125 22 Nov 2022 09:11 1
126 22 Nov 2022 10:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 84
Begin Snapshot Id specified: 84
Enter value for end_snap: 98
End Snapshot Id specified: 98
declare
*
ERROR at line 1:
ORA-20200: The instance was shutdown between snapshots 84 and 98
ORA-06512: at line 42
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
- 최종 확인
- html 파일을 ftp를 통해 로컬로 추출 후 확인
'DB > Oracle' 카테고리의 다른 글
[DATAPUMP] Table EXPDP IMPDP (0) | 2022.11.23 |
---|---|
[INSTALL][OL7.9 / Oracle 11.2.0.3] Oracle Slient mode 설치 (0) | 2022.11.22 |
[ETC] AWR Report 보는 방법 (0) | 2022.11.22 |
[Admin] Shared / Dedicated Server Test (1) | 2022.11.16 |
[Admin] Oracle Architecture (0) | 2022.11.16 |