Access Network File System from Autonomous Database

You can attach a Network File System to a directory location in your Autonomous Database.

This allows you to load data from Oracle Cloud Infrastructure File Storage in your Virtual Cloud Network (VCN) or from any other Network File System in on-premises data centers. Depending on the version of the Network File System you want to access, both NFSv3 and NFSv4 are supported.

Supporting Network File System allows you to do the following:

• Connect to an Autonomous Database instance from a legacy application and use the file system to load and unload data.

• Analyze data from different sources in an Autonomous Database.

• Secure access to data in an Autonomous Database from the file systems in an on-premises data center or Private Virtual Cloud Networks (VCNs).

Topics

• Attach Network File System to Autonomous Database
  Use DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM to attach a file system to a directory in your Autonomous Database.
• Detach Network File System from Autonomous Database
  Use the DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure to detach a file system from a directory in your Autonomous Database.
• Example: Set Up an NFSv4 Server on Oracle Cloud Compute
  Provides an example for setting up an NSFv4 server for use with Autonomous Database.
• DBA_CLOUD_FILE_SYSTEMS View
  The DBA_CLOUD_FILE_SYSTEMS view lists information about the network file system attached to a directory location in the database.

Attach Network File System to Autonomous Database

Use DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM to attach a file system to a directory in your Autonomous Database.

With an attached file system you can load data from any of the following:

• Oracle Cloud Infrastructure File Storage in your Virtual Cloud Network (VCN).

  See How to Attach a File System to your Autonomous Database for details on setting up Oracle Cloud Infrastructure File Storage with Autonomous Database.

• From a Network File System in an on-premises data center. Depending on the version of the Network File System you want to access, both NFSv3 and NFSv4 are supported.

  See Example: Set Up an NFSv4 Server on Oracle Cloud Compute for a configuration example with an NFSv4 Network File System.

Note:

TheDBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure can only attach a private File Storage Service when the Autonomous Database instance is on a private endpoint.

To access data in an Autonomous Database from the file systems in an on-premises data center you must set up FastConnect or a Site-to-Site VPN to connect to the on-premises data center. See FastConnect and Site-to-Site VPN for more information.

1. Create a directory or use an existing directory to attach a Network File System in your Autonomous Database. You must have WRITE privilege on the directory object on your Autonomous Database instance to attach a file system to a directory location in the database.

   For example, the following command creates the database directory named FSS_DIR and creates the file system directory fss:

   CREATE DIRECTORY FSS_DIR AS 'fss';

   See Create Directory in Autonomous Database for more information.

2. Run DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM to attach a file system to a directory in your Autonomous Database. To run this procedure, you must be logged in as the ADMIN user or have EXECUTE privilege on DBMS_CLOUD_ADMIN.

   • By default DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM uses NFSv3:

     BEGIN
        DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM(
         file_system_name      => 'FSS',
         file_system_location  => 'myhost.sub000445.myvcn.oraclevcn.com:/results',
         directory_name        => 'FSS_DIR',  
         description           => 'Source NFS for sales data'
     );
     END;
     /

     Optionally you can use the params parameter and specify the nfs_version with value 3 to specify NFSv3.

   • To use NFSv4, include the params parameter with DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM and specify the nfs_version with value 4 to specify NFSv4:

     BEGIN
        DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM(
         file_system_name      => 'FSS',
         file_system_location  => 'myhost.sub000445.myvcn.oraclevcn.com:/results',
         directory_name        => 'FSS_DIR',  
         description           => 'Source NFS for sales data',
         params                => JSON_OBJECT('nfs_version' value 4)
     );
     END;
     /

   These examples attach the network file system specified in the file_system_name parameter to the Autonomous Database.

   The file_system_location parameter specifies the location of the file system. The value you supply with file_system_location consists of a Fully Qualified Domain Name (FQDN) and a file path in the form: FQDN:file_path.

   For example:

   • FQDN: myhost.sub000445.myvcn.oraclevcn.com

     For Oracle Cloud Infrastructure File Storage set the FQDN in Show Advanced Options when you create a file system. See Creating File Systems for more information.

   • File Path: /results

   The directory_name parameter specifies the directory name in the Autonomous Database where you want to attach the file system. This is the directory you created in Step 1, or another directory you previously created.

   The description parameter specifies the description for the task.

   The params parameter is a JSON value that specifies an additional attribute nfs_version, whose value can be either 3 or 4 (NFSv3 or NFSv4).

After you attach a file system you can query the DBA_CLOUD_FILE_SYSTEMS view to retrieve information about the attached file system.

For example:

SELECT file_system_name, file_system_location, directory_path                      
   FROM dba_cloud_file_systems
   WHERE file_system_name = 'FSS';

This query returns details for the FSS file system name.

See DBA_CLOUD_FILE_SYSTEMS View for more information.

With an attached file system you can read and write to files on an attached file system using any PL/SQL API that accepts a directory name. For example, you can use any of the following methods to work with an attached NFS directory:

• The UTL_FILE package.

• Data Pump Export and Import utilities.

• The DBMS_CLOUD APIs that work with directories such as DBMS_CLOUD.LIST_FILES and DBMS_CLOUD.PUT_OBJECT.

Example showing a write a file on an attached file system using UTL_FILE:

DECLARE
  l_file         UTL_FILE.FILE_TYPE;
  l_location     VARCHAR2(100) := 'FSS_DIR';
  l_filename     VARCHAR2(100) := 'test.csv';
