Managing Tablespaces : #3

Managing Tablespaces with Oracle Managed Files


Oracle-managed files eliminates the need for you to directly manage the operating system files in an Oracle database. You specify operations in terms of database objects rather than file names. The database internally uses standard file system interfaces to create and delete files as needed for the following database structures:

• Tablespaces
• Redo log files
• Control files
• Archived logs
• Block change tracking files
• Flashback logs
• RMAN backups

A database can have a mixture of Oracle-managed and unmanaged files. The file system directory specified by either of these parameters must already exist; the database does not create it. The
directory must also have permissions for the database to create the files in it.
The example shows that after DB_CREATE_FILE_DEST is set, the DATAFILE clause can be
omitted from a CREATE TABLESPACE statement. The data file is created in the location
specified by DB_CREATE_FILE_DEST. When you create a tablespace as shown, default values are assigned to all parameters.

when you use an OMF approach, the DB_CREATE_FILE_DEST parameter in the parameter file specifies that datafiles are to be created and defines their location.  The DATAFILE clause to name files is not used because filenames are automatically generated by the Oracle Server, for example, /u01/oracle/ducat/ducat_tbs01.dbf’.
Oracle-managed files have a specific naming format. For example, on Linux and Unix based systems the following format is used:
<destination_prefix>/o1_mf_%t_%u_.dbf
Do not rename an Oracle-managed file. The database identifies an Oracle-managed file based on its name. If you rename the file, the database is no longer able to recognize it as an Oraclemanaged file and will not manage the file accordingly.

Note: By default, ASM uses OMF files, but if you specify an alias name for an ASM data file at tablespace creation time or when adding an ASM data file to an existing tablespace, then that file will not be OMF.
You can also use the ALTER SYSTEM command to dynamically set this parameter in the SPFILE parameter file.

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata/ducat/';

Additional tablespaces are specified with the CREATE TABLESPACE command shown here that specifies not the datafile name, but the datafile size.  You can also add datafiles with the ALTER TABLESPACE command.

CREATE TABLESPACE ducat_data DATAFILE SIZE 100M;
ALTER TABLESPACE ducat_data ADD DATAFILE;

example:
SQL> show parameter DB_CREATE_FILE_DEST
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
SQL>
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/ducat/omf' scope=both;
System altered.
SQL> show parameter DB_CREATE_FILE_DEST
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle/oradata/ducat/
                                                 omf
SQL> create tablespace testomf;
Tablespace created.
SQL>

[oracle@localhost omf]$ pwd

/u01/app/oracle/oradata/ducat/omf

[oracle@localhost omf]$ ls -lrt

total 4

drwxr-x--- 3 oracle oinstall 4096 Jan 11 05:00 DUCAT

[oracle@localhost omf]$

[oracle@localhost omf]$ ls -lrt

total 4

drwxr-x--- 3 oracle oinstall 4096 Jan 11 05:00 DUCAT

[oracle@localhost omf]$

[oracle@localhost omf]$

[oracle@localhost omf]$

[oracle@localhost omf]$ cd DUCAT/

[oracle@localhost DUCAT]$ ls -lrt

total 4

drwxr-x--- 2 oracle oinstall 4096 Jan 11 05:00 datafile

[oracle@localhost DUCAT]$ cd datafile/

[oracle@localhost datafile]$ ls -lrt

total 102408

-rw-r----- 1 oracle oinstall 104865792 Jan 11 05:00 o1_mf_testomf_f5f8jkxh_.dbf

[oracle@localhost datafile]$

[oracle@localhost datafile]$ pwd

/u01/app/oracle/oradata/ducat/omf/DUCAT/datafile

[oracle@localhost datafile]$

By default, Oracle-managed data files, including those for the SYSTEM and SYSAUX tablespaces, are 100MB and autoextensible.

Setting the DB_CREATE_ONLINE_LOG_DEST_n parameter prevents log files and control files from being located with datafiles – this will reduce I/O contention.
When OMF tablespaces are dropped, their associated datafiles are also deleted at the operating system level.