Oracle Cloud Infrastructure Offers both Autonomous and Co-managed Oracle Database cloud solutions. Autonomous databases are preconfigured, fully-managed environments that are suitable for either transaction processing or for data warehouse workloads. When compared to the co-managed Oracle Database cloud solutions, end users are not provided with the operating system host access where the autonomous databases (ADB) services are running.
For customers who are migrating from traditional application databases or co-managed Oracle Database cloud solutions to ADB, one of the common use case is to have an option available with the ADB to receive files via SFTP and ingest data from these files using an External Table or PLSQL procedure. Also the customer want these files to be stored securely, encrypted and provided with Life Cycle Management options for purging, archiving etc.
Due to no host level access available for the ADB, such a requirement can be achieved using the one or more OCI Services or by using one of the OCI PaaS Offerings.
This blog provides an overview on one of the solution for the above mentioned customer use case.
If you are already an Oracle Integration Cloud (OIC) Customer or planning to use OIC for integrations, OIC already includes an ATP adapter which can be used for the same use case. Details about this approach is available in one of blog article published in ATeam Chronicles: “New Concepts in Performance Enhancement with OIC for ATP databases”.
Oracle Autonomous Database service provides the PLSQL DBMS_CLOUD Package, which supports loading data files from the following Cloud sources: Oracle Cloud Infrastructure Object Storage, Oracle Cloud Infrastructure Object Storage Classic, and Amazon AWS S3.
For this solution, along with the DBMS_CLOUD Package I will be also leveraging the cloud storage gateway available on OCI, “Storage Gateway” which allows applications to write/read data to an NFS Target connected to an OCI Object Storage Bucket.
Below are the high level steps which I followed to execute a Proof of Concept for this Solution Architecture.
The components for this Solution Architecture includes:
|High Level Steps||Link/Description|
|1||Provision 2 OCI Compute Instances in the same Region & Availability Domain.||https://docs.oracle.com/en-us/iaas/Content/
|2||Install/Configure OCI Storage Gateway on the First Compute Instance provisioned.|
|3||Access Storage Gateway Management Console.|
|4||Create File System via Storage Gateway Management Console||
Note: This Step creates an Object Storage Bucket.
|5||Mount the newly created file system on the Second OCI Compute.||
|6||Install & Configure SFTP Server on the Second OCI Compute.||Follow the generic SFTP Server Installation & Configuration documentation for Linux Host.|
|7||Configure SFTP Server to use the Storage Gateway File System.||Configure the File system created during Step 4
as the Storage for SFTP Server. Files Uploaded to
SFTP server will be stored on this file system by default.
Once the steps in the above section are completed, the required components are in place and files can be sent to the object storage via the SFTP server.
|High Level Steps||Link/Description|
|1||Provision an Autonomous Database (ADB) on Shared Exadata InfraStructure||
Click the link for detailed steps on ADB Provisioning
|2||Create Credential to Access the Object Storage from ADB.||
Create an External Table and Access the data in
|4||Load the Date in the file stored on Object Storage
using Copy_Data Procedure
This solution provides an high level overview of the architecture. Customer should follow the Storage Gateway Best Practices while Installing and Configuring the Storage Gateway Host, File System and also while mounting the Storage Gateway File System as NFS target.