Oracle9i introduces 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,
as shown in Listing A..
| Listing A |
 |
|
|
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, as shown in Listing B.
| Listing B |
|
|
|
|
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 ;
|
The output is shown in Listing C.
| Listing C |
|
|
|
|
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.
Listing D
presents the format mask that OMF uses when
creating new data files.
| Listing D |
|
|
|
|
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.
| Listing E |
|
|
|
|
FILE_NAME TABLESPACE BYTES
-------------------------------------------------- --------------- ------------
C:\ORACLE\ORADATA\DIOGENES\ORA_TEST_YNJ2K200.DBF TEST 104,857,600
|
Listing E 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 Oracle9i 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 command in
Listing F.
| Listing F |
|
|
|
|
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 (Figure A).
| Figure A |
 |
| The location of the Oracle
alert log |
When you check the last few lines of the alert log,
you’ll see that Oracle9i has logged the OMF
operations, and you have a full audit train of the
change, as shown in Listing G.
| Listing G |
|
|
|
|
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
Oracle9i 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 in Listing H.
| Listing H |
|
|
|
|
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, as shown in Listing I
| Listing I |
|
|
|
|
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 Oracle9i 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.
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
|
|
|
|
