Tag Archives: awr

Linux中导入AWR报告时文件名后缀问题

导入AWR报告的过程中报错:

SQL> @?/rdbms/admin/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
 ------------------------------ -------------------------------------------------
 ORA11G_DIR /home/ora11g
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: ORA11G_DIR
Using the dump directory: ORA11G_DIR
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: AWRDAT_5318_5498
Loading from the file name: AWRDAT_5318_5498.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 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
 ------------------------------ --------- ------------------
 S PERMANENT
 SYSAUX PERMANENT *
 USERS PERMANENT
Pressing will result in the recommended default
 tablespace (identified by *) being used.
Enter value for default_tablespace:
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 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/ora11g
 | AWRDAT_5318_5498.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/ora11g
 | AWRDAT_5318_5498.log
 |
 DBMS_DATAPUMP.ADD_FILE(dump file)
 ORA-39001: invalid argument value
 Exception encountered in AWR_LOAD
 begin
 *
 ERROR at line 1:
 ORA-20115: datapump import encountered error:
 ORA-39001: invalid argument value
 ORA-39000: bad dump file specification
 ORA-31640: unable to open dump file "/home/ora11g/AWRDAT_5318_5498.dmp" for read
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1717
 ORA-06512: at line 3
begin
 *
 ERROR at line 1:
 ORA-20106: AWR tables do not exist for the 'AWR_STAGE' user
 ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 2920
 ORA-00942: table or view does not exist
 ORA-06512: at line 3
... Dropping AWR_STAGE user
End of AWR Load

仔细查看报错,发现找不到/home/ora11g/AWRDAT_5318_5498.dmp这个文件。

查看/home/ora11g下的文件,发现有这个文件,不过仔细查看,其后缀为DMP!

[[email protected] ora11g]# ls -lt *.dmp *.DMP
 -rw-r--r-- 1 ora11g oinstall 56430592 Feb 15 16:41 awrdat_929_976.dmp
 -rw-r--r-- 1 ora11g oinstall 77807616 Feb 15 10:41 AWRDAT_5318_5498.DMP

我们将其修改为dmp再次尝试导入

 

[[email protected] ora11g]# mv AWRDAT_5318_5498.DMP awrdat_5318_5498.dmp
SQL> @?/rdbms/admin/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
 ------------------------------ -------------------------------------------------
 ORA11G_DIR /home/ora11g
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: ORA11G_DIR
Using the dump directory: ORA11G_DIR
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: awrdat_5318_5498
Loading from the file name: awrdat_5318_5498.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 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
 ------------------------------ --------- ------------------
 ALAND PERMANENT
 MARSHALL PERMANENT
 MARVEN PERMANENT
 S PERMANENT
 SYSAUX PERMANENT *
 USERS PERMANENT
 XXX PERMANENT
Pressing will result in the recommended default
 tablespace (identified by *) being used.
Enter value for default_tablespace:
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 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/ora11g
 | awrdat_5318_5498.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/ora11g
 | awrdat_5318_5498.log
 |
... Dropping AWR_STAGE user
End of AWR Load


导入成功!

注意:在导入dmp文件时,一定要注意以下几点:
1.Directory Name输入一定为大写。
2.注意此Directory Name的权限及dmp文件权限。
3.在linux中,dmp文件的后缀名一定要为小写的dmp,如果是大写的DMP,会导致无法导入。
4.细心就好,load不是关键的操作,不会导致什么过分严重的问题,往死里整就行。

由于权限问题无法生成AWR报告

生成AWR报告的时候遇到如下错误:

