AWR的SNAP生成后，不一定要在原数据库上生成报告，可以利用AWR提供的导出、导入功能，将AWR数据迁移到测试数据库上进一步分析。
将上一篇文章导出的dmp文件拷贝到目标数据库的指定DIRECTORY目录下，就可以调用$ORACLE_HOME/rdbms/admin/awrload.sql脚本执行导入了：
SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Disclaimer: This SQL/Plus script. should only be called under
the guidance of Oracle Support.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~
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
------------------------------ -------------------------------------------------
ADMIN_DIR /opt/ora10g/product/10.2.0/db_1/md/admin
DATA_PUMP_DIR /opt/ora10g/product/10.2.0/db_1/rdbms/log/
DMP_DIR /data/backup
DUMP_DIR /home/oracle/shiyq/
DUMP_FILE_DIR /data/backup/dmp
D_OUTPUT /home/oracle
D_TRANS /data/oradata/test08
EXTDIR /home/oracle/script/
TRAN /data/backup
WORK_DIR /opt/ora10g/product/10.2.0/db_1/work
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: D_OUTPUT
Using the dump directory: D_OUTPUT
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: awr_testrac_2782_2806
Loading from the file name: awr_testrac_2782_2806.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
------------------------------ --------- ------------------
BOOKS PERMANENT
GPO PERMANENT
INFO PERMANENT
JIANGSU PERMANENT
JIESUAN PERMANENT
JSHC_TEST PERMANENT
JSTBS229 PERMANENT
JS_HC PERMANENT
JS_LYG PERMANENT
JS_NDMAIN PERMANENT
JS_TEMP PERMANENT
JS_TRADE PERMANENT
NDMAIN PERMANENT
SCKC PERMANENT
SCOTT_TBS PERMANENT
SHIYQ PERMANENT
SYSAUX PERMANENT *
TEST PERMANENT
TJSQ PERMANENT
ZJTBS PERMANENT
ZJ_LPD PERMANENT
Pressing <return> will result in the recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: sysaux
Using tablespace SYSAUX 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.
Enter value for temporary_tablespace:
Using tablespace TEMP as the temporary tablespace for AWR_STAGE
... Creating AWR_STAGE user
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /home/oracle
| awr_testrac_2782_2806.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:
| /home/oracle
| awr_testrac_2782_2806.log
|
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_0" 16.67 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQL_PLAN" 3.088 MB 8851 rows
. . imported "AWR_STAGE"."WRH$_SQLTEXT" 923.4 KB 807 rows
. . imported "AWR_STAGE"."WRH$_FILESTATXS":"WRH$_FILEST_3944144691_0" 9.187 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT":"WRH$_SQLSTA_3944144691_0" 23.03 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_3944144691_0" 9.476 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT":"WRH$_SEG_ST_3944144691_0" 16.61 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSMETRIC_SUMMARY" 479.5 KB 6750 rows
. . imported "AWR_STAGE"."WRH$_LATCH_MISSES_SUMMARY":"WRH$_LATCH__3944144691_0" 7.070 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER":"WRH$_PARAME_3944144691_0" 6.75 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT":"WRH$_SYSSTA_3944144691_0" 6.125 KB 0 rows
. . imported "AWR_STAGE"."WRH$_ENQUEUE_STAT" 170.1 KB 3200 rows
. . imported "AWR_STAGE"."WRH$_SYSTEM_EVENT":"WRH$_SYSTEM_3944144691_0" 6.757 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SERVICE_STAT":"WRH$_SERVIC_3944144691_0" 6.437 KB 0 rows
. . imported "AWR_STAGE"."WRH$_ROWCACHE_SUMMARY":"WRH$_ROWCAC_3944144691_0" 9.507 KB 0 rows
. . imported "AWR_STAGE"."WRH$_DB_CACHE_ADVICE":"WRH$_DB_CAC_3944144691_0" 8.625 KB 0 rows
. . imported "AWR_STAGE"."WRH$_BG_EVENT_SUMMARY" 84.07 KB 2179 rows
. . imported "AWR_STAGE"."WRH$_JAVA_POOL_ADVICE" 15.94 KB 200 rows
. . imported "AWR_STAGE"."WRH$_PGASTAT" 38.73 KB 726 rows
. . imported "AWR_STAGE"."WRH$_SHARED_POOL_ADVICE" 47.21 KB 770 rows
. . imported "AWR_STAGE"."WRH$_SQL_WORKAREA_HISTOGRAM" 21.71 KB 375 rows
. . imported "AWR_STAGE"."WRH$_TEMPSTATXS" 11.72 KB 50 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_96312462_2735" 9.476 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_96312462_2759" 48.30 KB 788 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_96312462_2783" 940.7 KB 18912 rows
. . imported "AWR_STAGE"."WRH$_LIBRARYCACHE" 39.07 KB 550 rows
. . imported "AWR_STAGE"."WRH$_PGA_TARGET_ADVICE" 38.35 KB 700 rows
. . imported "AWR_STAGE"."WRH$_SERVICE_WAIT_CLASS":"WRH$_SERVIC_3944144691_0" 7.070 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SGASTAT":"WRH$_SGASTA_3944144691_0" 6.421 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQL_BIND_METADATA" 138.1 KB 2396 rows
. . imported "AWR_STAGE"."WRH$_STREAMS_POOL_ADVICE" 23.07 KB 500 rows
. . imported "AWR_STAGE"."WRH$_TABLESPACE_SPACE_USAGE" 16.67 KB 200 rows
. . imported "AWR_STAGE"."WRH$_TABLESPACE_STAT":"WRH$_TABLES_3944144691_0" 7.679 KB 0 rows
. . imported "AWR_STAGE"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_96312462_2615" 16.67 KB 0 rows
. . imported "AWR_STAGE"."WRH$_BUFFER_POOL_STATISTICS" 16.67 KB 50 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_96312462_2615" 9.476 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_96312462_2639" 9.476 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_96312462_2663" 9.476 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_96312462_2687" 9.476 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_96312462_2711" 9.476 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LOG" 27.14 KB 300 rows
.
.
.
. . imported "AWR_STAGE"."WRM$_SNAP_ERROR" 0 KB 0 rows
. . imported "AWR_STAGE"."WRR$_CAPTURES" 0 KB 0 rows
. . imported "AWR_STAGE"."WRR$_CAPTURE_STATS" 0 KB 0 rows
. . imported "AWR_STAGE"."WRR$_FILTERS" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 08:10:04
Register the DBID: 96312462
Append Data for AWR_STAGE.WRM$_DATABASE_INSTANCE.
Append Data for AWR_STAGE.WRM$_SNAPSHOT.
Append Data for AWR_STAGE.WRH$_STAT_NAME.
Append Data for AWR_STAGE.WRH$_PARAMETER_NAME.
Append Data for AWR_STAGE.WRH$_EVENT_NAME.
Append Data for AWR_STAGE.WRH$_LATCH_NAME.
Append Data for AWR_STAGE.WRH$_FILESTATXS.
Append Data for AWR_STAGE.WRH$_FILESTATXS_BL.
Append Data for AWR_STAGE.WRH$_TEMPSTATXS.
Append Data for AWR_STAGE.WRH$_DATAFILE.
Append Data for AWR_STAGE.WRH$_TEMPFILE.
.
.
.
Append Data for AWR_STAGE.WRH$_STREAMS_CAPTURE.
Append Data for AWR_STAGE.WRH$_STREAMS_APPLY_SUM.
Append Data for AWR_STAGE.WRH$_BUFFERED_QUEUES.
Append Data for AWR_STAGE.WRH$_BUFFERED_SUBSCRIBERS.
Append Data for AWR_STAGE.WRH$_RULE_SET.
Append Data for AWR_STAGE.WRH$_ACTIVE_SESSION_HISTORY.
Append Data for AWR_STAGE.WRH$_ACTIVE_SESSION_HISTORY_BL.
Append Data for AWR_STAGE.WRM$_SNAP_ERROR.
Finished MOVE_TO_AWR procedure
... Dropping AWR_STAGE user
End of AWR Load
导入需要注意几点，输入DIRECTORY的时候注意大小写，另外输入导入的文件名时只输入前缀，不要输入完整的文件名。以这篇文档为例，输入文件名前缀为awr_testrac_2782_2806，如果输入awr_testrac_2782_2806.dmp则会导致错误。
在导致完成后，可以从dba_hist_snap中查询到导入数据库snap信息：
SQL> select snap_id, dbid, instance_number, to_char(begin_interval_time, 'yyyy-mm-dd hh24:mi:ss.ff')
2 from dba_hist_snapshot
3 where dbid = 96312462
4 order by 1, 3;
SNAP_ID DBID INSTANCE_NUMBER TO_CHAR(BEGIN_INTERVAL_TIME,'
---------- ---------- --------------- -----------------------------
2782 96312462 1 2010-11-14 23:00:33.558
2782 96312462 2 2010-11-14 22:18:21.039
2783 96312462 1 2010-11-15 00:00:47.878
2783 96312462 2 2010-11-14 23:18:37.328
2784 96312462 1 2010-11-15 01:00:06.999
2784 96312462 2 2010-11-15 00:17:56.418
2785 96312462 1 2010-11-15 02:00:23.281
2785 96312462 2 2010-11-15 01:18:12.682
2786 96312462 1 2010-11-15 03:00:40.411
2786 96312462 2 2010-11-15 02:18:29.772
2787 96312462 1 2010-11-15 04:00:54.418
2787 96312462 2 2010-11-15 03:18:43.736
2788 96312462 1 2010-11-15 05:00:07.425
2788 96312462 2 2010-11-15 04:17:56.718
2789 96312462 1 2010-11-15 06:00:22.258
2789 96312462 2 2010-11-15 05:18:11.522
2790 96312462 1 2010-11-15 07:00:36.352
2790 96312462 2 2010-11-15 06:18:23.579
2791 96312462 1 2010-11-15 08:00:48.631
2791 96312462 2 2010-11-15 07:18:35.829
2792 96312462 1 2010-11-15 09:00:05.920
2792 96312462 2 2010-11-15 08:17:53.085
2793 96312462 1 2010-11-15 10:00:18.628
2793 96312462 2 2010-11-15 09:18:05.760
2794 96312462 1 2010-11-15 11:00:37.411
2794 96312462 2 2010-11-15 10:18:24.515
2795 96312462 1 2010-11-15 12:00:52.561
2795 96312462 2 2010-11-15 11:18:39.625
2796 96312462 1 2010-11-15 13:00:04.401
2796 96312462 2 2010-11-15 12:17:51.461
2797 96312462 1 2010-11-15 14:00:15.792
2797 96312462 2 2010-11-15 13:18:04.793
2798 96312462 1 2010-11-15 15:00:30.367
2798 96312462 2 2010-11-15 14:18:19.335
2799 96312462 1 2010-11-15 16:00:44.030
2799 96312462 2 2010-11-15 15:18:32.968
2800 96312462 1 2010-11-15 17:00:59.182
2800 96312462 2 2010-11-15 16:18:48.089
2801 96312462 1 2010-11-15 18:00:13.600
2801 96312462 2 2010-11-15 17:18:02.475
2802 96312462 1 2010-11-15 19:00:31.430
2802 96312462 2 2010-11-15 18:18:20.276
2803 96312462 1 2010-11-15 20:00:46.153
2803 96312462 2 2010-11-15 19:18:34.964
2804 96312462 1 2010-11-15 21:00:59.480
2804 96312462 2 2010-11-15 20:18:48.255
2805 96312462 1 2010-11-15 22:00:13.243
2805 96312462 2 2010-11-15 21:18:01.986
2806 96312462 1 2010-11-15 23:00:25.410
2806 96312462 2 2010-11-15 22:18:12.157
50 rows selected.
可以看到，RAC两个实例对应的snap全部导入到目标数据库中。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