BEGIN
  -- Open the file.
  l_file := UTL_FILE.FOPEN(l_location, l_filename, 'w');
   
  UTL_FILE.PUT(l_file, 'Scott, male, 1000');
 
  -- Close the file.
  UTL_FILE.FCLOSE(l_file);
END;
/

Example showing a read a file on an attached file system using UTL_FILE:

DECLARE
  l_file         UTL_FILE.FILE_TYPE;
  l_location     VARCHAR2(100) := 'FSS_DIR';
  l_filename     VARCHAR2(100) := 'test.csv';
  l_text         VARCHAR2(32767);
BEGIN
  -- Open the file.
  l_file := UTL_FILE.FOPEN(l_location, l_filename, 'r');
 
  UTL_FILE.GET_LINE(l_file, l_text, 32767);
 
  -- Close the file.
  UTL_FILE.FCLOSE(l_file);
END;
/

Example showing list files on an attached file system using DBMS_CLOUD.LIST_FILES:

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

Notes for using DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM:

• Oracle Cloud Infrastructure File Storage uses NFSv3 to share. See Overview of File Storage for more information.

• If you attach to non-Oracle Cloud Infrastructure File Storage systems, the procedure supports NFSv3 and NFSv4.

• If you have an attached NFS server that uses NFSv3 and the NFS version is updated to NFSv4 in the NFS server, you must run DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM and then DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM (using the params parameter with nfs_version set to 4). This attaches NFS with the matching protocol so that Autonomous Database can access the NFSv4 Server. Without detaching and then reattaching, the NFS server will be inaccessible and you may see an error such as: "Protocol not supported".

See the following for more information:

• UTL_FILE

• LIST_FILES Function

• OCI File Storage Service

• ATTACH_FILE_SYSTEM Procedure

Detach Network File System from Autonomous Database

Use the DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure to detach a file system from a directory in your Autonomous Database.

Note:

The DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure can only detach a private File Storage Service from databases that are on private endpoints.

You must have WRITE privilege on the directory object to detach a file system from a directory location.

Run DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure to detach a file system from a directory location in your Autonomous Database. To run this procedure, you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

For example:

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

This example detaches the network file system specified in the file_system_name parameter from the Autonomous Database. You must provide a value for this parameter.

The information about this file system is removed from the DBA_CLOUD_FILE_SYSTEMS view.

See the following for more information:

• OCI File Storage Service

• Creating and Managing Directories on Autonomous Database

• DETACH_FILE_SYSTEM Procedure

Example: Set Up an NFSv4 Server on Oracle Cloud Compute

Provides an example for setting up an NSFv4 server for use with Autonomous Database.

1. Set up a private endpoint for the Autonomous Database instance.

   See Configuring Network Access with Private Endpoints for more information.

   The following ingress and egress rules need to be set for your VCN’s security list so that Autonomous Database and the NFSv4 server can talk to each other

   • Stateful ingress from ALL ports in source CIDR block to TCP port 2049.
   • Stateful egress from TCP ALL ports to port 2049 in destination CIDR block.
2. Set up the NFS server on an Oracle Cloud VM with Oracle Linux 8 in the private subnet, which can connect to the Autonomous Database instance.

   Compute Instance Imeage: Oracle-Linux-8.8-2023.09.26-0
   $ sudo su -
   $ mkdir /exports
   $ chown nobody /exports
   $ chgrp nobody /exports
    
   # If the VM is using Linux 7, "data" directory may need having the privilege 777 so that ADB can have access to NFS.
   uname -a
   chmod 777 /exports/data
    
   $ mkdir /exports/data
   $ chown nobody /exports/data
   $ chgrp nobody /exports/data
    
   # Either the private IP or the private FQDN can be used in "/etc/exports".
   # Both can be found in the information of the autonomous database on the OCI console.
   $ tee -a /etc/exports <<'EOF'
     /exports/data *(rw,insecure)
     /exports/data example.adb.us-phoenix-1.oraclecloud.com(rw,insecure)
     EOF
    
   $ systemctl start nfs-server
   $ systemctl enable nfs-server
    
   # Configure the firewall to allow NFS connections. "public" is the default zone on Oracle Cloud VM.
   $ firewall-cmd --get-default-zone
   public
    
   $ firewall-cmd --zone=public --add-service=nfs
   $ firewall-cmd --permanent --zone=public --add-service=nfs
    
   $ firewall-cmd --reload
    
   # Display a list of the exported file systems.
   $ showmount -e
    
   # Displays all of the current clients and all of the file systems that the clients have mounted.
   $ showmount -a

3. Mount the NFSv4 file system with DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM.

   See Attach Network File System to Autonomous Database for more information.

DBA_CLOUD_FILE_SYSTEMS View

The DBA_CLOUD_FILE_SYSTEMS view lists information about the network file system attached to a directory location in the database.

Column	Data Type	Description
FILE_SYSTEM_NAME	VARCHAR2(128)	File system name
FILE_SYSTEM_LOCATION	VARCHAR2(4000)	File system location
DIRECTORY_NAME	VARCHAR2(128)	Attached directory name
DIRECTORY_PATH	VARCHAR2(4000)	Attached directory path
NFS_VERSION	NUMBER	The NFS version.
DESCRIPTION	VARCHAR2(4000)	The value provided for the description parameter when you run DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM
CREATION_TIME	TIMESTAMP(6) WITH TIME ZONE	Creation timestamp
UPDATE_TIME	TIMESTAMP(6) WITH TIME ZONE	Update timestamp