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

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 INFA

1542553735 CRMOUT TSRRAC01

1542553735 CRMOUT TSRRAC02

The default database id is the local one: '3248492087'. To use this

database id, press 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 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 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文件，导出成功。

本文作者：黎俊杰（网名：踩点），从事”系统架构、操作系统、存储设备、数据库、中间件、应用程序“六个层面系统性的性能优化工作

欢迎加入 系统性能优化专业群，共同探讨性能优化技术。群号：258187244