ITPub博客

首页 > Linux操作系统 > Linux操作系统 > AWR概述——导出

AWR概述——导出

原创 Linux操作系统 作者:yangtingkun 时间:2011-02-03 23:50:12 0 删除 编辑

AWRSNAP生成后,不一定要在原数据库上生成报告,可以利用AWR提供的导出、导入功能,将AWR数据迁移到测试数据库上进一步分析。

 

 

AWR的导出并不复杂,利用有权限的用户调用$ORACLE_HOME/rdbms/admin/awrextr.sql脚本就可以了:

SQL> @?/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
------------ ------------ ------------
* 96312462   TESTRAC      racnode1
* 96312462   TESTRAC      racnode2
* 96312462   TESTRAC      racnode3
  3944144691 CCICDB       ccicdbsrv5

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

输入 dbid 的值

Using   96312462 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 without
specifying a number lists all completed snapshots.


输入 num_days 的值:  3

Listing the last 3 days of Completed Snapshots

DB Name        Snap Id    Snap Started
------------ --------- ------------------
TESTRAC           2758 14 11
2010 00:00
                  2759 14 11
2010 01:00
                  2760 14 11
2010 02:00
.
.
.
                  2780 14 11
2010 22:00
                  2781 14 11
2010 23:00
                  2782 15 11
2010 00:00
                  2783 15 11
2010 01:00
                  2784 15 11
2010 02:00
                  2785 15 11
2010 03:00
                  2786 15 11
2010 04:00
                  2787 15 11
2010 05:00
                  2788 15 11
2010 06:00
                  2789 15 11
2010 07:00
                  2790 15 11
2010 08:00
                  2791 15 11
2010 09:00
                  2792 15 11
2010 10:00
                  2793 15 11
2010 11:00
                  2794 15 11
2010 12:00
                  2795 15 11
2010 13:00
                  2796 15 11
2010 14:00
                  2797 15 11
2010 15:00
                  2798 15 11
2010 16:00
                  2799 15 11
2010 17:00
                  2800 15 11
2010 18:00
                  2801 15 11
2010 19:00
                  2802 15 11
2010 20:00
                  2803 15 11
2010 21:00
                  2804 15 11
2010 22:00
                  2805 15 11
2010 23:00
                  2806 16 11
2010 00:00
.
.
.
                  2822 16 11
2010 16:00
                  2823 16 11
2010 17:00


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  2782
Begin Snapshot Id specified: 2782

输入 end_snap 的值:  2806
End   Snapshot Id specified: 2806


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

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
ADMIN_DIR                      /data/oracle/product/10.2/database/md/admin
DATA_PUMP_DIR                  /data/oracle/product/10.2/database/rdbms/log/
D_AWR                          /data1/awr
ORACLE_OCM_CONFIG_DIR          /data/oracle/product/10.2/database/ccr/state
WORK_DIR                       /data/oracle/product/10.2/database/work

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

输入 directory_name 的值:  D_AWR

Using the dump directory: D_AWR

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

输入 file_name 的值:  awr_testrac_2782_2806

