11gR2; AWR Reports in RAC

October 2nd, 2011 | Posted in 11gR2, AWR, Blog, RAC | 6 Comments

Download the post in pdf version here:

1- AWR Concept:

In this post, I am going to show you how to generate AWR reports for Real Application Clusters (RAC) environment.

AWR was first introduced in 10g version of Oracle as a repository of all important statistics for an Oracle database. At a regular intervals, Oracle makes a snapshots of those vital statistics and stores them in SYSAUX tablespace. In 11g version, These snapshots are retained for 8 days. It is the MMON background process who is responsible of AWR collection.

AWR scripts allow you to generate the reports in HTML or TEXT formats. These reports allow you to analyze the workload of the database at a given intervals. Oracle extends the AWR tool for RAC databases by adding the possibility to generate snapshots reports against the whole cluster database, some database instances of the cluster database, a specific database instance of the cluster database, statistics for a specific select query. In the following sections, I am going to show you how to generate AWR reports for each case.

2- Generate AWR reports for the current Instance:

Run the awrrpt
script from $ORACLE_HOME/rdbms/admin/
as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids …

In my Example below, this is the output of a six node RAC database.


-bash-3.2$ cd /opt/app/oracle/product/11.2.0/db_1/rdbms/admin/

-bash-3.2$



-bash-3.2$ sqlplus / as sysdba



SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 28 13:04:54 2011



Copyright (c) 1982, 2010, Oracle.  All rights reserved.





Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options



SQL> @awrrpt



Current Instance

~~~~~~~~~~~~~~~~



   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

 1399294885 ddb               1 ddb1





Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: html



Type Specified:  html





Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

  1399294885        6 ddb        ddb6       srvdb06

  1399294885        4 ddb        ddb4       srvdb04

  1399294885        2 ddb        ddb2       srvdb02

  1399294885        5 ddb        ddb5       srvdb05

* 1399294885        1 ddb        ddb1       srvdb01

  1399294885        3 ddb        ddb3       srvdb03



Using 1399294885 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:





                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

ddb1       ddb              286 26 Sep 2011 23:00      1

                                287 27 Sep 2011 00:00      1

                                288 27 Sep 2011 01:00      1

                                289 27 Sep 2011 02:00      1

                                290 27 Sep 2011 03:00      1

                                291 27 Sep 2011 04:00      1

                                292 27 Sep 2011 05:00      1

                                293 27 Sep 2011 06:00      1

                                294 27 Sep 2011 07:00      1

                                295 27 Sep 2011 08:00      1

                                296 27 Sep 2011 09:00      1

                                297 27 Sep 2011 10:00      1

                                298 27 Sep 2011 11:00      1

                                299 27 Sep 2011 12:00      1

                                300 27 Sep 2011 13:00      1

                                301 27 Sep 2011 14:00      1

                                302 27 Sep 2011 15:00      1

                                303 27 Sep 2011 16:00      1

                                304 27 Sep 2011 17:00      1

                                305 27 Sep 2011 18:00      1

                                306 27 Sep 2011 19:00      1

                                307 27 Sep 2011 20:00      1

                                308 27 Sep 2011 21:00      1

                                309 27 Sep 2011 22:00      1

                                310 27 Sep 2011 23:00      1

                                311 28 Sep 2011 00:00      1

                                312 28 Sep 2011 01:00      1

                                313 28 Sep 2011 02:00      1

                                314 28 Sep 2011 03:00      1

                                315 28 Sep 2011 04:00      1

                                316 28 Sep 2011 05:00      1

                                317 28 Sep 2011 06:00      1

                                318 28 Sep 2011 07:00      1

                                319 28 Sep 2011 08:00      1

                                320 28 Sep 2011 09:00      1

                                321 28 Sep 2011 10:00      1

                                322 28 Sep 2011 11:00      1

                                323 28 Sep 2011 12:00      1

                                324 28 Sep 2011 13:00      1







Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 323

Begin Snapshot Id specified: 323



Enter value for end_snap: 324

End   Snapshot Id specified: 324







Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_323_324.html.  To use this name,

press  to continue, otherwise enter an alternative.



Enter value for report_name:

...

...



End of Report



Report written to awrrpt_1_323_324.html

SQL>



3- Generate AWR reports for any instance in the cluster:

Run the awrrpti script from $ORACLE_HOME/rdbms/admin/
as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the instance number, the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids …
In the example below, I am generating a report for the instance number 5, which is ddb5.



