有些时候,我们分析客户的数据库的性能,需要请客户将AWR数据导出,然后导入本机数据库进行分析。下面简单介绍下怎样用awr导出分析数据。
一、在导出数据之前,一般需要创建以下路径
DATA_FILE_DIR
DATA_PUMP_DIR
EXP
IMP
SQL> create directory exp as 'd:\exp';
SQL> create directory data_file_dir as 'd:\dir';
SQL> create directory exp as 'd:\exp';
二、导出
SQL> @?/rdbms/admin/awrextr --运行awrextr.sql脚本
~~~~~~~~~~~~~
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
------------ ------------ ------------
* 1520519778 STREAM STREAM
2400249746 CNDERPDB p5a1
2400249746 CNDERPDB p5b1
The default database id is the local one: '1520519778'. To use this
database id, press <return> to continue, otherwise enter an alternative.
输入 dbid 的值: 1520519778
Using 1520519778 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 <return> without
specifying a number lists all completed snapshots.
输入 num_days 的值: 1 ---输入要导出数据的天数
Listing the last day's Completed Snapshots
DB Name Snap Id Snap Started
------------ --------- ------------------
STREAM 7 23 6月 2011 09:30
8 23 6月 2011 11:00
9 23 6月 2011 12:00
10 23 6月 2011 13:00
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值: 7
Begin Snapshot Id specified: 7
输入 end_snap 的值: 10
End Snapshot Id specified: 10
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 above list (case-sensitive).
输入 directory_name 的值: DATA_FILE_DIR --注意一定要大写
Using the dump directory: DATA_FILE_DIR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_7_10.
To use this name, press <return> to continue, otherwise enter
an alternative.
输入 file_name 的值: awr_7_10 --导出文件的名字,不要加后缀
Using the dump file prefix: awr_7_10
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| D:\dir
| awr_7_10.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:
| D:\dir
| awr_7_10.log
|
End of AWR Extract
SQL>
三、下面是部分导出的日志
启动 "SYS"."SYS_EXPORT_TABLE_01":
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 375.3 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
已完成 128 TABLE 个对象, 用时 11 秒
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
已完成 121 CONSTRAINT 个对象, 用时 1 秒
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
已完成 1 REF_CONSTRAINT 个对象, 用时 1 秒
. . 导出了 "SYS"."WRH$_SQL_PLAN" 1.829 MB 5318 行
. . 导出了 "SYS"."WRH$_SQLTEXT" 208.9 KB 202 行
. . 导出了 "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_2400249746_0" 31.83 KB 0 行
. . 导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_2400249746_0" 10.99 KB 0 行
. . 导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_2400249746_0" 36.64 KB 0 行
. . 导出了 "SYS"."WRH$_SEG_STAT":"WRH$_SEG_ST_2400249746_0" 22.76 KB 0 行
. . 导出了 "SYS"."WRH$_LATCH_MISSES_SUMMARY":"WRH$_LATCH__2400249746_0" 7.820 KB 0 行
. . 导出了 "SYS"."WRH$_PARAMETER":"WRH$_PARAME_2400249746_0" 7.406 KB 0 行
. . 导出了 "SYS"."WRH$_SEG_STAT_OBJ" 44.17 KB 365 行
. . 导出了 "SYS"."WRH$_SYSSTAT":"WRH$_SYSSTA_2400249746_0" 6.593 KB 0 行
. . 导出了 "SYS"."WRH$_SYSMETRIC_SUMMARY" 59.72 KB 632 行
. . 导出了 "SYS"."WRH$_SQL_BIND_METADATA" 104.5 KB 1721 行
. . 导出了 "SYS"."WRH$_SYSTEM_EVENT":"WRH$_SYSTEM_2400249746_0" 8.632 KB 0 行
. . 导出了 "SYS"."WRM$_SNAP_ERROR" 0 KB 0 行
已成功加载/卸载了主表 "SYS"."SYS_EXPORT_TABLE_01"
******************************************************************************
SYS.SYS_EXPORT_TABLE_01 的转储文件集为:
D:\DIR\AWR_7_10.DMP
作业 "SYS"."SYS_EXPORT_TABLE_01" 已于 13:45:08 成功完成