OMF (Oracle Managed Files) Usage for Datafiles [ID 137482.1]

PURPOSE
This document explains the usage of Oracle Managed Files (OMF files) under
different configurations.


SCOPE & APPLICATION
Users of Oracle Managed Files.


OMF (Oracle Managed Files) Usage for Datafiles:
===============================================

You decide to manage your datafiles with OMF.

*** *************************************************
*** Homogeneous Test : tablespace with OMF files only
*** *************************************************

1. In init.ora:

DB_CREATE_FILE_DEST=/oradata/V900SUP

2. Create a tablespace using the OMF syntax:

SQL> create tablespace omf_domi datafile size 100k;
Tablespace created.

SQL> select name from v$datafile;

NAME
------------------------------------------
...
/oradata/V900SUP/ora_omf_domi_xcpsj3tv.dbf


3. Add a datafile to the tablespace using the OMF syntax:

SQL> select name from v$datafile;
NAME
------------------------------------------
...
/oradata/V900SUP/ora_omf_domi_xcpsj3tv.dbf
/oradata/V900SUP/ora_omf_domi_xcpspo63.dbf

SQL> alter tablespace omf_domi add datafile '/oradata/V900SUP/ora_do03.dbf'
2 size 100k;
alter tablespace omf_domi add datafile '/oradata/V900SUP/ora_do03.dbf'
*
ERROR at line 1:
ORA-01276: Cannot add a file with an Oracle Managed Files file name.


4. Add another datafile naming it explicitly:

SQL> alter tablespace omf_domi add datafile '/oradata/V900SUP/do03.dbf';
Tablespace altered.


!!! Be cautious: it is strongly recommended NOT to explicitly name the OMF
!!! files. It is likely not to work in the future.


5. Drop the tablespace and check that all datafiles are removed on the
operating system:

SQL> drop tablespace omf_domi;
Tablespace dropped.

SQL> select name from v$datafile where name like 'ora_omf%';
no rows selected.

In alert.log file:

Deleted Oracle managed file /oradata/V900SUP/ora_omf_domi_xcpsj3tv.dbf
Deleted Oracle managed file /oradata/V900SUP/ora_omf_domi_xcpspo63.dbf
Fri Mar 23 16:09:36 2001

Completed: drop tablespace omf_domi

$ ls -l /oradata/V900SUP/ora_omf*
/oradata/V900SUP/ora_omf*: No such file or directory

$ ls -l /oradata/V900SUP
-rw-r----- 1 ora901 dba 114688 juin 15 11:30 do03.dbf


*** ***********************************************************************
*** Homogeneous test : tablespace with OMF located in different directories
*** ***********************************************************************

SQL> create tablespace omf_domi;
Tablespace created.

SQL> alter system set db_create_file_dest='/oradata/V900SUP/TEST';
System altered.

SQL> alter tablespace omf_domi add datafile;
Tablespace altered.

SQL> select name from v$datafile;

NAME
-----------------------------------------------
...
/oradata/V900SUP/ora_omf_domi_xcpvst5q.dbf
/oradata/V900SUP/TEST/ora_omf_domi_xcpvxk0l.dbf

SQL> drop tablespace omf_domi;
Tablespace dropped.

In alert log file:

Fri Mar 23 16:46:29 2001
Deleted Oracle managed file /oradata/V900SUP/ora_omf_domi_xcpvst5q.dbf
Deleted Oracle managed file /oradata/V900SUP/TEST/ora_omf_domi_xcpvxk0l.dbf

$ ls -l /oradata/V900SUP/ora_omf*
/oradata/V900SUP/ora_omf*: No such file or directory
$ ls -l /oradata/V900SUP/TEST/ora_omf*
/oradata/V900SUP/TEST/ora_omf*: No such file or directory


All OMF have been correctly removed on the OS.


*** **********************************************************************
*** Heterogeneous test: OMF and non-OMF datafiles within the same database
*** **********************************************************************

1. You leave in the init.ora the DB_CREATE_FILE_DEST parameter.
You create a new tablespace with datafiles located in another directory than
the OMF directory:

SQL> create tablespace omf_domi datafile
2 '/ora/ora9i/admin/V900SUP/bdump/omf_domi01.dbf' size 100k;

Tablespace created.

2. Drop the tablespace and check that the datafile is still remaining on the
operating system.

SQL> drop tablespace omf_domi;
Tablespace dropped.

$ ls /ora/ora9i/admin/V900SUP/bdump
omf_domi01.dbf


*** *************************************************************
*** Heterogeneous test: tablespace with OMF and non-OMF datafiles
*** *************************************************************

SQL> create tablespace omf_domi datafile size 100k;
Tablespace created.

SQL> alter tablespace omf_domi
2 add datafile '/ora/ora9i/admin/V900SUP/bdump/omf_domi02.dbf'
3 size 100k;

Tablespace altered.

SQL> drop tablespace omf_domi;
Tablespace dropped.

$ ls /oradata/V900SUP
=> no more files

$ ls /ora/ora9i/admin/V900SUP/bdump/omf*
/ora/ora9i/admin/V900SUP/bdump/omf_domi02.dbf


It is preferrable in this case to use the following command that drops the
tablespace and related datafiles (OMF and non-OMF):

SQL> drop tablespace omf_domi including contents and datafiles;
Tablespace dropped.

In the alert.log you will see:

drop tablespace omf_domi including contents and datafiles
Thu Mar 29 17:09:22 2001
Deleted Oracle managed file /ora/ora9i/admin/V900SUP/bdump/omf_domi01.dbf
Deleted file /ora/ora9i/admin/V900SUP/bdump/omf_domi02.dbf
Completed: drop tablespace omf_domi including contents and datafiles


Explanation:
============

In RDBMS version 9.0.1, we identify OMF files when they are located in the
DB_CREATE_FILE_DEST directory and if the name respects the 'ora_' prefix
and the '.dbf' extension.

Only datafiles created in the DB_CREATE_FILE_DEST directory are automatically
removed from the operating system when dropping a tablespace without the
INCLUDING CONTENTS AND DATAFILES clause. Even if the value of
DB_CREATE_FILE_DEST has changed, the different locations of OMF files are taken
into account.


!!! Please Note:
!!! Be cautious: it is strongly recommended not to explicitly name the OMF
!!! files. It will probably not work successfully in the future.


IMPORTANT NOTE:
===============

Beginning in 9.0.1.2, the naming conventions for OMF files have changed. Please
reference Note: 159888.1 for details of the changes.

Search Words:
=============

ORA-1276

[@more@]