A step-by-step guide to attaching external NFS volumes to Oracle Autonomous Transaction Processing (ATP) using DBMS_CLOUD_ADMIN, creating DBA directories, and performing file I/O with UTL_FILE.
Purpose
Oracle Autonomous Transaction Processing (ATP) is a fully managed, cloud-native database that abstracts away infrastructure concerns. However, many real-world enterprise workloads require the database to interact directly with files — reading flat files, writing exports, or integrating with shared network storage.
This blog walks you through how to mount an NFS (Network File System) volume from an OCI Compute VM onto an ATP database instance, create a DBA_DIRECTORY pointing to that mount, and use Oracle's UTL_FILE package to read and write files — all without leaving the SQL/PL-SQL environment.
📌 Note: This guide assumes you have an existing ATP instance (Dedicated or Serverless) and an OCI Compute VM that is already exporting an NFS share. The VM and the ATP instance must reside within the same VCN or have appropriate network peering and security list rules in place.
Architecture Overview
The solution bridges a Compute VM's NFS export with ATP's internal directory system:
When Do You Need to Mount a File System on ATP?
ATP's managed nature means direct OS-level access is not available. Yet many integration patterns depend on file-based exchange. Here are common real-world scenarios.
Where mounting an NFS volume becomes essential:
- Bulk Data Ingestion :- Legacy systems drop flat files (CSV, fixed-width) on a shared NFS volume. ATP must read and load them via external tables or UTL_FILE.
- Report Exports :- Regulatory or BI reports generated by PL/SQL procedures must be written to a shared drive for downstream consumption by other services.
- ETL Staging Area :- ETL pipelines stage intermediate files on an NFS mount that ATP reads before transforming and loading into final tables.
- Document Storage :- CMS or content-driven applications store documents on NFS. ATP needs to read metadata or contents directly from the file system.
- Audit & Compliance Logs :- PL/SQL audit routines write log files to a secured NFS location for external SIEM or archival tools to consume.
- Cross-System Integration :- Middleware or on-premises systems communicate with ATP via file drops on a shared volume — a common brownfield integration pattern.
Prerequisites:
- ATP Instance Running => An active Oracle ATP instance (Serverless or Dedicated) in OCI. You need ADMIN or DBA privileges.
- OCI Compute VM as NFS Server => A Compute VM with an NFS server configured, exporting the directory /mydocs over NFS v4.
- Network Connectivity => ATP's private endpoint subnet can reach the VM on TCP port 2049. Security lists and NSGs must allow this traffic.
- NFS Export Permissions => The VM's /etc/exports file must allow the ATP instance's private IP with appropriate read/write permissions.
Step 1 — Create the DBA Directory
A DBA Directory is an Oracle database object that maps a logical name (used inside PL/SQL) to a physical path on the server's file system. Once the NFS share is mounted, this directory object tells ATP where to find it.
Run the following as ADMIN or a user with the CREATE ANY DIRECTORY privilege:
-- Creates (or replaces) a directory object named MY_DIR
-- pointing to the physical path 'mydocs' on the ATP server.
-- This path will resolve once the NFS mount is attached in Step 2.
CREATE OR REPLACE DIRECTORY MY_DIR AS 'mydocs';
⚠️ Important
The path 'mydocs' is a relative path that ATP resolves internally after the NFS mount is attached. Do not use an absolute OS path here — DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM handles the binding. The directory name MY_DIR must match exactly in all subsequent steps.
Step 2 — Attach the NFS File System to ATP
With the directory object in place, use DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM to mount the NFS export from your Compute VM. This procedure instructs ATP to establish an NFS mount and link it to the MY_DIR directory object.
Replace <VM_NAME_OR_IP> with the hostname or private IP address of your Compute VM.
PL/SQL — Attach File System via DBMS_CLOUD_ADMIN
BEGIN
DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM(
file_system_name => 'mydocsdocs', -- Unique name for this mount
file_system_location => '<VM_NAME_OR_IP>:/mydocs', -- NFS server export path
directory_name => 'MY_DIR', -- Must match Step 1 directory
description => 'Source NFS for data',
params => JSON_OBJECT('nfs_version' value 4)
);
END;
/
Parameter Reference =>
Step 3 — Write a File to the Mounted Directory
With the NFS volume attached and mapped to MY_DIR, you can now use Oracle's UTL_FILE package to write files directly to the NFS mount. The following block creates a text file named cms2.txt in the mounted directory:
PL/SQL — Write File to MY_DIR using UTL_FILE
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_location VARCHAR2(100) := 'MY_DIR'; -- DBA Directory name
l_filename VARCHAR2(100) := 'cms2.txt'; -- File to create
BEGIN
-- Open the file in write mode ('w' creates or overwrites)
l_file := UTL_FILE.FOPEN(l_location, l_filename, 'w');
-- Write a line of data to the file
UTL_FILE.PUT(l_file, 'Chetan1, male, 1002');
-- Close the file handle (important: always close to flush buffers)
UTL_FILE.FCLOSE(l_file);
END;
/
✅ What Happens Here
UTL_FILE.FOPEN opens a file handle at MY_DIR/cms2.txt in write mode. The PUT call writes the string without a newline (use PUT_LINE if you want a newline appended). FCLOSE flushes and closes the file — always close to prevent handle leaks. The file will physically appear on the VM at /mydocs/cms2.txt.
Step 4 — Read the File from the Mounted Directory
Now verify the write succeeded by reading the same file back from ATP using UTL_FILE.GET_LINE. This also demonstrates that ATP can read files placed on the NFS share by external systems.
PL/SQL — Read File from MY_DIR using UTL_FILE
SET SERVEROUTPUT ON;
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_location VARCHAR2(100) := 'MY_DIR'; -- DBA Directory name
l_filename VARCHAR2(100) := 'cms2.txt'; -- File to read
l_text VARCHAR2(32767); -- Buffer for file content
BEGIN
-- Open the file in read mode ('r')
l_file := UTL_FILE.FOPEN(l_location, l_filename, 'r');
-- Read one line from the file into the buffer
UTL_FILE.GET_LINE(l_file, l_text, 32767);
-- Output the read content to the console
DBMS_OUTPUT.PUT_LINE('File content: ' || l_text);
-- Close the file
UTL_FILE.FCLOSE(l_file);
END;
/
💡 Note
To read multi-line files, wrap UTL_FILE.GET_LINE in a loop and catch the NO_DATA_FOUND exception to detect end-of-file. The buffer size of 32767 is the maximum per-line limit for UTL_FILE.
Step 5 — Detach the File System
When the NFS mount is no longer required — for maintenance, decommissioning, or to switch to a different volume — use DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM to cleanly unmount it from ATP. Always detach before deleting the directory object.
PL/SQL — Detach File System
BEGIN
DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM(
file_system_name => 'mydocsdocs' -- Must match the name used in ATTACH
);
END;
/
Before You Detach
Ensure all open file handles using UTL_FILE or External Tables pointing to MY_DIR are closed. Detaching an active mount may cause I/O errors in running sessions.
Also note that the DBA_DIRECTORY object (MY_DIR) remains after detach — drop it separately with DROP DIRECTORY MY_DIR if no longer needed.
Troubleshooting Common Issues
ORA-29283: Invalid File Operation
This usually means the directory path is not yet mounted or the NFS server is unreachable. Verify network connectivity from ATP's subnet to the VM on port 2049 and
confirm ATTACH_FILE_SYSTEM completed without errors.
ORA-29284: File Read Error
The file does not exist at the expected path, or the NFS mount permissions don't allow the Oracle process to read it. Check NFS export options
(rw, no_root_squash as needed) in /etc/exports on the VM.
ATTACH_FILE_SYSTEM Hangs or Times Out
The most common cause is a blocked port. Ensure OCI Security Lists allow TCP/UDP on port 2049 between the ATP private endpoint subnet and the Compute VM subnet.
Also confirm the VM's OS-level firewall (firewalld / iptables) allows NFS traffic.
NFS Version Mismatch
ATP's ATTACH_FILE_SYSTEM with nfs_version => 4 requires the VM to export via NFSv4. Verify with nfsstat -s or check /proc/fs/nfsd/versions on the VM.
Summary
Mounting an NFS file system on Oracle ATP bridges the gap between the managed database world and file-based integration patterns. The workflow is clean and entirely SQL/PL-SQL-driven:
- Create the Directory:- CREATE OR REPLACE DIRECTORY MY_DIR AS 'mydocs' — registers the logical path in Oracle's data dictionary.
- Attach the NFS Mount:- DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM — mounts the NFS export from the Compute VM and binds it to MY_DIR.
- Write Files:- UTL_FILE.FOPEN / PUT / FCLOSE — creates and writes files directly on the NFS share from PL/SQL.
- Read Files:- UTL_FILE.FOPEN / GET_LINE / FCLOSE — reads files from the NFS share into PL/SQL variables.
- Detach When Done:- DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM — cleanly unmounts the NFS share.
🎉 Key Takeaway
This approach lets you leverage ATP's enterprise-grade managed database capabilities while still integrating with the file-based workflows that many enterprise architectures depend on — with no OS access required and full auditability through Oracle's data dictionary.
No comments:
Post a Comment