Configuring OMF in Oracle 10g

Configuring Oracle 10g for OMF

To use OMF you must set the db_create_file_dest parameter. One set, 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, let’s look at the result of this command by querying the dba_tablespaces view.

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


Here we see that OMF created the file as follows:

* File location – c:\oracle\oradata\diogenes

* File name – ora_test_ynj2k200.dbf

* File size – 100 megabytes

The 100 megabyte file size cannot be overridden at the command line, and you can only specify the file size if you also specify the file name and location in the datafile clause.

We also see that Oracle has enhanced the Oracle 10g alert log to display messages about our operation. To see the alert log, we must go to our background_dump_destination directory

SQL> show parameter background_dump

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

Now that we know the location of the alert log, we go to that directory, issue a “dir” command, and we can see the alert log file, named diogenesALRT.LOG (Figure 1)

Figure 1 – Locating your Oracle alert log

Now, when we check the last few lines of the alert log we see that Oracle 10g has logged our OMF operations:

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 10g has also introduced the ability to use OMF with online redo log files. This feature is especially useful and removes the tedium from multiplexing and sizing the redo logs. This is done 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 Oracle database creation time, these parameters must be set in the init.ora file prior to creating the database. When multiplexing, we also want 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.

Sample init.ora file

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’

Now Oracle database creation syntax is greatly simplified. Before, we had to specify the size and location of the redo logs at Oracle database creation time:

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:

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
;

Conclusions about OMF

In sum, OMF is great for vendor-based application whereby scripts can be sent to all Oracle customers without regard for their specific file configuration. The downside to OMF is that seasoned Oracle database professional to not like to uniform file sizes and obtuse file names.



For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.