Oracle Managed Files
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 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.
|Redo Log Files||ora_%g_%u.log|
- Managing Controlfiles Using OMF
- Managing Redo Log Files Using OMF
- Managing Tablespaces Using OMF
- Default Temporary Tablespace
- DBCA in Oracle 18c
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 = 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 LOGF'ILE; 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 tsh_data;
The resulting datafiles will have a default size of 100M and AUTOEXTEND UNLIMITED. For a specific size file use the full syntax.
CREATE TABLESPACE tsh_data DATAFILE SIZE 150M;
To add a datafile to a tablespace do the following.
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.
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.
Hope this helps. Regards Tim...