~~~~~~~~~~~~~~~~~~~~~~~
 The default report file name is awrrpt_1_7796_7797.html. To use this name,
 press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_7796_7797.html
 select output from table(dbms_workload_repository.awr_report_html( :dbid,
 *
 ERROR at line 1:
 ORA-00904: : invalid identifier
Report written to awrrpt_1_7796_7797.html

这是因为用户没有生成AWR报告的权限。如果希望一个用户能生成AWR报告,最少需要几个权限呢?
我认为应是三个:
1.connect/create session
2.select any dictionary(查询字表)
3.execute on any dbms_workload_repository(负载信息库)

通过以下实验证明:

Last login: Tue Dec 13 16:00:29 2011 from 58.242.2.52
 [[email protected] ~]$ sql
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 14 07:51:45 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user test1 cascade;
User dropped.
SQL> create user awr identified by awr;
User created.
SQL> grant connect to awr;
Grant succeeded.
SQL> grant select any dictionary to awr;
Grant succeeded.
SQL> grant execute on DBMS_WORKLOAD_REPOSITORY to awr;
Grant succeeded.
SQL> conn awr/awr
 Connected.
 SQL> @?/rdbms/admin/awrrpt
Current Instance
 ~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
 ----------- ------------ -------- ------------
 1288916317 ORCL 1 orcl
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'
 Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
 ------------ -------- ------------ ------------ ------------
 * 1288916317 1 ORCL orcl hpserver
Using 1288916317 for database Id
 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 without
 specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
 Instance DB Name Snap Id Snap Started Level
 ------------ ------------ --------- ------------------ -----
 orcl ORCL 2136 14 Dec 2011 00:00 1
 2137 14 Dec 2011 01:00 1
 2138 14 Dec 2011 02:00 1
 2139 14 Dec 2011 03:00 1
 2140 14 Dec 2011 04:00 1
 2141 14 Dec 2011 05:00 1
 2142 14 Dec 2011 06:00 1
 2143 14 Dec 2011 07:00 1
Specify the Begin and End Snapshot Ids
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Enter value for begin_snap: 2142
 Begin Snapshot Id specified: 2142
Enter value for end_snap: 2143
 End Snapshot Id specified: 2143
Specify the Report Name
 ~~~~~~~~~~~~~~~~~~~~~~~
 The default report file name is awrrpt_1_2142_2143.html. To use this name,
 press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_2142_2143.html
 …省略…
End of Report
Report written to awrrpt_1_2142_2143.html

网上有人说连查询字典表的权限都不需要,我们测试下:

SQL> revoke select any dictionary from awr;
Revoke succeeded.
SQL> conn awr/awr
 Connected.
 SQL> @?/rdbms/admin/awrrpt
Current Instance
 ~~~~~~~~~~~~~~~~
 v$instance i
 *
 ERROR at line 6:
 ORA-00942: table or view does not exist
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'
 Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 from dba_hist_database_instance wr, v$database cd, v$instance ci
 *
 ERROR at line 14:
 ORA-00942: table or view does not exist

报错,提示没有此表或view。在什么情况下会提示呢?
1.真的没有这个对象(我们知道,这个可以有)
2.用户没权限查看此对象。

综上所述,如果一个用户需要生成awr,需要至少对他进行如下赋权:
grant connect to [user];
grant select any dictionary to [user];
grant execute on DBMS_WORKLOAD_REPOSITORY to [user];

参考资料:
http://cyzhang1983.itpub.net/post/40572/499634
http://www.itpub.net/thread-1330158-1-1.html

导入中断导致AWR报告内容为空

导入AWR报告后,命令行迟迟不返回成功的提示,但是log中已经显示成功了,故手动中断命令行。生成AWR报告时可看到DBID,但是生成的报告为空:

 

SQL> @?/rdbms/admin/awrrpti 
输入 report_type 的值: 
Type Specified:  html 
Instances in this Workload Repository schema 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
  DB Id     Inst Num DB Name      Instance     Host 
------------ -------- ------------ ------------ ------------ 
  1656146280        1 ACSCNPRD     acscnprd     CNPEKS024 
* 1276130704        1 ORCL         orcl         MARSHALL-PC 
输入 dbid 的值:  1656146280 
Using 1656146280 for database Id 
输入 inst_num 的值:  1 
Using 1 for instance number 
输入 num_days 的值:  1 
Listing the last day's Completed Snapshots 
                                                     Snap 
Instance     DB Name        Snap Id    Snap Started    Level 
------------ ------------ --------- ------------------ ----- 
acscnprd     ACSCNPRD         39147 17 8月  2011 00:00     1 
                              39160 17 8月  2011 13:00     1 
                              39161 17 8月  2011 14:00     1 
                              39162 17 8月  2011 15:00     1 
                              39163 17 8月  2011 16:00     1 
                              39164 17 8月  2011 17:00     1 
                              39165 17 8月  2011 18:00     1 
                              39166 17 8月  2011 19:00     1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
输入 begin_snap 的值:  39165 
Begin Snapshot Id specified: 39165 
输入 end_snap 的值:  39166 
End   Snapshot Id specified: 39166 
Specify the Report Name 
~~~~~~~~~~~~~~~~~~~~~~~ 
The default report file name is awrrpt_1_39165_39166.html.  To use this name,press <return> to ontinue, otherwise enter an alternative. 
输入 report_name 的值: 
Using the report name awrrpt_1_39165_39166.html

 

 

read more »

AWR数据导出

 

概述:本篇是对AWR数据导出操作的详解。某些时候,客户的数据库出现性能瓶颈,需要我们分析。我们可以请客户将AWR数据导出,然后导入本机数据库进行分析。对于服务器等硬件方面对机器造成的影响,将在别的文章中指出。
awrextr.sql 脚本用于导出;awrload.sql用于AWR数据的加载
(AWR数据导入参见:)

  

目录

  1. 创建文件夹(第一次使用时设置)
  2. 设置导出路径
  3. 运行awrextr脚本
  4. 输入所需信息
  5. 从数据库中导出AWR数据文件
1.创建文件夹(第一次使用时设置)
使用awrextr.sql脚本可以很轻松的将机器中的AWR数据导出。但是在导出之前,需要先创建一个专门的文件夹用来保存导出的数据,同时设置一个路径。
[[email protected] exp]$ cd
[[email protected] ~]$ id
uid=500(oracle) gid=500(oracle) groups=500(oracle),54323(davfs2)
[[email protected] ~]$ pwd
/home/oracle
[[email protected] ~]$ mkdir exp
2.设置导出路径
SQL>create or replace directory EXP as ‘/home/oracle/exp';
3.运行awrextr脚本
SQL> @?/rdbms/admin/awrextr
运行运行awrextr脚本。注意,在导出awr数据的过程中,需要用户输入以下信息:
(1) database id (如果本机有不止一个实例的AWR数据,需要选择dbid。默认为本机dbid)
(2) snapshot range to extract(输入想要导出的AWR报告的快照范围)
(3) name of directory object (输入之前设置的路径)
(4) name of dump file(对导出的AWR数据文件进行命名,通常可命名为awrdat_[begin_snap]_[end_snap],如awrdat_1070_1080,无需指定扩展名)
执行SQL> @?/rdbms/admin/awrextr命令后显示如下,黑体为需要手工输入的数据:
4.输入所需信息
~~~~~~~~~~~~~
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                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    DB Name                                                             
———– ————                                                        
 1229390655 ORCL                                                                
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id     DB Name      Host                                                  
———— ———— ————                                          
* 1229390655 ORCL         dhcp-4op5-4o                                          
                          p6-west-144-                                          
                          25-174-35.us                                          
                          dhcp.oraclec                                          
                          orp.com                                               
                                                                                
* 1229390655 ORCL         localhost                                             
* 1229390655 ORCL         localhost.lo                                          
                          caldomain                                             
                                                                                
                                       
                                                                                
The default database id is the local one: ‘1229390655’.  To use this
database id, press <return> to continue, otherwise enter an alternative.
Enter value for dbid: 此处需要手工输入,默认为本机数据库dbidW
Using 1229390655 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.
Enter value for num_days: 10
Listing the last 10 days of Completed Snapshots                                 
DB Name        Snap Id    Snap Started                                          
———— ——— ——————                                       
ORCL              1021 04 Jan 2011 22:21                                        
                  1022 04 Jan 2011 23:00                                        
                 为节约空间,此处省略中间snap                                       
                  1066 10 Jan 2011 01:00                                        
                  1067 10 Jan 2011 02:00                                        
                  1068 10 Jan 2011 16:48                                        
                  1069 10 Jan 2011 18:00                                        
                  1070 10 Jan 2011 19:00                                        
                  1071 10 Jan 2011 20:21                                        
                  1072 10 Jan 2011 21:00                                        
                  1073 10 Jan 2011 22:00                                        
                  1074 10 Jan 2011 23:00                                        
                  1075 11 Jan 2011 00:00                                        
                  1076 11 Jan 2011 01:00                                        
                  1077 11 Jan 2011 05:47                                        
                  1078 11 Jan 2011 07:00                                        
                  1079 11 Jan 2011 18:01                                        
                  1080 11 Jan 2011 19:00                                        
                  1081 11 Jan 2011 20:00                                        
                  1082 11 Jan 2011 21:00                                        
                                       
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1070
Begin Snapshot Id specified: 1070
Enter value for end_snap: 1080
End   Snapshot Id specified: 1080
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path                                   
—————————— ————————————————-
DATA_FILE_DIR                  /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/sales_history/                        
                                                                                
DATA_PUMP_DIR                  /home/oracle/app/oracle/admin/orcl/dpdump/       
EXP                            /home/oracle/exp                                 
LOG_FILE_DIR                   /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/log/                                  
                                                                                
MEDIA_DIR                      /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/product_media/                        
                                                                                
ORACLE_OCM_CONFIG_DIR          /home/oracle/app/oracle/product/11.2.0/dbhome_1/c
                               cr/state                                         
                                                                                
SS_OE_XMLDIR                   /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/order_entry/                          
                                                                                
SUBDIR                         /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/order_entry//2002/Sep                 
                                                                                
XMLDIR                         /ade/b/1191423112/oracle/rdbms/xml               
                                                                                
                                                                                
Choose a Directory Name from the above list (case-sensitive).
Enter value for directory_name: EXP
Using the dump directory: EXP                                                   
                                                                                
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_1070_1080.
To use this name, press <return> to continue, otherwise enter
an alternative.
Enter value for file_name: awrdat_1070_1080
Using the dump file prefix: awrdat_1070_1080                                    
|                                                                               
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                                 
|  The AWR extract dump file will be located                                    
|  in the following directory/file:                                             
|   /home/oracle/exp                                                            
|   awrdat_1070_1080.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:                                   
|   /home/oracle/exp                                                            
|   awrdat_1070_1080.log                                                        
|                       
|   End of AWR Extract
指定导出的AWR数据文件名后,点击回车,会等待一会,机器不会有任何提示或反应。导出完毕后显示End of AWR Extract。我仅导出了snap1070-1080之间的数据,花费时间2分钟左右,导出文件大小12MB。
导出后可转换到设置的EXP路径的位置,其中包含导出的AWR数据文件一个和导出过程中产生的log文件一个。log包含导出过程中产生的日志信息,很多视图的信息(这些视图在进行数据库性能分析时可能会用到),如:
Starting “SYS”.”SYS_EXPORT_TABLE_02″:  
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 199.5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 128 TABLE objects in 25 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 121 CONSTRAINT objects in 2 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 1 REF_CONSTRAINT objects in 1 seconds
. . exported “SYS”.”WRH$_ACTIVE_SESSION_HISTORY”:”WRH$_ACTIVE_1641171490_0″  31.83 KB       0 rows
. . exported “SYS”.”WRH$_SQL_PLAN”                       1.358 MB    3528 rows
. . exported “SYS”.”WRH$_SYSMETRIC_HISTORY”              573.7 KB   10940 rows
. . exported “SYS”.”WRH$_SQLTEXT”                        538.3 KB     344 rows
. . exported “SYS”.”WRH$_SQLSTAT”:”WRH$_SQLSTA_1641171490_0″  36.64 KB       0 rows
. . exported “SYS”.”WRH$_SYSMETRIC_SUMMARY”              146.1 KB    1738 rows
. . exported “SYS”.”WRH$_LATCH”:”WRH$_LATCH_1641171490_0″  10.99 KB       0 rows
. . exported “SYS”.”WRH$_ENQUEUE_STAT”                   48.96 KB     795 rows
. . exported “SYS”.”WRH$_FILESTATXS”:”WRH$_FILEST_1641171490_0″  10.60 KB       0 rows
. . exported “SYS”.”WRH$_PARAMETER”:”WRH$_PARAME_1641171490_0″  7.406 KB       0 rows
. . exported “SYS”.”WRH$_SEG_STAT”:”WRH$_SEG_ST_1641171490_0″  22.76 KB       0 rows
. . exported “SYS”.”WRH$_SQL_BIND_METADATA”              155.0 KB    2617 rows
. . exported “SYS”.”WRH$_SYSSTAT”:”WRH$_SYSSTA_1641171490_0″  6.593 KB       0 rows
. . exported “SYS”.”WRH$_WAITCLASSMETRIC_HISTORY”        51.23 KB     548 rows
. . exported “SYS”.”WRH$_BG_EVENT_SUMMARY”               28.85 KB     602 rows
. . exported “SYS”.”WRH$_LATCH_MISSES_SUMMARY”:”WRH$_LATCH__1641171490_0″  7.820 KB       0 rows
. . exported “SYS”.”WRH$_SERVICE_STAT”:”WRH$_SERVIC_1641171490_0″      7 KB       0 rows
. . exported “SYS”.”WRH$_SQLSTAT”:”WRH$_SQLSTA_1229390655_1049″  212.5 KB     632 rows
. . exported “SYS”.”WRH$_LATCH”:”WRH$_LATCH_1229390655_1049″  169.9 KB    3210 rows
. . exported “SYS”.”WRH$_LATCH”:”WRH$_LATCH_1229390655_1001″  10.99 KB       0 rows
. . exported “SYS”.”WRH$_LATCH”:”WRH$_LATCH_1229390655_1024″  10.99 KB       0 rows
. . exported “SYS”.”WRH$_SQLSTAT”:”WRH$_SQLSTA_1229390655_1001″  36.64 KB       0 rows
. . exported “SYS”.”WRH$_SQLSTAT”:”WRH$_SQLSTA_1229390655_1024″  36.64 KB       0 rows
. . exported “SYS”.”WRH$_TABLESPACE_SPACE_USAGE”         16.19 KB     176 rows
. . exported “SYS”.”WRH$_SYSTEM_EVENT”:”WRH$_SYSTEM_1641171490_0″  8.632 KB       0 rows
. . exported “SYS”.”WRH$_LATCH”:”WRH$_LATCH_1229390655_1076″  143.7 KB    2675 rows
. . exported “SYS”.”WRH$_LIBRARYCACHE”                   21.10 KB     194 rows
. . exported “SYS”.”WRH$_ROWCACHE_SUMMARY”:”WRH$_ROWCAC_1641171490_0″  11.02 KB       0 rows
. . exported “SYS”.”WRH$_SQLSTAT”:”WRH$_SQLSTA_1229390655_1076″  182.8 KB     533 rows
. . exported “SYS”.”WRH$_TABLESPACE_STAT”:”WRH$_TABLES_1641171490_0″  8.617 KB       0 rows
. . exported “SYS”.”WRH$_SYSSTAT”:”WRH$_SYSSTA_1229390655_1049″  104.5 KB    3624 rows
. . exported “SYS”.”WRH$_PARAMETER”:”WRH$_PARAME_1229390655_1049″  93.81 KB    2112 rows
. . exported “SYS”.”WRH$_SYSSTAT”:”WRH$_SYSSTA_1229390655_1001″  6.593 KB       0 rows
. . exported “SYS”.”WRH$_SYSSTAT”:”WRH$_SYSSTA_1229390655_1024″  6.593 KB       0 rows
. . exported “SYS”.”WRH$_EVENT_HISTOGRAM”:”WRH$_EVENT__1229390655_1001″  7.007 KB       0 rows
. . exported “SYS”.”WRH$_EVENT_HISTOGRAM”:”WRH$_EVENT__1229390655_1049″  74.07 KB    2173 rows
. . exported “SYS”.”WRH$_PARAMETER”:”WRH$_PARAME_1229390655_1001″  7.406 KB       0 rows
. . exported “SYS”.”WRH$_PARAMETER”:”WRH$_PARAME_1229390655_1024″  7.406 KB       0 rows
. . exported “SYS”.”WRH$_PGASTAT”                        13.90 KB     161 rows
. . exported “SYS”.”WRH$_PGA_TARGET_ADVICE”              16.78 KB     154 rows
. . exported “SYS”.”WRH$_SEG_STAT_OBJ”                   81.28 KB     730 rows
. . exported “SYS”.”WRH$_EVENT_HISTOGRAM”:”WRH$_EVENT__1229390655_1024″  7.007 KB       0 rows
. . exported “SYS”.”WRH$_EVENT_HISTOGRAM”:”WRH$_EVENT__1229390655_1076″  73.31 KB    2143 rows
. . exported “SYS”.”WRH$_MEM_DYNAMIC_COMP”               21.60 KB     176 rows
. . exported “SYS”.”WRH$_PARAMETER”:”WRH$_PARAME_1229390655_1076″  79.35 KB    1760 rows
. . exported “SYS”.”WRH$_SHARED_POOL_ADVICE”             16.67 KB     136 rows
. . exported “SYS”.”WRH$_SYSSTAT”:”WRH$_SYSSTA_1229390655_1076″  88.46 KB    3020 rows
. . exported “SYS”.”WRH$_UNDOSTAT”                       20.96 KB      61 rows
. . exported “SYS”.”WRH$_DB_CACHE_ADVICE”:”WRH$_DB_CAC_1641171490_0″  10.26 KB       0 rows
. . exported “SYS”.”WRH$_PARAMETER_NAME”                 102.6 KB    2399 rows
. . exported “SYS”.”WRH$_SEG_STAT”:”WRH$_SEG_ST_1229390655_1001″  22.76 KB       0 rows
. . exported “SYS”.”WRH$_SEG_STAT”:”WRH$_SEG_ST_1229390655_1049″  70.24 KB     400 rows
. . exported “SYS”.”WRH$_SERVICE_WAIT_CLASS”:”WRH$_SERVIC_1641171490_0″  7.820 KB       0 rows
. . exported “SYS”.”WRH$_SQL_WORKAREA_HISTOGRAM”         10.89 KB      69 rows
. . exported “SYS”.”WRH$_EVENT_NAME”                     86.57 KB    1118 rows
. . exported “SYS”.”WRH$_IOSTAT_DETAIL”                  15.53 KB      97 rows
. . exported “SYS”.”WRH$_IOSTAT_FILETYPE”                19.69 KB     132 rows
. . exported “SYS”.”WRH$_IOSTAT_FUNCTION”                17.20 KB     154 rows
. . exported “SYS”.”WRH$_JAVA_POOL_ADVICE”               13.73 KB     108 rows
. . exported “SYS”.”WRH$_MUTEX_SLEEP”                    9.320 KB      36 rows
. . exported “SYS”.”WRH$_PROCESS_MEMORY_SUMMARY”         12.12 KB      44 rows
. . exported “SYS”.”WRH$_SEG_STAT”:”WRH$_SEG_ST_1229390655_1024″  22.76 KB       0 rows
. . exported “SYS”.”WRH$_SEG_STAT”:”WRH$_SEG_ST_1229390655_1076″  72.71 KB     420 rows
. . exported “SYS”.”WRH$_SGASTAT”:”WRH$_SGASTA_1641171490_0″  6.984 KB       0 rows
. . exported “SYS”.”WRH$_SGA_TARGET_ADVICE”              9.367 KB      58 rows
. . exported “SYS”.”WRH$_ACTIVE_SESSION_HISTORY”:”WRH$_ACTIVE_1229390655_1049″  57.42 KB      98 rows
. . exported “SYS”.”WRH$_ACTIVE_SESSION_HISTORY”:”WRH$_ACTIVE_1229390655_1076″  68.17 KB     139 rows
. . exported “SYS”.”WRH$_LOG”                            11.37 KB      33 rows
. . exported “SYS”.”WRH$_RESOURCE_LIMIT”                 10.40 KB      44 rows
. . exported “SYS”.”WRH$_SERVICE_STAT”:”WRH$_SERVIC_1229390655_1049″  29.83 KB     672 rows
. . exported “SYS”.”WRH$_SYS_TIME_MODEL”:”WRH$_SYS_TI_1641171490_0″  6.601 KB       0 rows
. . exported “SYS”.”WRH$_WAITSTAT”:”WRH$_WAITST_1641171490_0″  6.992 KB       0 rows
. . exported “SYS”.”WRH$_ACTIVE_SESSION_HISTORY”:”WRH$_ACTIVE_1229390655_1001″  31.83 KB       0 rows
. . exported “SYS”.”WRH$_ACTIVE_SESSION_HISTORY”:”WRH$_ACTIVE_1229390655_1024″  31.83 KB       0 rows
. . exported “SYS”.”WRH$_BUFFER_POOL_STATISTICS”         14.98 KB      11 rows
. . exported “SYS”.”WRH$_DATAFILE”                       9.320 KB      15 rows
. . exported “SYS”.”WRH$_INSTANCE_RECOVERY”              13.93 KB      11 rows
. . exported “SYS”.”WRH$_LATCH_MISSES_SUMMARY”:”WRH$_LATCH__1229390655_1001″  7.820 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_MISSES_SUMMARY”:”WRH$_LATCH__1229390655_1049″  15.28 KB     128 rows
. . exported “SYS”.”WRH$_LATCH_MISSES_SUMMARY”:”WRH$_LATCH__1229390655_1076″  15.30 KB     127 rows
. . exported “SYS”.”WRH$_LATCH_NAME”                     29.35 KB     535 rows
. . exported “SYS”.”WRH$_MEMORY_TARGET_ADVICE”           9.695 KB      55 rows
. . exported “SYS”.”WRH$_METRIC_NAME”                    30.92 KB     278 rows
. . exported “SYS”.”WRH$_OPTIMIZER_ENV”                  34.02 KB     128 rows
. . exported “SYS”.”WRH$_OSSTAT”:”WRH$_OSSTAT_1641171490_0″  6.593 KB       0 rows
. . exported “SYS”.”WRH$_OSSTAT_NAME”                    6.398 KB      20 rows
. . exported “SYS”.”WRH$_PLAN_OPERATION_NAME”            9.601 KB     130 rows
. . exported “SYS”.”WRH$_PLAN_OPTION_NAME”               10.03 KB     155 rows
. . exported “SYS”.”WRH$_ROWCACHE_SUMMARY”:”WRH$_ROWCAC_1229390655_1001″  11.02 KB       0 rows
. . exported “SYS”.”WRH$_ROWCACHE_SUMMARY”:”WRH$_ROWCAC_1229390655_1024″  11.02 KB       0 rows
. . exported “SYS”.”WRH$_ROWCACHE_SUMMARY”:”WRH$_ROWCAC_1229390655_1049″  31.22 KB     330 rows
. . exported “SYS”.”WRH$_ROWCACHE_SUMMARY”:”WRH$_ROWCAC_1229390655_1076″  27.88 KB     275 rows
. . exported “SYS”.”WRH$_SERVICE_NAME”                   6.335 KB       4 rows
. . exported “SYS”.”WRH$_SERVICE_STAT”:”WRH$_SERVIC_1229390655_1001″      7 KB       0 rows
. . exported “SYS”.”WRH$_SERVICE_STAT”:”WRH$_SERVIC_1229390655_1024″      7 KB       0 rows
. . exported “SYS”.”WRH$_SERVICE_STAT”:”WRH$_SERVIC_1229390655_1076″  26.08 KB     560 rows
. . exported “SYS”.”WRH$_SESSMETRIC_HISTORY”             8.992 KB       0 rows
. . exported “SYS”.”WRH$_SGA”                            8.218 KB      44 rows
. . exported “SYS”.”WRH$_SQLCOMMAND_NAME”                10.50 KB     163 rows
. . exported “SYS”.”WRH$_SQL_SUMMARY”                    7.828 KB      11 rows
. . exported “SYS”.”WRH$_STAT_NAME”                      34.10 KB     621 rows
. . exported “SYS”.”WRH$_SYSTEM_EVENT”:”WRH$_SYSTEM_1229390655_1001″  8.632 KB       0 rows
. . exported “SYS”.”WRH$_SYSTEM_EVENT”:”WRH$_SYSTEM_1229390655_1024″  8.632 KB       0 rows
. . exported “SYS”.”WRH$_SYSTEM_EVENT”:”WRH$_SYSTEM_1229390655_1049″  28.06 KB     442 rows
. . exported “SYS”.”WRH$_SYSTEM_EVENT”:”WRH$_SYSTEM_1229390655_1076″  25.62 KB     384 rows
. . exported “SYS”.”WRH$_TEMPFILE”                       7.875 KB       1 rows
. . exported “SYS”.”WRH$_TEMPSTATXS”                     11.23 KB      11 rows
. . exported “SYS”.”WRH$_THREAD”                         8.679 KB      11 rows
. . exported “SYS”.”WRH$_TOPLEVELCALL_NAME”              9.843 KB     150 rows
. . exported “SYS”.”WRM$_DATABASE_INSTANCE”              8.804 KB       2 rows
. . exported “SYS”.”WRH$_DB_CACHE_ADVICE”:”WRH$_DB_CAC_1229390655_1001″  10.26 KB       0 rows
. . exported “SYS”.”WRH$_DB_CACHE_ADVICE”:”WRH$_DB_CAC_1229390655_1024″  10.26 KB       0 rows
. . exported “SYS”.”WRH$_DB_CACHE_ADVICE”:”WRH$_DB_CAC_1229390655_1049″   15.5 KB      84 rows
. . exported “SYS”.”WRH$_DB_CACHE_ADVICE”:”WRH$_DB_CAC_1229390655_1076″  14.88 KB      74 rows
. . exported “SYS”.”WRH$_DISPATCHER”                     8.976 KB      11 rows
. . exported “SYS”.”WRH$_FILESTATXS”:”WRH$_FILEST_1229390655_1001″  10.60 KB       0 rows
. . exported “SYS”.”WRH$_FILESTATXS”:”WRH$_FILEST_1229390655_1024″  10.60 KB       0 rows
. . exported “SYS”.”WRH$_FILESTATXS”:”WRH$_FILEST_1229390655_1049″  15.37 KB      90 rows
. . exported “SYS”.”WRH$_FILESTATXS”:”WRH$_FILEST_1229390655_1076″  14.60 KB      75 rows
. . exported “SYS”.”WRH$_IOSTAT_FILETYPE_NAME”           6.156 KB      12 rows
. . exported “SYS”.”WRH$_IOSTAT_FUNCTION_NAME”           6.148 KB      14 rows
. . exported “SYS”.”WRH$_LATCH_MISSES_SUMMARY”:”WRH$_LATCH__1229390655_1024″  7.820 KB       0 rows
. . exported “SYS”.”WRH$_MEMORY_RESIZE_OPS”              12.17 KB      24 rows
. . exported “SYS”.”WRH$_OSSTAT”:”WRH$_OSSTAT_1229390655_1001″  6.593 KB       0 rows
. . exported “SYS”.”WRH$_OSSTAT”:”WRH$_OSSTAT_1229390655_1024″  6.593 KB       0 rows
. . exported “SYS”.”WRH$_OSSTAT”:”WRH$_OSSTAT_1229390655_1049″  9.382 KB     108 rows
. . exported “SYS”.”WRH$_OSSTAT”:”WRH$_OSSTAT_1229390655_1076″  8.929 KB      90 rows
. . exported “SYS”.”WRH$_PERSISTENT_QUEUES”              16.16 KB      33 rows
. . exported “SYS”.”WRH$_PERSISTENT_SUBSCRIBERS”         12.82 KB      22 rows
. . exported “SYS”.”WRH$_RSRC_CONSUMER_GROUP”            20.03 KB      10 rows
. . exported “SYS”.”WRH$_RSRC_PLAN”                      8.265 KB       1 rows
. . exported “SYS”.”WRH$_RULE_SET”                       10.03 KB      11 rows
. . exported “SYS”.”WRH$_SERVICE_WAIT_CLASS”:”WRH$_SERVIC_1229390655_1001″  7.820 KB       0 rows
. . exported “SYS”.”WRH$_SERVICE_WAIT_CLASS”:”WRH$_SERVIC_1229390655_1024″  7.820 KB       0 rows
. . exported “SYS”.”WRH$_SERVICE_WAIT_CLASS”:”WRH$_SERVIC_1229390655_1049″  12.78 KB     103 rows
. . exported “SYS”.”WRH$_SERVICE_WAIT_CLASS”:”WRH$_SERVIC_1229390655_1076″  12.48 KB      96 rows
. . exported “SYS”.”WRH$_SESS_TIME_STATS”                8.968 KB      22 rows
. . exported “SYS”.”WRH$_SGASTAT”:”WRH$_SGASTA_1229390655_1001″  6.984 KB       0 rows
. . exported “SYS”.”WRH$_SGASTAT”:”WRH$_SGASTA_1229390655_1024″  6.984 KB       0 rows
. . exported “SYS”.”WRH$_SGASTAT”:”WRH$_SGASTA_1229390655_1049″  15.41 KB     184 rows
. . exported “SYS”.”WRH$_SGASTAT”:”WRH$_SGASTA_1229390655_1076″  13.94 KB     152 rows
. . exported “SYS”.”WRH$_SHARED_SERVER_SUMMARY”          13.42 KB      11 rows
. . exported “SYS”.”WRH$_STREAMS_POOL_ADVICE”            15.20 KB     220 rows
. . exported “SYS”.”WRH$_SYS_TIME_MODEL”:”WRH$_SYS_TI_1229390655_1001″  6.601 KB       0 rows
. . exported “SYS”.”WRH$_SYS_TIME_MODEL”:”WRH$_SYS_TI_1229390655_1024″  6.601 KB       0 rows
. . exported “SYS”.”WRH$_SYS_TIME_MODEL”:”WRH$_SYS_TI_1229390655_1049″  9.960 KB     114 rows
. . exported “SYS”.”WRH$_SYS_TIME_MODEL”:”WRH$_SYS_TI_1229390655_1076″  9.429 KB      95 rows
. . exported “SYS”.”WRH$_TABLESPACE_STAT”:”WRH$_TABLES_1229390655_1001″  8.617 KB       0 rows
. . exported “SYS”.”WRH$_TABLESPACE_STAT”:”WRH$_TABLES_1229390655_1024″  8.617 KB       0 rows
. . exported “SYS”.”WRH$_TABLESPACE_STAT”:”WRH$_TABLES_1229390655_1049″  14.78 KB      90 rows
. . exported “SYS”.”WRH$_TABLESPACE_STAT”:”WRH$_TABLES_1229390655_1076″  13.75 KB      75 rows
. . exported “SYS”.”WRH$_WAITSTAT”:”WRH$_WAITST_1229390655_1001″  6.992 KB       0 rows
. . exported “SYS”.”WRH$_WAITSTAT”:”WRH$_WAITST_1229390655_1024″  6.992 KB       0 rows
. . exported “SYS”.”WRH$_WAITSTAT”:”WRH$_WAITST_1229390655_1049″  10.82 KB     108 rows
. . exported “SYS”.”WRH$_WAITSTAT”:”WRH$_WAITST_1229390655_1076″  10.18 KB      90 rows
. . exported “SYS”.”WRM$_SNAPSHOT”                       10.30 KB      11 rows
. . exported “SYS”.”WRM$_WR_CONTROL”                     12.15 KB       1 rows
. . exported “SYS”.”WRH$_ACTIVE_SESSION_HISTORY”:”WRH$_ACTIVE_SES_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_ACTIVE_SESSION_HISTORY_BL”          0 KB       0 rows
. . exported “SYS”.”WRH$_BUFFERED_QUEUES”                    0 KB       0 rows
. . exported “SYS”.”WRH$_BUFFERED_SUBSCRIBERS”               0 KB       0 rows
. . exported “SYS”.”WRH$_CLUSTER_INTERCON”                   0 KB       0 rows
. . exported “SYS”.”WRH$_COMP_IOSTAT”                        0 KB       0 rows
. . exported “SYS”.”WRH$_CR_BLOCK_SERVER”                    0 KB       0 rows
. . exported “SYS”.”WRH$_CURRENT_BLOCK_SERVER”               0 KB       0 rows
. . exported “SYS”.”WRH$_DB_CACHE_ADVICE”:”WRH$_DB_CACHE_AD_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_DB_CACHE_ADVICE_BL”                 0 KB       0 rows
. . exported “SYS”.”WRH$_DLM_MISC”:”WRH$_DLM_MISC_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_DLM_MISC”:”WRH$_DLM_MI_1229390655_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_DLM_MISC”:”WRH$_DLM_MI_1641171490_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_DLM_MISC_BL”                        0 KB       0 rows
. . exported “SYS”.”WRH$_DYN_REMASTER_STATS”                 0 KB       0 rows
. . exported “SYS”.”WRH$_EVENT_HISTOGRAM”:”WRH$_EVENT_HISTO_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_EVENT_HISTOGRAM”:”WRH$_EVENT__1641171490_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_EVENT_HISTOGRAM_BL”                 0 KB       0 rows
. . exported “SYS”.”WRH$_FILEMETRIC_HISTORY”                 0 KB       0 rows
. . exported “SYS”.”WRH$_FILESTATXS”:”WRH$_FILESTATXS_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_FILESTATXS_BL”                      0 KB       0 rows
. . exported “SYS”.”WRH$_IC_CLIENT_STATS”                    0 KB       0 rows
. . exported “SYS”.”WRH$_IC_DEVICE_STATS”                    0 KB       0 rows
. . exported “SYS”.”WRH$_INST_CACHE_TRANSFER”:”WRH$_INST_CACHE_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_INST_CACHE_TRANSFER”:”WRH$_INST_C_1229390655_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_INST_CACHE_TRANSFER”:”WRH$_INST_C_1641171490_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_INST_CACHE_TRANSFER_BL”             0 KB       0 rows
. . exported “SYS”.”WRH$_INTERCONNECT_PINGS”:”WRH$_IC_PINGS_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_INTERCONNECT_PINGS”:”WRH$_INTERC_1229390655_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_INTERCONNECT_PINGS”:”WRH$_INTERC_1641171490_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_INTERCONNECT_PINGS_BL”              0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH”:”WRH$_LATCH_MXDB_MXSN”       0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_BL”                           0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_CHILDREN”:”WRH$_LATCH_CHILD_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_CHILDREN”:”WRH$_LATCH__1229390655_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_CHILDREN”:”WRH$_LATCH__1641171490_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_CHILDREN_BL”                  0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_MISSES_SUMMARY”:”WRH$_LATCH_MISSE_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_MISSES_SUMMARY_BL”            0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_PARENT”:”WRH$_LATCH_PAREN_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_PARENT”:”WRH$_LATCH__1229390655_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_PARENT”:”WRH$_LATCH__1641171490_0″      0 KB       0 rows
. . exported “SYS”.”WRH$_LATCH_PARENT_BL”                    0 KB       0 rows
. . exported “SYS”.”WRH$_MTTR_TARGET_ADVICE”                 0 KB       0 rows
. . exported “SYS”.”WRH$_OSSTAT”:”WRH$_OSSTAT_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_OSSTAT_BL”                          0 KB       0 rows
. . exported “SYS”.”WRH$_PARAMETER”:”WRH$_PARAMETER_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_PARAMETER_BL”                       0 KB       0 rows
. . exported “SYS”.”WRH$_ROWCACHE_SUMMARY”:”WRH$_ROWCACHE_SU_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_ROWCACHE_SUMMARY_BL”                0 KB       0 rows
. . exported “SYS”.”WRH$_SEG_STAT”:”WRH$_SEG_STAT_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_SEG_STAT_BL”                        0 KB       0 rows
. . exported “SYS”.”WRH$_SERVICE_STAT”:”WRH$_SERVICE_STAT_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_SERVICE_STAT_BL”                    0 KB       0 rows
. . exported “SYS”.”WRH$_SERVICE_WAIT_CLASS”:”WRH$_SERVICE_WAIT_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_SERVICE_WAIT_CLASS_BL”              0 KB       0 rows
. . exported “SYS”.”WRH$_SGASTAT”:”WRH$_SGASTAT_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_SGASTAT_BL”                         0 KB       0 rows
. . exported “SYS”.”WRH$_SQLSTAT”:”WRH$_SQLSTAT_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_SQLSTAT_BL”                         0 KB       0 rows
. . exported “SYS”.”WRH$_STREAMS_APPLY_SUM”                  0 KB       0 rows
. . exported “SYS”.”WRH$_STREAMS_CAPTURE”                    0 KB       0 rows
. . exported “SYS”.”WRH$_SYSSTAT”:”WRH$_SYSSTAT_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_SYSSTAT_BL”                         0 KB       0 rows
. . exported “SYS”.”WRH$_SYSTEM_EVENT”:”WRH$_SYSTEM_EVEN_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_SYSTEM_EVENT_BL”                    0 KB       0 rows
. . exported “SYS”.”WRH$_SYS_TIME_MODEL”:”WRH$_SYS_TIME_MO_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_SYS_TIME_MODEL_BL”                  0 KB       0 rows
. . exported “SYS”.”WRH$_TABLESPACE_STAT”:”WRH$_TABLESPACE_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_TABLESPACE_STAT_BL”                 0 KB       0 rows
. . exported “SYS”.”WRH$_WAITSTAT”:”WRH$_WAITSTAT_MXDB_MXSN”      0 KB       0 rows
. . exported “SYS”.”WRH$_WAITSTAT_BL”                        0 KB       0 rows
. . exported “SYS”.”WRM$_BASELINE_DETAILS”                   0 KB       0 rows
. . exported “SYS”.”WRM$_BASELINE_TEMPLATE”                  0 KB       0 rows
. . exported “SYS”.”WRM$_COLORED_SQL”                        0 KB       0 rows
. . exported “SYS”.”WRM$_SNAP_ERROR”                         0 KB       0 rows
Master table “SYS”.”SYS_EXPORT_TABLE_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_02 is:
  /home/oracle/exp/awrdat_1070_1080.dmp
Job “SYS”.”SYS_EXPORT_TABLE_02″ successfully completed at 22:20:58
5.从数据库中导出AWR数据文件
我通过secureCRT连接数据库,其中有SFTP工具可以导出数据库中的AWR文件。
sftp> pwd(查看所连服务器cwd)
/home/oracle/exp
sftp> ls(查看所连服务器中cwd中文件)
awrdat_1070_1080.dmp  awrdat_1070_1080.log  exp.txt
sftp> lpwd(l+pwd,查询本机cwd)
d:/Linuxftp/138localhost
sftp> get exp.txt(下载相关文件)
其他注意事项:
1.对于导出的文件,建议使用awrdat_[begin_snap]_[end_snap]这种格式命名
2.导出脚本awrextr的位置为$ORACLE_HOME/rdbms/admin/awrextr
未尽事宜:
1.对于awrextr.sql文件内容的探究将在将来的某个时间更新
                                                  
参考文献:

AWR数据导入

 

概述:本篇是对AWR数据导入操作的详解。某些时候,客户的数据库出现性能瓶颈,需要我们分析。我们可以请客户将AWR数据导出,然后导入本机数据库进行分析。(This is useful in cases where you want to use a separate system to perform analysis of the AWR data. )对于服务器等硬件方面对机器造成的影响,将在别的文章中指出。
awrextr.sql 脚本用于导出;awrload.sql用于AWR数据的加载
AWR数据导出参见下一篇
目录
  1. 将AWR数据文件导入数据库
  2. 设置导入路径
  3. 运行awrload脚本
  4. 指定导入路径
  5. 指定要导入的AWR数据文件名称
  6. 指定schema name
1.将AWR数据文件导入数据库
使用put命令上传
sftp> lpwd
d:/Linuxftp/138localhost
sftp> pwd
/root
sftp> cd /home/oracle/imp
sftp> pwd
/home/oracle/imp
sftp> ls
awrdat_13145_13328.dmpimp.log               imp1.log
perfstat.dmp
sftp> lls
1.doc                 awrdat_1070_1080.dmp  awrdat_1070_1080.log
AWRDAT_69_75.DMP      exp.txt
sftp> put AWRDAT_69_75.DMP
Uploading AWRDAT_69_75.DMP to /home/oracle/imp/AWRDAT_69_75.DMP
  100% 12368KB   6184KB/s 00:00:02    
d:/Linuxftp/138localhost/AWRDAT_69_75.DMP: 12664832 bytes transferred in 2 seconds (6184 KB/s)
2.运行awrload脚本
SQL> @?/rdbms/admin/awrload
运行运行awrload脚本。注意,在导出awr数据的过程中,需要用户输入以下信息:
(1) name of directory object(输入路径名)                            
(2) name of dump file (输入要导入的文件名,无需扩展名)       
(3) staging schema name to load AWR data into 
~~~~~~~~~~
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                  /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/sales_history/                       
                                                                               
DATA_PUMP_DIR                  /home/oracle/app/oracle/admin/orcl/dpdump/      
EXP                            /home/oracle/exp                                
IMP                            /home/oracle/imp                                
LOG_FILE_DIR                   /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/log/                                 
                                                                               
MEDIA_DIR                      /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/product_media/                       
                                                                               
ORACLE_OCM_CONFIG_DIR          /home/oracle/app/oracle/product/11.2.0/dbhome_1/c
                               cr/state                                        
                                                                               
SS_OE_XMLDIR                   /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/order_entry/                         
                                                                               
SUBDIR                         /home/oracle/app/oracle/product/11.2.0/dbhome_1/d
                               emo/schema/order_entry//2002/Sep                
                                                                               
XMLDIR                         /ade/b/1191423112/oracle/rdbms/xml              

                                                                               
                                                                               

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

Enter value for directory_name: IMP   (3.指定导入路径)

Using the dump directory: IMP                                                  

                                                                               

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: awrdat_13145_13328(4.指定要导入的AWR数据文件名称)

Loading from the file name: awrdat_13145_13328.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(5.指定schema name)                                        

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                    
—————————— ——— ——————                    
APEX_1295922881855015          PERMANENT                                       
AWRRPT                         PERMANENT                                       
EXAMPLE                        PERMANENT                                       
FLOW_1046101119510758          PERMANENT                                       
FLOW_1146416395631714          PERMANENT                                       
FLOW_1170420963682633          PERMANENT                                       
FLOW_1194425963955800          PERMANENT                                       
FLOW_1218408858999342          PERMANENT                                       
FLOW_1242310449730067          PERMANENT                                       
FLOW_1266412439758696          PERMANENT                                       
PERFSTAT                       PERMANENT                                       
SYSAUX                         PERMANENT *                                     
USERS                          PERMANENT                                       

Pressing <return> will result in the recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace:

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/imp                                                           
|   awrdat_13145_13328.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/imp                                                           
|   awrdat_13145_13328.log                                                     
|                                                                              
… Dropping AWR_STAGE user

End of AWR Load

未尽事宜:
1.关于如何删除导入的AWR数据,将在我搞明白后更新。这个问题很纠结

参考文献:
普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress