Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Tablespace creation is a snap with Oracle Managed Files

Oracle Tips by Burleson Consulting

September 5, 2002

Oracle9i first introduced a new feature that simplifies tablespace creation. This new feature, Oracle Managed Files (OMF), makes life easier for Oracle DBAs by removing the tedium from creating and managing Oracle data files.

Before OMF, Oracle DBAs had to take several manual steps before adding a data file. A simple example of these steps follows, but note that you must know the location of the directory that contains the Oracle data files:

create tablespace
   users_02
add
   'c:\oracle\oradata\diogenes\users02.dbf'
size
   20m ;

In this example, you must know the following information to create a new tablespace:

The proper file location
The proper filename
The proper file size

Prior to OMF, you needed to execute queries to get the filenames and file locations, interrogating the dba_data_files view to find the appropriate location for a new data file:

SQL> select file_name from dba_data_files;

 FILE_NAME

------------------------------------------------
 C:\ORACLE\ORADATA\DIOGENES\SYSTEM01.DBF
 C:\ORACLE\ORADATA\DIOGENES\UNDOTBS01.DBF
 C:\ORACLE\ORADATA\DIOGENES\CWMLITE01.DBF
 C:\ORACLE\ORADATA\DIOGENES\DRSYS01.DBF
 C:\ORACLE\ORADATA\DIOGENES\EXAMPLE01.DBF
 C:\ORACLE\ORADATA\DIOGENES\INDX01.DBF
 C:\ORACLE\ORADATA\DIOGENES\TOOLS01.DBF
 C:\ORACLE\ORADATA\DIOGENES\USERS01.DBF
 C:\ORACLE\ORADATA\DIOGENES\16K_TS.DBF

Using the output of the previous query, the c:\Oracle\Oradata\Diogenes directory is the proper location to add a new Oracle data file.

Viewing tablespace information was also cumbersome. You had to write a query to join dba_tablespaces with dba_data_files to get the file sizes:

column file_name  format a40
column tablespace format a15
column bytes      format 999,999,999

select
   file_name,
   t.tablespace_name tablespace,
   bytes
from
   dba_data_files  d,
   dba_tablespaces t
where
   t.tablespace_name = d.tablespace_name ;

FILE_NAME TABLESPACE BYTES

---------------------------------------- --------------- ------------

C:\ORACLE\ORADATA\DIOGENES\SYSTEM01.DBF SYSTEM 340,787,200

C:\ORACLE\ORADATA\DIOGENES\UNDOTBS01.DBF UNDOTBS 209,715,200

C:\ORACLE\ORADATA\DIOGENES\CWMLITE01.DBF CWMLITE 20,971,520

C:\ORACLE\ORADATA\DIOGENES\DRSYS01.DBF DRSYS 20,971,520

C:\ORACLE\ORADATA\DIOGENES\EXAMPLE01.DBF EXAMPLE 159,907,840

C:\ORACLE\ORADATA\DIOGENES\INDX01.DBF INDX 26,214,400

C:\ORACLE\ORADATA\DIOGENES\TOOLS01.DBF TOOLS 10,485,760

C:\ORACLE\ORADATA\DIOGENES\USERS01.DBF USERS 26,214,400

C:\ORACLE\ORADATA\DIOGENES\16K_TS.DBF TS_16K 10,485,760

Oracle Corporation recognized that this was a lot of work just to add a data file, so it developed OMF to reduce the complexity involved in specifying all the detailed file information. Some of the benefits of OMF are:

Easier Oracle file management—All files are placed into the proper OS directory.
Easier third-party application integration—Third-party apps don't have to be aware of OS-specific environments.
Reduction of Oracle file management errors—No risk of human error.
Enforcement of Optimal Flexible Architecture (OFA) standards—OMF will comply with the OFA standards for filename and file locations.
Default file sizes—OMF allows files to have standard, uniform sizes.

File size and naming standards

Before OMF appeared, Oracle DBAs could create data files with any name they chose. While the file suffix was normally .dbf, the Oracle DBA was free to create any type of filename desired. For example, the following is a silly but legitimate Oracle command:

create tablespace
   new_ts
datafile
   c:\windows\Program Files\autoexec.bat'
size
   300m;

As we can see from this example, allowing the developer to choose filenames and locations can have disastrous effects. When using OMF, files typically have a default size of 100 MB and are named using a format mask for the filename.

Here is the format mask that OMF uses when creating new data files.

u% is a unique 8 digit code,
g% is the logfile group number,
%t is the tablespace name:

Controlfiles        ora_%u.ctl
Redo Log Files      ora_%g_%u.log
Datafiles           ora_%t_%u.dbf
Temporary Datafiles ora_%t_%u.tmp

With OMF, tablespace creation syntax is simplified, and it becomes easy to allocate a new tablespace:

SQL> create tablespace new_ts;

Tablespace created.

