Generate AWR reports for Amazon RDS for Oracle read replicas

Oracle database administrators rely on tools such as Oracle’s Automatic Workload Repository (AWR) report to identify and resolve issues that are occurring in a database. Amazon Relational Database Service (Amazon RDS) for Oracle allows you to create read replicas to offload read-only workloads. Because the instances are read only, you can’t generate AWR reports to investigate issues such as slow response. As a result, DBAs are left in the dark as to how to respond to users on issues of slow response at certain periods of the day.

In this post, we demonstrate a way to generate AWR reports on a read replica and identify the user or SQL that is impacting performance on the read replica.

Solution overview

This solution is based on Oracle’s Remote Management Framework (RMF). The RMF topology provides us with a centralized architecture wherein we can add all the participating database nodes (RDS for Oracle primary and read replica in our case). The nodes in the topology are designated as destinations that store the data and sources from which data is collected. In this post, the RDS for Oracle primary will be the destination and the read replica will be the source. Connections between the destination and the source will be via a public database link owned by the SYS$UMF user.

The RMF topology is a centralized architecture that consists of all the participating database nodes along with their metadata and connection information. The RMF topology has one database node, called the destination, which is responsible for storing and managing performance data (AWR data) that is collected from the database nodes, called sources. A candidate destination is a source that can be configured in such way that it can replace the original destination when the original destination is unavailable or is downgraded. A topology can have only one destination, and one or more candidate destinations. The AWR reports will be run as a service on this topology allowing us to collect AWR reports from the source (read replica) on the destination (primary) database.

The architecture is as follows.

Within the AWS Cloud, we have an AWS Region. To isolate assets in the cloud, we deploy them in an Amazon virtual private cloud (VPC). To have high availability and the ability to offload read operations, we deploy the primary instance in one Availability Zone and the read replica in another Availability Zone in the same Region. The read replica functionality is based on Oracle Active Data Guard.

We then create an RMF topology consisting of the primary and read replicas.

To test this solution, we create an RDS for Oracle primary database and a read replica. We also configure the SYS$UMF user (refer to the Oracle documentation on the DBMS_UMF package), create database links between the primary and read replica database, and create the RMF configuration. With these resources, we generate some read load on the read replica and then generate an AWR report. The steps to create the environment are documented in the following sections.

Prerequisites

The following prerequisites are required to utilize this solution:

Access to the AWS Management Console.
Familiarity with Amazon RDS for Oracle.
Familiarity with Oracle databases and Automatic Workload Repository (AWR).
A bastion (or other) host with SQLDeveloper or any other similar GUI tool. If no GUI tool is available, the commands can be executed via a command line. The instructions to deploy these can be found at the following links:
The solution requires a public database link which could be a security issue.
This solution was tested with Amazon RDS for Oracle Enterprise Edition 19.0.
This solution utilizes AWR reports which are part of Oracle Diagnostics Pack; a licensed product

Create a primary database with a read replica

In this first step, we create an RDS for Oracle primary database with a read replica and confirm connectivity.

On the Amazon RDS console, create a new custom option group with Amazon Simple Storage Service (Amazon S3) integration.
Create a primary database via the Amazon RDS console, utilizing the newly created option group created.
After the primary database has been created, create a read replica in a different Availability Zone.
Confirm connectivity to both the primary and read replica. Connect to both databases via the bastion using either a GUI tool like SQLDeveloper or the command line. See Connecting to your RDS for Oracle DB instance.
Get the host names of both databases with the following SQL and note them down:
```
select host_name from v$instance;
```

We use this later to confirm connectivity over the database links.

Configure the SYS$UMF user

Now we configure the SYS$UMF user, which will be used by the database links later. The SYS$UMF user is the default database user that has all the privileges to access the system-level RMF views and tables. All the AWR related operations in RMF can be performed only by the SYS$UMF user. The SYS$UMF user is locked by default and it must be unlocked before deploying the RMF topology.

On the primary, check the status of the SYS$UMF user with the following SQL:

select   username
        ,common
        ,account_status
from     dba_users
where    username ='SYS$UMF';

Unlock the user (if required):

alter user sys$umf identified by sysumf account unlock;

The password used in this code is for demonstration purposes only. You should choose a secure password.

This user is typically locked. If it is open, skip the step to unlock the user.

Confirm that the user is unlocked with the following SQL:

select   username
        ,common
        ,account_status
from     dba_users
where    username ='SYS$UMF';

At this point, the SYS$UMF user should be open and unlocked, and you should have the password for the user.

Create database links

Next, we create a database link between the primary and read replica databases using the SYS$UMF user.

