How to create Oracle AWR report for a multiple instance (RAC) Oracle database
To create the AWR report for a multiple instance Oracle RAC database (use awrrpti.sql) as:
1. Find the script
2. Login as sysdba
3. Execute the script. While executing supply the following:
a. format of the report (html/text)
b. enter db id
c. enter instance id
d. for number of days (don’t enter anything if you want to generate for specific snapshots). press enter
e. enter starting snapshot id from the displayed list
f. enter ending snapshot id from the displayed list
g. give a name for the report including file extension (txt/html/htm)
4. Once report is generated you exit sql*plus and view the report in the server or
5. transfer the report to your local machine and view it.
Here are the steps:
[oracle@DEVSERV ~]$ ls -l /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrr*
-rw-r–r– 1 oracle oinstall 7575 Apr 18 2005 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
-rw-r–r– 1 oracle oinstall 1999 Oct 24 2003 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpt.sql
[oracle@DEVSERV ~]$
[oracle@DEVSERV ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Apr 20 15:39:08 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set pages 999 line 300
SQL>
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
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
———— ——– ———— ———— ————
* 2330100236 1 DEVDB DEVDB DEVSERV
Enter value for dbid: 2330100236
Using 2330100236 for database Id
Enter value for inst_num: 1
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:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
DEVDB DEVDB 24128 07 Apr 2011 00:00 1
24129 07 Apr 2011 01:00 1
24130 07 Apr 2011 02:00 1
24131 07 Apr 2011 03:00 1
24132 07 Apr 2011 04:00 1
24133 07 Apr 2011 05:00 1
24134 07 Apr 2011 06:00 1
24135 07 Apr 2011 07:00 1
24136 07 Apr 2011 08:00 1
24137 07 Apr 2011 09:00 1
24138 07 Apr 2011 10:00 1
24139 07 Apr 2011 11:00 1
24140 07 Apr 2011 12:00 1
24141 07 Apr 2011 13:00 1
24142 07 Apr 2011 14:00 1
24143 07 Apr 2011 15:00 1
24144 07 Apr 2011 16:00 1
24145 08 Apr 2011 18:00 1
24146 08 Nov 2011 18:00 1
24147 08 Feb 2012 13:56 1
24148 12 Oct 2013 18:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 24147
Begin Snapshot Id specified: 24147
Enter value for end_snap: 24148
End Snapshot Id specified: 24148
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_24147_24148.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awr_report_24147_24148.txt
Using the report name awr_report_24147_24148.txt
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
———— ———– ———— ——– ————— ———– —
DEVDB 2330100236 DEVDB 1 11-Mar-11 12:42 11.1.0.7.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
—————- ——————————– —- —– ——- ———-
DEVSERV Linux x86 64-bit 8 8 4 31.29
Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 24147 08-Feb-12 13:56:12 287 3.1
End Snap: 24148 12-Oct-13 18:00:50 123 .9
Elapsed: 881,524.64 (mins)
DB Time: 5,752,234.99 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ———- ———-
Buffer Cache: 1,536M 1,536M Std Block Size: 8K
Shared Pool Size: 2,560M 2,560M Log Buffer: 157,036K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ————— ————— ———- ———-
DB Time(s): 6.5 2,543.7 123.07 345.85
DB CPU(s): 0.0 0.1 0.00 0.01
Redo size: 34.4 13,407.1
Logical reads: 4.8 1,877.2
Block changes: 0.2 63.0
Physical reads: 0.0 0.2
Physical writes: 0.0 1.9
User calls: 0.0 7.4
Parses: 0.0 15.3
Hard parses: 0.0 0.1
W/A MB processed: 15,009.8 5,851,091.6
Logons: 0.0 0.4
Executes: 0.1 20.7
Rollbacks: 0.0 0.0
Transactions: 0.0
————————————————————-
————————————————————-
————————————————————-
————————————————————-
End of Report
Report written to awr_report_24147_24148.txt
SQL>
To create the AWR report for a multiple instance Oracle RAC database (use awrrpti.sql) as:
1. Find the script
2. Login as sysdba
3. Execute the script. While executing supply the following:
a. format of the report (html/text)
b. enter db id
c. enter instance id
d. for number of days (don’t enter anything if you want to generate for specific snapshots). press enter
e. enter starting snapshot id from the displayed list
f. enter ending snapshot id from the displayed list
g. give a name for the report including file extension (txt/html/htm)
4. Once report is generated you exit sql*plus and view the report in the server or
5. transfer the report to your local machine and view it.
Here are the steps:
[oracle@DEVSERV ~]$ ls -l /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrr*
-rw-r–r– 1 oracle oinstall 7575 Apr 18 2005 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
-rw-r–r– 1 oracle oinstall 1999 Oct 24 2003 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpt.sql
[oracle@DEVSERV ~]$
[oracle@DEVSERV ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Apr 20 15:39:08 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set pages 999 line 300
SQL>
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
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
———— ——– ———— ———— ————
* 2330100236 1 DEVDB DEVDB DEVSERV
Enter value for dbid: 2330100236
Using 2330100236 for database Id
Enter value for inst_num: 1
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:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
DEVDB DEVDB 24128 07 Apr 2011 00:00 1
24129 07 Apr 2011 01:00 1
24130 07 Apr 2011 02:00 1
24131 07 Apr 2011 03:00 1
24132 07 Apr 2011 04:00 1
24133 07 Apr 2011 05:00 1
24134 07 Apr 2011 06:00 1
24135 07 Apr 2011 07:00 1
24136 07 Apr 2011 08:00 1
24137 07 Apr 2011 09:00 1
24138 07 Apr 2011 10:00 1
24139 07 Apr 2011 11:00 1
24140 07 Apr 2011 12:00 1
24141 07 Apr 2011 13:00 1
24142 07 Apr 2011 14:00 1
24143 07 Apr 2011 15:00 1
24144 07 Apr 2011 16:00 1
24145 08 Apr 2011 18:00 1
24146 08 Nov 2011 18:00 1
24147 08 Feb 2012 13:56 1
24148 12 Oct 2013 18:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 24147
Begin Snapshot Id specified: 24147
Enter value for end_snap: 24148
End Snapshot Id specified: 24148
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_24147_24148.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awr_report_24147_24148.txt
Using the report name awr_report_24147_24148.txt
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
———— ———– ———— ——– ————— ———– —
DEVDB 2330100236 DEVDB 1 11-Mar-11 12:42 11.1.0.7.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
—————- ——————————– —- —– ——- ———-
DEVSERV Linux x86 64-bit 8 8 4 31.29
Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 24147 08-Feb-12 13:56:12 287 3.1
End Snap: 24148 12-Oct-13 18:00:50 123 .9
Elapsed: 881,524.64 (mins)
DB Time: 5,752,234.99 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ———- ———-
Buffer Cache: 1,536M 1,536M Std Block Size: 8K
Shared Pool Size: 2,560M 2,560M Log Buffer: 157,036K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ————— ————— ———- ———-
DB Time(s): 6.5 2,543.7 123.07 345.85
DB CPU(s): 0.0 0.1 0.00 0.01
Redo size: 34.4 13,407.1
Logical reads: 4.8 1,877.2
Block changes: 0.2 63.0
Physical reads: 0.0 0.2
Physical writes: 0.0 1.9
User calls: 0.0 7.4
Parses: 0.0 15.3
Hard parses: 0.0 0.1
W/A MB processed: 15,009.8 5,851,091.6
Logons: 0.0 0.4
Executes: 0.1 20.7
Rollbacks: 0.0 0.0
Transactions: 0.0
————————————————————-
————————————————————-
————————————————————-
————————————————————-
End of Report
Report written to awr_report_24147_24148.txt
SQL>
No comments:
Post a Comment