AWR导入分析数据

有些时候，我们分析客户的数据库的性能，需要请客户将AWR数据导出，然后导入本机数据库进行分析。

一、在导入数据之前，一般需要创建以下路径

DATA_FILE_DIR

DATA_PUMP_DIR

EXP

IMP

SQL> create directory data_file_dir as 'd:\dir'; --将要导入的dmp文件存到此路径下

SQL> create directory exp as 'd:\exp';

SQL> create directory exp as 'd:\exp';

 

 

SQL> create directory data_pump_dir as 'd:\dpdump';

create directory data_pump_dir as 'd:\dpdump'

                 *

第 1 行出现错误:

ORA-00955: 名称已由现有对象使用

data_pump_dir 这个路径数据库已经有了，就不用创建啦。

二、导入

SQL> @?/RDBMS/ADMIN/AWRLOAD      --执行awrload脚本

~~~~~~~~~~

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_FILE_DIR                  D:\dir

DATA_PUMP_DIR                  D:\oracle\admin\stream\dpdump\

EXP                            d:\exp

IMP                            d:\imp

ORACLECLRDIR                   D:\oracle\product\11.2.0\dbhome_1\bin\clr

ORACLE_OCM_CONFIG_DIR          D:\oracle\product\11.2.0\dbhome_1\ccr\state

XMLDIR                         D:\oracle\product\11.2.0\dbhome_1\rdbms\xml

 

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

 

输入 directory_name 的值:  DATA_FILE_DIR     ----输入dmp文件保存的路径名字注意一定要大写

 

Using the dump directory: DATA_FILE_DIR

 

Specify the Name of the Dump File to Load

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Please specify the prefix of the dump file (.dmp) to load:

 

输入 file_name 的值:  awrdat_50063_50209_20110622   ---输入dmp文件的名字，不写后缀，否则报错

 

Loading from the file name: awrdat_50063_50209_20110622.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.

 

输入 schema_name 的值:  AWR_STAGE     

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.

 

输入 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

------------------------------ --------- -----------------------

TEMP                           TEMPORARY *

 

Pressing <return> will result in the database's default temporary

tablespace (identified by *) being used.

 

输入 temporary_tablespace 的值:  TEMP  --输入临时表空间的名字

 

Using tablespace TEMP as the temporary tablespace for AWR_STAGE

 

 

... Creating AWR_STAGE user

 

|

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

|  Loading the AWR data from the following

|  directory/file:

|   D:\dir

|   awrdat_50063_50209_20110622.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:

|   D:\dir

|   awrdat_50063_50209_20110622.log

|

... Dropping AWR_STAGE user

 

End of AWR Load

SQL>

导入成功。

三、以下为部分导入的日志

处理对象类型 TABLE_EXPORT/TABLE/TABLE
     已完成 101 TABLE 个对象, 用时 6 秒
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "AWR_STAGE"."WRH$_SQL_PLAN"                 65.09 MB  218044 行
. . 导入了 "AWR_STAGE"."WRH$_SQLTEXT"                  22.38 MB    4127 行
. . 导入了 "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_2400249746_50009"  17.58 KB       0 行
. . 导入了 "AWR_STAGE"."WRH$_WAITCLASSMETRIC_HISTORY"  939.0 KB   12753 行
. . 导入了 "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_2400249746_50033"  17.58 KB       0 行
. . 导入了 "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_2400249746_50153"  2.877 MB   16168 行
. . 导入了 "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_2400249746_50177"  2.797 MB   15710 行
. . 导入了 "AWR_STAGE"."WRH$_SYSMETRIC_SUMMARY"        2.923 MB   39690 行

处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
     已完成 102 INDEX 个对象, 用时 22 秒
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     已完成 94 CONSTRAINT 个对象, 用时 6 秒
处理对象类型 TABLE_EXPORT/TABLE/COMMENT
     已完成 3 COMMENT 个对象, 用时 0 秒
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     已完成 1 REF_CONSTRAINT 个对象, 用时 1 秒
作业 "SYS"."SYS_IMPORT_FULL_01" 已于 11:04:49 成功完成

 四、验证导入信息

可以通过查看dba_hist_snap表来验证导入的snap信息：

 SQL> select snap_id, dbid, instance_number, to_char(begin_interval_time, 'yyyy-m
m-dd hh24:mi:ss.ff')from dba_hist_snapshot where dbid = 2400249746 order by 1, 3;

   SNAP_ID       DBID INSTANCE_NUMBER TO_CHAR(BEGIN_INTERVAL_TIME,'
---------- ---------- --------------- -----------------------------
     50118 2400249746               1 2011-06-18 14:00:22.112
     50118 2400249746               2 2011-06-18 13:59:50.559
     50119 2400249746               1 2011-06-18 15:00:27.512
     50119 2400249746               2 2011-06-18 14:59:55.957
     50120 2400249746               1 2011-06-18 16:00:28.561
     50120 2400249746               2 2011-06-18 15:59:57.006
     50121 2400249746               1 2011-06-18 17:00:28.000
     50121 2400249746               2 2011-06-18 16:59:56.445
     50122 2400249746               1 2011-06-18 18:00:38.011
     50122 2400249746               2 2011-06-18 18:00:06.456
     50123 2400249746               1 2011-06-18 19:00:50.033

     50123 2400249746               2 2011-06-18 19:00:18.476