Use awrextr. SQL to export original awr data

1. Differences between AWR raw data and AWR reports AWR raw data: the oracle database mmon Process regularly dumps statistics from memory to disk, the raw data of the automatic workload storage warehouse (AWR) is stored in several tables in a structured manner. Experienced DBAs can freely query the expected calendar.

1. Differences between AWR raw data and AWR reports AWR raw data: the oracle database mmon Process regularly dumps statistics from memory to disk, the raw data of the automatic workload storage warehouse (AWR) is stored in several tables in a structured manner. Experienced DBAs can freely query the expected calendar

1. Differences between original AWR data and AWR reports

AWR raw data:

The oracle Database mmon Process regularly dumps statistics from memory to disk and stores them in several tables in a structured manner to form an automatic workload storage warehouse (AutomaticWorkload repository, abbreviated as AWR) experienced DBAs can freely query the required historical data.

AWR report:

Use awrrpt. SQL, awrrpti. SQL, awrddrpt. SQL, awrddrpi. the AWR report generated by SQL is generally saved as an HTML or TXT file. The AWR report generated by tools such as Toad is also generated by calling the preceding SQL scripts, what we see is a report file that has been analyzed or formatted for a period of time.

2. Significance of AWR raw data export

(1) backup

(2) import the exported data to other databases for in-depth analysis.

3. awrextr. SQL storage location

(1) Storage path: $ ORACLE_HOME/rdbms/admin

(2) view existing conditions

[Root @ INFA ~] # Su-oracle [Oracle @ INFA ~] $ Cd $ ORACLE_HOME/rdbms/admin [Oracle @ INFA admin] $ ls-l | grep awrextr. SQL -Rw-r --. 1 oracle oinstall 11082 Mar 24 2009 awrextr. SQL

4. Use awrextr. SQL prerequisites (create directory)

When awrextr. SQL is used, the database must have directory

4.1 create directory on the Operating System

[Root @ INFA ~] # Mkdir-p/u01/awr_extr [Root @ INFA ~] # Chown-R oracle: oinstall/u01/awr_extr

4.2 create 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 will extract the AWR data for a range of snapshots ~ ~ Into a dump file. The script will prompt users for ~ ~ 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: '000000'. To use this Database id, press To continue, otherwise enter an alternative. Enter value for dbid:3248492087 -- enter the dbid of the awr database to export 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 -- enter the number of days to export AWR data Listing the last day's Completed Snapshots DB Name Snap Id Snap Started --------------------------------------- INFADB 612 03 Aug 2014 613 03 Aug 2014 614 03 Aug 2014 615 03 Aug 2014 616 03 Aug 2014 617 03 Aug 2014 618 03 Aug 2014 619 03 Aug 2014 620 03 Aug 2014 621 03 Aug 2014 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:612 -- Select Start snap_id Begin Snapshot Id specified: 612 Enter value for end_snap:620 -- select end 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/Cr/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 name for storing and exporting dump files 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 -- enter the dump file name. Note that the. dmp suffix is included. 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_61_dmp -- the dump file name is automatically suffixed with. dmp. | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~ | | *** AWR Extract Started... | | This operation will take a few moments. | Progress of the AWR extract operation can be | Monitored in the following directory/file: |/U01/awr_extr | Awr_dump_612_61_log | End of AWR Extract SQL>

6. Check whether the dump file exists.

[Oracle @ INFA ~] $ Cd/u01/awr_extr [Oracle @ INFA awr_extr] $ ls-l Total 12780 -Rw-r -----. 1 oracle oinstall 13058048 Aug 3 awr_dump_612_61_dmp -Rw-r --. 1 oracle oinstall 25359 Aug 3 awr_dump_612_61_log

You can see not only the exported dmp file, but also the exported log file, which is successfully exported.

Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work

Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244