On the primary, create a database link to the read replica with the following SQL. Change the SYS$UMF user’s password, the database host, port, and SID to match your environment:

CREATE PUBLIC DATABASE LINK PRIMARY_TO_REPLICA
CONNECT TO sys$umf IDENTIFIED BY sysumf
USING
'(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST= dbreplica.my-aws-account.rds.amazonaws.com)
(PORT=1521))
(CONNECT_DATA=
(SID= DBPRIM)))';

Confirm that the database link has been created:
```
select * from dba_db_links;
```
Test connectivity to the read replica via the database link:
```
select host_name from v$instance@primary_to_replica;
```

Create a database link to the primary with the following SQL. Change the SYS$UMF user’s password, the database host, port, and SID to match your environment:

CREATE PUBLIC DATABASE LINK REPLICA_TO_PRIMARY
CONNECT TO sys$umf IDENTIFIED BY sysumf
USING
'(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST= dbprimary.my-aws-account.rds.amazonaws.com)
(PORT=1521))
(CONNECT_DATA=
(SID= DBPRIM)))';

On the read replica, confirm that the database link has been created:
```
select * from dba_db_links;
```
Test connectivity to the read replica via the database link:
```
select host_name from v$instance@replica_to_primary;
```

Create the RMF configuration

To create the RMF configuration, complete the following steps:

On the primary, configure the primary node with the following SQL:
```
exec dbms_umf.configure_node ('aws_prim');
```

On the read replica, configure the read replica node:

exec dbms_umf.configure_node('aws_replica','REPLICA_TO_PRIMARY');

On the primary, create the topology:

exec DBMS_UMF.create_topology ('AWS_AWR_01');

Check the topology:
```
select * from dba_umf_topology;
```

To register the read replica, run the following SQL on the primary:

exec DBMS_UMF.register_node
('AWS_AWR_01',
'aws_replica',
'PRIMARY_TO_REPLICA',
'REPLICA_TO_PRIMARY',
'FALSE',
'FALSE');

Confirm the status of the configuration:
```
select * from dba_umf_registration;
```

exec DBMS_WORKLOAD_REPOSITORY.register_remote_database
(node_name=>'aws_replica');

Confirm the configuration:
```
select * from dba_umf_link;
```

You should see an entry from the primary to the replica and another from the replica to the primary

Create a directory for AWR reports

Now we create a directory for the AWR reports. Because these databases are hosted on Amazon RDS for Oracle we don’t have access to the underlying file structure. Therefore, we use the AWS rdsadmin utility.

Create a directory to store AWS reports on the primary with the following SQL:
```
EXEC rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'AWR_REPORTS');
```
This is required because we don’t have access to the database server in the context of an RDS database instance.

Confirm that the directory was created on the primary:

select * from dba_directories where directory_name = 'AWR_REPORTS';

Configure the snapshot time on both servers

Next, we configure the snapshot time intervals on both the primary and the read replica.

To check the AWR snapshot duration, run the following SQL on the primary:
```
select * from dba_hist_wr_control;
```
The typical AWR snapshot interval is 1 hour.
For testing purposes, reduce the snapshot time to 15 minutes by executing the following SQL for each of the database ids in the preceding SQL:
```
execute dbms_workload_repository.modify_snapshot_settings(interval => 15,dbid => 2294834080);
```
The database id in the preceding SQL is for demonstration purposes. You should change the value to the database id of your database.
Confirm that the snapshots are being taken on both sides:
```
select * from dba_hist_snapshot order by end_interval_time desc;
```
You should see two entries (one for the primary and one for the read replica) created every 15 minutes.
In the event that you have a problem that is currently occurring, you can run the following command to take a manual snapshot:
```
exec DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT('aws_replica');
```
The name of the read replica (‘aws_replica’) in the preceding SQL is for demonstration purposes. You should change the value to the corresponding read replica that is of concern. This value can be ascertained with the following SQL:
```
select * from dba_umf_registration;
```

Generate a synthetic workload on the read replica

Because the read replica is open only for read-only traffic, we need to generate a synthetic workload based on read (for example, SELECT) traffic.

If you don’t have objects that can be used to generate a load on the database, you can use the following SQL to generate activity. The SQL is based on the standard dictionary objects that should be present in any Oracle database.

For ease of identification, run the load such that it falls within the interval of one AWR report.

Use the following sample SQL to generate load:

SELECT * 
FROM all_objects OB
	full outer join all_tables TB
		ON OB.object_name = TB.table_name
	full outer join all_tab_cols CO
		ON TB.table_name = CO.table_name
	full outer join all_indexes IX
		ON CO.table_name = IX.table_name
	full outer join all_ind_columns IC
		ON IX.table_name = IC.table_name