Now that the benefits of OMF are apparent, I'll examine the process for installing and using it. To use OMF, you must set the db_create_file_dest parameter. Once this is set, OMF is installed and tablespace creation becomes super easy:

SQL> alter system set db_create_file_dest='c:\oracle\oradata\diogenes\;

System altered.

SQL> create tablespace test;

Tablespace created.

Now I'll look at the filename, directory name, and size for the file that was created as a result of this command in Listing E.

FILE_NAME                                          TABLESPACE             BYTES
-------------------------------------------------- --------------- ------------
C:\ORACLE\ORADATA\DIOGENES\ORA_TEST_YNJ2K200.DBF   TEST             104,857,600

The listing shows that OMF created the file as follows:

File location—C:\Oracle\Oradata\Diogenes
Filename—Ora_test_ynj2k200.dbf
File size—100 MB

Note that the OMF default file size is 100 MB, and the file size can't be overridden at the command line. You can specify the file size only if you bypass OMF and specify the filename and location in the data file clause.

Oracle enhanced the Oracle alert log to display messages about tablespace creation and data file creation. To see the alert log, you must go to the background_dump_destination directory. You get the location of this directory by issuing the show parameter command.

SQL> show parameter background_dump

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      C:\oracle\admin\diogenes\bdump

Now that you know the location of the alert log, go to that directory and issue a dir command to see the alert log file, named DiogenesALRT.LOG

When you check the last few lines of the alert log, you'll see that Oracle has logged the OMF operations, and you have a full audit train of the change, as shown below:

Wed Jul 31 12:02:30 2002

ALTER SYSTEM SET db_create_file_dest='c:\oracle\oradata\diogenes' SCOPE=BOTH;

 Wed Jul 31 12:02:42 2002
 create tablespace test
 Wed Jul 31 12:02:47 2002
 Created Oracle managed file C:\ORACLE\ORADATA\DIOGENES\ORA_TEST_YNJ2K200.DBF
 Completed: create tablespace test
 Wed Jul 31 12:08:26 2002
 drop tablespace test
 Wed Jul 31 12:08:26 2002
 Deleted Oracle managed file C:\ORACLE\ORADATA\DIOGENES\ORA_TEST_YNJ2K200.DBF
 Completed: drop tablespace test

Using OMF with online redo logs

Oracle also lets you use OMF with online redo log files. This feature is especially useful because it removes the tedium from multiplexing and sizing the redo logs. You do this by setting the db_create_online_log_dest_1 through db_create_online_log_dest_5 parameters. The one-through-five notation allows you to specify up to five multiplexed copies of the online redo log file.

Because the redo logs are allocated at database creation time, these parameters must be set in the init.ora file prior to creating the database. When multiplexing, you also need to segregate the online redo logs onto separate disks as protection against disk failure. In this UNIX example, the mount points u01, u02, u03, and u04 all map to different disk spindles.

Using OMF for the redo logs requires several parameters. Here's a sample init.ora file for Oracle9i OMF for redo logs:

db_create_online_log_dest_1 = ‘/u01/oracle/oradata/diogenes'
db_create_online_log_dest_2 = ‘/u02/oracle/oradata/diogenes'
db_create_online_log_dest_3 = ‘/u03/oracle/oradata/diogenes'
db_create_online_log_dest_4 = ‘/u04/oracle/oradata/diogenes'

Using OMF for redo logs greatly simplifies the syntax you need to create a new database. Before OMF, you had to specify the size and location of the redo logs at database creation time, as shown below:

create database
   "diogenes"
    maxinstances 1
    maxlogfiles  16
    maxloghistory 226
    maxlogmembers 2
    maxdatafiles 30
    noarchivelog
    character set "US7ASCII"
    SET TIME_ZONE = 'PST';
    datafile
        'c:\oracle\oradata\system01.dbf'   size 246M
    logfile
      group 1 'c:\oracle\oradata\log01.dbf'      size 50K,
      group 2 'c:\oracle\oradata\log02.dbf'      size 50K,
      group 3 'c:\oracle\oradata\log03.dbf'       size 50K

Now, OMF takes care of the details, and database creation is simple:

create database
   "diogenes"
    maxinstances 1
    maxlogfiles  16
    maxloghistory 226
    maxlogmembers 2
    maxdatafiles 30
    noarchivelog
    character set "US7ASCII"
    SET TIME_ZONE = 'PST';
    datafile
        'c:\oracle\oradata\system01.dbf'   size 246M
    logfile
      group 1,
      group 2,
      group 3

Who doesn't like OMF?

OMF is quite popular in large Oracle shops that handle hundreds of tablespaces and data files. OMF is also popular for vendor-based applications because vendor install scripts can be sent to all Oracle customers, no matter what their specific file configuration. The downside to OMF is that seasoned database professionals don't like to use uniform file sizes and obtuse filenames.

��