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        => '<replace-with-your-oci-user-ocid>',
          tenancy_ocid     => '<replace-with-your-oci-tenancy-ocid>',
          private_key      => '<replace-with-your-oci-api-signing-private-key>',
          fingerprint      => '<replace-with-your-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. Schedule exports while the required records are still available, before automatic retention removes 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.

For historized data, automatic retention is calculated from when a sample is inserted into the database, not from its time_observed value. The time_observed filter in this scenario selects samples by observation time. If a gateway buffers telemetry and uploads it later, an older sample can still be within its configured retention period. Choose the export time range for your archiving or transfer requirements. The example filters are not instructions for deleting data. 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 export scope, 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 the required historized rows while they are still available in the live schema, in a query-friendly format for reporting or offline analytics.

1. This example exports samples observed more than three months ago to Parquet output in Object Storage. The observation-time filter does not identify when those records become eligible for automatic removal.

   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. Export these rows before automatic retention removes them, using a format that preserves 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. These query exports are not full-schema backups or inputs for an impdp restore.

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.

For a data transfer, select all records required for that transfer independently of the age filters in these examples. This scenario does not export every IoT resource or its configuration; its examples cover selected historized, raw, and rejected records.

• 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 the copy procedure to load a compatible archive into a target table for inspection or validation.
• For onward transfer, see Moving Data to and from Object Storage.
• For configuration retrieval, see Getting the Flows from an IoT Flow Runtime. For configuration import or replacement, see Updating the Flows for an IoT Flow Runtime.
• For inbound CSV ingestion, see Ingesting Batch Data from Object Storage. That scenario is not a general archive restore procedure.
• For shared filesystem access, see Configuring File Storage Access for an IoT Flow Runtime.