AWR REPORT:
The Automatic Workload Repository (AWR) collects and maintains statistics of the
database. We can generate awr report for a particular time frame in the past using the script
awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin) script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql
step – 1
[oratest@oracle ~]$ export ORACLE_SID=test [oratest@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 7 11:06:50 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- TEST READ ONLY SQL> select status from v$instance; STATUS ------------ OPEN SQL> show user; USER is "SYS"
step – 2
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
Enter value for report_type:<strong> <span style="color:#ed0911" <br />class="has-inline-color">html</span></strong>
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) <br />report_type from dual
new 1: select 'Type Specified: ',lower(nvl('html','html')) <br />report_type from dual
Type Specified: html
old 1: select '&&report_type' report_type_def from dual
new 1: select 'html' report_type_def from dual
old 1: select '&&view_loc' view_loc_def from dual
new 1: select 'AWR_PDB' view_loc_def from dual
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------
2378581000 TEST 1 test test
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 2378581000 1 TEST test oracle.local
Using 2378581000 for database Id
Using 1 for instance number
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: 2 Listing the last 2 days of Completed Snapshots Instance DB Name Snap Id Snap Started Snap Level ------------ ------------ ---------- ------------------ ---------- test TEST 646 29 Sep 2021 00:30 1 647 29 Sep 2021 01:30 1 648 29 Sep 2021 02:30 1 649 29 Sep 2021 03:30 1 650 29 Sep 2021 04:30 1 651 29 Sep 2021 05:30 1 652 29 Sep 2021 06:30 1 653 30 Sep 2021 00:14 1 654 30 Sep 2021 01:30 1 655 30 Sep 2021 03:46 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:<strong><span style="color:#a3000d" <br />class="has-inline-color"> </span><span style="color:#ec0b1e" <br />class="has-inline-color">650</span></strong> Begin Snapshot Id specified: 650 Enter value for end_snap: <strong><span style="color:#e90c17" <br />class="has-inline-color">651</span></strong> </pre> <!-- /wp:preformatted --> <!-- wp:preformatted --> <pre class="wp-block-preformatted">Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_650_651.html. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: awrrpt_1_07_10_2021.html Using the report name awrrpt_1_07_10_2021.html -------------------------------------------- Analysis Period --------------- AWR snapshot range from 650 to 651. Time period starts at 29-SEP-21 04.30.25 AM Time period ends at 29-SEP-21 05.30.33 AM Analysis Target --------------- Database 'TEST' with DB ID 2378581000. Database version 19.0.0.0.0. ADDM performed an analysis of instance test, numbered 1 and hosted at oracle.localdomain. Activity During the Analysis Period ----------------------------------- Total database time was 0 seconds. The average number of active sessions was 0. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ There are no findings to report. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Miscellaneous Information ------------------------- There was no significant database activity to run the ADDM. </pre> <br /><a class="awr" href="#top">Back to Top</a><p /> <p /> <p /> <p /> <p /> <p /> <p /> <p /> <p /> <p /> End of Report </body></html> Report written to awrrpt_1_07_10_2021.html
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
