Use awrextr. SQL to export awr raw data and awrextr. SQL to export awr

Use awrextr. SQL to export awr raw data and awrextr. SQL to export awr
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 <return> 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 <return> 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 <return> 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

Database: Oracle 11g AWR Series 5: how to generate an AWR report?

1. generate an AWR report for a single instance: @ $ ORACLE_HOME/rdbms/admin/awrrpt. sql2. generate an Oracle rac awr Report: @ $ ORACLE_HOME/rdbms/admin/awrgrpt. sql3. generate an AWR report for a specific database instance in the RAC environment: @ $ ORACLE_HOME/rdbms/admin/awrrpti. sql4. how to generate AWR reports for multiple database instances in the Oracle RAC environment: @ $ ORACLE_HOME/rdbms/admin/awrkgti. sql5. generate an AWR report for the SQL statement: @ $ ORACLE_HOME/rdbms/admin/awrsqrpt. sql6. generate an AWR report for an SQL statement on a specific database instance: @ $ ORACLE_HOME/rdbms/admin/awrsqrpi. SQL -- generate AWR period comparison report 7. generate the AWR period comparison report for a single instance @ $ ORACLE_HOME/rdbms/admin/awrddrpt. sql9. generate the Oracle rac awr period comparison report @ $ ORACLE_HOME/rdbms/admin/awrgdrpt. sql10. generate AWR period comparison report for specific database instances 11. generate AWR time period comparison reports for specific (multiple) database instances in the Oracle RAC Environment

What permissions are required to generate an oracle database awr report?

The techniques for generating an AWR report () are as follows ::
1. log on to the Oracle database ::

Sqlplus/as sysdba

2. In sqlplus, run the following () command as sys to generate the first performance data snapshot ::

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (flush_level => 'all ');

3. Run all or many poorly performing applications or SQL statements you have found to keep the system under high pressure) status (such as CPU or I/O), and keep this status from half an hour to one hour.

4. In sqlplus, run the following () command again as sys to generate a second performance data snapshot ::

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (flush_level => 'all ');

5. In sqlplus, run the following () command as sys to generate a performance report ::

@? /Rdbms/admin/awrrpt. SQL

As prompted, select the two () performance data snapshots generated above, and finally generate a final () AWR performance report ..