前言：

 在生产环境中，当运行中的Oracle数据库出现性能问题的时候，DBA通常都需要我们生成Oracle数据库出现性能问题时间段的AWR或者ASH等报告来判断原因，所以维护人员学会收集AWR、ADDM、ASH报告是必须的。

环境：

 CentOS 7.5

 Docker 20.10.2

 Oracle_11g

AWR报告收集：

 AWR(Automatic Workload Repository)自动工作负载库是Oracle公司提供的一个工具。AWR来可以自动收集、处理、并保存性能统计结果，这种统计数据存放在内存中并随后存储在数据库中，这些数据既能在报告中显示也可以通过视图查询。可以协助我们找出Oracle的性能瓶颈。

 @?/rdbms/admin/awrrpt

[root@localhost ~]# docker ps
CONTAINER ID   IMAGE                                      COMMAND                  CREATED        STATUS         PORTS                    NAMES
3edfba76f476   registry.aliyuncs.com/helowin/oracle_11g   "/bin/sh -c '/home/o…"   6 months ago   Up 2 seconds   0.0.0.0:1521->1521/tcp   oracle_11g

[root@localhost ~]# docker exec -it oracle_11g /bin/bash

[oracle@3edfba76f476 /]$ su - root  //切换为root用户
Password:      # 密码默认为 helowin

[oracle@3edfba76f476 /]$ su - oracle   //再切换oracle用户，并且以SYS登录数据库

[oracle@3edfba76f476 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 20 16:13:26 2021

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/awrrpt     //执行这个SQL就可以开始AWR报告收集
Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1384114315 HELOWIN             1 helowin


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
------------ -------- ------------ ------------ ------------
  1384114315        1 ORCL         orcl         32e47e645a97
  1384114315        1 ORCL         helowin      a444e690ce3e
  1384114315        1 ORCL         orcl         a444e690ce3e
  1384114315        1 ORCL         helowin      85e4a929d0f8
* 1384114315        1 HELOWIN      helowin      3edfba76f476
  1384114315        1 HELOWIN      helowin      85e4a929d0f8

Using 1384114315 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  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1     //输入快照查看的间隔，一般一天的就行，实际根据DBA的要求来做

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
helowin      HELOWIN            253 21 Apr 2021 18:12      1
                                254 21 Apr 2021 19:00      1
                                255 21 Apr 2021 20:00      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 253      //首先输入开始的快照ID，此处为253
Begin Snapshot Id specified: 253

Enter value for end_snap: 255       //输入快照结束ID，可以根据要求的时间确定，此处为255
End   Snapshot Id specified: 255



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_253_255.html.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name:    //输入报告的名字，默认的就可以，例如：awrrpt_1_167_169.html. 实例1 快照167-169的AWR报告，此处回车就行，报告生成在当前目录下

Using the report name awrrpt_1_253_255.html




ASH Report For HELOWIN/helowin




DB Name
DB Id
Instance
Inst num
Release
RAC
Host
HELOWIN
1384114315
helowin
1
11.2.0.1.0
 
db file sequential read
1
2.17


Back to Top


End of Report

Report written to ashrpt_1_0421_1900.html     //输入报告的名字，默认回车即可

3.1 查看oracle容器内生成的ADDM报告

[root@test ~]# docker ps
CONTAINER ID   IMAGE                                      COMMAND                  CREATED        STATUS       PORTS                      NAMES
3edfba76f476   registry.aliyuncs.com/helowin/oracle_11g   "/bin/sh -c '/home/o…"   2 months ago   Up 3 hours   0.0.0.0:1521->1521/tcp     oracle_11g

[root@test ~]# docker exec -it oracle_11g bash

[oracle@3edfba76f476 /]$ su - root
Password:  helowin

[root@3edfba76f476 ~]# su - oracle

[oracle@3edfba76f476 ~]$ pwd
/home/oracle

[oracle@3edfba76f476 ~]$ ls
addmrpt_1_254_256.txt  app  ashrpt_1_0421_1900.html  awrrpt_1_253_255.html

3.2 导出oracle容器内部的ADDM报告至之宿主机

—》docker cp 容器ID:容器内文件路径 ./（宿主机路径）

[root@test ~]# docker cp 3edfba76f476:/home/oracle/ashrpt_1_0421_1900.html ./

其他（RAC）：

 因为有的系统一般都是RAC，所以需要在各个节点下的AWR报告，如果你愿意登录各个节点，可以使用一下SQL

@?/rdbms/admin/ashrpti.sql与上边的不同也就是需要输入DBID和实例NUMBER，明白提示的意思就行。

—注意— ：

 在生成报告期间，不能有数据库重启的操作，如果有会有报错。

查看生成的各项报告：

AWR报告

ADDM报告

ASH报告