Bring Your Own Warehouse

Use Bring Your Own Warehouse to retain AWR snapshots from selected databases for up to 25 months.

Bring Your Own Warehouse is optional and does not replace AWR Snapshot Collection for recent snapshots.

To set up a warehouse:

Prepare the required resources.
Create an Autonomous AI Lakehouse outside Ops Insights and configure its network access according to your security requirements. If the Autonomous AI Lakehouse is configured for private endpoint connections, select Enable DNS proxy on the Ops Insights private endpoint used for the warehouse. A publicly accessible Autonomous AI Lakehouse does not require a private endpoint. For information, see Create a Private Endpoint.

Choose an Object Storage bucket and compartment for temporarily staging AWR snapshots. Ensure that the bucket is reachable through a service gateway, and determine the prefix to use.
Register the warehouse.
In Ops Insights, start Bring Your Own Warehouse setup. Select the Autonomous AI Lakehouse and its compartment, then select the Object Storage bucket and compartment and enter the prefix.

The warehouse is created in the Lakehouse compartment. If setup is incomplete or interrupted, continue from the warehouse details page. For information, see Access and Manage Warehouses.
Configure access requirements.
Use the shared dynamic group BYO_AWR_Warehouse_Group. Console policy automation adds each warehouse OCID to the group’s matching rule.

For information on the required statements in BYO_AWR_Warehouse_Policies, see Create Policies Using the Console.
Validate and apply policies.
Review the three statements in BYO_AWR_Warehouse_Policies. These statements provide warehouse access to the selected Autonomous AI Lakehouse, warehouse access to the selected Object Storage bucket, and Lakehouse access to that bucket.

If BYO_AWR_Warehouse_Group or any required policy is missing, click Add policies in the console. The console creates the shared group if needed and adds the warehouse OCID to its matching rule.

Run the Lakehouse setup script.

Run the complete console-generated setup script in the selected Autonomous AI Lakehouse. Administrator privileges are required to run this script. The script enables OCI IAM external authentication, prepares the database user, and grants AWR and ADDM access.

Replace <WAREHOUSE_OCID> in l_iam_group_name with the warehouse OCID. The script prefixes this value with IAM_PRINCIPAL_OCID= and creates the user with global authentication. The default database username is opsi_awr_user.

DECLARE
l_count INTEGER := 0;
l_username_raw VARCHAR2(100) := 'opsi_awr_user';
l_username VARCHAR2(128);
l_iam_group_name VARCHAR2(128) := '<WAREHOUSE_OCID>';
l_stop_at_first_error BOOLEAN := TRUE;
l_tablespace_name VARCHAR2(128);

PROCEDURE run_sql(p_sql IN VARCHAR2, p_ignore_error IN BOOLEAN := FALSE)
IS
BEGIN
EXECUTE IMMEDIATE p_sql;
DBMS_OUTPUT.PUT_LINE('SUCCESS: ' || p_sql);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FAIL: ' || p_sql || CHR(10) || SQLERRM);
IF NOT p_ignore_error AND l_stop_at_first_error THEN
RAISE;
END IF;
END run_sql;
BEGIN
l_username := DBMS_ASSERT.SIMPLE_SQL_NAME(l_username_raw);

SELECT COUNT(1)
INTO l_count
FROM all_users
WHERE username = UPPER(l_username);

DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION( type => 'OCI_IAM', force => TRUE );
l_iam_group_name := 'IAM_PRINCIPAL_OCID=' || l_iam_group_name;
IF l_count = 0 THEN
EXECUTE IMMEDIATE 'create user '
|| l_username
|| ' IDENTIFIED GLOBALLY AS '
|| ''''
|| l_iam_group_name
|| '''';
END IF;

