使用awrextr.sql导出awr原始数据

转载至http://blog.csdn.net/ljunjie82/article/details/38326381

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       INFA   1542553735 CRMOUT       TSRRAC01   1542553735 CRMOUT       TSRRAC02   The default database id is the local one: '3248492087'.  To use this database id, press <return> to continue, otherwise enter an alternative.   Enter value for dbid: 3248492087           --输入需导出AWR数据库的DBID   Using 3248492087 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: 1        --输入需要导出AWR数据所选择的天数   Listing the last day's Completed Snapshots   DB Name        Snap Id    Snap Started ------------ --------- ------------------ INFADB             612 03 Aug 2014 00:06                    613 03 Aug 2014 00:15                    614 03 Aug 2014 00:30                    615 03 Aug 2014 00:45                    616 03 Aug 2014 01:00                    617 03 Aug 2014 01:15                    618 03 Aug 2014 01:30                    619 03 Aug 2014 01:45                    620 03 Aug 2014 02:00                    621 03 Aug 2014 02:15     Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 612          --选择开始snap_id号 Begin Snapshot Id specified: 612        Enter value for end_snap: 620         --选择结束snap_id号 End   Snapshot Id specified: 620     Specify the Directory Name ~~~~~~~~~~~~~~~~~~~~~~~~~~   Directory Name                 Directory Path ------------------------------ ------------------------------------------------- AWR_EXTR                       /u01/awr_extr DATA_PUMP_DIR                  /dba/oracle/admin/infadb/dpdump/ EXPDP_DIR                      /u01/expdp_dir ORACLE_OCM_CONFIG_DIR          /dba/oracle/product/11.2.0/db_1/ccr/state XMLDIR                         /dba/oracle/product/11.2.0/db_1/rdbms/xml   Choose a Directory Name from the above list (case-sensitive).   Enter value for directory_name: AWR_EXTR      --输入存放导出dump档的directory名称，注意大小写   Using the dump directory: AWR_EXTR   Specify the Name of the Extract Dump File ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The prefix for the default dump file name is awrdat_612_620. To use this name, press <return> to continue, otherwise enter an 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 Extract SQL>

6、查看dump文件是否存在

[oracle@INFA ~]$ cd /u01/awr_extr [oracle@INFA awr_extr]$ ls -l total 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文件，导出成功。