How to Attach a File System to your Autonomous Database

November 15, 2022 | 6 minute read
Can Tuzla
Principal Product Manager
Text Size 100%:

Autonomous Database (ADB) offers various options for interacting with external data sources. Calling web services, creating database links to other databases, or accessing cloud object stores are some of these capabilities. Reading from or writing into the files in object stores via the DBMS_CLOUD APIs is especially quite popular among ADB customers. However, there are certain use-cases around external data access that cannot be fulfilled via object stores yet. These use-cases include but not limited to the applications connecting to the Autonomous Database that need to:

  • Read from or write into an external file system
  • Store external data in a private network (i.e. private endpoint)

As you can guess, I’d like to share the news that Autonomous Database now supports interacting with external file systems that are on the same private network as the database itself. This means that our customers can now attach their file systems deployed in on-premises or OCI to their Autonomous Databases as long as the database and the file system are in the same private network. In this blog, I’ll demonstrate how to create a file system in OCI File Storage Service (FSS), attach it to my ADB instance, and perform read/write operations on the file system from the instance. Here are the steps that I will follow:

  • Create VCN security rules for OCI FSS access
  • Create a file system in OCI FSS
  • Obtain the FQDN for the mount target
  • Attach the file system to ADB
  • Query DBA_CLOUD_FILE_SYSTEMS
  • Create and write to a file via UTL_FILE
  • List files in the attached file System and read a file via UTL_FILE
  • Bonus content
Note: I’d like to point out once again that even though this demonstration is using a file system created in OCI FSS, on-premises file systems are also supported as long as they are deployed in the same private network as the ADB instance via FastConnect or Site-to-Site VPN.

 

Update as of 03/26/2024: Autonomous Database now supports attaching file systems that use NFSv4 in addition to NFSv3. To find out how to setup an NFSv4 server in your environment (e.g. VM), check out our documentation.

Create VCN security rules for OCI FSS access

For this use-case, I’m going to create a file system in OCI FSS with a mount target that is in the same private subnet as my ADB instance. However, before doing so, I need to add some ingress and egress rules in my VCN’s security list so that my ADB and file system can talk to each other. The required security rules for my use-case according to the OCI FSS documentation are:

  • Stateful ingress from ALL ports in source CIDR block to TCP ports 111, 2048, 2049, and 2050.

  • Stateful ingress from ALL ports in source CIDR block to UDP ports 111 and 2048.

  • Stateful egress from TCP  ALL ports to ports 111, 2048, 2049, and 2050 in destination CIDR block.

  • Stateful egress from UDP  ALL ports to port 111 in destination CIDR block.

Create a file system in OCI FSS

Next step is to create a file system by following Home -> Storage -> File Storage -> File Systems -> Create File System in OCI console. When configuring mount target details for my file system, I’ll select the VCN and the private subnet of that VCN in which my ADB is deployed, and provide a hostname so that an FQDN is automatically generated as shown in the screenshot below:

Create file system

Obtain the FQDN for the mount target

If you forget to specify a hostname during the file system creation, you can do it after creation as well by following File Storage -> Mount Targets -> <yourMountTargetName> in OCI console and obtain your FQDN as shown below:

Mount target FQDN

Attach the file system to ADB

I’m now ready to attach my file system named ctuzlafs to the FSS_DIR directory in my ADB via the DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure. Please note that I will first create my directory object since it must exist prior to calling this procedure:

CREATE DIRECTORY FSS_DIR AS 'fss';

Directory FSS_DIR created.

BEGIN
  DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM(
    file_system_name => 'CTUZLAFS',
    file_system_location => 'ctuzlamt.sub1********1.ctuzlavcn.oraclevcn.com:/ctuzlafs',
    directory_name => 'FSS_DIR',
    description => 'attach OCI file system'
  );
END;
/

PL/SQL procedure successfully completed.

Query DBA_CLOUD_FILE_SYSTEMS

I’ll query DBA_CLOUD_FILE_SYSTEMS to make sure I can see the attached file system:

SELECT file_system_name, file_system_location, directory_path FROM dba_cloud_file_systems;