-bash-3.2$ sqlplus / as sysdba



SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 28 13:09:48 2011



Copyright (c) 1982, 2010, Oracle.  All rights reserved.





Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options



SQL> @awrrpti



Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: text



Type Specified:  text





Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

  1399294885        6 ddb        ddb6       srvdb06

  1399294885        4 ddb        ddb4       srvdb04

  1399294885        2 ddb        ddb2       srvdb02

  1399294885        5 ddb        ddb5       srvdb05

* 1399294885        1 ddb        ddb1       srvdb01

  1399294885        3 ddb        ddb3       srvdb03



Enter value for dbid: 1399294885

Using 1399294885 for database Id

Enter value for inst_num: 5

Using 5 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:



                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

ddb5       ddb              230 24 Sep 2011 15:00      1

                                231 24 Sep 2011 16:00      1

                                232 24 Sep 2011 17:00      1

                                233 24 Sep 2011 18:00      1

                                234 24 Sep 2011 19:00      1

                                235 24 Sep 2011 20:00      1

                                236 24 Sep 2011 21:00      1

                                237 24 Sep 2011 22:00      1

                                238 24 Sep 2011 23:00      1

                                239 25 Sep 2011 00:00      1

                                240 25 Sep 2011 01:00      1

                                241 25 Sep 2011 02:00      1

                                242 25 Sep 2011 03:00      1

                                243 25 Sep 2011 04:00      1

                                244 25 Sep 2011 05:00      1

                                245 25 Sep 2011 06:00      1

                                246 25 Sep 2011 07:00      1

                                247 25 Sep 2011 08:00      1

                                248 25 Sep 2011 09:00      1

                                249 25 Sep 2011 10:00      1

                                250 25 Sep 2011 11:00      1

                                251 25 Sep 2011 12:00      1

                                252 25 Sep 2011 13:00      1

                                253 25 Sep 2011 14:00      1

                                254 25 Sep 2011 15:00      1

                                255 25 Sep 2011 16:00      1

                                256 25 Sep 2011 17:00      1

                                257 25 Sep 2011 18:00      1

                                258 25 Sep 2011 19:00      1

                                259 25 Sep 2011 20:00      1

                                260 25 Sep 2011 21:00      1

                                261 25 Sep 2011 22:00      1

                                262 25 Sep 2011 23:00      1

                                263 26 Sep 2011 00:00      1

                                264 26 Sep 2011 01:00      1

                                265 26 Sep 2011 02:00      1

                                266 26 Sep 2011 03:00      1

                                267 26 Sep 2011 04:00      1

                                268 26 Sep 2011 05:00      1

                                269 26 Sep 2011 06:00      1

                                270 26 Sep 2011 07:00      1

                                271 26 Sep 2011 08:00      1

                                272 26 Sep 2011 09:00      1

                                273 26 Sep 2011 10:00      1

                                274 26 Sep 2011 11:00      1

                                275 26 Sep 2011 12:00      1

                                276 26 Sep 2011 13:00      1

                                277 26 Sep 2011 14:00      1

                                278 26 Sep 2011 15:00      1

                                279 26 Sep 2011 16:00      1

                                280 26 Sep 2011 17:00      1

                                281 26 Sep 2011 18:00      1

                                282 26 Sep 2011 19:00      1

                                283 26 Sep 2011 20:00      1

                                284 26 Sep 2011 21:00      1

                                285 26 Sep 2011 22:00      1



                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

