Oracle 10g AWR的生成，导入，导出

1.AWR的生成

AWR由ORACLE自动产生，默认30分钟采集一次，保留5天的记录。但是也可以通过DBMS_WORKLOAD_REPOSITORY包来手工创建、删除和修改。使用脚本awrrpt.sql或awrrpti.sql来查看AWR报告，这两个脚本都在目录$ORACLE_HOME/rdbms/admin中，报告可以保存为文本文件或HTML文件。

生成AWR报告的步骤如下：

sqlplussys/sys@127.0.0.1/scmis as sysdba

SQL>@c:/oracle/product/10.2.4/db_1/RDBMS/ADMIN/awrrpt.sql

输入report_type的值:html （注：确定报告的格式）

输入num_days的值:1 （注：选择快照的天数）

输入begin_snap的值:425   （注：起始快照）

输入end_snap的值:427     （注：结束快照）

输入report_name的值:d:\scmis-awr-2011-10-29.html （注：报告生成的名称和位置）

2.AWR的导出

ORACLE10G  AWR是数据库DBA对数据库进行性能分析的核心，DBA经常运行的awrrpt等脚本，都是从AWR的相关表执行查询

 得到分析的结果，实际上，oracle提供了脚本，把AWR相关表的数据导出，这个在其它数据库上面来分析AWR的结果。大致过程如下：

  oracle的sys用户运行脚本awrextr.sql

SYS@PROD> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/awrextr.sql     
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Disclaimer: This SQL/Plus script should only be called under
the guidance of Oracle Support.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


~~~~~~~~~~~~~
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
------------ ------------ ------------
* 225523772  PROD         secdb1

The default database id is the local one: ' 225523772'.  To use this
database id, press <return> to continue, otherwise enter an alternative.

Enter value for dbid:                                              ----输入 dbid 的值：输入回车使用默认DBID

Using  225523772 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: 7                             -----输入想导出的天数

Listing the last 7 days of Completed Snapshots

DB Name        Snap Id    Snap Started
------------ --------- ------------------
PROD                 1 25 Apr 2013 17:00
                     2 25 Apr 2013 18:00
                     3 25 Apr 2013 19:00
                     4 25 Apr 2013 20:00
                     5 25 Apr 2013 21:00
                     6 25 Apr 2013 22:00
                     7 25 Apr 2013 23:24
                     8 26 Apr 2013 20:01


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1                        ------输入开始快照ID
Begin Snapshot Id specified: 1

Enter value for end_snap: 8                           -------输入结束快照ID
End   Snapshot Id specified: 8


Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
AWRDMP                         /home/oracle/awrdmp
DATA_PUMP_DIR                  /u01/app/oracle/product/10.2.0/db_1/rdbms/log/

Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: AWRDMP           ----输入数据存放导出DMP文件的目录

说明：该目录必须是通过create directory创建的oracle能识别的目录

Using the dump directory: AWRDMP

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_1_8.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for file_name: awr_04_26.dmp               ------输入DMP文件名称

根据输入的结果系统开始导出数据：

Using the dump file prefix: awr_04_26.dmp
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /home/oracle/awrdmp
|   awr_04_26.dmp.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:
|   /home/oracle/awrdmp
|   awr_04_26.dmp.log

这个时候实际上oracle调用的DATA PUMP导出输入：导出的日志如下：

Starting "SYS"."SYS_EXPORT_TABLE_02":
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/T