AWR dump文件导入——awrload

自动工作负载信息库 (AWR)，可以永久保存ASH信息，为DBA提供了更加有效的系统监测工具，性能分析，故障诊断，一般在检查数据库性能的时候都会用到这个，现在已经基本取代 Statspack了。
但是很多情况下在去帮客户分析数据库的时候，不能在客户现场完成，需要将dump文件拷贝回来，然后将dump文件导入到自己的数据库中，根据需要生成awr报告，再进行详细的分析。
这里就需要用到awrload，下面是详细步骤

step 1、创建目录，并授权。并将dump文件拷贝到该目录下
[oracle@localhost oradata]$ mkdir dump
[oracle@localhost oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 28 10:09:20 2013

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 133906432 bytes
Fixed Size 1335024 bytes
Variable Size 125829392 bytes
Database Buffers 4194304 bytes
Redo Buffers 2547712 bytes
Database mounted.
Database opened.
SQL> create directory dump as '/oracle/oradata/dump';

Directory created.

SQL> create user awen identified by oracle;

User created.

SQL> grant dba to awen;

Grant succeeded.

SQL> grant read,write on directory dump to awen;

Grant succeeded.



step 2、使用awrload.sql将dump文件导入
SQL> @/oracle/product/database/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will load the AWR data from a dump file. The ~
~ script will prompt users for the following information: ~
~ (1) name of directory object ~
~ (2) name of dump file ~
~ (3) staging schema name to load AWR data into ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

Directory Name Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR /oracle/product/database/rdbms/log/
DUMP /oracle/oradata/dump
ORACLE_OCM_CONFIG_DIR /oracle/product/database/ccr/state

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

Enter value for directory_name: DUMP

Using the dump directory: DUMP

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

Enter value for file_name: ecard_0321

Loading from the file name: ecard_0321.dmp

Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.

The default staging schema name is AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for schema_name:

Using the staging schema name: AWR_STAGE

Choose the Default tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users's default tablespace. This is the
tablespace in which the AWR data will be staged.

TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
------------------------------ --------- ------------------
SYSAUX PERMANENT *
USERS PERMANENT

Pressing <return> will result in the recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: USERS

Using tablespace USERS as the default tablespace for the AWR_STAGE

Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.

TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMPTS1 TEMPORARY *

Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace:

Using tablespace TEMPTS1 as the temporary tablespace for AWR_STAGE

... Creating AWR_STAGE user

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /oracle/oradata/dump
| ecard_0321.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Load Started ...
|
| This operation will take a few moments. The
| progress of the AWR load operation can be
| monitored in the following directory/file:
| /oracle/oradata/dump
| ecard_0321.log
|

... Dropping AWR_STAGE user

End of AWR Load

step 3、使用awrrpti.sql生成相关报告
SQL> @/oracle/product/database/rdbms/admin/awrrpti.sql

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:

Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
1972453558 1 ECARD ecard1 ecarddb1
2205835418 1 ORASQL orasql localhost
* 2205835418 1 ORASQL orasql localhost.lo
caldomain
1972453558 2 ECARD ecard2 ecarddb2

Enter value for dbid: 1972453558
Using 1972453558 for database Id
Enter value for inst_num: 1
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:

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 19538
Begin Snapshot Id specified: 19538

Enter value for end_snap: 19896
End Snapshot Id specified: 19896

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

Enter value for report_name: ecard1

Using the report name ecard1