In Oracle : Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA

-> Oracle Managed Files (OMF)

 

 Oracle Managed Files (OMF)

 

This feature was introduced in Oracle 9i.

 

Advantages of using Oracle Managed Files (OMF)

 

- Easy file management - data files are automatically placed into the proper OS directory (there is no need to specify this location nor the database file name)

- Easy third-party application integration - third-party apps don't have to be aware of OS-specific naming conventions or the location where the files must be located

- OMF use Oracle Optimal Flexible Architecture ( Oracle OFA ) guidelines

- Automatic cleanup of the filesystem when database objects are dropped (No unused files wasting disk space);

- Default file sizes and names - OMF allows files to have standard, uniform sizes

Enabling the OMF (Oracle Managed Files)

 

For using Oracle Managed Files (OMF) we have to set the following initialization parameters:

 

DB_CREATE_FILE_DEST = the default location for the data files;

DB_CREATE_ONLINE_LOG_DEST_n = the default location for the log files (and the control files if the CONTROL_FILES parameter is not specified).

 

Using or not using the OMF is established at the database creation time !!!

 

Exemple of creating a database using OMF:

 

[oracle@linux oracle]$ mkdir datafiles
[oracle@linux oracle]$ mkdir logfiles
[oracle@linux oracle]$ pwd
/u02/oracle

[oracle@linux oracle]$ ls
datafiles logfiles oradata

$ORACLE_HOME and $ORACLE_SID must be set  !!!

 

Create the initialization file (as oracle Linux user):

 

cat >> $ORACLE_HOME/dbs/inittestDB.ora<<EOF

db_name=testDB
DB_CREATE_FILE_DEST=/u02/oracle/datafiles
DB_CREATE_ONLINE_LOG_DEST_1 =/u02/oracle/logfiles
DB_CREATE_ONLINE_LOG_DEST_2 =/u02/oracle/logfiles

EOF

 

sqlplus "/as sysdba"
 

SQL> startup nomount
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
 

SQL> create database testDB
user sys identified by s
user system identified by s
maxlogfiles 3
maxlogmembers 3
maxdatafiles 20
character set al32utf8
logfile group 1 size 50M blocksize 512, group 2 size 50M blocksize 512
default temporary tablespace temp tempfile size 100M uniform size 1M
undo tablespace undotbs datafile size 100M;

 

Now the database is opened and you can run the following scripts:

 

(as sys oracle user)

 

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

 

(as system oracle user)

 

@?/sqlplus/admin/pupbld.sql

 

... and also you can create additional tablespaces.

 

 

Comments

 

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_system_7n23lzdq_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_sysaux_7n23m1hf_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_undotbs_7n23m36g_.dbf
 

 

As you can see the "/TESTDB/datafile" directory is created automatically by Oracle and the data files are named using the OMF rules.

 

TESTDB = database unique name

datafile    = data file type ("datafile" is always for DB_CREATE_FILE_DEST )

 

For DB_CREATE_ONLINE_LOG_DEST_n we can have "controlfile" or "onlinelog":

 

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/logfiles/TESTDB/controlfile/o1_mf_7n23lwf8_.ctl
/u02/oracle/logfiles/TESTDB/controlfile/o1_mf_7n23lwl7_.ctl
 

 

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u02/oracle/logfiles/TESTDB/onlinelog/o1_mf_1_7n23lwnk_.log
/u02/oracle/logfiles/TESTDB/onlinelog/o1_mf_1_7n23lwp6_.log
/u02/oracle/logfiles/TESTDB/onlinelog/o1_mf_2_7n23lxc8_.log
/u02/oracle/logfiles/TESTDB/onlinelog/o1_mf_2_7n23lxyr_.log
 

As you can see, using the same value for DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_1 could be a good thing. However, DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2 must be different.

 

 

Here is the easier way to create an Oracle tablespace using the Oracle Managed Files (OMF):

 

SQL> create tablespace test;
Tablespace created.

 

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_system_7n23lzdq_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_sysaux_7n23m1hf_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_undotbs_7n23m36g_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_test_7n2601mx_.dbf
 

SQL> drop tablespace test;

Tablespace dropped.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_system_7n23lzdq_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_sysaux_7n23m1hf_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_undotbs_7n23m36g_.dbf
 

The file "/u02/oracle/datafiles/TESTDB/datafile/o1_mf_test_7n2601mx_.dbf" was dropped automatically.

 

SQL> create tablespace test;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_system_7n23lzdq_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_sysaux_7n23m1hf_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_undotbs_7n23m36g_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_test_7n263x4d_.dbf
 

The file "/u02/oracle/datafiles/TESTDB/datafile/o1_mf_test_7n263x4d_.dbf" was created automatically.

As you can see the file name are used with different names => Oracle uses a random algorithm to assure that the file name are unique.

 

 

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_temp_7n23m5y2_.tmp

 

SQL> ALTER TABLESPACE test ADD DATAFILE;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_system_7n23lzdq_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_sysaux_7n23m1hf_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_undotbs_7n23m36g_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_test_7n263x4d_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_test_7n27b1gj_.dbf
 

A new file is added for the "test" tablespace.


 

When the Oracle Managed Files feature is enabled, you can also create tablespace using the NON-OMF commands :-) :

 

SQL> create tablespace USERDATA DATAFILE '/u02/oracle/userdata-01.dbf' size 5m;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_system_7n23lzdq_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_sysaux_7n23m1hf_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_undotbs_7n23m36g_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_test_7n263x4d_.dbf
/u02/oracle/datafiles/TESTDB/datafile/o1_mf_test_7n27b1gj_.dbf
/u02/oracle/userdata-01.dbf

6 rows selected.
 

So, you can enable the OMF (Oracle Managed Files) feature, but not using it :-). 

 

 

QUESTION:

       Is that possible to disable the Oracle Managed Files (OMF) feature ?

       The response is : YES, it's possible to disable the OMF (Oracle Managed Files) feature.

 

      To disable the Oracle Managed Files (OMF) feature you have to :

      - add the CONTROL_FILES parameter in the pfile/ spfile;

      - remove  DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameter from pfile/ spfile;

      - restart the database.

      NOTE:

      - Could be nice to rename the files as well not to have OMF names in a non-OMF database.

 

 

In-Oracle.com  -> Oracle DBA

-> Oracle Managed Files (OMF)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.