1 2 Previous Next 28 Replies Latest reply on Jul 17, 2013 11:55 PM by Dizwell

    OMF And Non OMF files

    PVM

      Hi All,

       

      Can we use OMF and Non OMF files together in oracle database?

       

      Thanks,

      Mani

        • 1. Re: OMF And Non OMF files

          No

           

           

          ----------

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: OMF And Non OMF files
            PVM

            Hi Bakker,

             

            Thanks for your response...db_create_online_log_dest_1 is set to +DATA_EXA1 in RAC database. My concern is can i add members to each redo log file to another operating system location like ALTER DATABASE ADD LOGFILE MEMBER '<OS location>' to group <no>;

             

            or do i need to first set db_create_online_log_dest_2 to an operating system location and then add members to that.

             

            Thanks,

            Mani

            • 3. Re: OMF And Non OMF files
              EdStevens

              sybrand_b wrote:

               

              No

               

               

              ----------

              Sybrand Bakker

              Senior Oracle DBA

               

              Really?

               

              This from my 'default' sandbox system ... Oracle 11.2.0.1 on OL 5.6 under VBox.  This system was just pretty much 'out of the box, all defaults'

               

              oracle:orcl$ sqlplus system

               

              SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 15 14:38:02 2013

               

              Copyright (c) 1982, 2009, Oracle.  All rights reserved.

               

              Enter password:

               

              Connected to:

              Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

              With the Automatic Storage Management option

               

              SQL> select file_name from dba_data_files order by 1;

               

              FILE_NAME

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

              +DATA/orcl/datafile/sysaux.257.806162021

              +DATA/orcl/datafile/system.256.806162017

              +DATA/orcl/datafile/undotbs1.258.806162023

              +DATA/orcl/datafile/users.259.806162025

               

              SQL> CREATE SMALLFILE TABLESPACE "FUBAR"

              DATAFILE '/u01/app/oracle/oradata/orcl/fubar_01.dbf'

              SIZE 100M

              LOGGING

              EXTENT MANAGEMENT LOCAL

              SEGMENT SPACE MANAGEMENT AUTO

              ;  2    3    4    5    6    7

               

              Tablespace created.

               

              SQL> select file_name from dba_data_files order by 1;

               

              FILE_NAME

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

              +DATA/orcl/datafile/sysaux.257.806162021

              +DATA/orcl/datafile/system.256.806162017

              +DATA/orcl/datafile/undotbs1.258.806162023

              +DATA/orcl/datafile/users.259.806162025

              /u01/app/oracle/oradata/orcl/fubar_01.dbf

               

              SQL>

               

              Now, whether or not it is advisable is a different question ...

              • 4. Re: OMF And Non OMF files
                EdStevens

                Manikandan wrote:

                 

                Hi Bakker,

                 

                Thanks for your response...db_create_online_log_dest_1 is set to +DATA_EXA1 in RAC database. My concern is can i add members to each redo log file to another operating system location like ALTER DATABASE ADD LOGFILE MEMBER '<OS location>' to group <no>;

                 

                Thanks,

                Mani

                 

                Can you?  What is the worst thing that could happen if you tried?

                And why would you want to have redo logs (or any other grouping of files) across both ASM and OS file systems?  What problem are you trying to solve?

                • 5. Re: OMF And Non OMF files
                  PVM

                  Hi Stevens,

                   

                  Some testing is going on in Exadata machine. Its default 11g R2 RAC with ASM. Developers want to have the redo logs multiplexed having copies out side of ASM box.

                  db_create_online_log_dest_1 is set to +DATA_EXA1. can i add members to each redo log file to another operating system location like ALTER DATABASE ADD LOGFILE MEMBER '<OS location>' to group <no>;

                   

                  or do i need to first set db_create_online_log_dest_2 to an operating system location and then add members to that.

                   

                  Thanks

                  • 6. Re: OMF And Non OMF files
                    EdStevens

                    Manikandan wrote:

                     

                    Hi Stevens,

                     

                    Some testing is going on in Exadata machine. Its default 11g R2 RAC with ASM. Developers want to have the redo logs multiplexed having copies out side of ASM box.

                     

                    Why are developers dictating the physical characteristics of the database?  Who is the DBA here?

                     

                     

                    db_create_online_log_dest_1 is set to +DATA_EXA1. can i add members to each redo log file to another operating system location like ALTER DATABASE ADD LOGFILE MEMBER '<OS location>' to group <no>;

                     

                    or do i need to first set db_create_online_log_dest_2 to an operating system location and then add members to that.

                     

                    Offhand, I don't know.  I've never had reason to look into it.  What does your own reading of the init parms 'db_create_online_log_dest_n' suggest?

                     

                     

                    Thanks

                    • 8. Re: OMF And Non OMF files
                      Dizwell

                      Wrong. Yes, of course you can mix the two: the datafile clause is still valid when DB_CREATE_FILE_DEST is set, and has been from the day that OMF was invented.

                      • 9. Re: OMF And Non OMF files
                        Dizwell

                        Ooops. I should have read to the end of the thread. Sorry. Ed beat me to it.

                        • 10. Re: OMF And Non OMF files
                          Dude!

                          Oracle OMF was introduced with 9i. It is meant to simply datafile creation as it puts files where they belong.

                           

                           

                          You can certainly mix OMF and non OMF files. If you do not want to use OFM, you simply need to specify the full path and name of the datafile.

                           

                           

                          The instance parameter DB_CREATE_ONLINE_LOG_DEST_n define the locations of multiplexed redo log files using OMF. So when you create a redo log without specifying the datafile it automatically creates logfile mirrors.

                           

                           

                          I think to create redo logs files multiplexed between ASM and an OS file system does not make sense. It is maybe useful for DBA admins who do not want to learn about ASM and don't want to use it. It seems many think that ASM is just some kind of a software RAID, which it is not. ASM is not RAID at all. DBA's need to study ASM.

                          • 11. Re: OMF And Non OMF files
                            PVM

                            HI Stevens,

                             

                            Benefits of Using OMF

                            You can create tablespaces with OMF-based files. You can also specify that your online redo log files and your control files are in the OMF format.

                            OMF files offer several advantages over user-managed files:

                            • Oracle automatically creates and deletes OMF files.

                            • You don’t have to worry about coming up with a naming convention for the files.

                            You can have a mix of traditional files and OMF files in the same database.


                            I got this thread from http://www.keplerinfotech.com/blog/2012/06/15/omf-oracle-managed-files/

                            This is what i want to know. I will try it out.

                             

                            Thanks,

                            Mani

                            • 12. Re: OMF And Non OMF files
                              PVM

                              Hi,

                               

                              I am here not to argue for anything. I just wanted to clarify some of my doubts. Thats it. As you suggested, I will learn.

                               

                              Thanks,

                              Mani

                              • 13. Re: OMF And Non OMF files
                                Dizwell

                                I think some people are confusing OMF and ASM. They're two completely different things. OMF just means Oracle will auto-name and auto-size datafiles and redo logs if you let it. It can be used on perfectly traditional file systems:

                                 

                                SQL> select name from v$datafile;

                                 

                                NAME
                                --------------------------------------------------------------------------------
                                /u01/app/oracle/oradata/orcl/system01.dbf
                                /u01/app/oracle/oradata/orcl/example01.dbf
                                /u01/app/oracle/oradata/orcl/sysaux01.dbf
                                /u01/app/oracle/oradata/orcl/undotbs01.dbf
                                /u01/app/oracle/oradata/orcl/users01.dbf

                                 

                                SQL> alter system set db_create_file_dest='/home/oracle/oradata';  <===OMF set to ordinary file system

                                System altered.

                                 

                                SQL> create tablespace TB1;

                                Tablespace created.

                                 

                                SQL> select name from v$datafile;

                                 

                                NAME
                                --------------------------------------------------------------------------------
                                /u01/app/oracle/oradata/orcl/system01.dbf
                                /u01/app/oracle/oradata/orcl/example01.dbf
                                /u01/app/oracle/oradata/orcl/sysaux01.dbf
                                /u01/app/oracle/oradata/orcl/undotbs01.dbf
                                /home/oracle/oradata/ORCL/datafile/o1_mf_tb1_8y90o65c_.dbf    <==OMF file on ordrinary file system
                                /u01/app/oracle/oradata/orcl/users01.dbf

                                 

                                It's switched on by setting the various DB_CREATE... parameters, but even if it's set on, you can override it:

                                 

                                SQL> show parameter db_create_file_dest

                                NAME                                 TYPE        VALUE
                                ------------------------------------ ----------- ------------------------------
                                db_create_file_dest                  string      /home/oracle/oradata    <===so, OMF is "on"

                                 

                                SQL> create tablespace tb2 datafile '/u01/app/oracle/oradata/orcl/tb2.dbf' size 5m;

                                Tablespace created.

                                 

                                SQL> select name from v$datafile where name like '%tb2%';

                                 

                                NAME
                                ------------------------------------------------------------
                                /u01/app/oracle/oradata/orcl/tb2.dbf            <=== And yet we have a non-OMF datafile

                                 

                                And you can even partially override it, by specifying a size clause, for example, but leaving the datafile clause blank :

                                 

                                SQL> create tablespace tb3;

                                Tablespace created.

                                 

                                SQL> select file_name, bytes/1024/1024 from dba_data_files
                                  2  where file_name like '%tb3%';

                                 

                                FILE_NAME                                                              BYTES/1024/1024
                                ---------------------------------------------------------------------- ---------------
                                /home/oracle/oradata/ORCL/datafile/o1_mf_tb3_8y9125bc_.dbf                         100   <==OMF datafiles are 100MB by default


                                SQL> create tablespace t4 datafile size 5M;

                                Tablespace created.

                                 

                                SQL> select file_name, bytes/1024/1024 from dba_data_files
                                  2  where file_name like '%t4%';

                                 

                                FILE_NAME                                                              BYTES/1024/1024
                                ---------------------------------------------------------------------- ---------------
                                /home/oracle/oradata/ORCL/datafile/o1_mf_t4_8y917xkc_.dbf                            5  <===but the SIZE can be specified, even though it's still OMF

                                 

                                Oh, and ASM isn't a single point of failure (any more than your traditional RAID controller is): you can cluster your ASM instances. Sure, if one fails, it takes down the instance(s) on that node, but the other nodes in the RAC survive perfectly well.

                                • 14. Re: OMF And Non OMF files
                                  Dizwell

                                  Well, it's not just some kind of software RAID, true enough.

                                   

                                  But one part of what it does is the software equivalent of RAID (i.e., when redundancy is not set to internal, it does duplicate or triplicate your data onto multiple devices, providing the sort of hardware fault tolerance that RAID was partly invented for.)

                                   

                                  "ASM is not RAID at all"

                                   

                                  Well, I think that's going too far, as well. "Redundant" -ASM provides redundancy. "Array" -ASM stripes across as many hard disks as you care to throw at it, so it's array-like, at least. "Disks" -yup, it does involve disks and storage and so on. "Inexpensive" it certainly isn't... but three out of four ain't bad !

                                  1 2 Previous Next