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数据,将在我搞明白后更新。这个问题很纠结

参考文献:
  1. Pingback: iOracle » 健康检查报告

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress