8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

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.

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.

File Type Format
Controlfiles ora_%u.ctl
Redo Log Files ora_%g_%u.log
Datafiles ora_%t_%u.dbf
Temporary Datafiles ora_%t_%u.tmp

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 V$CONTROLFILE view.

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.

The 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 150M;

To add a datafile to a tablespace do the following.

ALTER TABLESPACE app_ts ADD DATAFILE;

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...

Back to the Top.