联系：手机/微信(+86 17813235971) QQ(107644445)
标题：awr导出/导入/分析
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
很多时候我们直接在客户机器上分析awr不太方便,需要通过收集客户awr信息到另一台机器上进行分析数据库性能等.这种情况下,就需要对客户的awr数据进行导出,然后导入到其他机器上,再进行深入分析.
导出awr数据
SQL> @?/rdbms/admin/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
------------ ------------ ------------
* 1393262699 XIFENFEI XIFENFEI-PC
3753332923 FDJDB ora1
3753332923 FDJDB ora2
The default database id is the local one: '1393262699'. To use this
database id, press <return> to continue, otherwise enter an alternative.
输入 dbid 的值: 3753332923 <--需要输入
Using 3753332923 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
------------ --------- ------------------
FDJDB 906 23 4月 2012 00:00
907 23 4月 2012 01:00
908 23 4月 2012 02:00
909 23 4月 2012 03:00
910 23 4月 2012 04:00
911 23 4月 2012 05:00
912 23 4月 2012 06:00
913 23 4月 2012 07:00
914 23 4月 2012 08:00
915 23 4月 2012 09:00
916 23 4月 2012 10:00
917 23 4月 2012 11:00
918 23 4月 2012 12:00
919 23 4月 2012 13:00
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值: 906 <--需要输入
Begin Snapshot Id specified: 906
输入 end_snap 的值: 907 <--需要输入
End Snapshot Id specified: 907
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIR E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIR E:\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\ord er_entry\
SUBDIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIR E:\oracle\product\11.2.0\dbhome_1\rdbms\xml
Choose a Directory Name from the above list (case-sensitive).
输入 directory_name 的值: DATA_PUMP_DIR <--需要输入(注意大小写)
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_906_907.
To use this name, press <return> to continue, otherwise enter
an alternative.
输入 file_name 的值: xifenfei_awr <--需要输入
Using the dump file prefix: xifenfei_awr
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
| xifenfei_awr.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:
| E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
| xifenfei_awr.log
| 可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\xifenfei_awr.log
| 监控导出awr数据进度
End of AWR Extract
导入awr数据
SQL> @E:\oracle\product\11.2.0\dbhome_1\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_FILE_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\ DATA_PUMP_DIR E:\oracle\product\11.2.0\dbhome_1\rdbms\log\ LOG_FILE_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\ MEDIA_DIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\ ORACLE_OCM_CONFIG_DIR E:\oracle\product\11.2.0\dbhome_1\ccr\state SS_OE_XMLDIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\ SUBDIR E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep XMLDIR E:\oracle\product\11.2.0\dbhome_1\rdbms\xml Choose a Directory Name from the list above (case-sensitive). 输入 directory_name 的值: DATA_PUMP_DIR <--需要输入(注意大小写) Using the dump directory: DATA_PUMP_DIR Specify the Name of the Dump File to Load ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Please specify the prefix of the dump file (.dmp) to load: 输入 file_name 的值: awrdat_751_919 <--需要输入(文件后缀名一定要是.dmp) Loading from the file name: awrdat_751_919.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 的值: XFF_AWR <--需要输入(临时创建用户) Using the staging schema name: XFF_AWR Choose the Default tablespace for the XFF_AWR user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Choose the XFF_AWR users's default tablespace. This is the tablespace in which the AWR data will be staged. TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE ------------------------------ --------- ------------------ EXAMPLE PERMANENT SYSAUX PERMANENT * USERS PERMANENT Pressing <return> will result in the recommended default tablespace (identified by *) being used. 输入 default_tablespace 的值: EXAMPLE <--需要输入 Using tablespace EXAMPLE as the default tablespace for the XFF_AWR Choose the Temporary tablespace for the XFF_AWR user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Choose the XFF_AWR 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 XFF_AWR ... Creating XFF_AWR user (临时用户创建) | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Loading the AWR data from the following | directory/file: | E:\oracle\product\11.2.0\dbhome_1\rdbms\log\ | awrdat_751_919.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: | E:\oracle\product\11.2.0\dbhome_1\rdbms\log\ | awrdat_751_919.log | | 可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\awrdat_751_919.log | 监控导出awr数据进度 ... Dropping XFF_AWR user (临时用户被删除) End of AWR Load
查看awr报告
SQL> @?/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'
输入 report_type 的值: html <--需要输入
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
3753332923 2 FDJDB fdjdb2 ora2
3753332923 1 FDJDB fdjdb1 ora1
* 1393262699 1 XIFENFEI xff XIFENFEI-PC
输入 dbid 的值: 3753332923 <--需要输入
Using 3753332923 for database Id
输入 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.
输入 num_days 的值: 1 <--需要输入
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
fdjdb1 FDJDB 906 23 4月 2012 00:00 1
907 23 4月 2012 01:00 1
908 23 4月 2012 02:00 1
909 23 4月 2012 03:00 1
910 23 4月 2012 04:00 1
911 23 4月 2012 05:00 1
912 23 4月 2012 06:00 1
913 23 4月 2012 07:00 1
914 23 4月 2012 08:00 1
915 23 4月 2012 09:00 1
916 23 4月 2012 10:00 1
917 23 4月 2012 11:00 1
918 23 4月 2012 12:00 1
919 23 4月 2012 13:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值: 917 <--需要输入
Begin Snapshot Id specified: 917
输入 end_snap 的值: 918 <--需要输入
End Snapshot Id specified: 918
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_917_918.html. To use this name,
press <return> to continue, otherwise enter an alternative.
输入 report_name 的值:xifenfei_awr.html <--需要输入
导入时,建议所有值大写,不然可能出现如下错误
awr导出脚本
begin /* call PL/SQL routine to extract the data */ dbms_swrf_internal.awr_extract(dmpfile => :dmpfile, dmpdir => :dmpdir, bid => :bid, eid => :eid, dbid => :dbid); dbms_swrf_internal.clear_awr_dbid; end; /awr导入脚本
begin /* call PL/SQL routine to load the data into the staging schema */ dbms_swrf_internal.awr_load(schname => :schname, dmpfile => :dmpfile, dmpdir => :dmpdir); end; / begin /* call PL/SQL routine to move the data into AWR */ dbms_swrf_internal.move_to_awr(schname => :schname); dbms_swrf_internal.clear_awr_dbid; end; /使用 exec DBMS_SWRF_INTERNAL.UNREGISTER_DATABASE(dbid);清理掉不需要的数据库的awr报告