FILE_SYSTEM_NAME         FILE_SYSTEM_LOCATION                                       DIRECTORY_PATH 
----------------         --------------------------------------------------------   ----------------------------------------
CTUZLAFS                 ctuzlamt.sub1********1.ctuzlavcn.oraclevcn.com:/ctuzlafs   /u03/dbfs/ED****************EA/data/fss/ 

Create and write to a file via UTL_FILE

I will create a file called channel.csv and write into it:

DECLARE
  l_file         UTL_FILE.file_type;
  l_location     VARCHAR2(100) := 'FSS_DIR';
  l_filename     VARCHAR2(100) := 'channel.csv';
BEGIN
  -- Open file.
  l_file := UTL_FILE.fopen(l_location, l_filename, 'w');
   
  UTL_FILE.PUT(l_file, 'Catalog, Indirect, 12');
 
  -- Close the file.
  UTL_FILE.fclose(l_file);
END;
/

PL/SQL procedure successfully completed.

List files in the attached file System and read a file via UTL_FILE

As the final step of this demo, I’ll list the files in this attached file system via DBMS_CLOUD.LIST_FILES and read the content of file that I just created:

SELECT object_name FROM DBMS_CLOUD.LIST_FILES('FSS_DIR');

OBJECT_NAME 
-----------
channel.csv 
DECLARE
  l_file         UTL_FILE.file_type;
  l_location     VARCHAR2(100) := 'FSS_DIR';
  l_filename     VARCHAR2(100) := 'channel.csv';
  l_text         VARCHAR2(32767);
BEGIN
  -- Open file.
  l_file := UTL_FILE.fopen(l_location, l_filename, 'r');
 
 UTL_FILE.GET_LINE(l_file, l_text, 32767);
 DBMS_OUTPUT.put_line(l_text);

  -- Close the file.
  UTL_FILE.fclose(l_file);
END;
/

Catalog, Indirect, 12

PL/SQL procedure successfully completed.

Bonus content

In case you are wondering if the file we created indeed lives in the external file system or not, here's some bonus content for you. The code snippet below lists the files in our external file system, detaches the file system, checks the content of the directory once again to make sure the file is not visible anymore, and attaches back the file system to show that the file we created is back:

SELECT object_name FROM DBMS_CLOUD.LIST_FILES('FSS_DIR');

OBJECT_NAME 
______________ 
channel.csv

BEGIN                                                                         
  DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM(                                               
    file_system_name   => 'CTUZLAFS'                                 
  );                                                                          
END;                                                                          
/

PL/SQL procedure successfully completed.

SELECT object_name FROM DBMS_CLOUD.LIST_FILES('FSS_DIR');

no rows selected

BEGIN
  DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM(
    file_system_name => 'CTUZLAFS',
    file_system_location => 'ctuzlamt.sub1********1.ctuzlavcn.oraclevcn.com:/ctuzlafs',
    directory_name => 'FSS_DIR',
    description => 'attach OCI file system'
  );
END;
/

PL/SQL procedure successfully completed.

SELECT object_name FROM DBMS_CLOUD.LIST_FILES('FSS_DIR');

OBJECT_NAME 
______________ 
channel.csv

That brings us to the end of another blog. To recap, you can now attach external file systems to your Autonomous Database whether they are deployed in your on-premises data centers or in OCI FSS. This feature closes the gap for those use-cases and requirements that cannot be satisfied by an object store for applications that need private access to external data or interact with on-premises file systems. If you’d like to learn more about it, please check out our documentation.

Can Tuzla

Principal Product Manager

Can is a Principal Product Manager for Oracle Autonomous Database (ADB-S) and has been with the company since 2014. Prior to joining the ADB-S team, he worked on the Oracle Multitenant and Oracle Query Optimizer teams. Can holds a MS (Computer Science) from Case Western Reserve University and a BS (Computer Engineering) from Bilkent University.


Previous Post

November's edition of our regular YouTube recording for "What's New" in Autonomous Database

Keith Laker | 1 min read

Next Post


Configure your Autonomous Database with Customer-Managed Keys and Cross-Region Autonomous Data Guard

Can Tuzla | 3 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider