Exporting AWR raw Data using Awrextr.sql

1. The difference between AWR original data and AWR report

awr raw Data:

is the Oracle database Mmon process periodically dumps statistics from memory to disk, and in a structured form into several tables that form the raw data for an automated workload storage warehouse (Automaticworkload repository,) Experienced DBAs are free to query the required historical data

AWR report:

Use Awrrpt.sql, Awrrpti.sql, Awrddrpt.sql, Awrddrpi.sql generated for the AWR report, typically saved as HTML or txt format files, using tools such as Toad generated AWR reports are generated by invoking the above several SQL scripts, we see that has been analyzed or has been formatted for a period of time the report file

2, the significance of the AWR raw data export

(1) Play a role in backup

(2) After export, import into other database for in-depth analysis

3, Awrextr.sql storage location

(1) Storage path: $ORACLE _home/rdbms/admin

(2) Check the presence status

[Email protected] ~]# su-oracle [Email protected] ~]$ CD $ORACLE _home/rdbms/admin [Email protected] admin]$ ls-l |grep awrextr.sql -rw-r--r--. 1 Oracle oinstall 11082 Mar awrextr.sql

4. Prepare with Awrextr.sql prerequisites (Create directory)

With Awrextr.sql, you must have directory in your database

4.1 Creating directory directories on the operating system

[Email protected] ~]# mkdir-p/u01/awr_extr [Email protected] ~]# chown-r oracle:oinstall/u01/awr_extr

4.2 Creating a directory on the database

Sql> Create directory awr_extr as '/u01/awr_extr '; Directory created.

5. Use Awrextr.sql to export AWR data

SQL> @?/rdbms/admin/awrextr.sql ~~~~~~~~~~~~~ AWR EXTRACT ~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ This script would extract the AWR data for a range of snapshots ~ ~ into a dump file. The script would 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 -- Enter the DBI to be exported to the AWR database D Using 3248492087 for Database ID Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) would 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 -- Enter The number of days you choose to export the AWR data Listing the last day ' s completed snapshots DB Name snap Id snap Started ------------ --------- ------------------ INFADB 612 2014 00:06 613 2014 00:15 614 2014 00:30 615 2014 00:45 616 2014 01:00 617 2014 01:15 618 2014 01:30 619 2014 01:45 620 2014 02:00 621 2014 02:15 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for Begin_snap: 612 -- Select Start snap_id number Begin Snapshot Id specified:612 Enter value for END_SNAP: 620 -- Select End snap_id number 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 -- Enter The directory where the exported dump is stored c6> name 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 the this name, press <return> to continue, otherwise enter An alternative. Enter value for file_name: awr_dump_612_620 -- Enter The name of the dump file, as long as the band . DMP suffix Using the dump file prefix:awr_dump_612_620 | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | The AWR extract dump file would be located | In the following directory/file: | /u01/awr_extr | awr_dump_612_620.dmp --dump file name is automatically brought up . dmp suffix | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | | AWR Extract Started ... | | This operation would 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. See if the dump file exists

[Email protected] ~]$ CD/U01/AWR_EXTR [Email protected] awr_extr]$ ls-l Total 12780 -rw-r-----. 1 Oracle Oinstall 13058048 3 02:18 awr_dump_612_620.dmp -rw-r--r--. 1 Oracle Oinstall 25359 3 02:18 awr_dump_612_620.log

Not only saw the exported DMP file, but also saw the exported log file, the export was successful.

This article Li Junjie (Network Name: casing), engaged in "system architecture, operating systems, storage devices, databases, middleware, applications" six levels of systematic performance optimization work

Welcome to the System performance Optimization Professional group, to discuss performance optimization technology together. Group number: 258187244