ADB-S Prerequisites

The source Autonomous Databases on which AWR Hub is being enabled need to have manage permissions on the Object Storage bucket into which AWR snapshots will be uploaded. To grant these permissions, do the following:

1. Create a user-defined dynamic group for the Autonomous Databases in the compartment from the OCI console.

   ALL {resource.type = 'autonomousdatabase', resource.compartment.id = '<compartmentId>'}

   Replace the compartmentId placeholder with the appropriate value in the above policy

   This policy may require multiple lines if more than one compartment is involved (one line for each compartment).

2. Give permission to the dynamic group defined in step 1 to manage the Object Storage bucket.

   allow dynamic-group '<UserDefinedDynamicGroupName>' to manage object-family in compartment <Compartment name of the bucket> where ANY{target.bucket.name='<Bucket Name>'} 

   Replace the dynamic group name, compartment name and object storage bucket name placeholders with the appropriate values in the above policy.

ADB-D Prerequisites

Prior to registering an ADB-D database, you need to perform the following steps:

1. Create an Identity and Access Management (IAM) user and write policies to provide access to the AWR Hub Object Storage Bucket by the IAM user.

   Add the IAM user to an IAM group and give permission to the group to manage the Object Storage bucket by writing the following policy:

   allow group '<UserDefinedGroupName>' to manage object-family in compartment <Compartment name of the bucket> where ANY{target.bucket.name='<Bucket Name>'}

2. Generate API keys for the IAM user using the Add API Key option and download the private and public key for future reference.
   
   
   
   
   
   
   
   
3. Once the API key is added for the IAM user, click View Configuration File and copy and save the contents locally.
   
   
   
   
4. Run the following script to create an OCI User principal based credential in ADB-D (ATP-D/ ADW-D) that you want to register as an AWR source database.

   
   BEGIN                                                                                                                     
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'AWRHUB_KEY_NEW',                                                                                                                                                                               
       user_ocid       => '<user_ocid>',                                                                                                                           
       tenancy_ocid    => '<tenancy_ocid>',                                                                                                                         
       private_key     => '<content of the private key file (excluding -----BEGIN RSA PRIVATE KEY----- / -----END RSA PRIVATE KEY----->',
       fingerprint     => '<finger_print>') ;
    END ;/

Configure the Source Database

External Databases

For information on registering external databases with AWR Hub, refer to the MOS note: Set up AWR Hub source for pre-19c (Doc ID 2877452.1).

ADB-S and ADB-D

1. From the Operations Insights menu, select Administration and then Warehouse.
2. From the AWR Hub tab, click Register Database Information. The Register Database Information pop-up displays.
   
   
   
   

   Here, you'll find PL/SQL scripts used to register the source database with the AWR Hub.

3. Click Copy to copy the PL/SQL code blocks.

   For ADB-S, the following PL/SQL block is used to register the source database with AWR Hub:

   DECLARE
     -- Ask for database source name (max 10 characters).
     -- The value entered must be unique for each database registered to the same AWR Hub.
     db_src_name VARCHAR2(10) := '&SourceName';
   BEGIN
     -- Enable the resource principal in the database.
     -- This allows uploading AWR snapshots to OCI Object Storage using resource principal credentials.
     DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
     -- Register the database as an AWR source.
     dbms_awrhub.register_source(
       hub_name => 'HUB_CLOUD',
       source_name => db_src_name,
       hub_mailbox => 'http://the.awr.hub.mailbox.Url.returned.by.the.restUrl',
       mailbox_type => 'OBJECT_STORE',
       mailbox_cred => 'OCI$RESOURCE_PRINCIPAL');
     -- Setup a cache at the source side to upload AWR snapshots via data pump directory.
     dbms_awrhub.setup_source_cache_mailbox(
       hub_name => 'HUB_CLOUD',
       source_name => db_src_name,
       cache_mailbox => 'DATA_PUMP_DIR');
   END;
   /

   For ADB-D, the following PL/SQL block is used to register the source database with AWR Hub:

   DECLARE
     -- Ask for database source name (max 10 characters).
     -- The value entered must be unique for each database registered to the same AWR Hub.
     db_src_name VARCHAR2(10) := '&SourceName';
   BEGIN
     
     -- Register the database as an AWR source.
     dbms_awrhub.register_source(
       hub_name => 'HUB_CLOUD',
       source_name => db_src_name,
       hub_mailbox => 'http://the.awr.hub.mailbox.Url.returned.by.the.restUrl',
       mailbox_type => 'OBJECT_STORE',
       mailbox_cred => 'AWRHUB_KEY_NEW');
     -- Setup a cache at the source side to upload AWR snapshots via data pump directory.
     dbms_awrhub.setup_source_cache_mailbox(
       hub_name => 'HUB_CLOUD',
       source_name => db_src_name,
       cache_mailbox => 'DATA_PUMP_DIR');
   END;
   /

4. Execute the PL/SQL on each target database using any SQL editor, such as SQL Plus or SQL Developer.

   The source database name is user-provided and can be anything up to 10 characters in length. When you run the script, you will be prompted for the source database name. Running the script uploads the AWR snapshot from the source DB to the AWR Hub.

   It may take some time to run the blocks. Data transfer may take up to 24 hours.