ORDER BY OB.object_type,
		OB.created,
		OB.last_ddl_time,
		OB.timestamp,
		OB.status,
		OB.TEMPORARY,
		OB.generated,
		OB.secondary,
		OB.namespace,
		OB.edition_name,
		OB.sharing,
		OB.editionable,
		OB.oracle_maintained,
		OB.application,
		OB.default_collation,
		OB.duplicated,
		OB.sharded,
		OB.created_appid,
		OB.created_vsnid,
		OB.modified_appid,
		OB.modified_vsnid,
		OB.owner,
		OB.object_name,
		OB.subobject_name DESC,
		OB.object_id,
		OB.data_object_id;

Generate the AWR report

Now that we have created and configured the environment and generated a synthetic workload, we generate the AWR report for the time period of the synthetic workload. In a real-world scenario, you should generate the report for the time period that users of the system have complained about slow response.

Log on to the read replica that you wish to run the AWR report on and execute the following command to confirm you are on the correct read replica. This is especially important if you have multiple read replicas.

SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME 
from v$database;

For Example:

NAME   OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
------ -------------------- ---------------- --------------
TESTDB READ ONLY WITH APPLY PHYSICAL STANDBY TESTDB_B

After you have confirmed that you are on the correct read replica, get the database id by executing the following SQL:

select  dbid, src_dbname 
from    dba_hist_wr_control 
where   SRC_DBNAME='aws_replica';

For Example:

 DBID          SRC_DBNAME
 ----------    ---------- 
 2294834080    aws_replica

Log on to the primary, populate the database id from the previous SQL and execute the following SQL:
```
select * from dba_hist_snapshot 
where dbid = 2294834080
order by  end_interval_time desc;
```
After you have identified the identifiers of the snapshots, run the following SQL on the primary to generate the AWR report. Again, because these databases are hosted on Amazon RDS for Oracle, we don’t have access to the underlying file structure, so we will use the AWS rdsadmin utility:
```
EXEC rdsadmin.rdsadmin_diagnostic_util.awr_report(73,74,'HTML', 'AWR_REPORTS', '2294834080');
```
“AWR_REPORTS” refers to the directory we created earlier to store the AWR reports. The start and stop AWR snapshot IDs as well as the database id (last parameter) are for demonstration purposes only. You should use the values that correspond to the period that you are interested in from your database.

On the primary, list the file that contains the AWR report:

SELECT * 
FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'AWR_REPORTS'))
order by  mtime;

Create an SQL file to run the following commands. In this post, the file is called gen_awr.sql and is referenced on the command line.
The contents of gen_awr.sql are as follows:

set heading off;
set verify off;
set feedback off; 
set echo off;
set term off;
set pagesize 0; 
set linesize 10000;
set trimout on;
set trimspool on; 
set timing off;
spool awrrpt_73_74.html;
SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('AWR_REPORTS', 'awrrpt_73_74.html'));
spool off;

Run the gen_awr.sql file from the SQL command line:
```
SQL> @gen_awr.sql
```
Transfer the output (named awrrpt_73_74.html in this example) file using a client and view it in a browser.
Confirm that you have generated the AWR report for the correct read replica by checking the information at the top of the AWR report. For example:
Review the AWR report to identify the SQL that was causing the poor performance reported by the users.

Conclusion

Read only replicas are a very effective method of offloading read intensive workloads from your primary database. However, like any database, read only databases also pose performance challenges. The read only nature of the database prevents a database administrator from directly generating AWR reports to analyze such problems. In this post, we showed you a solution that allows a database administrator to overcome this issue and respond to performance issues with a familiar tool.

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

About the Authors

Dean Capps is a Database Consultant at AWS and has experience providing database support on legacy, open systems, and cloud platforms. Dean is currently a member of the AWS Global Competency Centre, providing homogeneous and heterogenous database migration support to commercial and public sector clients.

Mohammad Waseem is a Database Consultant at AWS and works as a database migration specialist, helping and enabling customers to build highly available, cost-effective database solutions and migrate their database workloads to the AWS Cloud. Waseem works with AWS customers and partners to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.

Feng Cai is a Database Consultant at AWS. He has a wide background in providing database services to clients. Feng is currently a member of the AWS Global Competency Centre, providing homogeneous and heterogenous database migration support to customers.

Wajid Ali Mir is a Database Consultant at AWS. He has extensive database experience with customers ranging from banking, telecom, and cloud consultancy. Wajid is currently a member of the AWS Global Competency Centre, providing homogeneous and heterogenous database migration support to commercial clients.