AWR report can be generating in RAC database using 2 scripts awrrpt.sql or awrrpti.sql
awrrpt.sql – > This will generate the one report for the database across all the nodes(i.e for all instances) for a partiular snapshot range.
awrrpti.sql – > This will genereate report for a particular instance, i.e for a 2 node RAC database , there will be two reports( one for each instance).
USING AWRRPT.SQL SCRIPT:
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
229213524 CLPRE 1 CLPRE1
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:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
229213524 2 CLPRE CLPRE2 local95-2
* 229213524 1 CLPRE CLPRE1 local94-2
Using 229213524 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 without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
CLPRE1 CLPRE 1224 28 Feb 2020 00:00 1
1225 28 Feb 2020 01:00 1
1226 28 Feb 2020 02:00 1
1227 28 Feb 2020 03:00 1
1228 28 Feb 2020 04:00 1
1229 28 Feb 2020 05:00 1
1230 28 Feb 2020 06:00 1
1231 28 Feb 2020 07:00 1
1232 28 Feb 2020 08:00 1
1233 28 Feb 2020 09:00 1
1234 28 Feb 2020 10:00 1
1235 28 Feb 2020 11:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1234
Begin Snapshot Id specified: 1234
Enter value for end_snap: 1235
End Snapshot Id specified: 1235
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1234_1235.html. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
USING AWRRPTI.SQL SCRIPT:
This script will prompt for providing the instance number :
SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.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:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
229213524 2 CLPRE CLPRE2 local95-2
* 229213524 1 CLPRE CLPRE1 local94-2
Enter value for dbid: 229213524
Using 229213524 for database Id
Enter value for inst_num: 1 ------------------->>> Here you need to the pass the instance number
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 without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
CLPRE1 CLPRE 1224 28 Feb 2020 00:00 1
1225 28 Feb 2020 01:00 1
1226 28 Feb 2020 02:00 1
1227 28 Feb 2020 03:00 1
1228 28 Feb 2020 04:00 1
1229 28 Feb 2020 05:00 1
1230 28 Feb 2020 06:00 1
1231 28 Feb 2020 07:00 1
1232 28 Feb 2020 08:00 1
1233 28 Feb 2020 09:00 1
1234 28 Feb 2020 10:00 1
1235 28 Feb 2020 11:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1234
Begin Snapshot Id specified: 1234
Enter value for end_snap: 1235
End Snapshot Id specified: 1235
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1234_1235.html. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_1234_1235.html
