Scenario: Archive IoT Data to Object Storage

• Before You Begin.
• Step 1: Plan Data Retention and Archiving.
• Step 2: Choose the Archive Format.
• Step 3: Archive Historized Data to Object Storage.
• Step 4: Archive Raw or Rejected Data to Object Storage.
• Step 5: Verify the Archive Objects.
• Optional Step 6: Load a Data Pump Archive into a Table.

You need the system privileges required to read from and write to DATA_PUMP_DIR, see Using Data Pump. To complete this scenario, you need access to the Autonomous AI Database, the IoT domain database schema, and the Object Storage bucket that receives the archive files.

Confirm the following is set up for your user:

• Create or select an existing Object Storage bucket for the exported files. For more information, see Putting Data into Object Storage.
  Your user must be a member of a specific user group with permissions to create a bucket. This policy lets the specified user group do everything with buckets and the associated objects.

  Allow group <user-group-in-customer-tenancy> to manage objects in compartment <bucket-compartment> where target.bucket.name = '<bucket-name>'

• Confirm the database user can query the source IoT schema: <domain-short-id>__IOT

  Use an IoT policy to let a group of users have full access to the IoT resources in a specific compartment.

  Allow group <group-name> to manage iot-family in compartment <compartment-name>.

  Or use this policy to let a group of users have read only access to IoT resources in a specific compartment.

  Allow group <group-name> to read iot-family in compartment <compartment-name>.

• Create a database credential for Object Storage, or use an existing credential that can write to the bucket in Object Storage, see Setting Up Credentials and Location Parameters for Object Stores.

  To create a database credential use this statement. Replace your user OCID, the tenancy ID with your IoT service tenancy, and the OCI API key with your OCI API key:

• BEGIN
      dbms_cloud.create_credential(
          credential_name => 'IOT_OBJ_STORE_CRED',
          user_ocid       => 'ocid1.user.oc1..<>',
          tenancy_ocid    => 'ocid1.tenancy.oc1..<>',
          private_key     => '-----BEGIN PRIVATE KEY-----
  <>
  -----END PRIVATE KEY-----
  OCI_API_KEY',
          fingerprint     => '<>'
      );
  EXCEPTION
  WHEN OTHERS THEN
     dbms_output.put_line('Credential IOT_OBJ_STORE_CRED creation error.');
  END;
  / 

Data retention defines how long IoT records remain in the live IoT domain database schema. Keep recent data live when applications, dashboards, operational analytics, or troubleshooting workflows need low-latency access. When records fall outside the retention window, archive them to Object Storage before you remove them from the live schema. This approach limits growth in the operational database and still preserves older telemetry, raw payloads, and rejected messages for audit, investigation, offline analytics, or restore. For more information, see Updating an IoT Domain's Data Retention.

Choose the retention cutoff from your business, reporting, and compliance requirements. Use the same cutoff in the export query and in any later cleanup process so the archive boundary is clear. Timestamp columns such as time_observed for historized data and time_received for raw or rejected data are typical retention keys. Record the bucket, object prefix, export format, credential name, source table, and time range for each archive set so you can find and reload the data later.

After you define the retention cutoff, choose the export format based on the IoT domain database table that you want to archive and whether you need to preserve BLOB data.

Historized data

Use DBMS_CLOUD.EXPORT_DATA with a text format such as Parquet, CSV, JSON, or XML. Parquet is useful when older data should remain queryable outside the live IoT environment.

Raw or rejected data

Use DBMS_CLOUD.EXPORT_DATA with Data Pump format when the table includes the content BLOB column and you need to preserve the full original payload.

Historized data is already interpreted by the IoT platform. Export historized rows that have aged out of the live retention window in a query-friendly format so reporting or offline analytics can read the archive from Object Storage.

