Attach External File Storage to Autonomous AI Database on Dedicated Exadata Infrastructure

You can attach an external file storage deployed in Oracle Cloud Infrastructure or on-premises to your Autonomous AI Database, provided the database and the file system are in the same private network. Autonomous AI Database on Dedicated Exadata Infrastructure supports NFSv4, by default.

This allows you to load data from OCI File Storage in your Virtual Cloud Network (VCN) or any other Network File System in on-premises data centers.

Supporting external file storage allows you to do the following:

Connect to an Autonomous AI Database instance from a legacy application and use the file system to load and unload data.
Analyze data from different sources in an Autonomous AI Database.
Secure access to data in an Autonomous AI Database from the file systems in an on-premises data center or Private VCNs.

External File Storage Requirements

To access data in an Autonomous AI Database from the file system in:

On-premises Data Center: 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.
Oracle Cloud Infrastructure
- Create Virtual Cloud Network (VCN) security rules for OCI File Storage Service (FSS) access: Before you can mount a file storage, you must configure security rules to allow traffic to the mount target's VNIC using specific protocols and ports. Security rules enable traffic for the following:
  - Open Network Computing Remote Procedure Call (ONC RPC) rpcbind utility protocol
  - Network File System (NFS) protocol
  - Network File System (MOUNT) protocol
  - Network Lock Manager (NLM) protocol
  See Configuring VCN Security Rules for File Storage for more details.
- Create a file system in OCI FSS: Create a file system using the OCI console. When configuring mount target details for the file system, select the VCN and the private subnet of that VCN in which your database is deployed, and provide a hostname to automatically generate a Fully Qualified Domain Name (FQDN). See Creating File Systems for more information.
- Obtain the FQDN for the mount target: View the details of your file system's mount target from the OCI console as described in To view details of a mount target to get its FQDN.

Attach External File System

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

Note: The DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure can only attach a private File Storage Service to databases that are on private endpoints.

Create a directory or use an existing directory to attach the external file system in your Autonomous AI Database. You must have WRITE privilege on the directory object on your Autonomous AI 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 a Directory for more information.
Run DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM to attach a file system to a directory in your Autonomous AI 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. However, you can choose to explicitly use the params parameter and specify the nfs_version with value 3 to specify 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 FSS for sales data'
);END;
/
```
- 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 FSS for sales data',

    **params => JSON_OBJECT('nfs_version' value 4)**
  );
END;
/
```
This example attaches the network file system specified in the file_system_name parameter to the Autonomous AI 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
- File Path: /results
The directory_name parameter specifies the directory name in the Autonomous AI 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.

Notes for using DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM:

Oracle Cloud Infrastructure File Storage uses NFS Version 3 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 AI 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".

Query DBA_CLOUD_FILE_SYSTEMS

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 DBMS_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 FSS 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');

DBMS_CLOUD_FILE_SYSTEMS View

The DBA_CLOUD_FILE_SYSTEMS view lists the information about the external 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

Detach External File System

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

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

You must have the 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 AI Database. To run this procedure, you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

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 AI Database. You must provide a value for this parameter. The information about this file system is removed from the DBA_CLOUD_FILE_SYSTEMS view.