Prerequisites

Required Policy for Cloud Bridge:

allow group <user_group> to manage opsi-warehouses in tenancy

Required Policies for Autonomous Data Warehouse:

allow group <user_group> to {OPSI_EM_WAREHOUSE_CREATE} in tenancy
allow group <user_group> to {OPSI_EM_WAREHOUSE_INSPECT} in tenancy
allow group <user_group> to {OPSI_EM_WAREHOUSE_READ} in tenancy
allow group <user_group> to {OPSI_EM_WAREHOUSE_DELETE} in tenancy
allow group <user_group> to {OPSI_EM_WAREHOUSE_UPDATE} in tenancy

Alternatively, the above policies are covered by the following:

allow group <user_group> to manage opsi-em-warehouse-family in tenancy

For more information on policies required by Operations Insights, see OCI Prerequisites: Set Up Groups, Users and Policies.

1. Navigate to your OCI environment, make a note of some specifics of your environment and create an OCI storage bucket:
   1. Make a note of your tenancy OCID, User OCID, Public Key Fingerprint, Private Key and Region. For more information, see Required Keys and OCIDs and Where to Get the Tenancy's OCID and User's OCID.
   2. Ensure that appropriate policies are set in order to have access to Object Storage. See Common Policies.
   3. Navigate to the base URL for the OCI Storage (this will vary based on your region, for example https://cloud.oracle.com/object-storage/buckets?region=us-phoenix-1) and create your own private bucket. Make a note of your private bucket name, as shown below. For more information, see Managing Buckets and Using the Console - Create a Bucket.
      
      
      
      
2. On the EM side, log in as a Super Administrator and create a Named Credentials for OCI (your identification to OCI)
   1. From the Setup menu, select Security and then Named Credentials. Click Create. The Create Credential page displays.
   2. Input your OCI details you noted above, Tenancy OCID, User OCID, Public Key Fingerprint and Private Key. Click Save.
      
      
      
      
3. Create Host Preferred Credentials for all hosts supporting the targets you want to export data from. The user provided in the preferred host credential should be the same as the user that was used to push the agent on the host. This user should have read, write and execute privileges on the content in the agent folder. Navigate to Setup and then Preferred Credentials to assign these credentials.
4. Next, create a Preferred Credential for the host(s) where the primary and any additional Oracle Management Services (OMS) are installed. In addition, ensure that the Database Plug-in has been pushed to all OMS hosts. The Database Plug-in is usually pushed by default with an agent, so verify that it has not been removed.
5. Best practice: Set up an exclusive Enterprise Manager Super Administrator user dedicated for exporting data to OCI. See Creating a New Administrator.
6. Verify that the OMS host(s) and hosts that have agents monitoring the database targets included in the data extraction have connectivity to OCI/Object Storage.
7. Create an Enterprise Manager group that includes all the targets for which you want data exported. See Create an Enterprise Manager group. Make a note of this group name.
8. Log in as the newly created data exporting Super Administrator user and create an Cloud Bridge by navigating to Setup menu and then selecting Cloud Bridge.
   1. Click on Manage OCI Connectivity.
   2. Enter the credential name you created above, the base URL for the Storage Bucket (usually in the format https://cloud.oracle.com/object-storage/buckets?region=<your region>), the bucket name and optionally a name for the bridge you are about to create. Then, click Create and OK.
      
      
      
      
   3. Click on Enable Data Export
   4. Under OCI Service, select Operations Insights: EM Warehouse
   5. Select the group you created in a prior step, that contains all the targets for which you want data exported.
   6. Select the Cloud Bridge and click Submit. Data from your Enterprise Manager instance should start uploading to the OCI bucket you defined.
      
      
      
      
9. In OCI, you can verify the data stored in your storage bucket. Navigate to OCI Storage, select Buckets and, in the compartment you created your bucket, click on the name of your bucket. The data in this bucket should look similar to the one shown below. Make a note of the name of your object store folder and emid.
   
   
   
   

For more information on exporting data to OCI, see Integrating Enterprise Manager with OCI Services.

1. Open the navigation menu, click Observability & Management. Under Operations Insights, click Overview. The Operations Insights console displays.
2. From the Operation Insights menu, click Administration and then Operations Insights Warehouse. The Operations Insights Warehouse page displays.
3. Enter a Warehouse Name and the number of OCPU cores to enable.
4. Click Create Warehouse. A work request will be submitted to create the Operations Insights Warehouse. To view the progress of the work request, click the Work Requests tab.

To create an EM Bridge:

1. Open the navigation menu and click Observability and Management. Under Operations Insights, click Administration. The Database Fleet option is selected by default in the Operation Insights navigation menu.
2. Click EM Bridges. The EM Bridge Administration page displays.
3. Click Create Bridge. The Create Enterprise Manager Bridge dialog displays.
4. Enter the following:
   • EM Bridge Name: A user-friendly name that lets you easily identify the source.
   • Compartment: The compartment where the EM bridge will be located.
   • Bridge Description: A meaningful description detailing specifics about the bridge.
   • Bucket Name: The name of the Object Storage bucket where Enterprise Manager target-level data is being uploaded. For more information about buckets, see Managing Buckets.
5. Click Create Bridge.

1. From the Operation Insights menu, click Administration, then Operations Insights Warehouse and then EM Warehouse. The EM Warehouse page displays.
2. Enter a name for your EM Warehouse.
3. Select the EM bridge through which data from Enterprise Manager will be imported into the EM Warehouse. If no EM bridge is available in the current compartment, you can either click the Change Compartment drop-down list to select another compartment and from there select an existing bridge. You can click Create a new bridge to define a new EM bridge in the current compartment. For more information about creating EM bridges, see Create an EM Bridge.
4. Click Create. A work request is submitted to create the new EM Warehouse. The creation process will take 5 to 10 minutes.
   
   
   
   

   Once the Work Request completes, and the EM Warehouse has been successfully created, the Lifecycle State will become Active.

Viewing ETL (Extract, Transfer, Load) Runs

The ETL Runs tab shows the data extraction jobs that have been run. The purpose of an ETL Run job is to move information that the EM bridge points to into an Autonomous Data Warehouse (ADW). An ETL Run will be processed every 24 hours if new data is present.

By monitoring ETL Runs, you can see whether data is being successfully uploaded to the EM Warehouse on a regular basis in addition to the amount of data that's regularly being transferred.

You need to create a warehouse database user who will have read-only access to the EM Warehouse.

1. From the Operation Insights menu, click Administration and then Operations Insights Warehouse. The Operations Insights Warehouse page displays.
2. Click the Warehouse Database Users tab.
3. Click Create User. The Create Warehouse Database User page displays.
4. Enter a User Name and Password.
   Note
   
   Password must be 12 to 30 characters and contain at least one uppercase letter, one lowercase letter, and one number. The password cannot contain the double quote (") character or the username entered above. It must be different than the last four passwords. You cannot reuse a password within 24 hours.
5. Select the Access type. Click EM Warehouse.
   
   
   
   
6. Click Create User.

You can set which EM Bridge is used to upload data to EM Warehouse.

1. Navigate to the EM Warehouse page. From the Operations Insights left navigation menu, click Administration, then Operations Insights Warehouse, and then EM Warehouse. The EM Warehouse page displays.
2. Click Configure. The Configure Enterprise Manager Data Warehouse dialog displays.
3. Click Change Compartment to switch to a compartment where the desired EM Bridge exists.
   If you want to create a new EM Bridge in the current compartment, click Create a new bridge to begin the process of defining a new EM Bridge.
4. Click Configure.

To delete an EM Warehouse, do the following:

1. Open the navigation menu, click Observability & Management. Under Operations Insights, click Overview. Operations Insights console displays.
2. From the Operation Insights menu, click Administration and then EM Warehouse. The EM Warehouse page displays.
3. Click Delete.

   The EM Warehouse deletion process will take 5-10 minutes.

See Set Up Jupyter Notebooks for more details on setting up and configuring Jupyter Notebooks.

The Jupyter Notebooks demonstrate the following:

• The utility of high density grid plots for visualizing database instance telemetry for a wide variety of metrics collected by Enterprise Manager. The notebook displays database metrics as well as a representation of the cumulative distribution of these metrics. This notebook illustrates the use of interactive controls to enable the user to select the databases and the metrics being visualized.
  
  
  
  
• A variety of chart types that are useful in understanding the distribution of database instance time series telemetry values collected by Enterprise Manager. This notebook illustrates the use of interactive controls to enable the user to select the databases and the metrics being visualized.
  
  
  
  
• Advanced statistical analysis, including forecasting, for database time series telemetry values collected by Enterprise Manager. Analytics from that are either available as either native Python libraries or as third-party add-ons are demonstrated. This notebook illustrates the use of interactive controls to enable the user to select the data being visualized.
  
  
  
  

The following SQL examples illustrate the various types of useful information that can be extracted from EM Warehouse. SQL can be run using your preferred DB interface tool. For example, Oracle SQL*Plus or Oracle SQL Developer.

SELECT * FROM db_init_params WHERE change = 'C' and LAST_COLLECTION_TIMESTAMP > sysdate-1;

Show Latest Changes to Database Initialization Parameters

The following SQL will display parameter value changes that have occurred to the db_init_params configuration metric for a given parameter over a specific period of time. The example shows the changes for open_cursors parameter.

SELECT
    a.extractor_timestamp AS previous_timestamp,
    b.extractor_timestamp AS change_timestamp,
    a.target_guid,
    a.target_name,
    a.name,
    a.value               AS old_value,
    b.value               AS new_value
FROM
    db_init_params a,
    (
        SELECT
            extractor_timestamp,
            target_guid,
            target_name,
            name,
            value,
            change
        FROM
            db_init_params
        WHERE
                change = 'C'
            AND name = 'open_cursors'
    )              b
WHERE
        a.extractor_timestamp = (
            SELECT
                MAX(c.extractor_timestamp)
            FROM
                db_init_params c
            WHERE
                    c.extractor_timestamp < b.extractor_timestamp
                AND c.target_guid = b.target_guid
                AND c.name = b.name
        )
    AND a.name = 'open_cursors'
    AND a.target_guid = b.target_guid
ORDER BY
    a.target_guid,
    change_timestamp ASC;

Output

Target Information Changes Over Time

The following SQL extracts the targetinfo column values that have been changed over a period of time. The following example gives the OLD_VALUE (previous value) and the NEW_VALUE (current value) for display_name column and the extractor timestamp when it was changed. This query can be modified to get the changes for other columns from targetinfo such as host_name, emd_url, etc. by replacing display_name with these column names as shown in the following SQL query.

select a.extractor_timestamp as previous_timestamp,
       b.extractor_timestamp as change_timestamp,
       a.target_guid,
       a.target_name,
       a.display_name as old_value,
       b.display_name as new_value
  from targetinfo a,
    (select extractor_timestamp, target_guid, target_name, display_name, change
     from targetinfo where change='C') b
   where
      a.extractor_timestamp =
         (select MAX(c.extractor_timestamp) from targetinfo c
           where c.extractor_timestamp < b.extractor_timestamp
           and c.target_guid = b.target_guid)
     and a.target_guid = b.target_guid
     order by a.target_guid, change_timestamp asc ;

Output

Property Changes Over Time

The following SQL extracts the property values that have been changed over a period of time for a given property name. It displays the OLD_VALUE (previous value) and NEW_VALUE (current value) and the extractor timestamps. The following example queries for a property called access_aud_table_max.

SELECT
    a.extractor_timestamp AS previous_timestamp,
    b.extractor_timestamp AS change_timestamp,
    a.target_guid,
    a.target_name,
    a.property_name,
    a.property_value      AS old_value,
    b.property_value      AS new_value
FROM
    properties a,
    (
        SELECT
            extractor_timestamp,
            target_guid,
            target_name,
            property_name,
            property_value,
            change
        FROM
            properties
        WHERE
                change = 'C'
            AND property_name = 'access_aud_table_max'
    )          b
WHERE
        a.extractor_timestamp = (
            SELECT
                MAX(c.extractor_timestamp)
            FROM
                properties c
            WHERE
                    c.extractor_timestamp < b.extractor_timestamp
                AND c.target_guid = b.target_guid
                AND c.property_name = b.property_name
        )
    AND a.target_guid = b.target_guid
    AND a.property_name = 'access_aud_table_max'
ORDER BY
    a.target_guid,
    change_timestamp ASC; 

Output

As shown in the following output, the value for access_aud_table_max changed from 25 to 28 on 1-Apr.

Session Usage Distribution for Database Targets

The following SQL query gives the distribution of session usage percentage and the number of targets in each bucket. The query can be modified to add more filters for the collection timestamps or target GUIDs.

SELECT
    '0-20' AS sessionUsage,
    COUNT(*) AS numberOfTargets
FROM
    gc_metric_values_hourly
WHERE
    metric_group_name = 'Database_Resource_Usage'
    AND metric_column_name = 'session_usage'
    AND avg_value <= 20
UNION
SELECT
    '21-40' AS sessionUsage,
    COUNT(*) AS numberOfTargets
FROM
    gc_metric_values_hourly
WHERE
    metric_group_name = 'Database_Resource_Usage'
    AND metric_column_name = 'session_usage'
    AND avg_value > 20
    AND avg_value <= 40
UNION
SELECT
    '41-60' AS sessionUsage,
    COUNT(*) AS numberOfTargets
FROM
    gc_metric_values_hourly
WHERE
    metric_group_name = 'Database_Resource_Usage'
    AND metric_column_name = 'session_usage'
    AND avg_value > 40
    AND avg_value <= 60
UNION
SELECT
    '61-80' AS sessionUsage,
    COUNT(*) AS numberOfTargets
FROM
    gc_metric_values_hourly
WHERE
    metric_group_name = 'Database_Resource_Usage'
    AND metric_column_name = 'session_usage'
    AND avg_value > 60
    AND avg_value <= 80
UNION
SELECT
    '81-100' AS sessionUsage,
    COUNT(*) AS numberOfTargets
FROM
    gc_metric_values_hourly
WHERE
    metric_group_name = 'Database_Resource_Usage'
    AND metric_column_name = 'session_usage'
    AND avg_value > 80
    AND avg_value <= 100;

Output

Oracle Analytics Cloud provides a variety of options for intelligent analysis of data stored in EM Warehouse without being overwhelming to deploy and manage, making it an effective way to access and capitalize on cloud analytics.

Working with data and creating connected experiences is simple and smart with Oracle Analytics Cloud:

• Automatic visualization for all your data, whether you load it self-service or through operational integrations, so you can quickly see its shape and quality.
• Data connectors are available for a wide array of sources, Oracle and non-Oracle.
• Intelligence is embedded in connectors for Oracle SaaS applications, getting you to meaningful information right away and accelerating your analysis.
• Powerful inline data preparation and visual data flows with the power of machine learning that allow you to see the steps to transform and enrich data, then automate them.
• A Software Developer Kit enables you to incorporate custom visualizations, and an extensible framework makes it easy to embed Oracle Analytics Cloud into other applications and user experiences so people can incorporate analysis into their daily work.

For comprehensive information about OAC, see Oracle Analytics Cloud.

Setting up OAC for use with the EM Warehouse ADW repository involves the following:

• Step 1: Create a Connection to ADW
• Step 2: Create a Dataset
• Step 3: Create a Workbook

A data set contains a set of tables you’re interested in for further analysis.

1. From the Analytics Home Page, click Create and then Dataset. The Create Dataset dialog displays.
2. Select the ADW connection you defined earlier. The New Dataset page displays.

   Under Schema in the tree list, you can drag and drop tables of interest to add to the dataset. When you add a table to the dataset, the table data will be displayed in two distinct regions: The lower regions displays the tabular view of the table. The upper region provides rollup information for the table in addition to graphic visualizations. For example, creating a dataset containing the GC_METRIC_VALUES_HOURLY table, would generate the following:

   
   
   
   
   
3. From the File drop-down menu, select Save. The Save Dataset As dialog displays.
4. Enter a Name and Description.
5. Click OK. The New Dataset page becomes the home page for the data set you just defined.