1. This example exports records older than three months to a Parquet object in Object Storage.

   begin
     dbms_cloud.export_data(
       credential_name => 'IOT_OBJ_STORE_CRED',
       file_uri_list   => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/iot-archive/historized/historized_data_older_than_3_months.parquet',
       format          => json_object(
                            'type' value 'parquet'
                          ),
       query           => q'[
         select *
         from <domain-short-id>__IOT.HISTORIZED_DATA
         where time_observed < add_months(systimestamp, -3)
       ]'
     );
   end;
   /

2. To load the Parquet archive into a target table later, create a compatible target table and use DBMS_CLOUD.COPY_DATA.

   begin
     dbms_cloud.copy_data(
       table_name      => 'HISTORIZED_DATA_ARCHIVE',
       credential_name => 'IOT_OBJ_STORE_CRED',
       file_uri_list   => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/iot-archive/historized/historized_data_older_than_3_months.parquet',
       format          => json_object(
                            'type' value 'parquet'
                          )
     );
   end;
   /

Raw and rejected data can include the content BLOB column. Use a Data Pump export when the retention process moves these rows out of the live environment and you need to preserve the full row, including BLOB content. To archive IoT database tables, use Exporting Data from Autonomous AI Database to Object Store or to Other Oracle Databases.

1. Export raw data:

   begin
     dbms_cloud.export_data(
       credential_name => 'IOT_OBJ_STORE_CRED',
       file_uri_list   => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/iot-archive/raw/raw_data_older_than_3_months.dmp',
       format          => json_object(
                            'type'        value 'datapump',
                            'compression' value 'HIGH',
                            'version'     value 'LATEST'
                          ),
       query           => q'[
         select *
         from <domain-short-id>__IOT.RAW_DATA
         where time_received < add_months(systimestamp, -3)
       ]'
     );
   end;
   /

2. Export rejected data:

   begin
     dbms_cloud.export_data(
       credential_name => 'IOT_OBJ_STORE_CRED',
       file_uri_list   => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/iot-archive/rejected/rejected_data_older_than_3_months.dmp',
       format          => json_object(
                            'type'        value 'datapump',
                            'compression' value 'HIGH',
                            'version'     value 'LATEST'
                          ),
       query           => q'[
         select *
         from <domain-short-id>__IOT.REJECTED_DATA
         where time_received < add_months(systimestamp, -3)
       ]'
     );
   end;
   /

Use the oci os object list Object Storage CLI command to confirm whether the archive files were written to the Object Storage bucket. For more information, see Listing Object Storage Buckets and Listing Object Storage Objects in a Bucket.

oci os object list \
  --namespace <namespace> \
  --bucket-name <bucket> \
  --prefix iot-archive/

Use this copy procedure when you need to read archived raw or rejected rows from Object Storage back into an Autonomous AI Database table for investigation, validation, or a targeted restore. The procedure reads the Data Pump dump file from Object Storage and loads it into the specified target table; it does not make the archive part of the live IoT ingestion path. For a full schema-level restore, use Data Pump Import. For more information, see Download Dump Files, Run Data Pump Import, and Clean Up Object Store.

Load the dump file from Object Storage into a target archive table:

begin
  dbms_cloud.copy_data(
    table_name      => 'RAW_DATA_ARCHIVE',
    credential_name => 'IOT_OBJ_STORE_CRED',
    file_uri_list   => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/iot-archive/raw/raw_data_older_than_3_months.dmp',
    format          => json_object(
                         'type'        value 'datapump',
                         'rejectlimit' value 0
                       )
  );
end;
/ 

After you archive and verify IoT data, continue with the retention and restore tasks for your environment.

• Update the IoT domain data-retention setting or cleanup process so the live schema keeps only the required operational window.
• Apply your Object Storage retention, lifecycle, and access-control requirements to the archive bucket and prefixes.
• Document the archive location, source table, time range, format, and verification result for future audit or restore requests.
• Use DBMS_CLOUD.COPY_DATA or Data Pump Import to reload archived data when you need to inspect or restore older records.