oracledba.help
System

Oracle Managed Files (OMF)

Overview

Oracle Managed Files (OMF) is a service that automates naming, location, creation and deletion of database files such as control files, redo log files, data files and others.

OMF makes administration easier by:

  • Making the creation of a new database easier.
  • Eliminating the DBA from having to directly manage OS files.
  • Automatically deleting unused and obsolete files.
  • Enforces Optimal Flexible Architecture (OFA), i.e. standardization.

Default Properties

Standard Tablespaces:

  • BLOCKSIZE 8k
  • DATAFILE SIZE 100m
  • AUTOEXTEND OFF
  • EXTENT MANAGEMENT LOCAL
  • SEGMENT SPACE MANAGEMENT AUTO
  • LOGGING

Log Files (Redo, Archive etc.):

  • All log files are created as 100m.

OMF Parameters

  • DB_CREATE_FILE_DEST
    Data, temp, redo, control and block-change-tracking files.
  • DB_RECOVERY_FILE_DEST
    RMAN, archived log and flashback log files. Also multiplexed control and redo log files.
    When DB_RECOVERY_FILE_DEST is set DB_RECOVERY_FILE_DEST_SIZE must also be set.
    • DB_RECOVERY_FILE_DEST_SIZE
      Specifies (in bytes) the total space to be used by database recovery files created in the DB_RECOVERY_FILE_DEST location. The minimum recommended size is the sum of: the database size, the size of backups, the size of all archived redo logs that have not been copied to tape and the size of the flashback logs.
  • DB_CREATE_ONLINE_LOG_DEST_n
    To create custom locations for control files and redo log files.
    • If DB_CREATE_ONLINE_LOG_DEST_n is specified control files and redo logs will go there instead of under DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST.

Examples (File System and ASM) 

 DB_CREATE_FILE_DEST         = '/u01/oradata'
 DB_CREATE_FILE_DEST         = '+DATA'

 DB_RECOVERY_FILE_DEST       = '/u02/oradata'
 DB_RECOVERY_FILE_DEST       = '+FRA'
 DB_RECOVERY_FILE_DEST_SIZE  = 20G

 DB_CREATE_ONLINE_LOG_DEST_1 = '/u03/oradata'
 DB_CREATE_ONLINE_LOG_DEST_2 = '/u04/oradata'
 DB_CREATE_ONLINE_LOG_DEST_1 = '+CTL-REDO1'
 DB_CREATE_ONLINE_LOG_DEST_2 = '+CTL-REDO2'

Notice sub-directories not specified as OMF uses DB_NAME or DB_UNIQUE_NAME when creating the file paths.

File Naming

File System: 

<DB_|NAME|UNIQUE_NAME>/<FILE_TYPE>/o1_mf_<file_type_tag>_<eight-character string>.<ext>

 /u01/oradata/ORADB/datafile/o1_mf_system_dmbokg0g_.dbf
 /u01/oradata/ORADB/datafile/o1_mf_temp_dmbol1n8_.tmp
 /u01/oradata/ORADB/onlinelog/o1_mf_4_dmbokdds_.log

 /u02/oradata/ORADB/archivelog/2017_06_05/o1_mf_dmbokdds_.log
 /u02/oradata/ORADB/onlinelog/o1_mf_4_dmbokf4r_.log

ASM: 

<+GROUP>/<DB_|NAME|UNIQUE_NAME>/<FILE_TYPE>/<file_type_tag>.<file#>.<incarnation#>

 +DATA/ORADB/DATAFILE/system.258.905963653
 +DATA/ORADB/TEMPFILE/temp.261.905963775
 +FRA/ORADB/ONLINELOG/group_4.272.905963831
 +FRA/ORADB/ARCHIVELOG/2017_06_02/thread_2_seq_12130.2116.945586203

OMF Usage Examples

 -- Display Standard Files
 COL name FORMAT a75
 SELECT name, round(bytes/1024/1024/1024,2) "Size (gb)", status, file# 
 FROM v$datafile ORDER BY name;

 -- Display Temp Files
 COL file# FORMAT 999
 COL name  FORMAT a65
 SELECT file#, name, round(bytes/1024/1024/1024,2) "Size (gb)", status 
 FROM v$tempfile ORDER BY file#;

 -- Standard Tablespace Actions
 CREATE BIGFILE TABLESPACE x15 DATAFILE SIZE 2g AUTOEXTEND ON NEXT 64m MAXSIZE UNLIMITED;
 ALTER TABLESPACE x15 RESIZE 5g;
 ALTER TABLESPACE x15 OFFLINE NORMAL;
 DROP TABLESPACE x15 INCLUDING CONTENTS AND DATAFILES;

 -- Small File Tablespace (SFTS) Actions
 ALTER TABLESPACE users ADD DATAFILE;
 ALTER DATABASE DATAFILE '+DATA/TEST/sysaux01.dbf' RESIZE 10g;
 ALTER DATABASE DATAFILE '+DATA/TEST/DATAFILE/users.268.990255251' RESIZE 1g;

 -- TEMP Tablespace Actions:BFTS
 CREATE BIGFILE TEMPORARY TABLESPACE temp1;
 ALTER TABLESPACE temp1 AUTOEXTEND OFF;
 ALTER TABLESPACE temp1 RESIZE 5g;
 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

 -- TEMP Tablespace Actions:Legacy TS
 ALTER TABLESPACE temp ADD TEMPFILE '+DATA' SIZE 5G AUTOEXTEND ON NEXT 256m MAXSIZE 32767M;
 Get new file name.
 ALTER DATABASE TEMPFILE '+DATA/DNATEST/TEMPFILE/temp.315.1003825175' RESIZE 30g;

 -- Display Redo Log Groups and Files
 COL member FORMAT a55
 COL type   FORMAT a15
 SELECT group#,member,type FROM v$logfile ORDER BY group#,member;

 -- Display Archived Log Files
 COL name FORMAT a75
 SELECT * FROM  (
   SELECT recid, name, to_char(completion_time,'YYYY-MM-DD HH24:MI') "Completed" 
 FROM v$archived_log ORDER BY completion_time DESC
 ) WHERE rownum <= 25;

 -- Create New Group of Log Files
 ALTER DATABASE ADD LOGFILE;

 -- Drop Group of Log Files
 ALTER DATABASE DROP LOGFILE GROUP 3;