Using the dump file prefix: awr_testrac_2782_2806
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /data1/awr
|   awr_testrac_2782_2806.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:
|   /data1/awr
|   awr_testrac_2782_2806.log
|
WHERE (dbid, snap_id) IN (SELECT dbid, snap_id FROM SYS.WRM$_SNAPSHOT WHERE DBID = 96312462 AND SNAP_ID >=
2782 AND SNAP_ID <= 2806 AND STATUS   = 0 AND BL_MOVED = 1)
Table List String Length: 2212
('WRM$_WR_CONTROL', 'WRM$_DATABASE_INSTANCE', 'WRM$_SNAPSHOT', 'WRM$_SNAP_ERROR', 'WRH$_STAT_NAME',
'WRH$_PARAMETER_NAME', 'WRH$_EVENT_NAME', 'WRH$_LATCH_NAME', 'WRH$_FILESTATXS', 'WRH$_FILESTATXS_BL',
'WRH$_TEMPSTATXS', 'WRH$_DATAFILE', 'WRH$_TEMPFILE',
'WRH$_SQLSTAT', 'WRH$_SQLSTAT_BL', 'WRH$_SQLTEXT', 'WRH$_SQL_SUMMARY', 'WRH$_SQL_PLAN', 'WRH$_SYSTEM_EVENT',
'WRH$_SYSTEM_EVENT_BL', 'WRH$_BG_EVENT_SUMMARY', 'WRH$_WAITSTAT', 'WRH$_WAITSTAT_BL', 'WRH$_ENQUEUE_STAT',
'WRH$_LATCH', 'WRH$_LATCH_BL', 'WRH$_LA
TCH_CHILDREN', 'WRH$_LATCH_CHILDREN_BL', 'WRH$_LATCH_PARENT', 'WRH$_LATCH_PARENT_BL',
'WRH$_LATCH_MISSES_SUMMARY', 'WRH$_LATCH_MISSES_SUMMARY_BL', 'WRH$_LIBRARYCACHE', 'WRH$_DB_CACHE_ADVICE',
'WRH$_DB_CACHE_ADVICE_BL', 'WRH$_BUFFER_POOL_STATISTICS', 'WRH$
_ROWCACHE_SUMMARY', 'WRH$_ROWCACHE_SUMMARY_BL', 'WRH$_SGA', 'WRH$_SGASTAT', 'WRH$_SGASTAT_BL', 'WRH$_PGASTAT',
'WRH$_PROCESS_MEMORY_SUMMARY', 'WRH$_RESOURCE_LIMIT', 'WRH$_SHARED_POOL_ADVICE',
'WRH$_SQL_WORKAREA_HISTOGRAM', 'WRH$_PGA_TARGET_ADVICE', 'WRH$_
INSTANCE_RECOVERY', 'WRH$_SYSSTAT', 'WRH$_SYSSTAT_BL', 'WRH$_PARAMETER', 'WRH$_PARAMETER_BL', 'WRH$_UNDOSTAT',
'WRH$_SEG_STAT', 'WRH$_SEG_STAT_BL', 'WRH$_SEG_STAT_OBJ', 'WRH$_DLM_MISC', 'WRH$_DLM_MISC_BL',
'WRH$_SERVICE_NAME', 'WRH$_SERVICE_STAT', 'WRH$_S
ERVICE_STAT_BL', 'WRH$_SYSMETRIC_HISTORY', 'WRH$_FILEMETRIC_HISTORY', 'WRH$_WAITCLASSMETRIC_HISTORY',
'WRH$_TABLESPACE_STAT', 'WRH$_TABLESPACE_STAT_BL', 'WRH$_LOG', 'WRH$_MTTR_TARGET_ADVICE',
'WRH$_TABLESPACE_SPACE_USAGE', 'WRH$_METRIC_NAME', 'WRH$_SYSMET
RIC_SUMMARY', 'WRH$_SQL_BIND_METADATA', 'WRH$_JAVA_POOL_ADVICE', 'WRH$_THREAD', 'WRH$_SESSMETRIC_HISTORY',
'WRH$_OSSTAT', 'WRH$_OSSTAT_BL', 'WRH$_OSSTAT_NAME', 'WRH$_SYS_TIME_MODEL', 'WRH$_SYS_TIME_MODEL_BL',
'WRH$_OPTIMIZER_ENV', 'WRH$_SERVICE_WAIT_CLASS
', 'WRH$_SERVICE_WAIT_CLASS_BL', 'WRH$_CR_BLOCK_SERVER', 'WRH$_CURRENT_BLOCK_SERVER',
'WRH$_INST_CACHE_TRANSFER', 'WRH$_INST_CACHE_TRANSFER_BL', 'WRH$_STREAMS_POOL_ADVICE', 'WRH$_COMP_IOSTAT',
'WRH$_SGA_TARGET_ADVICE', 'WRH$_SESS_TIME_STATS', 'WRH$_STREAM
S_CAPTURE', 'WRH$_STREAMS_APPLY_SUM', 'WRH$_BUFFERED_QUEUES', 'WRH$_BUFFERED_SUBSCRIBERS', 'WRH$_RULE_SET',
'WRH$_ACTIVE_SESSION_HISTORY', 'WRH$_ACTIVE_SESSION_HISTORY_BL')
启动 "SYS"."SYS_EXPORT_TABLE_01":
正在使用 BLOCKS 方法进行估计
...
处理对象类型
TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计
: 2.170 GB
处理对象类型
TABLE_EXPORT/TABLE/TABLE
处理对象类型
TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型
TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型
TABLE_EXPORT/TABLE/COMMENT
处理对象类型
TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. .
导出了 "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_3944144691_0"  16.67 KB       0

. .
导出了 "SYS"."WRH$_SQL_PLAN"                       3.088 MB    8851
. .
导出了 "SYS"."WRH$_SQLTEXT"                        923.4 KB     807
. .
导出了 "SYS"."WRH$_FILESTATXS":"WRH$_FILEST_3944144691_0"  9.187 KB       0
. .
导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_3944144691_0"  23.03 KB       0
. .
导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_3944144691_0"  9.476 KB       0
. .
导出了 "SYS"."WRH$_SEG_STAT":"WRH$_SEG_ST_3944144691_0"  16.61 KB       0
. .
导出了 "SYS"."WRH$_SYSMETRIC_SUMMARY"              479.5 KB    6750
. .
导出了 "SYS"."WRH$_LATCH_MISSES_SUMMARY":"WRH$_LATCH__3944144691_0"  7.070 KB       0
. .
导出了 "SYS"."WRH$_PARAMETER":"WRH$_PARAME_3944144691_0"   6.75 KB       0
. .
导出了 "SYS"."WRH$_SYSSTAT":"WRH$_SYSSTA_3944144691_0"  6.125 KB       0
. .
导出了 "SYS"."WRH$_ENQUEUE_STAT"                   170.1 KB    3200
. .
导出了 "SYS"."WRH$_SYSTEM_EVENT":"WRH$_SYSTEM_3944144691_0"  6.757 KB       0
. .
导出了 "SYS"."WRH$_SERVICE_STAT":"WRH$_SERVIC_3944144691_0"  6.437 KB       0
. .
导出了 "SYS"."WRH$_ROWCACHE_SUMMARY":"WRH$_ROWCAC_3944144691_0"  9.507 KB       0
. .
导出了 "SYS"."WRH$_DB_CACHE_ADVICE":"WRH$_DB_CAC_3944144691_0"  8.625 KB       0
. .
导出了 "SYS"."WRH$_BG_EVENT_SUMMARY"               84.07 KB    2179
. .
导出了 "SYS"."WRH$_JAVA_POOL_ADVICE"               15.94 KB     200
. .
导出了 "SYS"."WRH$_PGASTAT"                        38.73 KB     726
. .
导出了 "SYS"."WRH$_SHARED_POOL_ADVICE"             47.21 KB     770
. .
导出了 "SYS"."WRH$_SQL_WORKAREA_HISTOGRAM"         21.71 KB     375
. .
导出了 "SYS"."WRH$_TEMPSTATXS"                     11.72 KB      50
. .
导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_96312462_2735"  9.476 KB       0
. .
导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_96312462_2759"  48.30 KB     788
. .
导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_96312462_2783"  940.7 KB   18912
. .
导出了 "SYS"."WRH$_LIBRARYCACHE"                   39.07 KB     550
. .
导出了 "SYS"."WRH$_PGA_TARGET_ADVICE"              38.35 KB     700
. .
导出了 "SYS"."WRH$_SERVICE_WAIT_CLASS":"WRH$_SERVIC_3944144691_0"  7.070 KB       0
. .
导出了 "SYS"."WRH$_SGASTAT":"WRH$_SGASTA_3944144691_0"  6.421 KB       0
. .
导出了 "SYS"."WRH$_SQL_BIND_METADATA"              138.1 KB    2396
. .
导出了 "SYS"."WRH$_STREAMS_POOL_ADVICE"            23.07 KB     500
. .
导出了 "SYS"."WRH$_TABLESPACE_SPACE_USAGE"         16.67 KB     200
. .
导出了 "SYS"."WRH$_TABLESPACE_STAT":"WRH$_TABLES_3944144691_0"  7.679 KB       0
. .
导出了 "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_96312462_2615"  16.67 KB       0
. .
导出了 "SYS"."WRH$_BUFFER_POOL_STATISTICS"         16.67 KB      50
. .
导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_96312462_2615"  9.476 KB       0
. .
导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_96312462_2639"  9.476 KB       0
. .
导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_96312462_2663"  9.476 KB       0
. .
导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_96312462_2687"  9.476 KB       0
. .
导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_96312462_2711"  9.476 KB       0
. .
导出了 "SYS"."WRH$_LOG"                            27.14 KB     300
. .
导出了 "SYS"."WRH$_OSSTAT":"WRH$_OSSTAT_3944144691_0"  6.125 KB       0
. .
导出了 "SYS"."WRH$_PROCESS_MEMORY_SUMMARY"         21.75 KB     200
. .
导出了 "SYS"."WRH$_RESOURCE_LIMIT"                 30.50 KB     400
. .
导出了 "SYS"."WRH$_SGA"                            13.39 KB     200
. .
导出了 "SYS"."WRH$_SYS_TIME_MODEL":"WRH$_SYS_TI_3944144691_0"  6.132 KB       0
. .
导出了 "SYS"."WRH$_UNDOSTAT"                       35.87 KB     300
. .
导出了 "SYS"."WRH$_WAITSTAT":"WRH$_WAITST_3944144691_0"  6.429 KB       0
. .
导出了 "SYS"."WRH$_SEG_STAT_OBJ"                   110.4 KB    1055
. .
导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_96312462_2615"  23.03 KB       0
. .
导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_96312462_2639"  23.03 KB       0
. .
导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_96312462_2663"  23.03 KB       0
. .
导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_96312462_2687"  23.03 KB       0
. .
导出了 "SYS"."WRH$_MTTR_TARGET_ADVICE"             8.351 KB       0
. .
导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_96312462_2711"  23.03 KB       0
. .
导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_96312462_2735"  23.03 KB       0
. .
导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_96312462_2759"  52.85 KB     143
. .
导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_96312462_2783"  779.6 KB    3588
. .
导出了 "SYS"."WRH$_INSTANCE_RECOVERY"              14.45 KB      50
. .
导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_96312462_2807"  9.476 KB       0
. .
导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_96312462_2807"  23.03 KB       0
. .
导出了 "SYS"."WRH$_THREAD"                         13.41 KB     150
. .
导出了 "SYS"."WRH$_PARAMETER":"WRH$_PARAME_96312462_2639"   6.75 KB       0
. .
导出了 "SYS"."WRH$_PARAMETER":"WRH$_PARAME_96312462_2663"   6.75 KB       0
. .
导出了 "SYS"."WRH$_PARAMETER":"WRH$_PARAME_96312462_2687"   6.75 KB       0
. .
导出了 "SYS"."WRH$_PARAMETER":"WRH$_PARAME_96312462_2735"   6.75 KB       0
.
.
.
. .
导出了 "SYS"."WRH$_WAITSTAT_BL"                        0 KB       0
. .
导出了 "SYS"."WRM$_SNAP_ERROR"                         0 KB       0
. .
导出了 "SYS"."WRR$_CAPTURES"                           0 KB       0
. .
导出了 "SYS"."WRR$_CAPTURE_STATS"                      0 KB       0
. .
导出了 "SYS"."WRR$_FILTERS"                            0 KB       0
已成功加载/卸载了主表 "SYS"."SYS_EXPORT_TABLE_01"
******************************************************************************
SYS.SYS_EXPORT_TABLE_01
的转储文件集为
:
/data1/awr/awr_testrac_2782_2806.dmp
作业 "SYS"."SYS_EXPORT_TABLE_01" 已于 17:32:11 成功完成

操作执行后,会生成两个文件,一个导出的dmp文件,另一个日志文件:

SQL> host ls -l /data1/awr/awr_testrac*
-rw-rw----   1 oracle   oinstall 17158144 Nov 16 17:32 /data1/awr/awr_testrac_2782_2806.dmp
-rw-rw-r--   1 oracle   oinstall   25939 Nov 16 17:32 /data1/awr/awr_testrac_2782_2806.log

其中dmp文件就是脚本利用数据泵导出的snap数据,将这个文件拷贝到目标数据库上执行导入,就可以实现AWR数据的迁移。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-686803/,如需转载,请注明出处,否则将追究法律责任。

下一篇: AWR概述——导入
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10525225