导出和导入AWR的信息(ORACLE)

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

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

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

SQL> @d:\oracle\ora10g\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
------------      ------------ ------------
  3469133250   racdb          node1
  3469133250   racdb          node2

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

输入 dbid 的值:    输入回车使用默认DBID

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.

输入 num_days 的值:  输入想导出的天数

输入 begin_snap 的值: 输入开始快照ID

输入 end_snap 的值:输入结束快照ID

输入 directory_name 的值：输入数据存放导出DMP文件的目录

输入 file_name 的值:输入DMP文件名称

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

Using the dump file prefix: awr_data_1100_1400
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   H:\
|   awr_data_1100_1400.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:
|   H:\
|   awr_data_1100_1400.log
这个时候实际上oracle调用的DATA PUMP导出输入：导出的日志如下：

Starting "SYS"."SYS_EXPORT_TABLE_01": 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 325.9 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/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_763927183_18843"  31.44 MB  190546 rows
. . exported "SYS"."WRH$_SQL_PLAN"                       12.74 MB   34438 rows
. . exported "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_763927183_18939"  23.42 MB  139826 rows
. . exported "SYS"."WRH$_SQLTEXT"                        6.571 MB    2727 rows
. . exported "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_763927183_18891"  10.73 MB   59392 rows
. . exported "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_763927183_18916"  11.19 MB   63455 rows
. . exported "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_763927183_18867"  9.001 MB   50345 rows
. . exported "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_763927183_18771"  2.047 MB   11343 rows
. . exported "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_763927183_18795"  7.359 MB   40062 rows
. . exported "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_763927183_18819"  7.423 MB   40523 rows

.................................................

. . exported "SYS"."WRH$_WAITCLASSMETRIC_HISTORY"            0 KB       0 rows
. . exported "SYS"."WRH$_WAITSTAT":"WRH$_WAITSTAT_MXDB_MXSN"      0 KB       0 rows
. . exported "SYS"."WRH$_WAITSTAT_BL"                        0 KB       0 rows
. . exported "SYS"."WRM$_SNAP_ERROR"                         0 KB       0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  H:\ awr_data_1100_1400.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 13:41:01

 

 

导入AWR的数据，使用脚本awrload.sql，事先在目录数据库创建一个目录，用来放上面的dmp文件，然后按照系统提示输入相关信息

基本可以完成导入(系统默认会创建一个AWR_STAGE用户来执行导入的步骤，使用INSERT 方式完成导入)。