8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle Managed Files (OMF)
Oracle Managed Files (OMF) simplifies the creation of databases as Oracle does all OS operations and file naming. It has several advantages.
- Automatic cleanup of the filesystem when database objects are dropped.
- Standardised naming of database files.
- Increased portability since file specifications are not needed.
- Simplified creation of test systems on differing operating systems.
- No unused files wasting disk space.
The location of database files is defined using the
DB_CREATE_FILE_DEST parameter. If it is defined on its own,
all files are placed in the same location. If the
DB_CREATE_ONLINE_LOG_DEST_n parameters are defined, alternate locations and levels of multiplexing can be defined for online log files. These parameters are dynamic and can be changed using the
ALTER SYSTEM command.
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u02/oradata';
Files typically have a default size of 100M and are named using the following formats where u% is a unique 8 digit code, g% is the logfile group number, and %t is the tablespace name.
|Redo Log Files||ora_%g_%u.log|
- Managing Controlfiles Using OMF
- Managing Redo Log Files Using OMF
- Managing Tablespaces Using OMF
- Automatic Storage Manager (ASM) in Oracle 10g
- DBCA in Oracle 18c
- Default Temporary Tablespace
Managing Controlfiles Using OMF
During database creation the controlfile names are not specified. Instead, a controlfile is
created for each
DB_CREATE_ONLINE_LOG_DEST_n specified in the init.ora file. Once the database
creation is complete the
CONTROL_FILES parameter can be set in the init.ora file using the generated
names shown in the
Managing Redo Log Files Using OMF
When using OMF for redo logs the
DB_CREAT_ONLINE_LOG_DEST_n parameters in the "init.ora" file decide on the locations and numbers of logfile members. For example:
DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata' DB_CREATE_ONLINE_LOG_DEST2 ='/u03/oradata'
If you are using an SPFILE, you can set these with the following commands.
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata'; ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST2 ='/u03/oradata';
The above parameters mean two members will be created for the logfile group in the specified locations when the
ALTER DATABASE ADD LOGFILE; statement is issued. Oracle will name the file and increment the group number if they are not specified.
ALTER DATABASE DROP LOGFILE GROUP 3; statement will remove the group and it members from the database
and delete the files at operating system level.
Managing Tablespaces Using OMF
As shown previously the
DB_CREATE_FILE_DEST parameter in the init.ora file specifies the location of the
datafiles for OMF tablespaces. Since the file location is specified and Oracle will name the file, new tablespaces
can be created using the following statement.
CREATE TABLESPACE app_ts;
The resulting datafiles 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;
To add a datafile to a tablespace do the following.
-- Default size. ALTER TABLESPACE app_ts ADD DATAFILE; -- Specific size. ALTER TABLESPACE app_ts ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G;
If a tablespace is dropped, Oracle will remove the OS files also. For tablespaces not using the OMF feature this cleanup can be performed by issuing the statement.
DROP TABLESPACE app_ts INCLUDING CONTENTS AND DATAFILES;
Automatic Storage Manager (ASM) in Oracle 10g
Oracle 10g introduced Automatic Storage Manager (ASM). OMF works fine with ASM, as you just reference the ASM disk group.
ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DATA';
DBCA in Oracle 18c
In Oracle 18c (18.4) you can use the
-useOMF flag to indicate you want to enable Oracle Managed Files (OMF) during the database creation with the Database Configuration Assistant (DBCA), as described here.
Default Temporary Tablespace
This is not related to OMF, but worth mentioning.
In previous releases, if you forgot to assign a temporary tablespace to a user, the SYSTEM tablespace was used. This can cause contention and is considered bad practice. To prevent this 9i gives you the ability to assign a default temporary tablespace. If a temporary tablespace is not explicitly assigned the user is assigned to this tablespace.
A default temporary tablespace can be created during database creation or assigned afterwards.
CREATE DATABASE orcl .... DEFAULT TEMPORARY TABLESPACE dts1 TEMPFILE '/u02/oradata/orcl/dts_1.f' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; -- or ALTER DATABASE DEFAULT TEMPORARY TABLESPACE dts2;
A default temporary tablespace cannot be taken offline until a new default temporary tablespace is brought online.
For more information see:
Hope this helps. Regards Tim...