ddb5       ddb              286 26 Sep 2011 23:00      1

                                287 27 Sep 2011 00:00      1

                                288 27 Sep 2011 01:00      1

                                289 27 Sep 2011 02:00      1

                                290 27 Sep 2011 03:00      1

                                291 27 Sep 2011 04:00      1

                                292 27 Sep 2011 05:00      1

                                293 27 Sep 2011 06:00      1

                                294 27 Sep 2011 07:00      1

                                295 27 Sep 2011 08:00      1

                                296 27 Sep 2011 09:00      1

                                297 27 Sep 2011 10:00      1

                                298 27 Sep 2011 11:00      1

                                299 27 Sep 2011 12:00      1

                                300 27 Sep 2011 13:00      1

                                301 27 Sep 2011 14:00      1

                                302 27 Sep 2011 15:00      1

                                303 27 Sep 2011 16:00      1

                                304 27 Sep 2011 17:00      1

                                305 27 Sep 2011 18:00      1

                                306 27 Sep 2011 19:00      1

                                307 27 Sep 2011 20:00      1

                                308 27 Sep 2011 21:00      1

                                309 27 Sep 2011 22:00      1

                                310 27 Sep 2011 23:00      1

                                311 28 Sep 2011 00:00      1

                                312 28 Sep 2011 01:00      1

                                313 28 Sep 2011 02:00      1

                                314 28 Sep 2011 03:00      1

                                315 28 Sep 2011 04:00      1

                                316 28 Sep 2011 05:00      1

                                317 28 Sep 2011 06:00      1

                                318 28 Sep 2011 07:00      1

                                319 28 Sep 2011 08:00      1

                                320 28 Sep 2011 09:00      1

                                321 28 Sep 2011 10:00      1

                                322 28 Sep 2011 11:00      1

                                323 28 Sep 2011 12:00      1

                                324 28 Sep 2011 13:00      1







Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 323

Begin Snapshot Id specified: 323



Enter value for end_snap: 324





Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_5_323_324.txt.  To use this name,

press  to continue, otherwise enter an alternative.



Enter value for report_name:

...

...

...



   Receive  Receive  Receive   Buffer    Frame

 Mbytes/sec   Errors  Dropped  Overrun   Errors

----------- -------- -------- -------- --------

bond0:1         169.254.37.89    NO

        .00        0        0        0        0

        .00        0        0        0        0

          -------------------------------------------------------------



Dynamic Remastering Stats                DB/Inst: ddb/ddb5  Snaps: 323-324



                  No data exists for this section of the report.

          -------------------------------------------------------------



End of Report

Report written to awrrpt_5_323_324.txt

SQL>

4- Generate AWR Cluster aggregated statistics from all the instances:

Run the awrgrpt script from $ORACLE_HOME/rdbms/admin/ as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids …
This report shows the aggregated statistics from the whole cluster database; also, described as a global AWR cluster report:



-bash-3.2$ sqlplus / as sysdba



SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 28 13:13:49 2011



Copyright (c) 1982, 2010, Oracle.  All rights reserved.





Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options



SQL> @awrgrpt.sql



Current Database

~~~~~~~~~~~~~~~~



   DB Id    DB Name

----------- ------------

 1399294885 ddb





Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: text

Type Specified:  text





Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



   DB Id      INSTT_NUM DB Name      INSTT_NAME       Host

------------ ---------- ------------ ---------------- ------------

* 1399294885          6 ddb        ddb6           srvdb06

* 1399294885          5 ddb        ddb5           srvdb05

* 1399294885          2 ddb        ddb2           srvdb02

* 1399294885          1 ddb        ddb1           srvdb01

* 1399294885          3 ddb        ddb3           srvdb03

* 1399294885          4 ddb        ddb4           srvdb04



Using 1399294885 for database Id

Using instances ALL (default 'ALL')





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:

                                          Snap

DB Name        Snap Id    Snap Started    Level

------------ --------- ------------------ -----

ddb              287 27 Sep 2011 00:00      1

                   288 27 Sep 2011 01:00      1

                   289 27 Sep 2011 02:00      1

                   290 27 Sep 2011 03:00      1

                   291 27 Sep 2011 04:00      1

                   292 27 Sep 2011 05:00      1

                   293 27 Sep 2011 06:00      1

                   294 27 Sep 2011 07:00      1

                   295 27 Sep 2011 08:00      1

                   296 27 Sep 2011 09:00      1

                   297 27 Sep 2011 10:00      1

                   298 27 Sep 2011 11:00      1

                   299 27 Sep 2011 12:00      1

                   300 27 Sep 2011 13:00      1

                   301 27 Sep 2011 14:00      1

                   302 27 Sep 2011 15:00      1

                   303 27 Sep 2011 16:00      1

                   304 27 Sep 2011 17:00      1

                   305 27 Sep 2011 18:00      1

                   306 27 Sep 2011 19:00      1

                   307 27 Sep 2011 20:00      1

                   308 27 Sep 2011 21:00      1

                   309 27 Sep 2011 22:00      1

                   310 27 Sep 2011 23:00      1

                   311 28 Sep 2011 00:00      1

                   312 28 Sep 2011 01:00      1

                   313 28 Sep 2011 02:00      1

                   314 28 Sep 2011 03:00      1

                   315 28 Sep 2011 04:00      1

                   316 28 Sep 2011 05:00      1

                   317 28 Sep 2011 06:00      1

                   318 28 Sep 2011 07:00      1

                   319 28 Sep 2011 08:00      1

                   320 28 Sep 2011 09:00      1

                   321 28 Sep 2011 10:00      1

                   322 28 Sep 2011 11:00      1

                   323 28 Sep 2011 12:00      1

                   324 28 Sep 2011 13:00      1





Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 323

