Running an AWR Compare Report Across Databases

Extract the AWR Data from Production Database

There are time when I want to run an AWR compare report from my production activity and my test database load test activity. We can do this by extracting the AWR data from the production database and loading it into the test database and then run a compare report between the 2 sets of snap data.

Oracle provides a utility to extract the AWR data from a database. In $ORACLE_HOME/rdbms the script awrextr.sql is used for this purpose for an interactive extract.

ORACLE_HOME/rdbms/admin/awrextr.sql

** Note: This will extract the AWR data for the snap periods that are selected. This processes uses the data pump utility to extract the AWR data to a data pump dump file. Therefore you must have a defined directory to write the dump file.


Move the AWR Datapump Dump File.

Move the Data Pump dump file created by the awrextr.sql script to the test server so it can be loaded.


Load the AWR Data into the Test Database

Oracle makes the import of AWR date as simple as the extraction of AWR data.

Oracle provides a script awrload.sql in the ORACLE_HOME/rdbms/admin directory.

** Note: Before attempting to load the AWR data from the data pump dump file ensure that an Oracle directory exists for the location of the data pump dump file the needs loaded. Also When loading AWR data into another database make sure the tablespace SYSAUX, the stage tablespace selected, example will use SYSAUX, and the TEMP tablespace have enough room to complete the operation. May want to have another session open to the database to monitor freespace.

ORACLE_HOME/rdbms/admin/awrload.sql


Run the Compare Report Between the 2 databases AWR Data

Now that we have AWR data from multiple databases we can compare across snap time periods from one database, run normal AWR reports over snap time periods, or even compare snap time periods across the different databases. Oracle provides an easy way to execute the compares across the script awrddrpi.sql in the ORACLE_HOME/rdbms/admin directory.

ORACLE_HOME/rdbms/admin/awrddrpi.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
------------ -------- ------------ ------------ ------------
3742289852 1 AWR2 awr2 ORADBPROD01
* 744414708 1 ORCL11G orcl11g MRMESSIN

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for dbid: 3742289852
Using 3742289852 for Database Id for the first pair of snapshots
Enter value for inst_num: 1
Using 1 for Instance Number 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: 3

Listing the last 3 days of Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
awr2 AWR2 1 20 Feb 2008 17:42 1
2 20 Feb 2008 19:00 1
3 20 Feb 2008 20:00 1
4 20 Feb 2008 21:00 1
5 20 Feb 2008 22:00 1
6 20 Feb 2008 23:00 1
7 21 Feb 2008 00:00 1
8 21 Feb 2008 01:00 1
9 21 Feb 2008 02:00 1
10 21 Feb 2008 03:00 1
………


Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
First Begin Snapshot Id specified: 1

Enter value for end_snap: 9
First End Snapshot Id specified: 9


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
3742289852 1 AWR2 awr2 ORADBPROD01
* 744414708 1 ORCL11G orcl11g MRMESSIN


Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for dbid2: 744414708
Using 744414708 for Database Id for the second pair of snapshots
Enter value for inst_num2: 1
Using 1 for Instance Number 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: 3

Listing the last 3 days of Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl11g ORCL11G 355 26 Feb 2008 07:34 1
356 26 Feb 2008 09:00 1
357 26 Feb 2008 10:00 1
358 26 Feb 2008 11:00 1
……………
405 02 Mar 2008 00:00 1
406 02 Mar 2008 01:00 1
407 02 Mar 2008 02:00 1
408 02 Mar 2008 03:00 1
409 02 Mar 2008 04:00 1
410 02 Mar 2008 05:00 1
411 02 Mar 2008 06:00 1
412 02 Mar 2008 07:00 1
413 02 Mar 2008 08:00 1
………


Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 405
Second Begin Snapshot Id specified: 405

Enter value for end_snap2: 413
Second End Snapshot Id specified: 413


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_1_1_405.txt To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: awrdiff_awr2_orcl11g.txt