Tag Archives: awrextr

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