run_sql('GRANT CONNECT TO ' || l_username );
run_sql('GRANT CREATE SESSION, ALTER SESSION TO ' || l_username );
run_sql('GRANT CREATE TABLE TO ' || l_username );
run_sql('GRANT CREATE VIEW TO ' || l_username );
run_sql('GRANT CREATE PROCEDURE TO ' || l_username );
run_sql('GRANT CREATE SEQUENCE TO ' || l_username );
run_sql('GRANT CREATE TYPE TO ' || l_username );
run_sql('GRANT CREATE JOB TO '|| l_username);
run_sql('GRANT SELECT ANY DICTIONARY TO ' || l_username );
run_sql('GRANT SELECT_CATALOG_ROLE TO ' || l_username );
run_sql('GRANT SELECT ON SYS.AWR_PDB_SNAPSHOT TO ' || l_username );
run_sql('GRANT SELECT ON SYS.DBA_ADDM_TASKS TO ' || l_username );
run_sql('GRANT EXECUTE ON DBMS_CLOUD TO ' || l_username );
run_sql('GRANT EXECUTE ON DBMS_CLOUD_ADMIN TO ' || l_username );
run_sql('GRANT EXECUTE ON DBMS_WORKLOAD_REPOSITORY TO ' || l_username );
run_sql('GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO ' || l_username );
run_sql('GRANT EXECUTE ON DBMS_ADDM TO ' || l_username );
run_sql('GRANT ADVISOR TO ' || l_username );
run_sql('GRANT EXECUTE ON dbms_advisor TO ' || l_username );
run_sql('GRANT EXECUTE ON SYS.DBMS_LOCK to ' || l_username );
run_sql('GRANT EXECUTE ON SYS.DBMS_UTILITY to ' || l_username );
run_sql('GRANT EXECUTE ON SYS.DBMS_ASSERT to ' || l_username );
run_sql('GRANT EXECUTE ON SYS.DBMS_OUTPUT to ' || l_username );
run_sql('GRANT EXECUTE ON SYS.DBMS_SCHEDULER to ' || l_username );
run_sql('GRANT EXECUTE ON SYS.DBMS_STATS to ' || l_username );
run_sql('GRANT EXECUTE ON SYS.DBMS_APPLICATION_INFO to ' || l_username );
run_sql('GRANT EXECUTE ON utl_file TO ' || l_username );
-- Additional grants from Awr Explorer APIs
run_sql('GRANT SELECT ON V$DATABASE to '|| l_username);
-- grants needed for dbaas_api_dbperf to work.
run_sql('GRANT EXECUTE ON DBMS_SQL TO ' || l_username);
run_sql('grant select on v$parameter to ' || l_username);
run_sql('grant select on v$instance to ' || l_username);
run_sql('grant select on v$sqlcommand to ' || l_username);
run_sql('grant select on awr_pdb_parameter to ' || l_username);
run_sql('grant select on awr_pdb_snapshot to ' || l_username);
run_sql('grant select on awr_pdb_service_name to ' || l_username);
run_sql('grant select on awr_pdb_database_instance to ' || l_username);
run_sql('grant select on awr_pdb_active_sess_history to ' || l_username);
run_sql('grant select on awr_pdb_rsrc_consumer_group to ' || l_username);
run_sql('grant select on awr_pdb_osstat to ' || l_username);

DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(username => l_username);
DBMS_OUTPUT.PUT_LINE('DBMS_CLOUD_ADMIN.ENABLE_FEATURE for AUTO_DST_UPGRADE');
DBMS_CLOUD_ADMIN.ENABLE_FEATURE(feature_name => 'AUTO_DST_UPGRADE');

select default_tablespace
into l_tablespace_name
from dba_users
where username = upper(l_username);
run_sql('ALTER USER ' || l_username || ' QUOTA UNLIMITED ON ' || l_tablespace_name);
DBMS_OUTPUT.put_line ('User '|| l_username ||' created successfully');

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM);
RAISE;
END;

Complete Object Storage setup.
Confirm the selected bucket, compartment, prefix, and access policies, then complete setup. The Autonomous AI Lakehouse scheduler imports staged snapshots from the bucket.

Configure Object Storage lifecycle rules and warehouse retention to meet your requirements.
Associate databases.
Select the eligible database insights to associate with the warehouse. This determines which database snapshots are staged for import. Configure retention for up to 25 months for the selected databases.

Enable AWR Snapshot Collection separately for recent snapshots in the Ops Insights data store.

Oracle Cloud Infrastructure Documentation

Bring Your Own Warehouse