Create a centralized repository for Oracle AWR reports using Amazon EFS integration for Amazon RDS for Oracle

You can use the managed database offerings in the Amazon Relational Database Service (Amazon RDS) portfolio to remove much of the undifferentiated heavy lifting from day-to-day activities. With Amazon RDS for Oracle, you can significantly reduce the administrative overhead of managing and maintaining an Oracle database.

Oracle Automatic Workload Repository (AWR) provides a comprehensive view of the performance and resource usage of an Oracle database. Oracle AWR collects and maintains performance statistics and information about database resource usage over time, which can be used to diagnose and troubleshoot performance issues. You can control the AWR snapshots using the retention period. The retention period is the length of time that AWR data is kept in the SYSAUX tablespace. After this period, the data is deleted automatically. If the retention period is too high, more AWR statistics will be retained and the SYSAUX tablespace may grow faster than expected. Truncating objects in the SYSAUX tablespace could potentially cause severe database problems, such as data corruption, loss of important metadata, and instability of the entire database. Therefore, Amazon RDS for Oracle is a managed service that doesn’t allow users to truncate objects in the SYSAUX tablespace to prevent such problems from occurring.

To resolve these issues, having a centralized repository for AWR reports can be helpful to maintain historical performance data. This post demonstrates how to create a centralized repository for AWR reports when you would like to use a high retention period. For this solution, we use Amazon Elastic Compute Cloud (Amazon EC2), Amazon Elastic File System (Amazon EFS), and its integration with RDS for Oracle instances.

Solution overview

The Oracle AWR Warehouse is a central repository configured for long-term AWR data retention. It stores AWR snapshots from multiple database sources. Increasing AWR retention in the production systems would typically increase the overhead and cost of mission-critical databases. Therefore, offloading the AWR snapshots to a central repository is a better idea. Unlike the AWR default retention period of 8 days, the AWR Warehouse default retention period is forever. However, you can configure it for weeks, months, or years.

The following diagram illustrates our solution architecture.

A centralized repository for AWR reports offers the following benefits:

You can store performance data over time, providing a historical perspective on database performance. You can use this data to identify performance trends and issues, and perform root cause analysis on performance problems.
By analyzing the data stored in the AWR repository, you can identify resource-intensive SQL statements and database objects, as well as monitor resource utilization over time. This can help you plan for future resource needs and make informed decisions about database capacity.
You can perform trend analysis on database performance over time. By comparing performance data from different time periods, you can identify performance trends and take proactive steps to address potential performance problems before they become critical.
An AWR centralized repository provides a centralized location for storing performance data, which you can use to generate reports on database performance. You can use these reports to communicate performance information to management, stakeholders, and other teams.
Managing and administrating of the SYSAUX tablespace is straightforward.

To monitor the growth in the SYSAUX tablespace, you can query the V$SYSAUX_OCCUPANTS view to see which components are using the most space:

SELECT occupant_name, space_usage_kbytes FROM v$sysaux_occupants ORDER BY 2 DESC

Prerequisites

Before getting started, make sure you have the following prerequisites for this solution:

You need an AWS account with AWS Identity and Access Management (IAM) permissions to create and manage keys, Amazon RDS, Amazon EC2, and VPC-related resources.
You need an Oracle database on an EC2 instance.
AWR reports are included with Oracle Database Enterprise Edition. This functionality requires Enterprise Edition and the Diagnostics and Tuning option. You should ensure you have the correct licensing before using this functionality.
Because Amazon RDS is a managed service, Amazon RDS for Oracle doesn’t support DBMS_SWRF_INTERNAL.AWR_LOAD as of this writing. Therefore, for this demo, we create a centralized repository on Oracle hosted on Amazon EC2.
The database user should have privileges for exporting and importing the AWR reports.

In the following sections, we provide a step-by-step guide for setting up a centralized repository for AWR reports.

Create an EFS file system in your AWS account

To create an EFS file system, open the Amazon EFS console and choose Create file system. Choose the appropriate settings such as the performance mode and throughput capacity. This is the location where the AWR reports will be stored.

Mount the EFS file system on Amazon EC2

