How to Use Oracle Managed Files (OMF): Complete Guide to Configuration, Procedures, and Operations

Introduction (Summary)
Oracle Managed Files (OMF) is a mechanism that auto-names and auto-places data files, temp files, redo logs, and control files. Once you set DB_CREATE_FILE_DEST, you can create tablespaces without specifying DATAFILE. To keep you from getting lost, this article is organized as Quick Steps → Mechanism → Live SQL examples.

Conclusion & Quick Steps (Snippet-Friendly)
Background & Fundamentals
1. What Is OMF?
  1. Why Is It Useful?
Mechanism (Flow & Text Diagram)
Parameter Quick Reference
Procedures & Implementation (19c / non-CDB / Linux)
Troubleshooting (Cause → Check → Remedy)
Operations, Monitoring, and Security Notes
“How to Use OMF?” (Mini FAQ-Style Heading)
FAQ
Summary (Key Points)

Conclusion & Quick Steps (Snippet-Friendly)

Create the destination directories on the OS and grant permissions.
Set initialization parameters:
- DB_CREATE_FILE_DEST (default destination for data/temp/other managed objects)
- Optionally DB_CREATE_ONLINE_LOG_DEST_n (multiplexing destinations for redo/control files)
Verify the settings: SHOW PARAMETER DB_CREATE%
Create a tablespace via OMF: CREATE TABLESPACE omf_ts; (you can omit DATAFILE)
Automatic multiplexing of redo (optional): set two or more DB_CREATE_ONLINE_LOG_DEST_1..n, then run ALTER DATABASE ADD LOGFILE ... (one member per destination is created).
Operations: when dropping a tablespace, use INCLUDING CONTENTS AND DATAFILES to auto-remove the associated OMF files.
Don’ts: do not manually rename OMF files (they will no longer be recognized as OMF).

Background & Fundamentals

What Is OMF?

OMF is an Oracle feature in which the database manages file names and placement. DBAs only decide the location (directory or ASM disk group), and file names like o1_mf_... are generated automatically. After you set DB_CREATE_FILE_DEST, subsequent create operations (e.g., tablespace, temporary tablespace) can omit explicit file names.

Why Is It Useful?

No more naming hassles: prevents duplicates and typos.
Automatic cleanup on drop: dropping a tablespace removes its managed files.
Automated multiplexing: when multiple DB_CREATE_ONLINE_LOG_DEST_n are set, redo/control files are placed in each destination automatically (multiplexed).

Tip:
OMF applies to files created from now on. It does not “convert” existing files to OMF.

Mechanism (Flow & Text Diagram)

+------------------+
| Issue SQL        |  e.g., CREATE TABLESPACE omf_ts;
+------------------+
          |
          ▼
+------------------+
| OMF auto-names   |  e.g., o1_mf_omf_ts_xxxxx.dbf
+------------------+
          |
          ▼
+-------------------------------+
| Auto-placed at default dest   |  DB_CREATE_FILE_DEST etc.
+-------------------------------+

Default destinations (data/temp/partly managed objects): DB_CREATE_FILE_DEST
Default destinations for redo/control (recommended): DB_CREATE_ONLINE_LOG_DEST_n (n=1..5; multiple values enable multiplexing)

Alt: OMF flow diagram (SQL → auto naming → placement at default destination)

Parameter Quick Reference

Category	Parameter	Role/Behavior (Key Points)
Default	`DB_CREATE_FILE_DEST`	Default creation destination for data/temp, etc. When `DB_CREATE_ONLINE_LOG_DEST_n` is not set, it can also be used as the default for redo/control. The directory/ASM location must exist beforehand. Dynamic.
Multiplexing	`DB_CREATE_ONLINE_LOG_DEST_n`	Default destination for redo/control. When multiple are set, automatic multiplexing (one member at each destination). Recommend two or more. Dynamic.
Reference	`DB_RECOVERY_FILE_DEST`	FRA location. Some docs note that when `DB_CREATE_ONLINE_LOG_DEST_n` is not specified, redo/control may use this default (version/section caveats). For 19c operations, explicitly setting `DB_CREATE_ONLINE_LOG_DEST_n` is recommended.

Procedures & Implementation (19c / non-CDB / Linux)

Prerequisites

OS: the oracle user can write under /u01/app/oracle/...
DB: 19c non-CDB, SYSDBA privilege
We do not “convert” existing files to OMF (applies to future creations)

1) Prepare directories (OS)

# Example: default data location and redo multiplexing destinations
sudo mkdir -p /u01/app/oracle/oradata/V19
sudo mkdir -p /u01/app/oracle/redo1 /u01/app/oracle/redo2
sudo chown -R oracle:oinstall /u01/app/oracle

Intent: OMF only creates files in existing locations (Oracle does not create new directories).

2) Set initialization parameters

-- Default destination for data/temp files
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata/V19' SCOPE=BOTH;

-- Multiplex redo/control (recommended: two or more)
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1 = '/u01/app/oracle/redo1' SCOPE=BOTH;
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2 = '/u01/app/oracle/redo2' SCOPE=BOTH;

-- Verify
SHOW PARAMETER DB_CREATE;

Intent: subsequent create operations work without explicit file names. For redo/control, one member is created at each destination automatically.

3) Create tablespace (OMF)

CREATE TABLESPACE omf_ts;

Intent: even if you omit DATAFILE, OMF creates an o1_mf_...dbf file and places it under DB_CREATE_FILE_DEST.

4) Create temporary tablespace (OMF)

CREATE TEMPORARY TABLESPACE omf_temp;

Intent: omitting TEMPFILE triggers OMF to create it automatically.

5) Add redo logs (OMF, multiplexed)

ALTER DATABASE ADD LOGFILE SIZE 200M;

Intent: with multiple DB_CREATE_ONLINE_LOG_DEST_1..n already set, one member per destination is created automatically.

6) A convenient user and table (for behavior check)

-- Example: app user with OMF tablespace as default
CREATE USER app_user IDENTIFIED BY "Password1"
  DEFAULT TABLESPACE omf_ts
  TEMPORARY TABLESPACE omf_temp
  QUOTA UNLIMITED ON omf_ts;

GRANT CREATE SESSION, CREATE TABLE TO app_user;

-- Create a table (trigger extent allocation)
CONN app_user/Password1
CREATE TABLE t_omf(id NUMBER PRIMARY KEY, pad CHAR(1000));

-- Insert a small amount of data to cause extent allocations
INSERT INTO t_omf SELECT LEVEL, RPAD('x',1000,'x') FROM dual CONNECT BY LEVEL<=1000;
COMMIT;

Intent: extent allocations grow the OMF data file; check DBA_DATA_FILES for o1_mf_....
Result: SELECT file_name FROM dba_data_files WHERE tablespace_name='OMF_TS'; shows an auto-named file.

7) Cleanup (example)

-- Drop table/user
DROP USER app_user CASCADE;

-- Drop tablespaces along with files (OMF linkage cleanup)
DROP TABLESPACE omf_ts INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE omf_temp INCLUDING CONTENTS AND DATAFILES;

Intent: INCLUDING ... DATAFILES removes the physical files managed by OMF as well.

Troubleshooting (Cause → Check → Remedy)

Symptom/Error	Main Cause	How to Check	Reference & Remedy
`ORA-02199: missing DATAFILE/TEMPFILE clause`	`DB_CREATE_FILE_DEST` not set while omitting `DATAFILE/TEMPFILE`	`SHOW PARAMETER DB_CREATE_FILE_DEST`	Set the default destination, then rerun.
`ORA-15045` (invalid ASM name/reference form)	Wrong ASM reference like `+DATA` or permission issues	`SHOW PARAMETER DB_CREATE%`, `asmcmd ls +DATA`	Specify the correct ASM reference; verify permissions and existence.
Redo not multiplexed	Only one `DB_CREATE_ONLINE_LOG_DEST_n` set	`SHOW PARAMETER DB_CREATE_ONLINE_LOG_DEST`	Set two or more destinations, then add redo logs (one member per destination).
Want to “OMF-ize” existing files	OMF applies to newly created files only	`SELECT file_name FROM dba_*`	Keep existing as is; use OMF for subsequent additions. Plan migrations separately if needed.

Operations, Monitoring, and Security Notes

Directory/ASM health: regularly check free space, I/O, and permissions (OMF only auto-names; it doesn’t manage capacity).
Multiplex redo/control files: two or more destinations are recommended (basic fault tolerance).
Role separation with FRA: use FRA for backup/archivelogs/flashback files; explicitly use DB_CREATE_ONLINE_LOG_DEST_n for online redo/control multiplexing—this keeps roles clear.
Don’ts: do not rename or move OMF files manually (they won’t be recognized as OMF). To revert, restore the original name or recreate via supported procedures.
Reverting: to stop using OMF, clear the parameters. Existing OMF files keep working as is (do not rename/move them). Only new creations will be non-OMF.

“How to Use OMF?” (Mini FAQ-Style Heading)

Q. What must I set to enable OMF?
A. At minimum, set DB_CREATE_FILE_DEST. For redo/control defaults and multiplexing, set two or more DB_CREATE_ONLINE_LOG_DEST_n.

FAQ

Q1. Can I toggle OMF on/off dynamically?
A1. Yes. These parameters can be changed with ALTER SYSTEM (existing files remain as they are).

Q2. Under what condition can I omit DATAFILE in CREATE TABLESPACE?
A2. When DB_CREATE_FILE_DEST is set. If not, you’ll get ORA-02199.

Q3. Will redo log multiplexing happen automatically with OMF?
A3. Yes, if you set two or more DB_CREATE_ONLINE_LOG_DEST_n; one member per destination is created automatically.

Q4. Is it okay to let FRA (DB_RECOVERY_FILE_DEST) handle redo/control as well?
A4. Some behavior is noted in the docs, but for 19c it’s clearer operationally to explicitly set DB_CREATE_ONLINE_LOG_DEST_n for redo/control.

Q5. What happens if I rename an OMF file?
A5. The database can no longer recognize it as OMF (becomes unmanaged). Avoid renaming.

Summary (Key Points)

OMF = auto naming/placement; effective for newly created files.
Must-have: DB_CREATE_FILE_DEST; recommended: two or more DB_CREATE_ONLINE_LOG_DEST_n.
Drop with INCLUDING ... DATAFILES; renaming is not recommended.
Use FRA for backup/archivelogs, and explicit ...ONLINE_LOG_DEST_n for redo/control.
All settings are dynamic; existing files remain unchanged.

This article explains Oracle Database 19c (other versions may have different screens or defaults).

[reference]
Using Oracle Managed Files