X

Best Practices from Oracle Development's A‑Team

Using Object Storage as a File Server for Autonomous Database

Introduction

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.

Before you begin

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”.

Solution Architecture

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:

  • OCI Object Storage Bucket
  • OCI Storage Gateway
  • 2 OCI Compute Instances
    • First Compute Instance to install and run the Storage Gateway
    • Second Compute instance to be used as a NFS Client and SFTP Server
  • Autonomous Database
  • Required OCI Network Services

Configure & Install the Storage Gateway & SFTP Server:

  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/
Compute/Tasks/launchinginstance.htm#
Creating_an_Instance
2 Install/Configure OCI Storage Gateway on the First Compute Instance provisioned.

https://docs.oracle.com/en-us/iaas/Content/StorageGateway/Tasks/
installingstoragegateway.
htm#Installing_Storage_Gateway

3 Access Storage Gateway Management Console.

https://docs.oracle.com/en-us/iaas/Content/StorageGateway/Tasks/
accessingmanagementconsole.htm#
Logging_In_to_the_Storage_Gateway_Management_Console

4 Create File System via Storage Gateway Management Console

https://docs.oracle.com/en-us/iaas/Content/StorageGateway/Tasks/
creatingyourfirstfilesystem.
htm#Creating_Your_First_File_System

Note: This Step creates an Object Storage Bucket.

5 Mount the newly created file system on the Second OCI Compute.

https://docs.oracle.com/en-us/iaas/Content/StorageGateway/Tasks/
managingfilesystems.htm#mountingfilesystem


Note: Storage Gateway does not currently support NFS clients running on Windows or Mac OS.

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.

Provision & Configure ADB to access the files in the object Storage bucket:

  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.

DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name   IN VARCHAR2,
username          IN VARCHAR2,
password          IN VARCHAR2 DEFAULT NULL);
Click here for More Information

3

Create an External Table and Access the data in
the file stored on Object Storage.

DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name       IN VARCHAR2,
credential_name  IN VARCHAR2,
file_uri_list    IN CLOB, 
column_list      IN CLOB,
field_list       IN CLOB DEFAULT,
format           IN CLOB DEFAULT);
Click here for More Information

4 Load the Date in the file stored on Object Storage
using Copy_Data Procedure

DBMS_CLOUD.COPY_DATA (
table_name        IN VARCHAR2,
credential_name   IN VARCHAR2,          
file_uri_list     IN CLOB,
schema_name       IN VARCHAR2,
field_list        IN CLOB,
format            IN CLOB);
Click here for More Information

Pros:

  • Data written to Object Storage is always automatically encrypted in the cloud.
  • Storage Gateway transfers data to Oracle Cloud Infrastructure using HTTPS.
  • Can utilize the Object Lifecycle Management option available with the Object Storage for archiving/deleting the files.
  • OS command line utilities can be used for the Object Lifecycle Management since Object Storage Bucket is mounted as a NFS Filesystem on the Compute Instance.
  • Storage Gateway File System can be mounted on multiple Hosts.

Cons:

  • Storage Gateway does not currently support NFS clients running on Windows or Mac OS.
  • No in-built virus scan capability is available on the Object Storage. End user need to ensure that virus scan is enabled on the SFTP server.
  • End-user should restrict the access to the SFTP Server to the Applications/Users with required authorisation & authentication.
  • Storage Gateway does not support frequently modified files such as logs, databases, or virtual disks.

Conclusion

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.

Reference Links:

https://docs.oracle.com/en-us/iaas/Content/StorageGateway/Concepts/storagegatewayoverview.htm

https://docs.oracle.com/en-us/iaas/Content/StorageGateway/Reference/bestpracticesusingstoragegateway.htm

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha