Oracle Managed Files (OMF)

OMF simplifies the creation of databases as Oracle does all OS operations and file naming. It has several advantages including:

Automatic cleanup of the filesystem when database objects are dropped.
Standardized 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 parameter is defined alternate locations and levels of multiplexing can be defined for Logfiles. These parameters are dymanic and can be changed using the ALTER SYSTEM statement:

ALTER SYSTEM SET DB_CREATE_FILE_DEST='C:\Oracle\Oradata\TSH1';

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
Managing Redo Log Files Using OMF
Managing Tablespaces Using OMF
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 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 exmple:

DB_CREATE_ONLINE_LOG_DEST_1 = c:\Oracle\Oradata\TSH1
DB_CREATE_ONLINE_LOG_DEST_2 = d:\Oracle\Oradata\TSH1

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 tsh_data;

The resultant datafiles will have a default size of 100M and AUTOEXTEND UNLIMITED. For a specific size file use:

CREATE TABLESPACE tsh_data DATAFILE SIZE 150M;

To add a datafile to a tablespace use:

ALTER TABLESPACE tsh_data 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 tsh_data INCLUDING CONTENTS AND DATAFILES;

Default Temporary Tablespace

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 TSH1
....
DEFAULT TEMPORARY TABLESPACE dts1
TEMPFILE 'c:\Oracle\Oradata\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.

Hope this helps. Regards Tim...

Back to the Top.