Site

Dev

Overview

Oracle Managed Files (OMF) is Oracle's preferred method to manage database files. Most notably OMF:

Simplifies administration.
Deletes unused files.
Enforces Optimal Flexible Architecture (OFA).
Can provide file redundancy (control, redo, archive logs etc.).

OMF when used optimally consists of setting the location for three areas:

 DB_CREATE_FILE_DEST          Data, temp and block-change-tracking.
 DB_CREATE_ONLINE_LOG_DEST_n  Control and redo.
 DB_RECOVERY_FILE_DEST        FRA, RMAN, multiplexed control and redo.

When DB_RECOVERY_FILE_DEST is set DB_RECOVERY_FILE_DEST_SIZE must be set also.

The default creation size for an Oracle Managed Files (redo, datafiles and log files) is 100 mb.

Do not rename an Oracle managed file. The database identifies an Oracle managed file based on its name. If you rename the file, the database is no longer able to recognize it as an Oracle managed file and will not manage the file accordingly.

File Names

No two Oracle Managed Files are given the same name. The names are derived from: creation location, template and a unique string created by Oracle database.

A file is considered OMF if its base file name has:

"o1_mf_" prefix and
An "_" character immediately preceding the extension and.
".dbf", ".tmp", ".log", or ".ctl" extension.

Common format: destination_prefix/%t.%u

destination_prefix = destination_location/db_unique_name/datafile
- destination_location = location specified in DB_CREATE_FILE_DEST
- db_unique_name = the globally unique name (DB_UNIQUE_NAME initialization parameter) of the target database. If there is no DB_UNIQUE_NAME parameter, then the DB_NAME initialization parameter value is used.
%t = tablespace name.
%u = eight-character string that guarantees uniqueness.
g% = logfile group number.

ASM

 Control Files:  %u          Ex: +DATA/X15/CONTROLFILE/Current.257.944387203
 Datafiles:      %t.%u       Ex: +DATA/X15/DATAFILE/SYSTEM.270.944396221
 Redo Log Files: group_%g.%u Ex: +DATA/X15/ONLINELOG/group_1.260.944396217
 Temp Datafiles: %t.%u       Ex: +DATA/X15/TEMPFILE/TEMP.261.944396233

File Based

 Control Files:  %u.ctl          Ex: +DATA/X15/CONTROLFILE/Current.257.944387203
 Redo Log Files: group_%g.%u.log Ex: +DATA/X15/ONLINELOG/group_1.260.944396217
 Datafiles:      %t.%u.dbf       Ex: +DATA/X15/DATAFILE/SYSTEM.270.944396221
 Temp Datafiles: %t.%u.tmp       Ex: +DATA/X15/TEMPFILE/TEMP.261.944396233

Example

Configuration

 DB_CREATE_FILE_DEST = /u01/app/oracle/oradata
 DB_UNIQUE_NAME      = X15

Resulting Name

 /u01/app/oracle/oradata/X15/datafile/USERS.261.927031755.dbf

ASM

Configuration

 DB_CREATE_FILE_DEST = +DATA
 DB_UNIQUE_NAME      = X15

Result

 +data/X15/DATAFILE/USERS.261.927031755

File Admin Examples

Standard Tablespace

 CREATE BIGFILE TABLESPACE x15 EXTENT MANAGEMENT LOCAL DATAFILE SIZE 512m;
 ALTER TABLESPACE x15 RESIZE 2g;
 ALTER TABLESPACE x15 OFFLINE;
 DROP TABLESPACE x15 INCLUDING CONTENTS AND DATAFILES;

TEMP Tablespace

 CREATE BIGFILE TEMPORARY TABLESPACE temp1 EXTENT MANAGEMENT LOCAL DATAFILE SIZE 5g;
 ALTER TABLESPACE temp1 RESIZE 25g;
 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;