Default file locations in OMF

The following parameters enable database Oracle-Managed File feature:

• DB_CREATE_FILE_DEST

Default location for datafiles or tempfiles if no file specification is given in the creation operation. Also used as the default location for redo log files and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

• DB_CREATE_ONLINE_LOG_DEST_n

Default location for redo log files and control files if no file specification is given in the creation operation. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file. You can specify up to five multiplexed copies.

• DB_RECOVERY_FILE_DEST

Default location of RMAN backups if no “format” option is used, archived logs if no other local destination(LOG_ARCHIVE_DEST_N) is configured, and flashback logs. Also used as the default location for redo logs and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

In an OMF system, the locations for various database files are decided by such an order:

• location for datafiles and tempfiles
  1. as specified in creation operation;
  2. if it’s not specified in file creation, created in location defined by  DB_CREATE_FILE_DEST;
  3. if DB_CREATE_FILE_DEST is not defined, created in $ORACLE_HOME/dbs

• location for redo logs and control files
  1. as defined in DB_CREATE_ONLINE_LOG_DEST_n;
  2. if multiple DB_CREATE_ONLINE_LOG_DEST_n are defined, redo logs and control files are multiplexed;
  3. if NONE of DB_CREATE_ONLINE_LOG_DEST_n is defined, they are created in DB_CREATE_FILE_DEST (if defined), or in DB_RECOVERY_FILE_DEST(if defined), or in both locations if both DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST are defined
  4. If neither DB_CREATE_FILE_DEST nor DB_RECOVERY_FILE_DEST is defined, then you will receive error:

alter database add standby logfile group 4 size 50M;

Error line 1:

Ora-02236: invalid file name

• location of archived logs
  1. as defined in LOG_ARCHIVE_DEST_N; if multiple LOG_ARCHIVE_DEST_N are specified, archived logs are multiplexed;
  2. if NONE of LOG_ARCHIVE_DEST_N is defined, then archived logs are created in DB_RECOVERY_FILE_DEST(if defined);
  3. if DB_RECOVERY_FILE_DEST is not defined, then archived log are created in $ORACLE_HOME/dbs/arch.

• location of backup files
  1. as specified in “format” option during creation;
  2. if “format” is not specified, then they are created in DB_RECOVERY_FILE_DEST (if defined);
  3. if DB_RECOVERY_FILE_DEST is not defined, then they are stored in $ORACLE_HOME/dbs.