Oracle OMF(Oracle Managed Files ) 说明
一.官网说明
Using Oracle Managed Files
http://docs.oracle.com/cd/E11882_01/server.112/e25494/omf.htm#ADMIN003
1.1 What Are Oracle Managed Files?
官网链接：
http://docs.oracle.com/cd/E11882_01/server.112/e25494/omf001.htm#i1006122
Using Oracle Managed Files simplifies the administration of anOracle Database. Oracle Managed Files eliminate the need for you, the DBA, todirectly manage the operating system files that comprise an Oracle Database.With Oracle Managed Files, you specify file system directories in which thedatabase automatically creates, names, and manages files at the database objectlevel. For example, you need only specify that you want to create a tablespace;you do not need to specify the name and path of the tablespace's datafile withthe DATAFILE clause. This feature works well with a logical volumemanager (LVM).
--使用OMF可以简化Oracle 数据库的管理，通过OMF，可以指定filesystem 目录，之后就可以在这个目录下创建和管理数据库。 比如创建表空间时，不需要指定datafile的名称，默认会将数据文件创建到这个目录下面。
The databaseinternally uses standard file system interfaces to create and delete files asneeded for the following database structures:
--Oracle 默认使用标准的文件系统接口来创建和删除如下的数据库结构：
（1）Tablespaces
（2）Redo log files
（3）Control files
（4）Archived logs
（5）Block change tracking files
（6）Flashback logs
（7）RMAN backups
Throughinitialization parameters, you specify the file system directory to be used fora particular type of file. The database then ensures that a unique file, anOracle managed file, is created and deleted when no longer needed.
--在初始化参数里指定特殊类型文件的文件系统目录，这样就可以保证文件的唯一性。
This featuredoes not affect the creation or naming of administrative files such as tracefiles, audit files, alert logs, and core files.
1.2 WhoCan Use Oracle Managed Files?
Oracle Managed Files are most useful forthe following types of databases:
--OMF 在如下情况使用最多：
（1）Databases that are supported by thefollowing:
1）A logical volume manager thatsupports striping/RAID and dynamically extensible logical volumes
2）A file system that provides large,extensible files
（2）Low end or test databases
The OracleManaged Files feature is not intended to ease administration of systems thatuse raw disks. This feature provides better integration with operating systemfunctionality for disk space allocation. Since there is no operating systemsupport for allocation of raw disks (it is done manually), this feature cannothelp. However, because Oracle Managed Files require that you use the operatingsystem file system (unlike raw disks), you lose control over how files are laidout on the disks and thus, you lose some I/O tuning ability.
--OMF 没有简化raw disks的管理，OMF 提供了一个整体的磁盘空间分配系统。因为OMF需要使用OS 文件系统，所以可以丢失了一些对磁盘的控制，比如具体的文件存放那个磁盘，I/O性能调整的。
1.3 WhatIs a Logical Volume Manager? –什么是LVM
A logical volume manager (LVM) is a software packageavailable with most operating systems. Sometimes it is called a logical diskmanager (LDM). It allows pieces of multiple physical disks to be combined intoa single contiguous address space that appears as one disk to higher layers ofsoftware. An LVM can make the logical volume have better capacity, performance,reliability, and availability characteristics than any of the underlyingphysical disks. It uses techniques such as mirroring, striping, concatenation,and RAID 5 to implement these characteristics.
--LVM 在大多数系统下都可以用，LVM 也称LDM。LVM 可以让逻辑卷有更好的容量，性能和可靠性。LVM 可以使用mirror，strip，concatenation和RAID技术。
Some LVMs allowthe characteristics of a logical volume to be changed after it is created, evenwhile it is in use. The volume may be resized or mirrored, or it may berelocated to different physical disks.
--一些LVM 允许在其创建之后改变其特性，甚至在使用时，如resize或mirror或relocated到不同的物理磁盘。
1.4 WhatIs a File System? –什么是文件系统
A file system is a data structure built inside a contiguousdisk address space. A file manager (FM) is a software package that manipulatesfile systems, but it is sometimes called the file system. All operating systemshave file managers. The primary task of a file manager is to allocate anddeallocate disk space into files within a file system.
--文件系统是一个连续磁盘空间上的一个数据结构，FM 软件可以管理多个文件系统，File manager 有时也称为file system。 所有的操作系统都有file managers。 File manager 的主要任务是分配和收集file system 上的磁盘空间。
A file systemallows the disk space to be allocated to a large number of files. Each file ismade to appear as a contiguous address space to applications such as OracleDatabase. The files may not actually be contiguous within the disk space of thefile system. Files can be created, read, written, resized, and deleted. Eachfile has a name associated with it that is used to refer to the file.
--file system 允许磁盘空间分配给大量的文件。每个文件看起来在一个连续的磁盘空间上，但是实际上并不是一个连续的空间。 文件可以被创建，读取，resize，和 删除.
A file system iscommonly built on top of a logical volume constructed by an LVM. Thus all thefiles in a particular file system have the same performance, reliability, andavailability characteristics inherited from the underlying logical volume. Afile system is a single pool of storage that is shared by all the files in thefile system. If a file system is out of space, then none of the files in thatfile system can grow. Space available in one file system does not affect spacein another file system. However some LVM/FM combinations allow space to beadded or removed from a file system.
An operatingsystem can support multiple file systems. Multiple file systems are constructedto give different storage characteristics to different files as well as todivide the available disk space into pools that do not affect each other.
1.5 Benefitsof Using Oracle Managed Files
Consider the following benefits ofusing Oracle Managed Files:
--使用OMF有如下好处：
（1）They make the administration of thedatabase easier.
--简化数据库管理
There is no needto invent filenames and define specific storage requirements. A consistent setof rules is used to name all relevant files. The file system defines the characteristicsof the storage and the pool where it is allocated.
（2）They reduce corruption caused byadministrators specifying the wrong file.
--降低管理员指定错误文件的可能性。
Each Oraclemanaged file and filename is unique. Using the same file in two differentdatabases is a common mistake that can cause very large down times and loss ofcommitted transactions. Using two different names that refer to the same fileis another mistake that causes major corruptions.
（3）They reduce wasted disk spaceconsumed by obsolete files.
--减少空间浪费
Oracle Databaseautomatically removes old Oracle Managed Files when they are no longer needed.Much disk space is wasted in large systems simply because no one is sure if aparticular file is still required. This also simplifies the administrative taskof removing files that are no longer required on disk and prevents the mistakeof deleting the wrong file.
--DB 会自动移除长时间不用的旧OMF文件。
（4）They simplify creation of test anddevelopment databases.
--简化测试库的创建
You can minimizethe time spent making decisions regarding file structure and naming, and youhave fewer file management tasks. You can focus better on meeting the actualrequirements of your test or development database.
（5）Oracle Managed Files makedevelopment of portable third-party tools easier.
Oracle ManagedFiles eliminate the need to put operating system specific file names in SQLscripts.
1.6 OracleManaged Files and Existing Functionality
Using OracleManaged Files does not eliminate any existing functionality. Existing databasesare able to operate as they always have. New files can be created as managedfiles while old ones are administered in the old way. Thus, a database can havea mixture of Oracle managed and unmanaged files.
二. Enabling the Creation and Use of Oracle Managed Files
The followingtable lists the initialization parameters that enable the use of Oracle ManagedFiles.
启动OMF的初始化参数如下表：
The file systemdirectories specified by these parameters must already exist; the database doesnot create them. The directory must also have permissions to allow the databaseto create the files in it.
The defaultlocation is used whenever a location is not explicitly specified for theoperation creating the file. The database creates the filename, and a file thuscreated is an Oracle managed file.
Both of theseinitialization parameters are dynamic, and can be set using the ALTERSYSTEM or ALTER SESSION statement.
--这些参数可以使用alter system 或者 alter session 动态修改。
2.1 Settingthe DB_CREATE_FILE_DEST Initialization Parameter
Includethe DB_CREATE_FILE_DEST initialization parameter in yourinitialization parameter file to identify the default location for the databaseserver to create:
-- DB_CREATE_FILE_DEST 指定如下参数的默认位置：
（1）Datafiles
（2）Tempfiles
（3）Redo log files
（4）Control files
（5）Block change tracking files
You specify thename of a file system directory that becomes the default location for thecreation of the operating system files for these entities. The followingexample sets /u01/app/oracle/oradata as the default directory to usewhen creating Oracle Managed Files:
DB_CREATE_FILE_DEST= '/u01/app/oracle/oradata'
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
----------------------------------------------- -------
db_create_file_dest string
--默认为空
2.2 Settingthe DB_RECOVERY_FILE_DEST Parameter
Includethe DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parametersin your initialization parameter file to identify the default location for theFast Recovery Area.
--这2个参数指定默认FRA的位置，FRA 可以包含如下文件：
The Fast Recovery Area contains:
（1）Redo log files or multiplexedcopies of redo log files
（2）Control files or multiplexed copiesof control files
（3）RMAN backups (datafile copies,control file copies, backup pieces, control file autobackups)
（4）Archived logs
（5）Flashback logs
可以通过如下SQL 查看保存的文件类型：
SQL> select file_type fromv$flash_recovery_area_usage;
更多内容参考：
http://blog.csdn.net/tianlesoftware/article/details/5808279
http://blog.csdn.net/tianlesoftware/article/details/5806756
You specify thename of file system directory that becomes the default location for creation ofthe operating system files for these entities. For example:
DB_RECOVERY_FILE_DEST ='/u01/app/oracle/fast_recovery_area'
DB_RECOVERY_FILE_DEST_SIZE = 20G
2.3 Setting the DB_CREATE_ONLINE_LOG_DEST_n InitializationParameters
Includethe DB_CREATE_ONLINE_LOG_DEST_n initialization parameters in yourinitialization parameter file to identify the default locations for thedatabase server to create:
（1）Redo log files
（2）Control files
You specify thename of a file system directory or Oracle ASM disk group that becomes thedefault location for the creation of the files for these entities. You canspecify up to five multiplexed locations.
--可以指定文件系统目录或者ASM disk group来存放这些文件， 最多可以指定5个路径。
For the creationof redo log files and control files only, this parameter overrides any defaultlocation specified inthe DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST initializationparameters.
If you do notspecify a DB_CREATE_FILE_DEST parameter, but you do specifythe DB_CREATE_ONLINE_LOG_DEST_n parameter, then only redo log filesand control files can be created as Oracle Managed Files.
--如果没有指定DB_CREATE_FILE_DEST 参数，那么就需要指定DB_CREATE_ONLINE_LOG_DEST_n 参数，并且只有redolog 和 control file 可以在OMF 里创建。
It is recommended that you specify at leasttwo parameters. For example:
--推荐至少指定2个参数：
DB_CREATE_ONLINE_LOG_DEST_1= '/u02/oradata'
DB_CREATE_ONLINE_LOG_DEST_2= '/u03/oradata'
This allowsmultiplexing, which provides greater fault-tolerance for the redo log andcontrol file if one of the destinations fails.
SQL> show parameter db_create_
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> show parameter db_recovery
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_recovery_file_dest string D:\app\Administrator\flash_recovery_area
db_recovery_file_dest_size big integer 1300M
三.Creating Oracle Managed Files
If you have metany of the following conditions, then Oracle Database creates Oracle ManagedFiles for you, as appropriate, when no file specification is given in thecreate operation:
（1）You haveincluded any of the DB_CREATE_FILE_DEST, DB_REDOVERY_FILE_DEST,or DB_CREATE_ONLINE_LOG_DEST_n initialization parameters in yourinitialization parameter file.
（2）You haveissued the ALTER SYSTEM statement to dynamically set anyof DB_RECOVERY_FILE_DEST, DB_CREATE_FILE_DEST, or DB_CREATE_ONLINE_LOG_DEST_n initializationparameters
（3）You haveissued the ALTER SESSION statement to dynamically set any ofthe DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST,or DB_CREATE_ONLINE_LOG_DEST_n initialization parameters.
--配置以上参数之后才启用OMF。
If a statementthat creates an Oracle managed file finds an error or does not complete due tosome failure, then any Oracle Managed Files created by the statement areautomatically deleted as part of the recovery of the error or failure. However,because of the large number of potential errors that can occur with filesystems and storage subsystems, there can be situations where you must manuallyremove the files using operating system commands.
--如果启用了OMF，在创建文件失败后，在OMF recover error 或者failure时时会自动删除这部分创建失败的文件。但是因为一些大量的一些潜在的错误，在一些情况还还是需要手工的从OS上移除这些文件。
The following topics are discussed in thissection:
（1）HowOracle Managed Files Are Named
（2）CreatingOracle Managed Files at Database Creation
（3）CreatingDatafiles for Tablespaces Using Oracle Managed Files
（4）CreatingTempfiles for Temporary Tablespaces Using Oracle Managed Files
（5）CreatingControl Files Using Oracle Managed Files
（6）CreatingRedo Log Files Using Oracle Managed Files
（7）CreatingArchived Logs Using Oracle Managed Files
关于手工使用createdatabase 创建数据库参考：
http://blog.csdn.net/tianlesoftware/article/details/4680230
3.1 How Oracle Managed Files Are Named
The filenames of Oracle Managed Filescomply with the Optimal Flexible Architecture (OFA) standard for file naming.
--Oracle 管理文件的名称通过OFA来实现。 这部分内容参考：
Oracle OFA（OptimalFlexible Architecture） 说明
http://blog.csdn.net/tianlesoftware/article/details/7086104
The assigned names are intended to meet thefollowing requirements:
--命名的相关约束：
（1）Database files are easilydistinguishable from all other files.
（2）Files of one database type areeasily distinguishable from other database types.
（3）Files are clearly associated withimportant attributes specific to the file type. For example, a datafile namemay include the tablespace name to allow for easy association of datafile totablespace, or an archived log name may include the thread, sequence, andcreation date.
No two OracleManaged Files are given the same name. The name that is used for creation of anOracle managed file is constructed from three sources:
--OMF的文件不会重名，OMF命名由如下三部分组成：
（1）The default creation location
（2）A file name template that is chosenbased on the type of the file. The template also depends on the operatingsystem platform and whether or not Oracle Automatic Storage Management is used.
（3）A unique string created by OracleDatabase or the operating system. This ensures that file creation does notdamage an existing file and that the file cannot be mistaken for some otherfile.
As a specificexample, filenames for Oracle Managed Files have the following format on aSolaris file system:
destination_prefix/o1_mf_%t_%u_.dbf
where:
--示例中的相关参数说明：
（1）destination_prefix is destination_location/db_unique_name/datafile
where:
1）destination_location is thelocation specified in DB_CREATE_FILE_DEST
2）db_unique_name is the globallyunique name (DB_UNIQUE_NAME initialization parameter) of the targetdatabase. If there is no DB_UNIQUE_NAME parameter, thenthe DB_NAME initialization parameter value is used.
（2）%t is the tablespace name.
（3）%u is an eight-character stringthat guarantees uniqueness
For example, assume the following parametersettings:
DB_CREATE_FILE_DEST = /u01/app/oracle/oradata
DB_UNIQUE_NAME = PAYROLL
Then an example datafile name would be:
/u01/app/oracle/oradata/PAYROLL/datafile/o1_mf_tbs1_2ixh90q_.dbf
Names for otherfile types are similar. Names on other platforms are also similar, subject tothe constraints of the naming rules of the platform.
The examples onthe following pages use Oracle managed file names as they might appear with aSolaris file system as an OMF destination.
Caution:
Do not rename anOracle managed file. The database identifies an Oracle managed file based onits name. If you rename the file, the database is no longer able to recognizeit as an Oracle managed file and will not manage the file accordingly.
--注意，不要对OMF的文件进行重命名，数据库根据OMF的名称来识别文件，如果修改了文件名，那么数据库就会不能识别到这个文件，也就不会管理这个文件。
3.2 CreatingOracle Managed Files at Database Creation
The actions ofthe CREATEDATABASE statement when using Oracle Managed Files are discussed in thissection.
Note:
The rules anddefaults in this section also apply to creating a database with DatabaseConfiguration Assistant (DBCA). With DBCA, you use a graphical interface toenable Oracle Managed Files and to specify file locations that correspond tothe initialization parameters described in this section.
--在DBCA 命令中也可以使用OMF，在界面上我们可以选择是否启用OMF，默认是禁用的。
3.2.1 SpecifyingControl Files at Database Creation
--创建数据库时指定控制文件位置
At databasecreation, the control file is created in the files specified bythe CONTROL_FILES initialization parameter. Ifthe CONTROL_FILES parameter is not set and at least one of theinitialization parameters required for the creation of Oracle Managed Files isset, then an Oracle managed control file is created in the default control filedestinations. In order of precedence, the default destination is defined asfollows:
--如果CONTROL_FILES参数没有设置，并且设置了至少一个OMF参数，那么控制文件就会使用OMF目录：
（1）One or morecontrol files as specified inthe DB_CREATE_ONLINE_LOG_DEST_n initialization parameter. The file inthe first directory is the primary control file.When DB_CREATE_ONLINE_LOG_DEST_n is specified, the database does notcreate a control file in DB_CREATE_FILE_DEST orin DB_RECOVERY_FILE_DEST (the Fast Recovery Area).
（2）If no valueis specified for DB_CREATE_ONLINE_LOG_DEST_n, but values are set for boththe DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST, then thedatabase creates one control file in each location. The location specifiedin DB_CREATE_FILE_DEST is the primary control file.
（3）If a value isspecified only for DB_CREATE_FILE_DEST, then the database creates onecontrol file in that location.
（4）If a value isspecified only for DB_RECOVERY_FILE_DEST, then the database creates onecontrol file in that location.
Ifthe CONTROL_FILES parameter is not set and none of theseinitialization parameters are set, then the Oracle Database default action isoperating system dependent. At least one copy of a control file is created inan operating system dependent default location. Any copies of control filescreated in this fashion are not Oracle Managed Files, and you must adda CONTROL_FILES initialization parameter to any initializationparameter file.
If the databasecreates an Oracle managed control file, and if there is a server parameterfile, then the database creates a CONTROL_FILES initializationparameter entry in the server parameter file. If there is no server parameterfile, then you must manually include a CONTROL_FILES initializationparameter entry in the text initialization parameter file.
3.2.2 Specifying Redo Log Files at Database Creation
--创建DB时指定redo log 的位置
The LOGFILE clauseis not required in the CREATE DATABASE statement, and omitting itprovides a simple means of creating Oracle managed redo log files. Ifthe LOGFILE clause is omitted, then redo log files are created in thedefault redo log file destinations.
--使用create database 命令创建DB时，logfile参数不是必须的。
In order of precedence, the defaultdestination is defined as follows:
（1）If eitherthe DB_CREATE_ONLINE_LOG_DEST_n is set, then the database creates alog file member in each directory specified, up to the value ofthe MAXLOGMEMBERS initialization parameter.
（2）Ifthe DB_CREATE_ONLINE_LOG_DEST_n parameter is not set, but boththe DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST initializationparameters are set, then the database creates one Oracle managed log file memberin each of those locations. The log file inthe DB_CREATE_FILE_DEST destination is the first member.
（3）If onlythe DB_CREATE_FILE_DEST initialization parameter is specified, thenthe database creates a log file member in that location.
（4）If only the DB_RECOVERY_FILE_DEST initializationparameter is specified, then the database creates a log file member in thatlocation.
The default size of an Oracle managed redolog file is 100 MB.
--在OMF 下，默认的redolog 是100M。
Optionally, youcan create Oracle managed redo log files, and override default attributes, byincluding the LOGFILE clause but omitting a filename. Redo log filesare created the same way, except for the following: If no filename is providedin the LOGFILE clause of CREATE DATABASE, and none of theinitialization parameters required for creating Oracle Managed Files areprovided, then the CREATE DATABASE statement fails.
3.2.3 Specifyingthe SYSTEM and SYSAUX Tablespace Datafiles at Database Creation
--创建DB 时指定SYSTEM 和 SYSAUX 表空间
The DATAFILE or SYSAUX DATAFILE clauseis not required in the CREATE DATABASE statement, and omitting itprovides a simple means of creating Oracle managed datafiles forthe SYSTEM and SYSAUXtablespaces.
--使用create database 命令时，datafile 参数不是必须的，如果忽略，就会使用OMF。
If the DATAFILE clause isomitted, then one of the following actions occurs:
（1）If DB_CREATE_FILE_DEST isset, then one Oracle managed datafile for the SYSTEM tablespace andanother for the SYSAUX tablespace are created inthe DB_CREATE_FILE_DEST directory.
（2）If DB_CREATE_FILE_DEST isnot set, then the database creates one SYSTEM andone SYSAUX tablespace datafile whose names and sizes are operatingsystem dependent. Any SYSTEM or SYSAUX tablespace datafilecreated in this manner is not an Oracle managed file.
By default,Oracle managed datafiles, including those forthe SYSTEM and SYSAUX tablespaces, are 100MB andautoextensible. When autoextension is required, the database extends thedatafile by its existing size or 100 MB, whichever is smaller. You can alsoexplicitly specify the autoextensible unit using the NEXT parameterof the STORAGE clause when you specify the datafile (ina CREATE or ALTER TABLESPACEoperation).
--默认情况下，OMF 的datafile 是100M，并且是autoextensible。在自动宽展时如果没有指定扩展的size，每次扩展也是100M。 也可以显示的指定扩展大小。
Optionally, youcan create an Oracle managed datafile forthe SYSTEM or SYSAUX tablespace and override defaultattributes. This is done by including the DATAFILE clause, omitting afilename, but specifying overriding attributes. When a filename is not suppliedand the DB_CREATE_FILE_DEST parameter is set, an Oracle manageddatafile for the SYSTEM or SYSAUX tablespace is created inthe DB_CREATE_FILE_DESTdirectory with the specified attributes beingoverridden. However, if a filename is not supplied andthe DB_CREATE_FILE_DEST parameter is not set, then the CREATEDATABASE statement fails.
--也可以指定datafile 参数来覆盖默认的属性。
When overridingthe default attributes of an Oracle managed file, if a SIZE value isspecified but no AUTOEXTEND clause is specified, then the datafileis not autoextensible.
--使用覆盖默认属性时，如果只指定了SIZE 大小，没有指定autoextent，那么datafile不会autoextensible。
3.2.4 Specifyingthe Undo Tablespace Datafile at Database Creation
--创建DB时指定undo datafile
The DATAFILE subclauseof the UNDO TABLESPACE clause is optional and a filename is notrequired in the file specification. If a filename is not supplied andthe DB_CREATE_FILE_DEST parameter is set, then an Oracle manageddatafile is created in the DB_CREATE_FILE_DEST directory.If DB_CREATE_FILE_DEST is not set, then the statement fails with asyntax error.
The UNDOTABLESPACE clause itself is optional in the CREATEDATABASE statement. If it is not supplied, and automatic undo managementmode is enabled (the default), then a default undo tablespacenamedSYS_UNDOTS is created and a 20 MB datafile that is autoextensible isallocated as follows:
--使用create database 创建DB时，undotablespace是可选的，如果没有指定，默认的undo 表空间名称是SYS_UNDOTS,默认大小是20M，并且默认是autuextensible。
（1）If DB_CREATE_FILE_DEST isset, then an Oracle managed datafile is created in the indicated directory.
（2）If DB_CREATE_FILE_DEST isnot set, then the datafile location is operating system specific.
3.2.5 Specifyingthe Default Temporary Tablespace Tempfile atDatabase Creation
--创建DB时指定默认临时表空间
The TEMPFILE subclauseis optional for the DEFAULT TEMPORARY TABLESPACE clause and afilename is not required in the file specification. If a filename is notsupplied and the DB_CREATE_FILE_DEST parameter set, then an Oraclemanaged tempfile is created in the DB_CREATE_FILE_DEST directory.If DB_CREATE_FILE_DEST is not set, then the CREATEDATABASE statement fails with a syntax error.
The DEFAULTTEMPORARY TABLESPACE clause itself is optional. If it is not specified,then no default temporary tablespace is created.
The default sizefor an Oracle managed tempfile is 100 MB and the file is autoextensible with anunlimited maximum size.
--默认OMF下的tempfile 是100M，并且是autoextensible，没有最大限制。
3.2.6 CREATEDATABASE Statement Using Oracle Managed Files: Examples
This sectioncontains examples of the CREATEDATABASE statement when using the Oracle Managed Files feature.
3.2.6.1 CREATE DATABASE: Example1
This example creates a database with thefollowing Oracle Managed Files:
（1）A SYSTEM tablespacedatafile in directory /u01/app/oracle/oradata that is autoextensibleup to an unlimited size.
（2）A SYSAUX tablespacedatafile in directory /u01/app/oracle/oradata that is autoextensibleup to an unlimited size. The tablespace is locally managed with automaticsegment-space management.
（3）Two online log groups with twomembers of 100 MB each, one each in /u02/oradata and /u03/oradata.
（4）If automatic undo management modeis enabled (the default), then an undo tablespace datafile indirectory /u01/app/oracle/oradata that is 20 MB and autoextensible upto an unlimited size. An undo tablespace named SYS_UNDOTS is created.
（5）If no CONTROL_FILES initializationparameter is specified, then two control files, one eachin /u02/oradata and /u03/oradata. The control filein /u02/oradata is the primary control file.
The followingparameter settings relating to Oracle Managed Files, are included in theinitialization parameter file:
--初始化参数文件配置
DB_CREATE_FILE_DEST ='/u01/app/oracle/oradata'
DB_CREATE_ONLINE_LOG_DEST_1 ='/u02/oradata'
DB_CREATE_ONLINE_LOG_DEST_2 ='/u03/oradata'
The following statementis issued at the SQL prompt:
SQL>CREATE DATABASE sample;
--根据初始化参数创建数据库
To create thedatabase with a locally managed SYSTEM tablespace, addthe EXTENT MANAGEMENT LOCAL clause:
SQL>CREATEDATABASE sample EXTENT MANAGEMENT LOCAL;
Without thisclause, the SYSTEM tablespace is dictionary managed. Oraclerecommends that you create a locally managed SYSTEM tablespace.
3.2.6.2 CREATE DATABASE: Example2
This example creates a database with thefollowing Oracle Managed Files:
（1）A SYSTEM tablespacedatafile in directory /u01/app/oracle/oradata that is autoextensibleup to an unlimited size.
（2）A SYSAUX tablespacedatafile in directory /u01/app/oracle/oradata that is autoextensibleup to an unlimited size. The tablespace is locally managed with automaticsegment-space management.
（3）Two redo log files of 100 MB eachin directory /u01/app/oracle/oradata. They are not multiplexed.
（4）An undo tablespace datafile indirectory /u01/app/oracle/oradata that is 20 MB and autoextensible upto an unlimited size. An undo tablespace named SYS_UNDOTS is created.
（5）A control filein /u01/app/oracle/oradata.
In this example, it is assumed that:
（1）No DB_CREATE_ONLINE_LOG_DEST_n initializationparameters are specified in the initialization parameter file.
（2）No CONTROL_FILES initializationparameter was specified in the initialization parameter file.
（3）Automatic undo management mode isenabled.
The following statements are issued at theSQL prompt:
SQL>ALTER SYSTEM SET DB_CREATE_FILE_DEST= '/u01/app/oracle/oradata';
SQL>CREATE DATABASE sample2 EXTENTMANAGEMENT LOCAL;
This databaseconfiguration is not recommended for a production database. The exampleillustrates how a very low-end database or simple test database can easily becreated. To better protect this database from failures, at least one morecontrol file should be created and the redo log should be multiplexed.
3.2.6.3 CREATE DATABASE: Example3
In this example,the file size for the Oracle Managed Files for the default temporary tablespaceand undo tablespace are specified. A database with the following Oracle ManagedFiles is created:
（1）A 400MB SYSTEM tablespace datafile indirectory /u01/app/oracle/oradata. Because SIZE is specified,the file in not autoextensible.
（2）A 200 MB SYSAUX tablespacedatafile indirectory /u01/app/oracle/oradata. Because SIZE isspecified, the file in not autoextensible. The tablespace is locally managedwith automatic segment-space management.
（3）Two redo log groups with twomembers of 100 MB each, one each indirectories /u02/oradata and /u03/oradata.
（4）For the default temporarytablespace dflt_ts, a 10 MB tempfile indirectory /u01/app/oracle/oradata. Because SIZE is specified,the file in not autoextensible.
（5）For the undotablespace undo_ts, a 100 MB datafile indirectory /u01/app/oracle/oradata. Because SIZE is specified,the file is not autoextensible.
（6）Ifno CONTROL_FILES initialization parameter was specified, then twocontrol files, one each indirectories /u02/oradata and /u03/oradata. The control filein /u02/oradata is the primary control file.
The following parameter settings areincluded in the initialization parameter file:
--初始化参数配置
DB_CREATE_FILE_DEST ='/u01/app/oracle/oradata'
DB_CREATE_ONLINE_LOG_DEST_1 ='/u02/oradata'
DB_CREATE_ONLINE_LOG_DEST_2 ='/u03/oradata'
The following statement is issued at theSQL prompt:
--创建数据库命令
CREATE DATABASE sample3
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 400M
SYSAUX DATAFILE SIZE 200M
DEFAULT TEMPORARY TABLESPACE dflt_tsTEMPFILE SIZE 10M
UNDO TABLESPACE undo_ts DATAFILE SIZE 100M;
3.3 Creating Datafiles for Tablespaces Using Oracle ManagedFiles
--使用OMF来创建表空间
The followingstatements that can create datafiles are relevant to thediscussion in this section:
（1）CREATE TABLESPACE
（2）CREATE UNDO TABLESPACE
（3）ALTER TABLESPACE ... ADD DATAFILE
When creating atablespace, either a permanent tablespace or an undo tablespace,the DATAFILE clause is optional. When you includethe DATAFILE clause the filename is optional. Ifthe DATAFILE clause or filename is not provided, then the followingrules apply:
（1）Ifthe DB_CREATE_FILE_DEST initialization parameter is specified, thenan Oracle managed datafile is created in the location specified by theparameter.
（2）Ifthe DB_CREATE_FILE_DEST initialization parameter is not specified,then the statement creating the datafile fails.
When you add adatafile to a tablespace with the ALTER TABLESPACE...ADDDATAFILE statement the filename is optional. If the filename is notspecified, then the same rules apply as discussed in the previous paragraph.
By default, anOracle managed datafile for a permanent tablespace is 100 MB and isautoextensible with an unlimited maximum size. However, if inyour DATAFILE clause you override these defaults by specifyingaSIZE value (and no AUTOEXTEND clause), then the datafileis not autoextensible.
--默认情况，永久表空间默认是100M，并且自动增长，不受限制。可以指定SIZE 来覆盖这个默认值。
3.3.1 CREATE TABLESPACE: Examples
The following are some examples of creatingtablespaces with Oracle Managed Files.
3.3.1.1 CREATE TABLESPACE:Example 1
The followingexample sets the default location for datafile creationsto /u01/oradata and then creates a tablespace tbs_1 with adatafile in that location. The datafile is 100 MB and is autoextensible with anunlimited maximum size.
SQL> ALTER SYSTEM SETDB_CREATE_FILE_DEST = '/u01/oradata';
SQL> CREATE TABLESPACE tbs_1;
3.3.1.2 CREATE TABLESPACE: Example 2
This examplecreates a tablespace named tbs_2 with a datafile in thedirectory /u01/oradata. The datafile initial size is 400 MB, and becausethe SIZE clause is specified, the datafile is not autoextensible.
The following parameter setting is includedin the initialization parameter file:
DB_CREATE_FILE_DEST = '/u01/oradata'
The following statement is issued at theSQL prompt:
SQL> CREATE TABLESPACE tbs_2 DATAFILESIZE 400M;
3.3.1.3 CREATETABLESPACE: Example 3
This examplecreates a tablespace named tbs_3 with an autoextensible datafile inthe directory /u01/oradata with a maximum size of 800 MB and aninitial size of 100 MB:
The following parameter setting is includedin the initialization parameter file:
DB_CREATE_FILE_DEST = '/u01/oradata'
The following statement is issued at theSQL prompt:
SQL> CREATE TABLESPACE tbs_3 DATAFILEAUTOEXTEND ON MAXSIZE 800M;
3.3.1.4 CREATE TABLESPACE:Example 4
The followingexample sets the default location for datafile creationsto /u01/oradata and then creates a tablespacenamed tbs_4 in that directory with two datafiles. Both datafiles havean initial size of 200 MB, and because a SIZE value is specified,they are not autoextensible
SQL> ALTER SYSTEM SETDB_CREATE_FILE_DEST = '/u01/oradata';
SQL> CREATE TABLESPACE tbs_4 DATAFILESIZE 200M, SIZE 200M;
3.3.2 CREATE UNDO TABLESPACE: Example
The followingexample creates an undo tablespace named undotbs_1 with a datafile inthe directory /u01/oradata. The datafile for the undo tablespace is 100 MBand is autoextensible with an unlimited maximum size.
The following parameter setting is includedin the initialization parameter file:
DB_CREATE_FILE_DEST = '/u01/oradata'
The following statement is issued at theSQL prompt:
SQL> CREATE UNDO TABLESPACE undotbs_1;
3.3.3 ALTER TABLESPACE: Example
This exampleadds an Oracle managed autoextensible datafile tothe tbs_1 tablespace. The datafile has an initial size of 100 MB anda maximum size of 800 MB.
The following parameter setting is includedin the initialization parameter file:
DB_CREATE_FILE_DEST = '/u01/oradata'
The following statement is entered at theSQL prompt:
SQL> ALTER TABLESPACE tbs_1 ADD DATAFILEAUTOEXTEND ON MAXSIZE 800M;
3.4 CreatingTempfiles for Temporary Tablespaces Using Oracle Managed Files
The followingstatements that create tempfiles are relevant to the discussion in this section:
（1）CREATE TEMPORARY TABLESPACE
（2）ALTER TABLESPACE ... ADD TEMPFILE
When creating atemporary tablespace the TEMPFILE clause is optional. If you includethe TEMPFILE clause, then the filename is optional. Ifthe TEMPFILE clause or filename is not provided, then the followingrules apply:
（1）Ifthe DB_CREATE_FILE_DEST initialization parameter is specified, thenan Oracle managed tempfile is created in the location specified by theparameter.
（2）Ifthe DB_CREATE_FILE_DEST initialization parameter is not specified,then the statement creating the tempfile fails.
When you add atempfile to a tablespace with the ALTER TABLESPACE...ADDTEMPFILE statement the filename is optional. If the filename is notspecified, then the same rules apply as discussed in the previous paragraph.
When overridingthe default attributes of an Oracle managed file, if a SIZE value isspecified but no AUTOEXTEND clause is specified, then the datafileis not autoextensible.
3.4.1 CREATE TEMPORARY TABLESPACE: Example
The followingexample sets the default location for datafile creationsto /u01/oradata and then creates a tablespacenamed temptbs_1 with a tempfile in that location. The tempfile is 100MB and is autoextensible with an unlimited maximum size.
--tempfile 默认是100M，自动扩展，不受大小限制
SQL> ALTER SYSTEM SETDB_CREATE_FILE_DEST = '/u01/oradata';
SQL> CREATE TEMPORARY TABLESPACEtemptbs_1;
3.4.2 ALTER TABLESPACE... ADD TEMPFILE: Example
The followingexample sets the default location for datafile creationsto /u03/oradata and then adds a tempfile in the default location to atablespace named temptbs_1. The tempfile initial size is 100 MB. It isautoextensible with an unlimited maximum size.
SQL> ALTER SYSTEM SETDB_CREATE_FILE_DEST = '/u03/oradata';
SQL> ALTER TABLESPACE TBS_1 ADDTEMPFILE;
3.5 Creating Control Files Using Oracle Managed Files
--使用OMF 创建控制文件
When you issuethe CREATE CONTROLFILE statement, a control file is created (or reused,if REUSE is specified) in the files specified bythe CONTROL_FILES initialization parameter. Ifthe CONTROL_FILESparameter is not set, then the control file is created inthe default control file destinations. The default destination is determinedaccording to the precedence documented in "SpecifyingControl Files at Database Creation".
If OracleDatabase creates an Oracle managed control file, and there is a serverparameter file, then the database createsa CONTROL_FILES initialization parameter for the server parameterfile. If there is no server parameter file, then you must createa CONTROL_FILES initialization parameter manually and include it inthe initialization parameter file.
If the datafilesin the database are Oracle Managed Files, then the database-generated filenamesfor the files must be supplied in the DATAFILE clause of thestatement.
If the redo logfiles are Oracle Managed Files, thenthe NORESETLOGS or RESETLOGS keyword determines what can besupplied in the LOGFILE clause:
（1）Ifthe NORESETLOGS keyword is used, then the database-generatedfilenames for the Oracle managed redo log files must be supplied inthe LOGFILE clause.
（2）Ifthe RESETLOGS keyword is used, then the redo log file names can besupplied as with the CREATE DATABASE statement. See "SpecifyingRedo Log Files at Database Creation".
The sectionsthat follow contain examples of using the CREATECONTROLFILE statement with Oracle Managed Files.
3.5.1 CREATECONTROLFILE Using NORESETLOGS Keyword: Example
Thefollowing CREATE CONTROLFILE statement is generated by an ALTERDATABASE BACKUP CONTROLFILE TO TRACE statement for a database with Oraclemanaged datafiles and redo log files:
--以下的脚本是使用alter database backup controlfile to trace 命令生成的。
CREATE CONTROLFILE
DATABASE sample
LOGFILE
GROUP 1 ('/u01/oradata/SAMPLE/onlinelog/o1_mf_1_o220rtt9_.log',
'/u02/oradata/SAMPLE/onlinelog/o1_mf_1_v2o0b2i3_.log')
SIZE 100M,
GROUP 2 ('/u01/oradata/SAMPLE/onlinelog/o1_mf_2_p22056iw_.log',
'/u02/oradata/SAMPLE/onlinelog/o1_mf_2_p02rcyg3_.log')
SIZE 100M
NORESETLOGS
DATAFILE '/u01/oradata/SAMPLE/datafile/o1_mf_system_xu34ybm2_.dbf'
SIZE 100M,
'/u01/oradata/SAMPLE/datafile/o1_mf_sysaux_aawbmz51_.dbf'
SIZE 100M,
'/u01/oradata/SAMPLE/datafile/o1_mf_sys_undo_apqbmz51_.dbf'
SIZE 100M
MAXLOGFILES 5
MAXLOGHISTORY 100
MAXDATAFILES 10
MAXINSTANCES 2
ARCHIVELOG;
3.5.2 CREATECONTROLFILE Using RESETLOGS Keyword: Example
The following isan example of a CREATE CONTROLFILE statement withthe RESETLOGS option. Some combinationof DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST,and DB_CREATE_ONLINE_LOG_DEST_n or must be set.
CREATE CONTROLFILE
DATABASE sample
RESETLOGS
DATAFILE '/u01/oradata/SAMPLE/datafile/o1_mf_system_aawbmz51_.dbf',
'/u01/oradata/SAMPLE/datafile/o1_mf_sysaux_axybmz51_.dbf',
'/u01/oradata/SAMPLE/datafile/o1_mf_sys_undo_azzbmz51_.dbf'
SIZE 100M
MAXLOGFILES 5
MAXLOGHISTORY 100
MAXDATAFILES 10
MAXINSTANCES 2
ARCHIVELOG;
Later, you mustissue the ALTER DATABASE OPEN RESETLOGS statement to re-create theredo log files. This is discussed in "Usingthe ALTER DATABASE OPEN RESETLOGS Statement". If the previous logfiles are Oracle Managed Files, then they are not deleted.
3.6 Creating Redo Log Files Using Oracle Managed Files
Redo log files are created at databasecreation time. They can also be created when you issue either of the followingstatements:
--Online redo log 在我们创建DB时会创建，在如下的两条命令下也会创建：
（1）ALTER DATABASE ADD LOGFILE
（2）ALTER DATABASE OPEN RESETLOGS
3.6.1 Usingthe ALTER DATABASE ADD LOGFILE Statement
The ALTER DATABASE ADD LOGFILE statement lets you lateradd a new group to your current redo log. The filename in the ADDLOGFILE clause is optional if you are using Oracle Managed Files. If afilename is not provided, then a redo log file is created in the default logfile destination. The default destination is determined according to theprecedence documented in "SpecifyingRedo Log Files at Database Creation".
If a filename isnot provided and you have not provided one of the initialization parametersrequired for creating Oracle Managed Files, then the statement returns anerror.
The default sizefor an Oracle managed log file is 100 MB.
--默认大小是100M。
You continue toadd and drop redo log file members by specifying complete filenames.
Adding New Redo Log Files:Example
The followingexample creates a log group with a member in /u01/oradata and anothermember in /u02/oradata. The size of each log file is 100 MB.
The following parameter settings areincluded in the initialization parameter file:
DB_CREATE_ONLINE_LOG_DEST_1 ='/u01/oradata'
DB_CREATE_ONLINE_LOG_DEST_2 ='/u02/oradata'
The following statement is issued at theSQL prompt:
SQL> ALTER DATABASE ADD LOGFILE;
3.6.2 Usingthe ALTER DATABASE OPEN RESETLOGS Statement
If youpreviously created a control file specifying RESETLOGS and either didnot specify filenames or specified nonexistent filenames, then the databasecreates redo log files for you when you issue the ALTER DATABASE OPENRESETLOGS statement. The rules for determining the directories in which tostore redo log files, when none are specified in the control file, are the sameas those discussed in "SpecifyingRedo Log Files at Database Creation".
3.7 Creating Archived Logs Using Oracle Managed Files
Archived logs are created inthe DB_RECOVERY_FILE_DEST location when:
（1）The ARC or LGWR backgroundprocess archives an online redo log or
（2）An ALTER SYSTEM ARHIVE LOGCURRENT statement is issued.
For example,assume that the following parameter settings are included in the initializationparameter file:
DB_RECOVERY_FILE_DEST_SIZE = 20G
DB_RECOVERY_FILE_DEST = '/u01/oradata'
LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
四. Operation of Oracle Managed Files
The filenamesof Oracle Managed Files are accepted in SQLstatements wherever a filename is used to identify an existing file. Thesefilenames, like other filenames, are stored in the control file and, if usingRecovery Manager (RMAN) for backup and recovery, in the RMAN catalog. They arevisible in all of the usual fixed and dynamic performance views that areavailable for monitoring datafiles and tempfiles (forexample, V$DATAFILE or DBA_DATA_FILES).
The following are some examples ofstatements using database-generated filenames:
SQL> ALTER DATABASE
2> RENAME FILE '/u01/oradata/mydb/datafile/o1_mf_tbs01_ziw3bopb_.dbf'
3> TO '/u01/oradata/mydb/tbs0101.dbf';
SQL> ALTER DATABASE
2> DROP LOGFILE '/u01/oradata/mydb/onlinelog/o1_mf_1_wo94n2xi_.log';
SQL> ALTER TABLE emp
2> ALLOCATE EXTENT
3> (DATAFILE '/u01/oradata/mydb/datafile/o1_mf_tbs1_2ixfh90q_.dbf');
You can backupand restore Oracle managed datafiles, tempfiles, and control files as you wouldcorresponding non Oracle Managed Files. Using database-generated filenames doesnot impact the use of logical backup files such as export files. This isparticularly important for tablespace point-in-time recovery (TSPITR) andtransportable tablespace export files.
There are somecases where Oracle Managed Files behave differently. These are discussed in thesections that follow.
4.1 DroppingDatafiles and Tempfiles
Unlike filesthat are not managed by the database, when an Oracle managed datafile ortempfile isdropped, the filename is removed from the control file and the file isautomatically deleted from the file system. The statements that delete OracleManaged Files when they are dropped are:
（1）DROP TABLESPACE
（2）ALTER DATABASE TEMPFILE ... DROP
--这一点file system 不同，如果使用file system，那么我们删除表空间，只是将表空间的新从控制文件中移除，物理上并不会移除对应的物理文件，但是如果使用OMF，删除表空间时，表空间和物理文件会一起删除。
You can also use these statements, whichalways delete files, Oracle managed or not:
（1）ALTER TABLESPACE ... DROP DATAFILE
（2）ALTER TABLESPACE ... DROP TEMPFILE
4.2 Dropping Redo Log Files
When an Oraclemanaged redo log file is dropped its Oracle ManagedFiles are deleted. You specify the group or members to be dropped. Thefollowing statements drop and delete redo log files:
（1）ALTER DATABASE DROP LOGFILE
（2）ALTER DATABASE DROP LOGFILE MEMBER
4.3 RenamingFiles
The following statements are used to rename files:
（1）ALTER DATABASE RENAME FILE
（2）ALTER TABLESPACE ... RENAME DATAFILE
These statementsdo not actually rename the files on the operating system, but rather, the namesin the control file are changed. If the old file is an Oracle managed file andit exists, then it is deleted. You must specify each filename using theconventions for filenames on your operating system when you issue thisstatement.
--注意，在OMF下的rename 并不会实际对物理文件进行rename，而是修改控制文件中记录。
4.4 Managing Standby Databases
The datafiles,control files, and redo log files in a standby database can be managed by thedatabase. This is independent of whether Oracle Managed Files are used on theprimary database.
When recovery ofa standby database encounters redo for the creation of a datafile, if thedatafile is an Oracle managed file, then the recovery process creates an emptyfile in the local default file system location. This allows the redo for thenew file to be applied immediately without any human intervention.
When recovery ofa standby database encounters redo for the deletion of a tablespace, it deletesany Oracle managed datafiles in the local file system. Note that this isindependent of the INCLUDING DATAFILESoption issued at the primarydatabase.
五. 小结
在创建instance的时候，我们可以选择存的类型，一般来说，我们都是选择File System。如下图：
一般我们都没有启动OMF，这个可以通过查询我们第二节中提到的相关参数值来确认。如果为空，即没有启动OMF，如：
SQL> show parameter db_create_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
在启动OMF 之后，我们创建表空间不需要写数据文件路径，名称和大小，默认创建的数据文件大小是100M，自动扩展，unlimited.
并且删除Tablespace 时，会连同操作系统级别的物理文件一起删除。
但是实际上对于一个系统，上线之前我们都有详细的规划，所以很少会采用OMF来管理，我们会手动的指定数据文件存放的具体位置，数据文件名称和数据文件的大小，而不是使用OMF，虽然OMF是管理上看起来比较简单，但是实际上管理起来也并见得省多少事。
总之，OMF 是Oracle 的一种技术，虽然很少使用它，但是要了解这种技术的存在。
-------------------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
Email: tianlesoftware@gmail.com
Skype: tianlesoftware
Blog: http://www.tianlesoftware.com
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook:http://www.facebook.com/tianlesoftware
-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823
DBA6 群：158654907 DBA7 群：172855474