Begin Snapshot Id specified: 323



Enter value for end_snap: 324

End   Snapshot Id specified: 324







Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_rac_323_324.txt.  To use this name,

press  to continue, otherwise enter an alternative.



Enter value for report_name:

...

....

.....

End of Report

Report written to awrrpt_rac_323_324.txt

SQL>


5- Generate AWR Global Cluster Difference Report:

Run the awrgdrpt.sql script from $ORACLE_HOME/rdbms/admin/ as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids of the first and second pair diff intervals…
This report compares the statistic results of differences between two different snapshot intervals, for the whole cluster database.



-bash-3.2$ sqlplus / as sysdba



SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 28 13:16:36 2011



Copyright (c) 1982, 2010, Oracle.  All rights reserved.





Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options



SQL> @awrgdrpt.sql



Current Instance

~~~~~~~~~~~~~~~~



   DB Id       DB Id    DB Name

----------- ----------- ------------

 1399294885  1399294885 ddb





Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: text



Type Specified:  text





Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

  1399294885        6 ddb        ddb6       srvdb06

  1399294885        4 ddb        ddb4       srvdb04

  1399294885        2 ddb        ddb2       srvdb02

  1399294885        5 ddb        ddb5       srvdb05

* 1399294885        1 ddb        ddb1       srvdb01

  1399294885        3 ddb        ddb3       srvdb03



Database Id and Instance Number for the First Pair of Snapshots

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 1399294885 for Database Id for the first pair of snapshots

Using instances ALL for the first pair of snapshots





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: 2

Listing the last 2 days of Completed Snapshots



                                           Snap

DB Name        Snap Id    Snap Started    Level

------------ --------- ------------------ -----

ddb              287 27 Sep 2011 00:00      1

                   288 27 Sep 2011 01:00      1

                   289 27 Sep 2011 02:00      1

                   290 27 Sep 2011 03:00      1

                   291 27 Sep 2011 04:00      1

                   292 27 Sep 2011 05:00      1

                   293 27 Sep 2011 06:00      1

                   294 27 Sep 2011 07:00      1

                   295 27 Sep 2011 08:00      1

                   296 27 Sep 2011 09:00      1

                   297 27 Sep 2011 10:00      1

                   298 27 Sep 2011 11:00      1

                   299 27 Sep 2011 12:00      1

                   300 27 Sep 2011 13:00      1

                   301 27 Sep 2011 14:00      1

                   302 27 Sep 2011 15:00      1

                   303 27 Sep 2011 16:00      1

                   304 27 Sep 2011 17:00      1

                   305 27 Sep 2011 18:00      1

                   306 27 Sep 2011 19:00      1

                   307 27 Sep 2011 20:00      1

                   308 27 Sep 2011 21:00      1

                   309 27 Sep 2011 22:00      1

                   310 27 Sep 2011 23:00      1

                   311 28 Sep 2011 00:00      1

                   312 28 Sep 2011 01:00      1

                   313 28 Sep 2011 02:00      1

                   314 28 Sep 2011 03:00      1

                   315 28 Sep 2011 04:00      1

                   316 28 Sep 2011 05:00      1

                   317 28 Sep 2011 06:00      1

                   318 28 Sep 2011 07:00      1

                   319 28 Sep 2011 08:00      1

                   320 28 Sep 2011 09:00      1

                   321 28 Sep 2011 10:00      1

                   322 28 Sep 2011 11:00      1

                   323 28 Sep 2011 12:00      1

                   324 28 Sep 2011 13:00      1





Specify the First Pair of Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 323

First Begin Snapshot Id specified: 323



Enter value for end_snap: 324

First End   Snapshot Id specified: 324









Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

  1399294885        6 ddb        ddb6       srvdb06

  1399294885        4 ddb        ddb4       srvdb04

  1399294885        2 ddb        ddb2       srvdb02

  1399294885        5 ddb        ddb5       srvdb05

