1、AWR原始数据与AWR报告的区别 AWR原始数据： 是oracle数据库mmon进程定期将统计量从内存转储至磁盘，并以结构化的形式存入若干张表组成自动工作负荷存储仓库（AutomaticWorkload repository﹐简写为AWR）的原始数据，有经验的DBA可以自由的查询所需要的历
1、AWR原始数据与AWR报告的区别
awr原始数据：
是oracle数据库mmon进程定期将统计量从内存转储至磁盘，并以结构化的形式存入若干张表组成自动工作负荷存储仓库（AutomaticWorkload repository﹐简写为AWR）的原始数据，有经验的DBA可以自由的查询所需要的历史数据
AWR报告：
使用awrrpt.sql、awrrpti.sql、awrddrpt.sql、awrddrpi.sql所生成的为AWR报告，一般为保存为HTML或TXT格式文件，使用Toad等工具生成的AWR报告也是通过调用上面几个sql脚本所生成的，我们所看到的是已经分析好或已经排版好了的在一段时间内的报告文件
2、AWR原始数据导出的意义
（1）起到备份的作用（2）导出后，导入到其它数据库中进行深度分析
3、awrextr.sql存放位置
（1）存放路径：$ORACLE_HOME/rdbms/admin（2）查看存在情况[root@INFA ~]# su - oracle[oracle@INFA ~]$ cd $ORACLE_HOME/rdbms/admin[oracle@INFA admin]$ ls -l |grep awrextr.sql-rw-r--r--. 1 oracle oinstall 11082 Mar 24 2009 awrextr.sql 4、使用awrextr.sql前提条件准备（创建directory）
使用awrextr.sql，数据库中必须具有directory 4.1 在操作系统上创建directory的目录[root@INFA ~]# mkdir -p /u01/awr_extr[root@INFA ~]# chown -R oracle:oinstall /u01/awr_extr 4.2 在数据库上创建directory SQL> create directory awr_extr as '/u01/awr_extr';Directory created.
5、使用awrextr.sql导出awr数据
SQL> @?/rdbms/admin/awrextr.sql~~~~~~~~~~~~~AWR EXTRACT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This script will extract the AWR data for a range of snapshots ~~ into a dump file. The script will prompt users for the ~~ following information: ~~ (1) database id ~~ (2) snapshot range to extract ~~ (3) name of directory object ~~ (4) name of dump file ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Databases in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~DB Id DB Name Host------------ ------------ ------------* 3248492087 INFADB INFA1542553735 CRMOUT TSRRAC011542553735 CRMOUT TSRRAC02The default database id is the local one: '3248492087'. To use thisdatabase id, pressto continue, otherwise enter an alternative.Enter value for dbid:3248492087 --输入需导出AWR数据库的DBIDUsing 3248492087 for Database IDSpecify 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. Pressingwithoutspecifying a number lists all completed snapshots.Enter value for num_days:1 --输入需要导出AWR数据所选择的天数Listing the last day's Completed SnapshotsDB Name Snap Id Snap Started------------ --------- ------------------INFADB 612 03 Aug 2014 00:06613 03 Aug 2014 00:15614 03 Aug 2014 00:30615 03 Aug 2014 00:45616 03 Aug 2014 01:00617 03 Aug 2014 01:15618 03 Aug 2014 01:30619 03 Aug 2014 01:45620 03 Aug 2014 02:00621 03 Aug 2014 02:15Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap:612 --选择开始snap_id号Begin Snapshot Id specified: 612Enter value for end_snap:620--选择结束snap_id号End Snapshot Id specified: 620Specify the Directory Name~~~~~~~~~~~~~~~~~~~~~~~~~~Directory Name Directory Path------------------------------ -------------------------------------------------AWR_EXTR /u01/awr_extrDATA_PUMP_DIR /dba/oracle/admin/infadb/dpdump/EXPDP_DIR /u01/expdp_dirORACLE_OCM_CONFIG_DIR /dba/oracle/product/11.2.0/db_1/ccr/stateXMLDIR /dba/oracle/product/11.2.0/db_1/rdbms/xmlChoose a Directory Name from the above list (case-sensitive).Enter value for directory_name: AWR_EXTR --输入存放导出dump档的directory名称Using the dump directory: AWR_EXTRSpecify the Name of the Extract Dump File~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~The prefix for the default dump file name is awrdat_612_620.To use this name, pressto continue, otherwise enteran alternative.Enter value for file_name:awr_dump_612_620 --输入dump档名称，注意只要带.dmp后缀Using the dump file prefix: awr_dump_612_620|| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| The AWR extract dump file will be located| in the following directory/file:| /u01/awr_extr|awr_dump_612_620.dmp --dump文件名称自动带上了.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:| /u01/awr_extr| awr_dump_612_620.log|End of AWR ExtractSQL>
6、查看dump文件是否存在
[oracle@INFA ~]$ cd /u01/awr_extr[oracle@INFA awr_extr]$ ls -ltotal 12780-rw-r-----. 1 oracle oinstall 13058048 Aug 3 02:18 awr_dump_612_620.dmp-rw-r--r--. 1 oracle oinstall 25359 Aug 3 02:18 awr_dump_612_620.log不仅看到了导出的dmp文件，还看到了导出的log文件，导出成功。
本文作者：黎俊杰（网名：踩点），从事”系统架构、操作系统、存储设备、数据库、中间件、应用程序“六个层面系统性的性能优化工作欢迎加入 系统性能优化专业群，共同探讨性能优化技术。群号：258187244