Oracle Managed Files (OMF)

 

Oracle Managed Files (OMF) simplifies the management of Oracle database files by automating their creation, deletion, and maintenance. When using OMF, the database server automatically creates and manages the database files, eliminating the need for explicit file naming and location management by the DBA.

Benefits of Using OMF:

• Simplified File Management: OMF handles the naming and location of database files, reducing administrative overhead.
• Consistency and Organization: OMF maintains a consistent naming convention and file organization, making it easier to manage and understand the database structure.
• Reduced Risk of Human Error: Automating file management reduces the chances of errors related to file naming and placement.
• Enhanced Flexibility: OMF allows for easier management of storage and database files, particularly in dynamic environments.

Enabling OMF:

To enable OMF, set the following initialization parameters using an ALTER SYSTEM statement:

Parameter	Description
DB_CREATE_FILE_DEST	Specifies the default location for data, and temp files.
DB_CREATE_ONLINE_LOG_DEST_n	Specifies the default location for online redo log files, and control files. You can have multiplex the files by configuring many destinations.
DB_RECOVERY_FILE_DEST	Specifies the default location for recovery-related files, including archived logs, flashback logs, BCT, and backup files. You can control the amount of space your database uses for it by configuring the DB_RECOVERY_FILE_DEST_SIZE parameter.

Example:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u02/oracle/oradata';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oracle/oradata/redo1';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oracle/oradata/redo2';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u04/oracle/fast_recovery_area';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G;

It can also be used with ASM, you just need to specify the disk group name instead of the entire path.

Using OMF with SQL Statements:

When creating tablespaces, control files, or other database objects, OMF automatically manages the file creation if the OMF parameters are set.

Example of creating a tablespace with OMF:

CREATE TABLESPACE app_ts;

In this case, Oracle automatically creates the data files for the tablespace in the location specified by DB_CREATE_FILE_DEST.

The resulting data files will have a default size of 100M and AUTOEXTEND UNLIMITED. For a specific size file, use the full syntax:

CREATE TABLESPACE app_ts DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 20G;

Managing Tablespaces Using OMF

Default size:

ALTER TABLESPACE app_ts ADD DATAFILE;

Specific size:

ALTER TABLESPACE app_ts ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 20G;

Dropping a tablespace and its data files:

DROP TABLESPACE app_ts INCLUDING CONTENTS AND DATAFILES;

Another important thing to mention when we talk about tablespaces and datafiles, not specifically related to OMF, is that Oracle has a limitation in the datafile sizes, and also the number of datafiles you can have in a tablespace.

BlkSze	Type	MaxDTFSze
2k	smallfile	8 GB
4k	smallfile	16 GB
8k	smallfile	32 GB
16k	smallfile	64 GB
32k	smallfile	128 GB
2k	bigfile	8 GB
4k	bigfile	16 GB
8k	bigfile	32 GB
16k	bigfile	64 GB
32k	bigfile	128 GB

You can check your tablespace blocksize using this script

Maximum number of datafiles per tablespace: 1022

Maximum number of datafiles per database: 65533

Source: Oracle Documentation