* 1399294885        1 ddb        ddb1       srvdb01

  1399294885        3 ddb        ddb3       srvdb03



INSTNUM1

--------------------------------------------------------------------------------





Database Id and Instance Number for the Second Pair of Snapshots

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 1399294885 for Database Id for the second pair of snapshots

Using instances ALL for the second pair of snapshots





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_days2: 4

Listing the last 2 days of Completed Snapshots



                                           Snap

DB Name        Snap Id    Snap Started    Level

------------ --------- ------------------ -----

ddb              287 27 Sep 2011 00:00      1

                   288 27 Sep 2011 01:00      1

                   289 27 Sep 2011 02:00      1

                   290 27 Sep 2011 03:00      1

                   291 27 Sep 2011 04:00      1

                   292 27 Sep 2011 05:00      1

                   293 27 Sep 2011 06:00      1

                   294 27 Sep 2011 07:00      1

                   295 27 Sep 2011 08:00      1

                   296 27 Sep 2011 09:00      1

                   297 27 Sep 2011 10:00      1

                   298 27 Sep 2011 11:00      1

                   299 27 Sep 2011 12:00      1

                   300 27 Sep 2011 13:00      1

                   301 27 Sep 2011 14:00      1

                   302 27 Sep 2011 15:00      1

                   303 27 Sep 2011 16:00      1

                   304 27 Sep 2011 17:00      1

                   305 27 Sep 2011 18:00      1

                   306 27 Sep 2011 19:00      1

                   307 27 Sep 2011 20:00      1

                   308 27 Sep 2011 21:00      1

                   309 27 Sep 2011 22:00      1

                   310 27 Sep 2011 23:00      1

                   311 28 Sep 2011 00:00      1

                   312 28 Sep 2011 01:00      1

                   313 28 Sep 2011 02:00      1

                   314 28 Sep 2011 03:00      1

                   315 28 Sep 2011 04:00      1

                   316 28 Sep 2011 05:00      1

                   317 28 Sep 2011 06:00      1

                   318 28 Sep 2011 07:00      1

                   319 28 Sep 2011 08:00      1

                   320 28 Sep 2011 09:00      1

                   321 28 Sep 2011 10:00      1

                   322 28 Sep 2011 11:00      1

                   323 28 Sep 2011 12:00      1

                   324 28 Sep 2011 13:00      1





Specify the First Pair of Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap2: 321

First Begin Snapshot Id specified: 321



Enter value for end_snap2: 322

First End   Snapshot Id specified: 322







Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrracdiff_1st_323_2nd_321.txt  To use this name,

press  to continue, otherwise enter an alternative.



Enter value for report_name:



Using the report name awrracdiff_1st_323_2nd_321.txt





....

....

.....



--------------------------------------------------------------



End of Report

Report written to awrracdiff_1st_323_2nd_321.txt

SQL> SQL>

6- Generate AWR Report Single Select Statement:

Run the awrsqrpt.sql script from $ORACLE_HOME/rdbms/admin/ as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids intervals and the SQL_ID for the specific SELECT statement …



SQL> select SQL_ID  from V$SQLAREA where rownum =1 ;



SQL_ID

-------------

4qm8a3w6a1rfd



SQL>





SQL> @awrsqrpt.sql



Current Instance

~~~~~~~~~~~~~~~~



   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

 1399294885 ddb               1 ddb1





Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: text

Type Specified:  text





Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

  1399294885        6 ddb        ddb6       srvdb06

  1399294885        4 ddb        ddb4       srvdb04

  1399294885        2 ddb        ddb2       srvdb02

  1399294885        5 ddb        ddb5       srvdb05

* 1399294885        1 ddb        ddb1       srvdb01

  1399294885        3 ddb        ddb3       srvdb03



Using 1399294885 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:

                                                      Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

