Storing Files in Object Storage

Overview

Install DBMS_CLOUD packages

If you are using a autonomous oracle database you probably already have DBMS_CLOUD installed.

If you are using a non-autonomous database, you may need to install the DBMS_CLOUD package manually. Follow these guides from Oracle:

• 23ai
• 19c: MOS-NOTE with Doc ID 2748362.1 (requires support login)
• Additional information on Oracle Base

Grant privileges

Now we need to grant execute privileges on the DBMS_CLOUD package to the ADM schema user. Replace {adm_schema} with your actual ADM schema name.

grant execute on DBMS_CLOUD to {adm_schema};

Additionally compile adm_oci package with the following command:

ALTER PACKAGE adm_oci COMPILE PLSQL_CCFLAGS = 'DBMS_CLOUD:TRUE';

Setting Up Object Storage in OCI

Create Bucket

Log-in to your Oracle Cloud Infrastructure (OCI) account and navigate to the Buckets section under Storage.

Click Create Bucket and provide a name for your bucket. And use these settings:

• Default Storage Tier: Standard
• Enable Auto-Tiering: You can decide if you want to save costs on infrequently accessed documents
• Enable Object Versioning: No (ADM does already handle versioning)
• Emit Object Storage Events: No
• Uncommitted Multipart Uploads Cleanup: Yes
• Encryption: You can decide which encryption to use
• Resource logging: Keep enabled (recommended)
• Tags: You can decide if you want to use tags

Open the bucket info page and copy the namespace attribute value. On this page you can also monitor the bucket usage and size.

While at it also open the user menu on the top right corner and note the email address visible. Then click on the region dropdown in the header and click on Manage regions. Then note the Region identifier of the current region.

Generate Auth Token

Click on your user icon in the top right corner and select User Settings. Then, navigate to the Tokens and keys section and click on Generate Token in the Auth Tokens section. Give a description and copy the generate token. You will not be able to see the token again after you closed the dialog.

Store credentials in the database

Now we need to enter the auth token into the database. Choose any credential name. The username is the email address of your OCI account. Click on the profile icon and on the email entry. On the page there is a username field.

begin
  dbms_cloud.create_credential (
    credential_name => '{credential_name}',
    username       => '{oci_username}',
    password     => '{oci_auth_token}'
  );
end;
/

Init object storage

Now run this code to set up object storage. The procedure will throw an exception if something goes wrong.

begin
  adm_context_api.system_login(apex_debug.c_log_level_app_trace);

  adm_settings_api.configure_object_storage(
    p_bucket        => '{bucket_name}',
    p_region        => '{region_identifier}',
    p_namespace     => '{namespace}',
    p_credential_id => '{credential_name}',
    p_verify_checksums => 'Y'
  );
end;
/

Add configuration

Now you can configure which files should be stored in object storage by creating storage policies.

declare
  l_policy_id number;
begin
  l_policy_id := adm_storage_api.create_storage_policy(
    p_folder_path_pattern => '/',
    p_storage_location    => 'OBJECT_STORAGE',
    p_description         => 'All files should be in object storage'
  );

  -- or
  l_policy_id := adm_storage_api.create_storage_policy(
    p_folder_path_pattern => '/groups',
    p_storage_location    => 'OBJECT_STORAGE',
    p_description         => 'All group files should be in object storage'
  );

  -- or
  l_policy_id := adm_storage_api.create_storage_policy(
    p_folder_path_pattern => '/users/philipp/test',
    p_storage_location    => 'OBJECT_STORAGE',
    p_description         => 'Philipps test files should be in object storage'
  );

  commit;
end;

Observe and run migration

Run this block to mark all impacted files for migration:

begin
  adm_storage_api.apply_adm_object_storage_policies (
    p_defer => true
  );

  commit;
end;
/

The p_defer parameter makes the procedure not upload all impacted files immediately, just mark them for upload.

The nightly job will pick up these marked files and upload them to object storage. Or you could manually run the first batch:

declare
  l_error_occurred boolean;
begin
  adm_job_automations_api.process_storage_migrations(l_error_occurred);
  sys.dbms_output.put_line('Error occurred: ' || case when l_error_occurred then 'Y' else 'N' end);

  commit;
end;

You can query the status of the migration this way:

select d.document_name
     , f.folder_path
     , dv.version_number
     , dv.migrate_to_storage
     , dv.migration_status
     , dv.storage_location
     , dv.object_storage_key
     , dv.migration_date
     , dv.migration_error
     , dv.migration_retry_count
  from adm_document_versions dv
  join adm_documents d on dv.document_id = d.document_id
  join adm_folders f on d.folder_id = f.folder_id
 where dv.migrate_to_storage is not null;

List bucket objects from the database

select *
  from dbms_cloud.list_objects(
        credential_name => adm_settings_api.get_object_storage_credential_id,
        location_uri    => adm_oci.get_bucket_url
       );