Install the necessary Oracle software on your EC2 instances, including creating the database and the EFS mount helper. The EFS mount helper is a tool provided by AWS that makes it easy to mount an EFS file system on an EC2 instance. See the following code:

$ mkdir /mnt/efs/fs2

$ sudo mount -t efs -o tls fs-0590179460895f626:/ /mnt/efs/fs2

Create an Oracle directory on Amazon RDS for Oracle

To transfer files between an RDS for Oracle instance and an EFS file system, you must create an Oracle directory on Amazon RDS for Oracle:

BEGIN
rdsadmin.rdsadmin_util.create_directory_efs(
p_directory_name => 'DATA_PUMP_DIR_RDS',
p_path_on_efs => '/rdsefs-fs-0590179460895f626/awr_exports');
END;
/

For more information, refer to Integrate Amazon RDS for Oracle with Amazon EFS.

Extract AWR data

To extract AWR data into a dump file manually, use the rdsadmin.rdsadmin_diagnostic_util.awr_extract procedure. The following example code extracts the snapshot range 84344–84345 manually:

SQL> EXEC rdsadmin.rdsadmin_diagnostic_util.awr_extract(84344,84345,'DATA_PUMP_DIR_RDS');

The output dump file is generated with file nameawrextract_84344_84345.dmpin the non-default database directory named DATA_PUMP_DIR_RDS. Verify the extract fileawrextract_84344_84345.dmpwas created on the shared EFS file system.

Alternatively, to extract the most recent snapshot dynamically, create a scheduler using the DBMS_SCHEDULER package:

CREATE OR REPLACE PROCEDURE generate_awr_report AS
job_id        NUMBER;
start_snap_id NUMBER;
end_snap_id   NUMBER;
BEGIN
-- Find the latest two snapshot IDs
SELECT MIN(snap_id),MAX(snap_id) INTO start_snap_id, end_snap_id FROM
(
SELECT snap_id FROM dba_hist_snapshot
WHERE end_interval_time > sysdate - 1
ORDER BY end_interval_time DESC
)
WHERE ROWNUM < 3;

rdsadmin.rdsadmin_diagnostic_util.awr_extract(start_snap_id, end_snap_id, 'DATA_PUMP_DIR_RDS');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"ADMIN"."AWR_EXTRACT_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN ADMIN.GENERATE_AWR_REPORT; END;',
number_of_arguments => 0,
start_date => TO_TIMESTAMP_TZ('2023-09-05 18:05:00.000000000 AMERICA/NEW_YORK','YYYY-MM-DD HH24:MI:SS.FF TZR'),
repeat_interval => 'FREQ=HOURLY;BYTIME=0500;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN',
end_date => NULL,
enabled => FALSE,
auto_drop => TRUE,
comments => '');

DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"ADMIN"."AWR_EXTRACT_JOB"',
attribute => 'store_output', value => TRUE);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"ADMIN"."AWR_EXTRACT_JOB"',
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

DBMS_SCHEDULER.enable(name => '"ADMIN"."AWR_EXTRACT_JOB"');
END;
/

Create an Oracle directory on Oracle hosted on Amazon EC2

Create an Oracle directory called DATA_PUMP_DIR_EC2 pointing to the EFS file system mounted on the EC2 instance:

SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR_EC2 AS '/mnt/efs/fs2/awr_exports';

Import the AWR report into a centralized repository

Import the AWR report into a centralized database using the awrload.sql procedure:

#!/bin/bash

directory_name='DATA_PUMP_DIR_EC2';
dumpfile='awrextract_84344_84345';
awr_usr='TEST';
awr_tbs='USERS';
awr_tmp='TEMP';

awr_load=`sqlplus -s /nolog <<EOL
connect / as sysdba
spool '$logfile'
define directory_name='$directory_name';
define file_name='$dumpfile';
define schema_name='$awr_usr';
define default_tablespace='$awr_tbs';
define temporary_tablespace='$awr_tmp';
@?/rdbms/admin/awrload.sql
spool off
exit;
EOL`

You can also do it manually using the following procedure:

SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                  ~
~     (2) name of dump file                      ~
~     (3) staging schema name to load AWR data into          ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name               Directory Path
------------------------- ---------------------------------
DATA_PUMP_DIR               /u01/app/oracle/admin/ORA19C/dpdump/
DATA_PUMP_DIR_EC2           /mnt/efs/fs2/awr_exports

Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR_EC2

Using the dump directory: DATA_PUMP_DIR_EC2

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

Enter value for file_name: awrextract_84344_84345

Loading from the file name: awrextract_84344_84345.dmp

Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.

The default staging schema name is AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for schema_name: AWR_STAGE

Using the staging schema name: AWR_STAGE

Choose the Default tablespace for the TEST user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the TEST users's default tablespace.  This is the
tablespace in which the AWR data will be staged.

TABLESPACE_NAME          CONTENTS          DEFAULT TABLESPACE
-------------------- ----------------- -----------
AWS_REPOSITORY           PERMANENT SYSAUX                   PERMANENT         *
TEST_DATA               PERMANENT
USERS                   PERMANENT

Pressing <return> will result in the recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: AWS_REPOSITORY

Using tablespace AWS_REPOSITORY as the default tablespace for the AWR_STAGE

Choose the Temporary tablespace for the TEST user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the TEST user's temporary tablespace.

TABLESPACE_NAME   CONTENTS      DEFAULT TEMP TABLESPACE
----------------- ------------- ------------------------
TEMP              TEMPORARY        *

Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as the temporary tablespace for AWR_STAGE

... Creating AWR_STAGE user

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /mnt/efs/fs2/awr_exports
|   awrextract_84344_84345.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Load Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR load operation can be
|  monitored in the following directory/file:
|   /mnt/efs/fs2/awr_exports
|   awrextract_84344_84345.log
|
... Dropping AWR_STAGE user

End of AWR Load

Now you should see database ID 659205682 of the database snapshot that was imported. You can query to retrieve the snapshot IDs and their corresponding start and end times for all database IDs that currently exist in the database:

SQL> SELECT DBID, max(SNAP_ID), max(BEGIN_INTERVAL_TIME), min(SNAP_ID), min(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot GROUP BY dbid;

Generate an AWR report from the centralized repository hosted on Amazon EC2

We use the awrrpti.sql script to generate a specific instance-level AWR report for a specified time range. Run the script on the centralized Oracle instance using SQL*Plus, providing the necessary parameters such as the database ID, begin and end snapshots, and output format:

SQL> @?/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
------------ ---------- ---------    ----------  ------
659205682     1         ORA18C       ORA18C      ip-172-17-4-1
* 1148650533  1         ORA19C       ORA19C      ip-10-0-5-22

Enter value for dbid: 659205682
Using 659205682 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
Instance   DB Name   Snap Id    Snap Started     Snap Level
---------- --------- -------- ----------------- --------

ORA18C     ORA18C       83094   21 Aug 2023 19:15  2
84344   06 Sep 2023 05:45   2
84345   06 Sep 2023 06:00   2

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 84344
Begin Snapshot Id specified: 84344

Enter value for end_snap: 84345
End Snapshot Id specified: 84345

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_84344_84345.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: awrrpt_1_84344_84345.html

We have successfully generated the AWR reportawrrpt_1_84344_84345.htmlfrom the centralized database.

Clean up

Delete the resources created along this post should you no longer need them.

Delete the EFS file system
Terminate the EC2 instance

Conclusion

In this post, we showed how to create a centralized repository for Oracle AWR reports. By implementing centralized AWR reports, administrators can improve performance, reduce the time and effort required to access performance data, and ensure that performance issues are addressed before they become critical.

If you have any questions or suggestions about this post, leave a comment.

About the authors

Sudhakar Darse is a Database Specialist Solutions Architect at AWS. He works with AWS customers to provide guidance and technical assistance on database services, helping them with database migrations to the AWS Cloud and improving the value of their solutions when using AWS.

Sarabjeet Singh is a Database Specialist Solutions Architect at Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.

Robert Daly is a Senior Database Specialist Solutions architect at AWS, focusing on Amazon RDS, Amazon Aurora, and AWS DMS. He has helped multiple enterprise customers move their databases to AWS, providing assistance on performance and best practices. Robert enjoys helping customers build technical solutions to solve business problems.

AWS Database Blog