ddb1       ddb              286 26 Sep 2011 23:00      1

                                287 27 Sep 2011 00:00      1

                                288 27 Sep 2011 01:00      1

                                289 27 Sep 2011 02:00      1

                                290 27 Sep 2011 03:00      1

                                291 27 Sep 2011 04:00      1

                                292 27 Sep 2011 05:00      1

                                293 27 Sep 2011 06:00      1

                                294 27 Sep 2011 07:00      1

                                295 27 Sep 2011 08:00      1

                                296 27 Sep 2011 09:00      1

                                297 27 Sep 2011 10:00      1

                                298 27 Sep 2011 11:00      1

                                299 27 Sep 2011 12:00      1

                                300 27 Sep 2011 13:00      1

                                301 27 Sep 2011 14:00      1

                                302 27 Sep 2011 15:00      1

                                303 27 Sep 2011 16:00      1

                                304 27 Sep 2011 17:00      1

                                305 27 Sep 2011 18:00      1

                                306 27 Sep 2011 19:00      1

                                307 27 Sep 2011 20:00      1

                                308 27 Sep 2011 21:00      1

                                309 27 Sep 2011 22:00      1

                                310 27 Sep 2011 23:00      1

                                311 28 Sep 2011 00:00      1

                                312 28 Sep 2011 01:00      1

                                313 28 Sep 2011 02:00      1

                                314 28 Sep 2011 03:00      1

                                315 28 Sep 2011 04:00      1

                                316 28 Sep 2011 05:00      1

                                317 28 Sep 2011 06:00      1

                                318 28 Sep 2011 07:00      1

                                319 28 Sep 2011 08:00      1

                                320 28 Sep 2011 09:00      1

                                321 28 Sep 2011 10:00      1

                                322 28 Sep 2011 11:00      1

                                323 28 Sep 2011 12:00      1

                                324 28 Sep 2011 13:00      1







Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 319

Begin Snapshot Id specified: 319



Enter value for end_snap: 324

End   Snapshot Id specified: 324









Specify the SQL Id

~~~~~~~~~~~~~~~~~~

Enter value for sql_id: 4qm8a3w6a1rfd

.....

7- License:

AWR tool is part of the diagnostic pack option which needs an Oracle license. The substitute of the AWR report in the case you are not licensed to use AWR tool is the Statspack tool which is detailed in my previous post: Statspack in RAC (http://www.oracle-class.com/?p=2384).

In 11g, if you are not licensed to use the diagnostic pack tool you have to disable the option by setting the control_management_pack_access to NONE;
alter system set control_management_pack_access=’none’;

In 10g version., you can disable the AWR tool following the MOS ID: 436386.1 which provides a script to disable the AWR package.

To disable database option, you can use chopt command line; for example; to disable partitioning option, you can run the following command from ALL nodes of the cluster as oracle database home owner (usually called oracle user):



-bash-3.2$ chopt disable partitioning



Writing to /opt/app/oracle/product/11.2.0/db_1/install/disable_partitioning.log...

/usr/bin/make -f /opt/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk part_off ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1

/usr/bin/make -f /opt/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1



6 Comments to “11gR2; AWR Reports in RAC

  1. Rafi says:

    Hi Wissem,
    For a cluster Database this post will be of great use.Thanks very much.

    Best regards,

    Rafi.

  2. gopalredy says:

    really its very use full to dbas

  3. Manish says:

    Hi Wissem,

    is this possible to take an AWR report for an update statement also ?

    Regards
    Manish

  4. Koti says:

    Can you please explain all the sections of AWR report in RAC. I mean to ask output produced by awrgrpt.sql script


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • Mike: Very nice article!! Thanks for sharing!!
  • yathish: I really appreciate if you can send me, if you have tutorial for 11g (11.2.0.1) RAC & ASM (prefer to be...
  • Farrukh Kamal: Fahad, I’m interesting in pursuing remote employment with Pythian. What tips would you suggest...
  • pavankumar: for the error it need to set env correctly http://orcl11gdba.blogspot.in/ 2014/08/asmcmd-08102-no-con...
  • wissem: Post result off; crsctl check has crsctl check crs
  • ron: unable to connect to Cluster Synchronization Service
  • KevinB: Please post or attach the response file
  • nnarimanov: I’d like to clarify the reason why rman restores all datafiles into +DATA. It’s because of...
  • Sergio: Hi, Where is declared the function agent_clean() ? I dont see agent_clean() definition !! regards, Sergio
  • Sikandar: I do salute you brother, This is very………..nti mes helpful and correct documents. I...
  • Karthik Singh: Thanks for sharing a useful article. Looks like you have installed weblogic 10.3.4. Then shall I...
  • Joseph: Paul, Here target is from which database we are taking cloning. and auxilary is the cloning database. Wissem,...
  • sundar: In Doc ID 1472171.1 it says that the snapshot control file should be in a shared location for RAC databases....
  • Taoqir: Hello, This is a wonderfull and very helping material.now I am going to configure Data Guard. Thanks
  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!