Accessing AWR Explorer

To access AWR Explorer:

1. Open the navigation menu and click Observability & Management. Under Operations Insights, click AWR Hub. The AWR Hub page is displayed.
   Note
   
   All databases providing AWR snapshots are shown in the table. If you have not yet set up AWR Hub or configured source databases to send AWR snapshots to AWR Hub, see Configuring AWR Hub.
2. Click More (vertical ellipses) in the last column to display the pop-up menu and select Launch AWR Explorer.
   
   
   
   

   AWR Explorer is displayed showing performance charts for the chosen database.

   
   
   
   
   

The following fields are displayed in AWR Explorer. You can select the options in these fields to determine the data to be displayed:

• Database: The name used when registering the database with AWR Hub.
• Time Range: Duration of the snapshot range for which AWR data is displayed. By default, the duration of the latest AWR snapshot range is selected. However, if the snapshot range has more than six snapshots, then the default time range is the duration of the most recent six snapshots in the snapshot range. For example, if the duration of each snapshot (snapshot interval) is 10 minutes, then the time range is 6 * 10 minutes = 1 hour and the data within this time range is displayed.

  Click the Time Range field to view the Custom Time Range dialog and change the duration within the selected time range, if required.

• Time Zone: Time zone in which AWR data is displayed. By default, the UTC (Coordinated Universal Time) time zone is selected. Click this field to replace the default time zone with your local browser time zone.

AWR Explorer Tabs

AWR Explorer displays the following tabs, which provide information on the important aspects of database performance within the selected time range. These tabs display charts that greatly simplify the ­interpretation and comparison of AWR data.

• Load Profile: This tab displays historical system statistics from AWR tables such as DBA_HIST_SYSSTAT. The charts display the default SYSSTAT categories, such as Time Model Overview, Connections, and Logons, which provide an overview of the global health of the database.

  On the Load Profile tab, you can add charts to display other supported system statistics. To do so:

  1. Ensure that Custom is selected in the System Statistics drop-down list.
  2. Click Add Load Profile Chart.
  3. In the Add Custom Load Profile Chart dialog, enter a title for the new chart, select the system statistics that you want to display, and click Add Load Profile Chart.
• Wait Events: This tab displays the top ten wait events sorted by wait time from AWR tables such as DBA_HIST_SYSTEM_EVENT. The charts display the wait event and the corresponding Waits per Second and Average Wait Time information.

  On the Wait Events tab, you can also:

  1. Add new charts to display more wait events. To do so, select a wait event in the Wait Event drop-down list and click Add Wait Event Chart.
  2. View a histogram of the wait event with a breakdown of the percentage of waits by their duration, within a selected snapshot range. To do so, click the Show Histogram option available for each wait event chart. Note that you can also click on the chart to navigate to the Show Histogram dialog.
• Activity: This tab enables you to view the performance trend of the database by selecting an Average Active Sessions dimension and viewing the top activity for that dimension in the selected time range. You can also scroll down to view the top activity by additional dimensions based on the dimension selected in the Average Active Sessions chart. For example, if the default dimension, Wait Class is selected in the Average Active Sessions chart, then you can view the top SQL ID and Wait Event by Wait Class or select other dimensions in the menus on the top left of the tables.
• Database Parameters: This tab displays all the database parameters that were changed and you can click the link in the Changed column to view change history. The change history information includes details such as the ID of the snapshot when the database parameter was changed, the start and end time of the snapshot, and the changed value.

  On this tab, you can also view all the database parameters, including those that were not changed. To do so, deselect the Hide Unchanged Parameters check box.

AWR Chart Visualization Options

AWR Explorer provides the following data visualization options to enable you to filter data and make changes to how data is displayed in the charts.

• Show the data: Click to view raw data in the chart. You can use this option to copy and analyze raw data.
• Change to bar chart or Change to line chart Click to toggle between a bar chart and a line chart. Note that a change made to the visualization option in one chart applies to all the charts on the tab.
• Zoom In/Out: Click to alter the scope of what is displayed in the chart. Note that after clicking the Zoom in icon, you can use mouse and touch pad to scroll to the right or left of the chart, and zoom in and out. Note that a change made using the zoom options in one chart applies to all the charts on the tab.
• Restore: Click to undo any changes made to the charts using the other options and return to the default display.
• Save chart as: Click to save the chart in .png format.
• Remove chart: Click to remove the chart from the tab. Note that you can add the chart again by selecting it in the drop-down list on the tab and clicking Add <name of the tab> Chart.

Generate Reports

You can generate and download various reports from the database in AWR Explorer.

To do so, click Reports and select one of the following options:

• AWR: In the Generate AWR Report dialog, select the snapshot range and click Generate Report to generate and download an AWR report with additional performance data.
• SQL: In the Generate SQL Report dialog, select the snapshot range, enter a SQL ID, and click Generate Report to generate and download a SQL Health Check report with additional performance data for the specified SQL statement.
• ASH: In the Generate ASH Report dialog, specify the start and end time and click Generate Report to generate and download an ASH report with additional Active Session History data.